In [12]:

    

from sqlalchemy import create_engine
from connect import mysqlusername, mysqlpassword, mysqlserver, mysqldbname


    

In [14]:

    

mysqlserver = 'localhost'
mysqldbname = 'clinicaltrials2'
conn = create_engine('mysql://%s:%s@%s/%s' % (mysqlusername, mysqlpassword, mysqlserver, mysqldbname))


    

In [15]:

    

result = conn.execute('''
select 
    a.table_name, 
    a.column_name, 
    data_type, 
    character_maximum_length,
    column_key, 
    referenced_table_name, 
    referenced_column_name
from information_schema.columns a 
    join information_schema.tables b using (table_schema,table_name)
    left join information_schema.key_column_usage c 
using (table_schema,table_name,column_name)
where a.table_schema='clinicaltrials2' and b.table_type != 'VIEW'
order by a.table_name, a.ordinal_position
''')
info = list(result)


    

In [16]:

    

type_trans = {'bigint': 'BigInteger',
              'date': 'Date',
              'decimal': 'Float',
              'double': 'Float',
              'float': 'Float',
              'int': 'Integer',
              'text': 'Text',
              'varchar': 'String',
              'tinyint': 'Integer',
              'char': 'String',
              'datetime': 'DateTime'
              }


    

In [17]:

    

cur_tab = ''
cols = []

f = open('db_tables2.py','w')

f.write('from sqlalchemy import MetaData, Table, Column, BigInteger, Date, Float, Integer, Text, String, DateTime, ForeignKey\n')
f.write('\n')
f.write('metadata = MetaData()\n')
f.write('\n')

for i in range(len(info)):
    
    tab, col, typ, charlen, colkey, reftab, refcol = info[i]
    
    if tab != cur_tab:
        cur_tab = tab
        f.write("%s = Table('%s', metadata,\n" % (tab.replace('_',' ').title().replace(' ',''), tab))
    
    colname = col.lower()
    coltype = type_trans[typ]
    
    keystr = ')'
    if colkey == 'PRI':
        keystr = ', primary_key = True)'
    elif reftab:
        keystr = ", ForeignKey('%s.%s'))" % (reftab, refcol.lower())
    f.write("    Column('%s', %s%s,\n" % (colname, coltype, keystr))
    
    if i == len(info) - 1 or tab != info[i+1][0]:
        f.write(')\n')
        f.write('\n')

f.close()


    

In [ ]: