How to import existing MySQL database into web2py?
I am working on a project which requires that I import an already existing database and use it's data in Web2py. I have been able to link to the database by changing the DAL URI to:
db = DAL('mysql://root:password@localhost/database_name',pool_size=1,check_reserved=['all'])
and it creates all of my web2py defined fields just fine, and it can interact with them, but I can not access any of the data that was already in the database before. I tried running the web2py script extract_mysql_models.py, which is the supported way of importing the data but all I got returned was:
legacy_db = DAL('mysql://root:password@localhost/localhost/database_name')
which just creates another dal object. Trying to access anything through legacy_db only gives the same options as trying to get something through db. Has anyone done this before? Any tips?
I had the same problem. This is how I did.
dumping it from old db
mysqldump --user USERNAME --password=PASSWORD dbname > backup.sql
importing backupfile to new mysql server
mysql -h mysql.server -u USERNAME -p 'DBNAME' < backup.sql
Add add the DAL connection to new mysql server.
It is then identical to the old db. The problem I had was that setting up the db from pythonanywhere UI created a DB that was not identical with my old db. So if I remember right, you could also let web2py first create the db tables and then do step 2 last. Thats how I did anyways ... if I remember right ;) Hope it helps you. Cheers
我有同样的问题，并且在appadmin.py中创建了以下函数来自动创建表定义
get_db():
import re
datyp = dict(
bit='boolean', tinyint='boolean',
smallint='integer', mediumint='integer', int='integer', bigint='bigint',
decimal='decimal', double='double', float='double',
char='password', varchar='string',
longtext='list:string', mediumtext='text', text='text', tinytext='text',
enum='json',
timestamp='datetime', datetime='datetime', date='date', time='time',
year='integer',
tinyblob='blob', blob='blob', mediumblob='blob', longblob='blob',
binary='blob', varbinary='blob'
# upload reference list:string list:integer list:reference
# json big-id big-reference
)
tdat = []
tbls = [t[0] for t in db.executesql('SHOW TABLES;')]
for tbl in tbls:
cols = [k.strip() for k in db.executesql(
'SHOW CREATE TABLE `' + tbl + '`;')[0][1].split('n')]
fname = OrderedDict()
for col in cols:
if col.startswith('KEY'):
continue
coln = re.findall('`([^`]*)`', col)
if (col.startswith('`')):
st = col.index(' ') + 1
typ = col[st:col.index('(' if '(' in col else
(' ' if ' ' in col[st:] else ','), st)]
ft = datyp[typ]
if (ft == 'string'):
st = col.index('(', st) + 1
ft = "'" + ft + "', length=" + col[st:col.index(')', st)]
elif (ft == 'decimal'):
st = col.index('(', st)
ft = "'" + ft + col[st:col.index(')', st) + 1] + "'"
else:
ft = "'" + ft + "'"
if (not "DEFAULT NULL" in col) and ("'" in col):
st = col.index("DEFAULT '") + len("DEFAULT '")
ft += ', default=' + col[st:col.index("'", st)]
if 'NOT NULL' in col:
ft += ', required=True'
fname.update({coln[0]: ft})
continue
if (len(coln) == 2 and col.startswith('UNIQUE KEY')):
fname[coln[1]] += ', unique=True'
continue
if (len(coln) == 4):
sval = fname[coln[1]]
if 'integer' in sval:
sval = sval.replace('integer', 'reference db.' + coln[2])
elif 'bigint' in sval:
sval = sval.replace('bigint',
'big-reference db.' + coln[2])
fname[coln[1]] = sval
if ('ON DELETE ' in col) and (not 'CASCADE' in col):
st = col.index("ON DELETE ") + len("ON DELETE ")
sval = ', ondelete="' + col[st:col.index(" ON", st)] + '"'
fname[coln[1]] += sval
colstr = ["db.define_table('" + tbl + "',"]
for key, val in fname.items():
if (key == 'id'):
continue
colstr.append("Field('" + key + "', " + val + "),")
colstr.append('migrate=False)')
tdat.append(colstr)
response.view = 'default/index.html'
return dict(msg=tdat)