I was trying to extract the
CREATE TABLE statements from my Pylons application, to keep some text file in sync with the models, and know from revision to revision which columns were added/modified.
Asking on #sqlalchemy on Freenode, I was refered to this FAQ which told me exactly what to do, so I wrote a little script that would take my Pylons models, and spit out one file per database (in case someone uses my app with another database). That way, I could easily automate the generation of those files and not have to worry about that any more.
Here I share it with you:
#!/usr/bin/env python from sqlalchemy import * from StringIO import StringIO from vigilia import model buf = StringIO() engine1 = create_engine('postgres://', strategy='mock', executor=lambda s, p=';': buf.write(s + p)) engine2 = create_engine('mysql://', strategy='mock', executor=lambda s, p=';': buf.write(s + p)) engine3 = create_engine('sqlite://', strategy='mock', executor=lambda s, p=';': buf.write(s + p)) for engine, file in [(engine1, 'SCHEMA.postgres'), (engine2, 'SCHEMA.mysql'), (engine3, 'SCHEMA.sqlite')]: print "Writing %s" % file buf.truncate(0) tables = [x for x in sorted(model.meta.metadata.tables.items(), key=lambda x: x)] for table in tables: table.create(engine) f = open(file, 'w') f.write(buf.getvalue()) f.close()
NOTE: this is using Pylons 0.9.7
UPDATED: it was pretty useless in DVCS since the order of the tables wasn't always the same. The updated script corrects this problem, so the tables are always written in alphabetical order
UPDATED 2nd: the script wasn't writing ';' at the end of the statements, so the files were useless to `cat` in a mysql prompt. Fixed it with p=';'.