Skip to content


Subversion checkout URL

You can clone with
Download ZIP
Fetching contributors…

Cannot retrieve contributors at this time

108 lines (98 sloc) 4.433 kB
Create the web2py code needed to access your mysql legacy db.
To make this work all the legacy tables you want to access need to have an "id" field.
This plugin needs:
installed and globally available.
Under Windows you will probably need to add the mysql executable directory to the PATH variable,
you will also need to modify mysql to mysql.exe and mysqldump to mysqldump.exe below.
Just guessing here :)
Access your tables with:
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 and Ron McOuat
import subprocess
import re
import sys
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',
def mysql(database_name, username, password):
p = subprocess.Popen(['mysql',
'--user=%s' % username,
'--password=%s'% password,
'--execute=show tables;',
sql_showtables, stderr = p.communicate()
tables = [re.sub('\|\s+([^\|*])\s+.*', '\1', x) for x in sql_showtables.split()[1:]]
connection_string = "legacy_db = DAL('mysql://%s:%s@localhost/%s')"%(username, password, database_name)
legacy_db_table_web2py_code = []
for table_name in tables:
#get the sql create statement
p = subprocess.Popen(['mysqldump',
'--user=%s' % username,
'--password=%s' % password,
'--no-data', database_name,
table_name], stdin=subprocess.PIPE, stdout=subprocess.PIPE,stderr=subprocess.PIPE)
sql_create_stmnt,stderr = p.communicate()
if 'CREATE' in sql_create_stmnt:#check if the table exists
#remove garbage lines from sql statement
sql_lines = sql_create_stmnt.split('\n')
sql_lines = [x for x in sql_lines if not(x.startswith('--') or x.startswith('/*') or x =='')]
#generate the web2py code from the create statement
web2py_table_code = ''
table_name ='CREATE TABLE .(\S+). \(', sql_lines[0]).group(1)
fields = []
for line in sql_lines[1:-1]:
if'KEY', line) or'PRIMARY', line) or' ID', line) or line.startswith(')'):
hit ='(\S+)\s+(\S+)(,| )( .*)?', line)
if hit!=None:
name, d_type =,
d_type = re.sub(r'(\w+)\(.*',r'\1',d_type)
name = re.sub('`','',name)
web2py_table_code += "\n Field('%s','%s'),"%(name,data_type_map[d_type])
web2py_table_code = "legacy_db.define_table('%s',%s\n migrate=False)"%(table_name,web2py_table_code)
#write the legacy db to file
legacy_db_web2py_code = connection_string+"\n\n"
legacy_db_web2py_code += "\n\n#--------\n".join(legacy_db_table_web2py_code)
return legacy_db_web2py_code
regex = re.compile('(.*?):(.*?)@(.*)')
if len(sys.argv)<2 or not regex.match(sys.argv[1]):
print 'USAGE:\n\n username:password@data_basename\n\n'
m = regex.match(sys.argv[1])
print mysql(,,
Jump to Line
Something went wrong with that request. Please try again.