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[1] for x in sorted(model.meta.metadata.tables.items(), key=lambda x: x[0])]
    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=';'.