[web2py] Script to generate schema (models) from mysql

[Alexandre Andrade]

Some time ago I talk about to do it. 

Finally I have to do it. 

It can be improved to a form in appadmin, use the model to another db (postgresql, etc), and generate the file in /models.

You can see the code below: 

----------------------------------------------------------

#!/usr/bin/env python

# -*- coding: utf-8 -*-

#script to generate schemas from dbs

#by Alexandre Andrade alexand...@gmail.com

#www.hipercenter.com

#config it here

passwd="mypass"

user="myuser"

host="localhost"

db = 'mydb'

port='3306'

def query(bd,sql):

    bd.query(sql)

    r = bd.store_result()

    rows = r.fetch_row(maxrows=0,how=1)

    return rows

    

def get_tables():

    rows = query(bd,'show tables')

    tables=[]

    for row in rows:

        tables.append(row['Tables_in_'+db])

    return tables

    

#tables()

def get_fields(table):

    print table

    rows = query(bd,'show fields from '+ table)

    fields=[]

    for row in rows:

        #print row

        fields.append(row)

    return fields

    

def field_type(field):

    if field['Type'][0:7]=='varchar':

        tipo = ",'string'"

    elif field['Type'][:8]=='longtext':

        tipo = ",'text'"

    elif field['Type'][:3]=='int':

        tipo = ",'integer'"

    elif field['Type'][:4]=='date':

        tipo = ",'date'"

    elif field['Type'][:7]=='tinyint':

        tipo = ",'int'"

    elif field['Type'][:11]=='mediumtext':

        tipo = ",'text'"

    elif field['Type'][:4]=='char':

        tipo = ",'text'"

    else:

        print  field['Type'][0:10]

    return tipo

    

def primarykey(field):

    if field['Extra']=='auto_increment':

        pk = True

    else:

        pk = False

    return pk

def define_table(table):

    fields =  get_fields(table)

    result = []

    head = 'db = DAL("mysql://'+ user+ ':'+passwd+'@'+host+':'+port+'/'+db+'", pool_size=10)\r\r'

    line = "db.define_table('"+table+"'"

    result.append(line)  

    for field in fields:

        if primarykey(field) == True:

            pk =field['Field']

            #print pk

        tipo = field_type(field)

        line = "    Field('"+field['Field']+"'"+tipo+")"

        result.append(line)

        line

    try:

        line = "    primarykey=['"+pk+"']"

        result.append(line)

    except:

        pass

    out = ',\r'.join(result)

    output = head + out + '\r)'

    print output

    return output

def define_db():

    tables = get_tables()

    r = []

    for table in tables:

        r.append(define_table(table))

    result = '\r \r'.join(r)

    return result

r = define_db()

f = open('db_'+db+'.py', 'w')

f.write(r)

f.close()

-----------------------------------------------------------

--
Atenciosamente

--
=========================
Alexandre Andrade
Hipercenter.com

[mdipierro]

Thank you. I will not be able to look at this until tomorrow but I
will.

On May 20, 6:37 pm, Alexandre Andrade <alexandrema...@gmail.com>
wrote:

> Some time ago I talk about to do it.
>
> Finally I have to do it.
>
> It can be improved to a form in appadmin, use the model to another db
> (postgresql, etc), and generate the file in /models.
>
> You can see the code below:
>
> ----------------------------------------------------------
>
> #!/usr/bin/env python
> # -*- coding: utf-8 -*-
> #script to generate schemas from dbs

> #by Alexandre Andrade alexandrema...@gmail.com

>  importdb.py
> 3KViewDownload

[Nicol van der Merwe]

Nice! This is super excellent.

Just a simple question : will this work on SQL Server?

--
Old Gregg: Ever drink baileys from a shoe? Wanna go to a club where people wee on each other? I'm gonna hurt you. I like you. What do ya think of me?
Howard:I think your a nice..modern gentleman

[Jean Guy]

Hi,

Really nice!

