Have an SQLAlchemy + SQLite "create_function" issue with datetime representations

Question

We have sqlite databases, and datetimes are actually stored in Excel format (there is a decent reason for this; it's our system's standard representation of choice, and the sqlite databases may be accessed by multiple languages/systems)

Have been introducing Python into the mix with great success in recent months, and SQLAlchemy is a part of that. The ability of the sqlite3 dbapi layer to swiftly bind custom Python functions where SQLite lacks a given SQL function is particularly appreciated.

I wrote an ExcelDateTime type decorator, and that works fine when retrieving result sets from the sqlite databases; Python gets proper datetimes back.

However, I'm having a real problem binding custom python functions that expect input params to be python datetimes; I'd have thought this was what the bindparam was for, but I'm obviously missing something, as I cannot get this scenario to work. Unfortunately, modifying the functions to convert from excel datetimes to python datetimes is not an option, and neither is changing the representation of the datetimes in the database, as more than one system/language may access it.

The code below is a self-contained example that can be run "as-is", and is representative of the issue. The custom function "get_month" is created, but fails because it receives the raw data, not the type-converted data from the "Born" column. At the end you can see what I've tried so far, and the errors it spits out...

Is what I'm trying to do impossible? Or is there a different way of ensuring the bound function receives the appropriate python type? It's the only problem I've been unable to overcome so far, would be great to find a solution!

import sqlalchemy.types as types
from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData
from sqlalchemy.sql.expression import bindparam
from sqlalchemy.sql import select, text
from sqlalchemy.interfaces import PoolListener
import datetime

# setup type decorator for excel<->python date conversions
class ExcelDateTime( types.TypeDecorator ):
    impl = types.FLOAT

    def process_result_value( self, value, dialect ):
        lxdays = int( value )
        lxsecs = int( round((value-lxdays) * 86400.0) )
        if lxsecs == 86400:
            lxsecs = 0
            lxdays += 1
        return ( datetime.datetime.fromordinal(lxdays+693594)
               + datetime.timedelta(seconds=lxsecs) )

    def process_bind_param( self, value, dialect ):
        if( value < 200000 ): # already excel float?
            return value
        elif( isinstance(value,datetime.date) ):
            return value.toordinal() - 693594.0
        elif( isinstance(value,datetime.datetime) ):
            date_part = value.toordinal() - 693594.0
            time_part = ((value.hour*3600) + (value.minute*60) + value.second) / 86400.0
            return date_part + time_part  # time part = day fraction

# create sqlite memory db via sqlalchemy
def get_month( dt ):
    return dt.month

class ConnectionFactory( PoolListener ):
    def connect( self, dbapi_con, con_record ):
        dbapi_con.create_function( 'GET_MONTH',1,get_month )

eng = create_engine('sqlite:///:memory:',listeners=[ConnectionFactory()])
eng.dialect.dbapi.enable_callback_tracebacks( 1 ) # show better errors from user functions
meta = MetaData()
birthdays = Table('Birthdays', meta, Column('Name',String,primary_key=True), Column('Born',ExcelDateTime), Column('BirthMonth',Integer))
meta.create_all(eng)
dbconn = eng.connect()
dbconn.execute( "INSERT INTO Birthdays VALUES('Jimi Hendrix',15672,NULL)" )

# demonstrate the type decorator works and we get proper datetimes out
res = dbconn.execute( select([birthdays]) )
tuple(res)
# >>> ((u'Jimi Hendrix', datetime.datetime(1942, 11, 27, 0, 0)),)

# simple attempt (blows up with "AttributeError: 'float' object has no attribute 'month'")
dbconn.execute( text("UPDATE Birthdays SET BirthMonth = GET_MONTH(Born)") )

# more involved attempt( blows up with "InterfaceError: (InterfaceError) Error binding parameter 0 - probably unsupported type")
dbconn.execute( text( "UPDATE Birthdays SET BirthMonth = GET_MONTH(:Born)",
                       bindparams=[bindparam('Born',ExcelDateTime)],
                       typemap={'Born':ExcelDateTime} ),
                Born=birthdays.c.Born )

Many thanks.

Answer 1

Instead of letting Excel/Microsoft dictate how you store date/time, it would be less trouble and work for you to rely on standard/"obvious way" of doing things.

1. Process objects according to the standards of their domain - Python's way (datetime objects) inside Python/SQLAlchemy, SQL's way inside SQLite (native date/time type instead of float!).

2. Use APIs to do the necessary translation between domains. (Python talks to SQLite via SQLAlchemy, Python talks to Excel via xlrd/xlwt , Python talks to other systems, Python is your glue.)

Using standard date/time types in SQLite allows you to write SQL without Python involve in standard readable way (WHERE date BETWEEN '2011-11-01' AND '2011-11-02' makes much more sense than WHERE date BETWEEN 48560.9999 AND 48561.00001). It allows you to easily port it to another DBMS (without rewriting all those ad-hoc functions) when your application/databse needs to grow.

Using native datetime objects in Python allows you to use a lot of freely available, well tested, and non-EEE (embrace, extend, extinguish) APIs. SQLAlchemy is one of those.

And I hope you are aware of that slight but dangerous difference between Excel datetime floats in Mac and Windows? Who knows that one of your clients would in the future submit an Excel file from a Mac and crash your application (actually, what's worse is they suddenly earned a million dollars from the error)?

So my suggestion is for you to use xlrd/xlwt when dealing with Excel from Python (there's another package out there for reading Excel 2007 up) and let SQLALchemy and your database use standard datetime types. However if you insist on continuing to store datetime as Excel float, it could save you a lot of time to reuse code from xlrd/xlwt. It has functions for converting Python objects to Excel data and vice-versa.

EDIT: for clarity...

You have no issues reading from the database to Python because you have that class that converts the float into Python datetime.

You have issues writing to the database through SQLAlchemy or using other native Python functions/modules/extensions because you are trying to force a non-standard type when they are expecting the standard Python datetime. ExcelDateTime type from the point of view Python is a float, not datetime.

Although Python uses dynamic/duck typing, it still is strongly typed. It won't allow you to do "nonsense/silliness" like adding integers to string, or forcing float for datetime.

At least two ways to address that:

1. Declare a custom type - Seems to be the path you wanted to take. Unfortunately this is the hard way. It's quite difficult to create a type that is a float that can also pretend to be datetime. Possible, yes, but requires a lot of study on type instrumentation. Sorry, you have to grok the documentation for that on your own.

2. Create utility functions - Should be the easier way, IMHO. You need 2 functions: a) float_to_datetime() for converting data from the database to return a Python datetime, and b) datetime_to_float() for converting Python datetime to Excel float.

About solution #2, as I was saying that you could simplify your life by reusing the xldate_from_datetime_tuple() from xlrd/xlwt. That function "Convert a datetime tuple (year, month, day, hour, minute, second) to an Excel date value." Install xlrd then go to /path_to_python/lib/site-packages/xlrd. The function is in xldate.py - the source is well documented for understanding.