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