Great idea - I was not aware of pymysql. I had assumed web2py depended on an
external MySQLdb module, which I already had installed.
I changed the couple of lines to make it use gluon.contrib.pymysql, and it
appears to still work. Revised copy is attached.
Kevin
'''
Create the web2py code needed to access your mysql legacy db.
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
sys.path.append("..")
from gluon.contrib import pymysql
# 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 = pymysql.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()
