Hi folks, Today I made some enhancements to the web2py/scripts/extract_mysql_models.py script that converts existing MySQL tables into a DAL representation. I've attached a copy for anyone that might find it useful.
The major changes are as follows: - I got rid of the dependencies on the external mysql and mysqldump executables and now use MySQLdb directly. This simplifies the parsing a lot and makes the tool a little more self-contained. (Linux users probably have the mysql command line tools installed, but Windows users may not -- at least not in the PATH) - If your id column does not have the default "id" name, the script looks for an AUTO INCREMENT column and marks that with type "id" instead. - If you have a COMMENT specified in your MySQL table, it is added as a Python comment to the Field line - You are no longer restricted to localhost - you can read tables form remote databases - There are several new command line options, such as: --dalname (specify the variable name used for the DAL; e.g. [var] = DAL(...); [var].define_table(...)) --colcomments (add a comment to the end of each Field line showing the original column specification from CREATE TABLE... useful for nothing things like default values, original datatype, etc.) --commentalign (aligns comments to some number of spaces in the generated text) --notnull (detects NOT NULL and adds notnull=True to Field) --singlemigrate (use a single migrate=False in DAL() rather than in each define_table()) --verbose (adds logging to stderr so that if it crashes, you have a chance of figuring out which table failed) You can see the full usage by running the script without any arguments. Example use: extract_mysql_models.py --user kevin --password secret --host example.com --database mywiki --dalname wikidb --colcomments --singlemigrate > mywiki.py Generated output (only looks pretty with fixed-width formatting!): wikidb = DAL('mysql://kevin:sec...@example.com/mywiki', migrate=False) wikidb.define_table('comment', Field('id', 'id'), # (`id` int(11) NOT NULL AUTO_INCREMENT,) Field('page_id', 'integer'), # (`page_id` int(11) DEFAULT NULL,) Field('body', 'text'), # (`body` longtext,) Field('created_on', 'datetime'), # (`created_on` datetime DEFAULT NULL,) Field('created_by', 'integer'), # (`created_by` int(11) DEFAULT NULL,) ) wikidb.define_table('document', Field('id', 'id'), # (`id` int(11) NOT NULL AUTO_INCREMENT,) Field('page_id', 'integer'), # (`page_id` int(11) DEFAULT NULL,) Field('name', 'string'), # (`name` varchar(255) DEFAULT NULL,) Field('file', 'string'), # (`file` varchar(255) DEFAULT NULL,) Field('created_on', 'datetime'), # (`created_on` datetime DEFAULT NULL,) Field('created_by', 'integer'), # (`created_by` int(11) DEFAULT NULL,) ) wikidb.define_table('page', Field('id', 'id'), # (`id` int(11) NOT NULL AUTO_INCREMENT,) Field('body', 'text'), # (`body` longtext,) Field('created_on', 'datetime'), # (`created_on` datetime DEFAULT NULL,) Field('created_by', 'integer'), # (`created_by` int(11) DEFAULT NULL,) Field('title', 'string'), # (`title` varchar(255) DEFAULT NULL,) ) Massimo: feel free to integrate into the web2py source if you think it would be useful. Cheers, Kevin
''' Create the web2py code needed to access your mysql legacy db. This plugin needs the MySQLdb module Access your tables with: legacy_db(legacy_db.mytable.id>0).select() If the script crashes this is might be due to that fact that the data_type_map dictionary below is incomplete. Please complete it, improve it and continue. Created by Falko Krause, minor modifications by Massimo Di Pierro, Ron McOuat, and Kevin Ivarsen ''' from optparse import OptionParser import re import sys import MySQLdb # If true, write some diagnostic messages to stderr. Overridden by -v option SHOW_LOG_MESSAGES = False data_type_map = dict( varchar = 'string', int = 'integer', integer = 'integer', tinyint = 'integer', smallint = 'integer', mediumint = 'integer', bigint = 'integer', float = 'double', double = 'double', char = 'string', decimal = 'integer', date = 'date', #year = 'date', time = 'time', timestamp = 'datetime', datetime = 'datetime', binary = 'blob', blob = 'blob', tinyblob = 'blob', mediumblob = 'blob', longblob = 'blob', text = 'text', tinytext = 'text', mediumtext = 'text', longtext = 'text', enum = 'string', ) def main(): options = parse_args() print extract_mysql(options) log("") log("PROCESS COMPLETE!") def parse_args(): global SHOW_LOG_MESSAGES optparser = OptionParser(add_help_option=False) # -h for help interferes with -h for host optparser.add_option("-u", "--user", dest="user", action="store", type="string", default="root", help="MySQL username (default: root)") optparser.add_option("-p", "--password", dest="password", action="store", type="string", default="", help="MySQL password (default: none)") optparser.add_option("-h", "--host", dest="host", action="store", type="string", default="localhost", help="MySQL host (default: localhost)") optparser.add_option("-D", "--database", dest="schema", action="store", type="string", help="MySQL database schema to use") optparser.add_option("-a", "--dalname", dest="dalname", action="store", type="string", default="legacy_db", help="Assign DAL to this variable name (default: legacy_db)") optparser.add_option("-c", "--colcomments", dest="column_comments", action="store_true", help="Add column definition comments") optparser.add_option("-C", "--commentalign", dest="alignment", action="store", type="int", default=45, help="Align comments this many spaces from start of line") optparser.add_option("-n", "--notnull", dest="notnulls", action="store_true", help="Add notnull=True for NOT NULL columns") optparser.add_option("-v", "--verbose", dest="verbose", action="store_true", help="Verbose: show what is happening while tables are being parsed") optparser.add_option("-s", "--singlemigrate", dest="single_migrate", action="store_true", help="Use a single 'migrate=False' in the DAL() construtor instead of one in each define_table()") (options, args) = optparser.parse_args() if options.schema is None: optparser.print_help() print print "Examples:" print " Read the guestbook schema from localhost, user root, no password" print " extract_mysql_models.py -D guestbook " print print " Do the same, but on a different host with login and password" print " extract_mysql_models.py -u bob -p secret -h db.example.com -D guestbook" print print " Assign the DAL to variable 'db', use a global migrate=False, add comments" print " describing each column (using long-style args), and add notnull args:" print " extract_mysql_models.py --database=guestbook --dalname db \\" print " --singlemigrate --colcomments --notnull" print print "You must at least specify a schema to read from." print print "Generated database is written to stdout. Add \"> filename.py\"" print "to save to a file" print sys.exit(1) if options.verbose: SHOW_LOG_MESSAGES = True return options def log(text): if SHOW_LOG_MESSAGES: sys.stderr.write(text + "\n") def extract_mysql(opt): db = MySQLdb.connect(user=opt.user, passwd=opt.password, host=opt.host, db=opt.schema) curs = db.cursor() curs.execute("SHOW TABLES") tables = curs.fetchall() tables = [row[0] for row in tables] # Convert tuple of tuples to tuple of strings if opt.single_migrate: dal_migrate = ", migrate=False" else: dal_migrate = "" connection_string = "%s = DAL('mysql://%s:%s@localhost/%s'%s)" % ( opt.dalname, opt.user, opt.password, opt.schema, dal_migrate) legacy_db_table_web2py_code = [] for table_name in tables: log("Processing table '%s'" % table_name) curs.execute("SHOW CREATE TABLE " + table_name) sql_create_stmnt = curs.fetchone()[1] if 'CREATE' in sql_create_stmnt: # make sure returned statement looks sensible sql_lines = sql_create_stmnt.split('\n') # remove garbage lines from sql statement # n.b. this is a relic of old mysqldump method -- may no longer be # necessary. SHOW CREATE TABLE doesn't seem to produce any junk sql_lines = [x for x in sql_lines if not(x.startswith('--') or x.startswith('/*') or x =='')] log("Processing create statement:") for line in sql_lines: log(" " + line) # generate the web2py code from the create statement web2py_table_code = '' table_name = re.search('CREATE TABLE .(\S+). \(', sql_lines[0]).group(1) log("Extracted table name '%s'" % table_name) fields = [] for line in sql_lines[1:-1]: if re.search('PRIMARY', line) or re.search('KEY', line) or re.search(' ID', line) or line.startswith(')'): continue hit = re.search('(\S+)\s+(\S+)(,| )( .*)?', line) if hit != None: name, coltype = hit.group(1), hit.group(2) name = re.sub('`', '', name) coltype = re.sub(r'(\w+)\(.*',r'\1', coltype) daltype = data_type_map[coltype] # Is it a primary key with a non-default name? if daltype == 'integer' and line.find("AUTO_INCREMENT") != -1: daltype = 'id' # Extract a column comment, if any. # Also append the original column definition comment_index = line.find(" COMMENT ") if comment_index != -1: comment = line[comment_index+9:] comment = comment.strip().lstrip("'").rstrip(",").rstrip("'") else: comment = "" # Check nullable status if opt.notnulls and line.find(" NOT NULL") != -1: notnull = ", notnull=True" else: notnull = "" if opt.column_comments: comment += " (%s)" % line.strip() if comment != "": comment = " # " + comment.strip() web2py_table_code += "\n" field_definition = " Field('%s', '%s'%s)," % ( name, daltype, notnull) field_definition = field_definition.ljust(opt.alignment) field_definition += comment web2py_table_code += field_definition if opt.single_migrate: define_table_migrate = "" else: define_table_migrate = "migrate=False" web2py_table_code = "%s.define_table('%s', %s\n %s)" % ( opt.dalname, table_name, web2py_table_code, define_table_migrate) legacy_db_table_web2py_code.append(web2py_table_code) # write the legacy db to file legacy_db_web2py_code = connection_string+"\n\n" legacy_db_web2py_code += "\n\n".join(legacy_db_table_web2py_code) return legacy_db_web2py_code if __name__ == '__main__': main()