SQLAlchemy + SQLite 的 "create_function" 日期时间表示问题

Question

我们有一些sqlite数据库，里面的日期时间实际上是以Excel格式存储的（这样做是有原因的；这是我们系统选择的标准表示方式，而且这些sqlite数据库可能会被多种语言和系统访问）。

最近几个月，我们成功地将Python引入到这个系统中，SQLAlchemy也是其中的一部分。sqlite3的dbapi层能够快速绑定自定义的Python函数，这一点特别受欢迎，因为SQLite本身缺少某些SQL函数。

我写了一个ExcelDateTime类型的装饰器，这在从sqlite数据库中获取结果集时工作得很好；Python能够正确地返回日期时间。

不过，我在绑定那些期望输入参数为Python日期时间的自定义函数时遇到了麻烦；我本以为这就是bindparam的用途，但显然我错过了什么，因为我无法让这个场景正常工作。不幸的是，修改这些函数以将Excel日期时间转换为Python日期时间并不是一个选项，而且改变数据库中日期时间的表示方式也不行，因为可能有多个系统或语言会访问这些数据。

下面的代码是一个可以直接运行的完整示例，代表了这个问题。自定义函数“get_month”被创建，但失败了，因为它接收到的是原始数据，而不是“Born”列中经过类型转换的数据。最后你可以看到我到目前为止尝试过的内容，以及它抛出的错误……

我想做的事情是不可行的吗？还是说有其他方法可以确保绑定的函数接收到合适的Python类型？这是我目前无法克服的唯一问题，找到解决方案会非常棒！

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 )

非常感谢。

sqlalchemy sqlite datetime excel database design custom functions data binding type conversion

SQLAlchemy + SQLite 的 "create_function" 日期时间表示问题

1 个回答

撰写回答