Automatically converting integer timestamps to python datetime in reflected SQLAlchemy models
What a title…
I’m working on slurchemy and I have a legacy database with tons of tables (many are dynamically created by another app). Each table has a few ‘*_time’ fields that are stored as Integers. A quick google showed me how to reflect SQLAlchemy models from the prexisting database, but getting the ‘*_time’ columns to play out in python as datetime objects (and not as `long`s) was a real nuisance.
I first tried to use the event framework like so:
from sqlalchemy import types from sqlalchemy import event def listen_for_reflect(table, column_info): if 'time' in column_info['name']: column_info['type'] = types.DateTime() event.listen(Table, 'column_reflect', listen_for_reflect)
This worked insofar as the Table’s column type was really changed to a
sqlalchemy.types.DateTime object in every case I wanted it to. But once a
sqlalchemy.orm.mapper was applied, my changes weren’t reflected, so to speak.
I banged my head against the sqlalchemy codebase and couldn’t make anything really elegant happen. Here’s what I settled with:
import datetime import time from sqlalchemy import MetaData, Table from sqlalchemy.orm import scoped_session, class_mapper def add_datetime_properties(cls): """ For every property of a class that contains '_time', add a corresponding '_datetime' property that converts to and from seconds since the epoch. Author: Ralph Bean <firstname.lastname@example.org> Use like:: >>> DBSession = scoped_session(maker) >>> DBSession.configure(bind=engine) >>> metadata = MetaData(engine.url) >>> table = Table('thing_table', metadata, autoload=True) >>> class Thing(object): ... pass >>> mapper(Thing, table) >>> add_datetime_properties(Thing) >>> t = DBSession.query(Thing).first() >>> print t.create_time ... 1314900554 >>> print t.create_datetime ... 2011-09-01 14:09:14 """ for prop in class_mapper(cls).iterate_properties: if '_time' not in prop.key: continue # Fugheddaboudit key = prop.key def getx(self): return datetime.datetime.fromtimestamp( float(getattr(self, key))) def setx(self, x): setattr(self, key, time.mktime(x.timetuple())) datetime_key = key.replace('_time', '_datetime') setattr(cls, datetime_key, property(getx, setx))
And it worked!