[three]Bean

Automatically converting integer timestamps to python datetime in reflected SQLAlchemy models

Sep 01, 2011 | categories: python, sqlalchemy, slurm View Comments

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 <ralph.bean@gmail.com>

    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!

View Comments