Hi,
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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web2py Web Framework" group.
To post to this group, send email to web2py@googlegroups.com
To unsubscribe from this group, send email to
web2py+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
