Script to generate web2py schema (models) from mysql

#!/usr/bin/env python
# -*- coding: utf-8 -*-
#script to generate schemas from dbs
#by Alexandre Andrade alexandremasbr@gmail.com
#www.hipercenter.com

#place your config here
passwd="mypass"
user="myuser"
host="localhost"
db = 'mydb'
port='3306'

import MySQLdb
bd=MySQLdb.connect(host=host,user=user,passwd=passwd,db = db, use_unicode=True)

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()

Finally I have done 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 alexandremasbr@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

--
Atenciosamente

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