Alexandre Bourget

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=’;’.

SQLAlchemy allows you to pass an optionnal `timezone` argument to the DateTime types object. However, it’s only used by the PostgreSQL backend.

I was looking for a solution that would save consistently time-offset-aware `datetime` objects.

I found something pretty simple in the PylonsHQ pasties:

import sqlalchemy as sqla
from pytz import UTC

class UTCDateTime(sqla.types.TypeDecorator):
    impl = sqla.types.DateTime
    def convert_bind_param(self, value, engine):
        return value
    def convert_result_value(self, value, engine):
        return UTC.localize(value)

Defining this in your application allows SQLAlchemy not to depend on the `pytz` package, and allows you to have simple UTC-everywhere support in your database. Best of all, it’s cross-database.

In the actual state, you wouldn’t be able to get from the database an offset-aware `datetime` object. The `convert_bind_param` should be modified to tweak the result retrieved from the database. This way, you’d have offset-aware `datetime`s throughout.

Please comment if you try this