I haven't try it, but I have to do the importation of postgresql database into a web2py model. I was just wondering if you can pointed the place that should be adapt for postgresql, I will change it and return the running code  for postgresql.

Thanks.

Jonhy

2010/5/20 Alexandre Andrade <alexand...@gmail.com>

[Nicol van der Merwe]

Oops! Stupid me. :) Just read the *entire* subject now "Script to generate schema (models) from mysql". So obviously - not for SQL Server.

*sigh* it's been a long day.

[Jean Guy]

Hi,

Could it be port to all database this way : http://blog.gmane.org/gmane.comp.python.sqlobject/month=20100101

SQLObject


??

Jonhy

2010/5/20 Alexandre Andrade <alexand...@gmail.com>

Some time ago I talk about to do it. 

[Alexandre Andrade]

To each new database, need to be adusted ( in a new script):

1 - the database driver (mysqlb is for mysql)
2 - maybe the sql sintax to select table and fields
3 - the treatment of result of select tables and field can need adjustments
4 - the field_type function have to be adjusted.

My first option to do it was postgresql, but I have to do for mysql first

2010/5/21 Jean Guy <jea...@gmail.com>

[Alexandre Andrade]

I will need to be adapted.

look my answer to Jonhy email.

2010/5/21 Nicol van der Merwe <asper...@gmail.com>

[Alexandre Andrade]

To each new database, need to be adusted ( in a new script):

1 - the database driver (mysqlb is for mysql)
2 - maybe the sql sintax to select table and fields
3 - the treatment of result of select tables and field can need adjustments
4 - the field_type function have to be adjusted.

My first option to do it was postgresql, but I have to do for mysql first.

Its possible because each database server has sql comands to describe their structure (databases, tables and fields).
So, its only a matter of study the result of this queries and treat them and generate the web2py schemas.

My script is like a logical model, to be adapted.

2010/5/21 Jean Guy <jea...@gmail.com>

[Alexandre Andrade]

I can adapt it  on demand to any database for just $100.
Obviously it will be returned to community too.

I usualy use only mysql, postresql, sqlite and now mongo (nosql).

This are the only one I will maybe do it sometime for free.

--
Atenciosamente

--
=========================
Alexandre Andrade
Hipercenter.com

2010/5/21 Nicol van der Merwe <asper...@gmail.com>

Nice! This is super excellent.

[Jean Guy]

I'm sorry I didn't have time to look at the script...

I try to look at it in the near future.

Jonhy

2010/5/22 Alexandre Andrade <alexand...@gmail.com>

[Álvaro Justen]

Hello Alexandre, nice work.
I did it in past for SQLite and made it integrated to DAL
I think we can union the implementations and try to get more databases
working with this code.

The email I sent to Massimo talking about my work (in the end of 2009)
is copied and my code is attached:

Note: I think we should discuss it in web2py-dev list.

----------------------

Hi Massimo,
Some weeks ago I had an idea: db.discover_tables() - a function that
will search DB and map it into web2py's DAL.
Today a user asked about it in mail list, so I decided to try: and my
first result is good, I think.

My implementation only have support to SQLite, but we can add all
databases with a little effort - I think you could help me in this
task.

I modified that simple 'news' app an gluon/sql.py. My modification is
NOT based on SVN, it is based in 1.62 rc1.
To test: modify db.py, uncomment define_table and comment
discover_tables. So, web2py will create tables. Enter in appadmin and
populate them. After that, comment all define_table and uncomment
discover_tables. So, enter in:

http://localhost:8000/news/appadmin/select/db?query=db.authors.id%3E0
http://localhost:8000/news/appadmin/select/db?query=db.news.id%3E0
http://localhost:8000/news/default/test
http://localhost:8000/news/default/create_code

In gluon/sql.py I added:
- method discover_tables in class SQLDB.
- some code in __init__ in class SQLDB.
- keys 'show_tables' and 'table_fields' in SQL_DIALECTS['sqlite'] ->
please add keys to other databases and test if you could.

