This site is deprecated, please visit http://threebean.org/blog/

Automatically converting integer timestamps to python datetime in reflected SQLAlchemy models

with one comment

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!

Written by threebean

September 1, 2011 at 6:21 pm

Posted in Code, Python, slurm, sqlalchemy

One Response

Subscribe to comments with RSS.

  1. Nice post, if you don’t have to deal with auto-generated tables and you can explicitly override columns a custom type is another good way to do this.

    class IntegerDateTime(types.TypeDecorator):
    “””Used for working with epoch timestamps.

    Converts datetimes into epoch on the way in.
    Converts epoch timestamps to datetimes on the way out.
    impl = types.INTEGER
    def process_bind_param(self, value, dialect):
    return mktime(value.timetuple())
    def process_result_value(self, value, dialect):
    return datetime.fromtimestamp(value)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

%d bloggers like this: