Importing an already existing MySQL database
Boriel
I'm rather new to web2py, but I think this script might be helpful to others.
I've created a little python script that will automatically dump on
the screen the creation of an already existing mysql database to be
ready to use with web2py.
So, for example, if you have already created a MySQL db called
"mybooks" and want to import it to web2py, just type:
mysqltoweb2py.py mybooks <user> <passwd>
And it will dump on the screen the orders needed to be copied in the db.py file.
This is the script:
============================= 8< == 8< == 8< ==============================
#!/usr/bin/env python
# -*- coding: utf-8 -*-
'''
mysqltoweb2py --> Import an already existing MySQL Database
into web2py.
Usage:
mysqltoweb2py.py <database> <userid> <passwd>
'''
import sys
import re
import MySQLdb
reFieldType = re.compile(r'([a-zA-Z]+)([(]\d+[)])?')
reLetter = re.compile('[a-zA-Z]')
datab = sys.argv[1]
user = sys.argv[2]
passwd = sys.argv[3]
db=MySQLdb.connect(host='localhost',user=user, passwd = passwd, db = datab)
def error_msg(msg):
''' This could be implemented as an exception
'''
sys.stderr.write("%s\n" % msg)
sys.exit(1)
def output_table(table):
cursor=db.cursor()
cursor.execute('show columns from `%s`' % table)
print "db.define_table('%s'," % table
# Extract table fields
for field in cursor.fetchall():
if field[0] == 'id':
continue # id field ignored
if not reLetter.match(field[0][0]):
error_msg("Error: field name [%s] in table [%s] does not
begin with a letter" % (field[0], table))
ftype = reFieldType.match(field[1])
if not ftype:
error_msg("Error: could not parse column type")
_type, _len = tuple(ftype.groups())
if _type == 'varchar':
_type = 'string'
elif _type in ('int', 'tinyint'):
_type = 'integer'
print "\tSQLField('%s'," % field[0],
print "type = '%s'" % _type,
if _len is not None: # Has a length?
print ", length = %i" % int(_len[1:-1]),
print "),"
print "\tmigrate = False)"
cursor = db.cursor()
cursor.execute('show tables')
for table in cursor.fetchall():
print
output_table(table[0])
=================== 8< == 8< == 8< ==============================
Many things to be done:
1) Fieldnames starting with "_" are not allowed by web2py (but MySQL
allows it). An error is printed
2) Some field types do not exist in web2py (e.g. tinyint) so an
equivalent is used.
Regards,
J.
--
Boriel http://www.boriel.com
mdipierro
messed up). I will include in the scrpts folder.
1) You can uses database views as a way around it but the new DAL will
allow this.
2) Perhaps the new DAL will allow this too but not sure yet.
Massimo
Rupesh Pradhan
Sweet! Boriel!!
Just what the doctor ordered!
Hey, If you happen to polish the script any further and have a new version of the same, just remember, I am here to put it to good use. I have subscribed to this thread, so that I know if you have put up anything here.
Also, Massimo, have you put the script yet in the 'scrpts' folder like you said in version 1.91.6?
DenesL
Hi Rupesh,
this is an old thread from 2009, the script is there:
/scripts/extract_mysql_models.py
Rupesh Pradhan
DenesL
It is there in all the sources I have:
/web2py_x.xx_dir/scripts/
which version do you have? which OS?.
Rupesh Pradhan
I have web2py_win_1.91.6 and Windows XP Professional OS.
Or do you mean the script folder is in the source code version of Web2Py? I am downloading that now and checking it.
Rupesh Pradhan
Okay, I found it.
The .py file was in the src version of web2py.
Anyway, the script is giving the following error, whereas the original script worked fine.
Given below is the traceback.
C:\Documents and Settings\rupeshpradhan\Desktop\scripts>python extract_mysql_models.py rupesh:password@rockvale
Traceback (most recent call last):
File "extract_mysql_models.py", line 106, in <module>
print mysql(m.group(3),m.group(1),m.group(2))
File "extract_mysql_models.py", line 62, in mysql
stderr=subprocess.PIPE)
File "D:\Python25\lib\subprocess.py", line 594, in __init__
errread, errwrite)
File "D:\Python25\lib\subprocess.py", line 822, in _execute_child
startupinfo)
WindowsError: [Error 2] The system cannot find the file specified