I'm sure that this code can be optimized.

What do you think?

--
Álvaro Justen - Turicas
http://blog.justen.eng.br/
21 9898-0141

[mdipierro]

I will take a look tonight.

On 11 Giu, 14:45, Álvaro Justen <alv...@justen.eng.br> wrote:
> Hello Alexandre, nice work.
> I did it in past for SQLite and made it integrated to DAL
> I think we can union the implementations and try to get more databases
> working with this code.
>
> The email I sent to Massimo talking about my work (in the end of 2009)
> is copied and my code is attached:
>
> Note: I think we should discuss it in web2py-dev list.
>
> ----------------------
>
> Hi Massimo,
> Some weeks ago I had an idea: db.discover_tables() - a function that
> will search DB and map it into web2py's DAL.
> Today a user asked about it in mail list, so I decided to try: and my
> first result is good, I think.
>
> My implementation only have support to SQLite, but we can add all
> databases with a little effort - I think you could help me in this
> task.
>
> I modified that simple 'news' app an gluon/sql.py. My modification is
> NOT based on SVN, it is based in 1.62 rc1.
> To test: modify db.py, uncomment define_table and comment
> discover_tables. So, web2py will create tables. Enter in appadmin and
> populate them. After that, comment all define_table and uncomment
> discover_tables. So, enter in:
>

> http://localhost:8000/news/appadmin/select/db?query=db.authors.id%3E0http://localhost:8000/news/appadmin/select/db?query=db.news.id%3E0http://localhost:8000/news/default/testhttp://localhost:8000/news/default/create_code

>
> In gluon/sql.py I added:
>  - method discover_tables in class SQLDB.
>  - some code in __init__ in class SQLDB.
>  - keys 'show_tables' and 'table_fields' in SQL_DIALECTS['sqlite'] ->
> please add keys to other databases and test if you could.
>
> I'm sure that this code can be optimized.
>
> What do you think?
>
> --
> Álvaro Justen - Turicas
>  http://blog.justen.eng.br/
>  21 9898-0141
>

>  newcoolfeatureautodiscovertables.zip
> 101KVisualizzaScarica

[Alexandre Andrade]

Alvaro,

I didn't see this post before.

this can be a great feature and I will trye to adapt it to postgresql and mysql, at least.

if massimo already don't use it, with sqlite, postresql and mysql, it can worth the value.

2010/6/11 Álvaro Justen <alv...@justen.eng.br>

[mdipierro]

we also have

scripts/extract_mysql_models.py

Massimo

[Álvaro Justen [Turicas]]

On Sun, Oct 3, 2010 at 19:37, Alexandre Andrade
<alexand...@gmail.com> wrote:
> Alvaro,
>
> I didn't see this post before.
>
> this can be a great feature and I will trye to adapt it to postgresql and
> mysql, at least.
>
> if massimo already don't use it, with sqlite, postresql and mysql, it can
> worth the value.

Please post news about this. :-)

--
Álvaro Justen - @turicas
 http://blog.justen.eng.br/
 21 9898-0141

[KMax]

Hi

I was trying to create model/db.py for RequestTracker mysql schema, and was fail.

I found quite good plugin_legacymysql and fixed for my needs 

• added few types
• remove 'SET' lines same as remarks
• mysql data types without params (like text,)
• import form_factory from sqlhtml

Please find fixed py attached.

Thanks

[Massimo Di Pierro]

thanks

On Feb 9, 9:29 am, KMax <mkostri...@gmail.com> wrote:
> Hi
> I was trying to create model/db.py for RequestTracker mysql schema, and was
> fail.
> I found quite good plugin_legacymysql and fixed for my needs
>

>    - added few types
>    - remove 'SET' lines same as remarks
>    - mysql data types without params (like text,)
>    - import form_factory from sqlhtml

>
> Please find fixed py attached.
>
> Thanks
>

>  plugin_legacymysql.py
> 5KViewDownload