Existing MySQL database structure to Web2Py table definitions
Web2Py is a nice Python framework for rapid development of web-based applications. I’ve been using it quite a bit in the past two years, and got accostumed to the ease and quickness of development with it.
Recently I’ve been tasked with the rewrite of a big PHP-based website, with an existing huge database (60+ tables, millions of rows). Since the database seemed to be well-designed, I decided that I’d base my rewrite around it.
For this, I needed the whole database’s structure as a web2py model definition. Doing this by hand would be too tedious, so I decided to write a small Python script to do the work for me.
This is of course far from a full converter, as it doesn’t include all possible variable types that a column might have, only the ones I encountered in the database I processed, but I hope that this can save some time to someone who’s trying to do the same thing. Feel free to use my script as a starting point, or unmodified. Just don’t hold me reliable for any damage that might occur. 
It also ignores defaults and other extra info.
#!/usr/bin/python
import os, sys
import MySQLdb
#use your own settings here
conn = MySQLdb.connect (host = 'localhost',
user = 'root',
passwd = '',
db = 'shop')
cur = conn.cursor ()
cur.execute ("SHOW TABLES")
tables = cur.fetchall()
for t in tables:
print 'db.define_table ("%s",' % t
cur.execute ("DESCRIBE %s" % t)
cols = cur.fetchall ()
for c in cols:
n = c[0]
t = "UNKNOWN"
l = ''
if c[1][:3] == 'int' or c[1][:7] in [ "decimal", "tinyint", "mediumi", "bigint(" ] or c[1][:8] == "smallint": t = 'integer'
if c[1][:7] == 'varchar':
t = 'string'
l = c[1][8:-1]
if c[1][:4] == 'char':
t = 'string'
l = c[1][5:-1]
if c[1] in [ 'text', 'datetime', 'date', 'time' ]: t = c[1]
if c[1] in [ "mediumtext", "longtext" ]: t = "text"
if c[1] == 'float': t = "double"
if l:
print ' Field ("%s", "%s", length = %s),' % (n, t, l)
else:
print ' Field ("%s", "%s"),' % (n, t)
if t == "UNKNOWN":
print c[1] # to review unknown col types
print " migrate = False)"
cur.close ()
conn.close ()