2012-04-13

A JSONDateTime TypeDecorator

JSON doesn't provide a date or date-time construct;  therefore every application is burdened with implementing a solution of it's own for receiving date-time information over the wire.  On common issue receiving JSON and serializing that data into some type of database - but the database knows the value is a date-time and you want to be able to perform date-time like operations on the value (locally).
Fortunately if your database is accessed via an SQLAlchemy ORM you can out-source this deserialization of the the values into your ORM by creating a TypeDecorator that wraps the datetime value and knows how to recognize and parse date-time values.
This example will allow an actual date-time value to be assigned to the attribute, or a 19 or 16 character string, or a NULL (None).  Conversion happens automatically when the value is assigned, but when the value is accessed an actual datetime is always seen.
from datetime import datetime
from sqlalchemy import TypeDecorator
from sqlalchemy.types import DateTime

class JSONDateTime(TypeDecorator):
    """ Allow storing a string into a datetime value, this allows 
        for automatically conversion of the JSON date strings into 
        date values"""
    impl = DateTime

    def __init__(self, *arg, **kw):
        TypeDecorator.__init__(self, *arg, **kw)

    def process_bind_param(self, value, dialect):
        if value:
            if isinstance(value, basestring):
                if (len(value) == 19):
                    return datetime.strptime(value, '%Y-%m-%dT%H:%M:%S')
                elif (len(value) == 16):
                    return datetime.strptime(value, '%Y-%m-%dT%H:%M')
                elif (len(value) == 0):
                    return None
            elif isinstance(value, datetime):
                return value
            raise Exception('Cannot store value "{0}" as
DateTime'.format(value))
        return None

# Create a class/table

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class LogEntry(Base):
    __tablename__ = 'logentry'
    ...
    actiondate = Column(JSONDateTime)
    ...
Text1: A TypeDecorator class for automatically converting string datetime representations to their datetime values; and a snippet of a table utilizing the new type.

One possible improvement is to make sure to assign a time-zone to the date value as comparing zoned and nieve datetime values will raise an exception.  But to do that you need to know the timezone you expect the JSON values to represent (in most cases this isn't present in the date-time string representation). The Python datetime's replace(tzinfo={tzinfoclass}) method is used to assign time zone information to a value. [Note: time zone information in Python.... not especially graceful or fun].

No comments:

Post a Comment