SQLAlchemy与PostgreSQL的BC日期

Question

我正在使用SQLAlchemy与PostgreSQL数据库进行工作。我需要使用PostgreSQL“日期”列类型处理公元前日期（它支持公元前日期，详见http://www.postgresql.org/docs/9.2/static/datatype-datetime.html）。虽然我可以以字符串形式插入公元前日期（例如'2000-01-01 BC'），但我无法检索它们。尝试从数据库获取公元前日期时会引发ValueError：年份超出范围。起初，我认为原因是SQLAlchemy在其Date类型中使用了Python datetime.date模块，该模块不支持公元前日期，但我并不完全确定。我试图找到一种将数据库中的“日期”类型映射到自定义Python类的方法（以绕过datetime.date模块的使用），但我尚未成功（我找到了将逻辑添加到SQLAlchemy的各个处理步骤中的方法，如result_processor或bind_expression with column_expression，但我未能使其正常工作）。

下面是一个复现问题的示例。

    from sqlalchemy import create_engine, Column, Integer, String, Date
    from sqlalchemy.orm import sessionmaker
    from sqlalchemy.ext.declarative import declarative_base

    engine = create_engine('postgresql://database:database@localhost:5432/sqlalchemy_test', echo=True)
    Base = declarative_base()

    class User(Base):
        __tablename__ = 'users'

        id = Column(Integer, primary_key=True)
        name = Column(String)
        date = Column(Date)

        def __repr__(self):
            return "<User(name='%s', date='%s')>" % (self.name, self.date)

    Base.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)
    session = Session()

    # remove objects from previous launches
    session.query(User).delete()

    import datetime
    a_date = datetime.date.today()
    # Insert valid date in datetime.date format, it works.
    ed_user = User(name='ed', date=a_date)
    # Insert BC date in string format, it works.
    al_user = User(name='al', date='1950-12-16 BC')

    session.add_all([ed_user, al_user])
    session.commit()

    # Insert data via raw sql, it works
    conn = engine.connect()
    conn.execute('INSERT INTO users (name, date) VALUES (%s, %s)', ("Lu", "0090-04-25 BC"))

    # Check that all 3 objects are present
    user_names = session.query(User.name).all()
    print user_names


    # Check entry with AD date, it works.
    user = session.query(User).filter_by(name='ed').first()
    print '-- user vith valid date: ', user, user.date

    # But when trying to fetch date fields in any way, it raises Value error

    # Trying to fetch model, it raises Value error
    users = session.query(User).all()
    print users

    # Trying to fetch only field, it raises Value error
    user_dates = session.query(User.date).all()
    print user_dates

    # Even trying to fetch dates in raw sql raises Value error
    a = conn.execute('SELECT * FROM users')
    print [c for c in a]

并使用跟踪输出：

    2014-06-19 16:06:20,466 INFO sqlalchemy.engine.base.Engine select version()
    2014-06-19 16:06:20,466 INFO sqlalchemy.engine.base.Engine {}
    2014-06-19 16:06:20,468 INFO sqlalchemy.engine.base.Engine select current_schema()
    2014-06-19 16:06:20,468 INFO sqlalchemy.engine.base.Engine {}
    2014-06-19 16:06:20,470 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
    2014-06-19 16:06:20,470 INFO sqlalchemy.engine.base.Engine {}
    2014-06-19 16:06:20,471 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
    2014-06-19 16:06:20,471 INFO sqlalchemy.engine.base.Engine {}
    2014-06-19 16:06:20,473 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
    2014-06-19 16:06:20,473 INFO sqlalchemy.engine.base.Engine {}
    2014-06-19 16:06:20,475 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and relname=%(name)s
    2014-06-19 16:06:20,475 INFO sqlalchemy.engine.base.Engine {'name': u'users'}
    2014-06-19 16:06:20,477 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
    2014-06-19 16:06:20,478 INFO sqlalchemy.engine.base.Engine DELETE FROM users
    2014-06-19 16:06:20,478 INFO sqlalchemy.engine.base.Engine {}
    2014-06-19 16:06:20,480 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, date) VALUES (%(name)s, %(date)s) RETURNING users.id
    2014-06-19 16:06:20,481 INFO sqlalchemy.engine.base.Engine {'date': datetime.date(2014, 6, 19), 'name': 'ed'}
    2014-06-19 16:06:20,482 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, date) VALUES (%(name)s, %(date)s) RETURNING users.id
    2014-06-19 16:06:20,482 INFO sqlalchemy.engine.base.Engine {'date': '1950-12-16 BC', 'name': 'al'}
    2014-06-19 16:06:20,483 INFO sqlalchemy.engine.base.Engine COMMIT
    2014-06-19 16:06:20,494 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, date) VALUES (%s, %s)
    2014-06-19 16:06:20,494 INFO sqlalchemy.engine.base.Engine ('Lu', '0090-04-25 BC')
    2014-06-19 16:06:20,495 INFO sqlalchemy.engine.base.Engine COMMIT
    2014-06-19 16:06:20,517 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
    2014-06-19 16:06:20,517 INFO sqlalchemy.engine.base.Engine SELECT users.name AS users_name 
    FROM users
    2014-06-19 16:06:20,517 INFO sqlalchemy.engine.base.Engine {}
    -- user names:  [(u'ed',), (u'al',), (u'Lu',)]
    2014-06-19 16:06:20,520 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.date AS users_date 
    FROM users 
    WHERE users.name = %(name_1)s 
     LIMIT %(param_1)s
    2014-06-19 16:06:20,520 INFO sqlalchemy.engine.base.Engine {'name_1': 'ed', 'param_1': 1}
    -- user with valid date:  <User(name='ed', date='2014-06-19')> 2014-06-19
    2014-06-19 16:06:20,523 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.date AS users_date 
    FROM users
    2014-06-19 16:06:20,523 INFO sqlalchemy.engine.base.Engine {}
    Traceback (most recent call last):
      File "test_script.py", line 49, in <module>
        users = session.query(User).all()
      File "/home/pavel/.virtualenvs/common/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2292, in all
        return list(self)
      File "/home/pavel/.virtualenvs/common/local/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 65, in instances
        fetch = cursor.fetchall()
      File "/home/pavel/.virtualenvs/common/local/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 788, in fetchall
        self.cursor, self.context)
      File "/home/pavel/.virtualenvs/common/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1111, in _handle_dbapi_exception
        util.reraise(*exc_info)
      File "/home/pavel/.virtualenvs/common/local/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 782, in fetchall
        l = self.process_rows(self._fetchall_impl())
      File "/home/pavel/.virtualenvs/common/local/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 749, in _fetchall_impl
        return self.cursor.fetchall()
    ValueError: year is out of range

我正在使用postgresql 9.1.11、sqlalchemy 0.9.4和python 2.7。

我的问题是：是否有一种方法可以通过sqlalchemy在postgresql“日期”类型列中使用BC日期（至少将其检索为字符串，但前景映射到某个可以处理BC日期的模块，如FlexiDate或astropy.time）？

- pavel_form

Answer 1

我正在研究这个问题，发现这个Value error异常不是由SQLAlchemy引起的，而是由数据库驱动程序（在这种情况下是psycopg2）引起的。如果使用上述创建的数据库执行此代码，则会引发相同的Value error异常。

    import psycopg2
    conn = psycopg2.connect("dbname=sqlalchemy_test user=database password=database host=localhost port=5432")
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    cursor.fetchall()

输出：

    Traceback (most recent call last):
      File "test_script.py", line 5, in <module>
        values = cursor.fetchall()
    ValueError: year is out of range

看起来它试图从数据库检索的值创建datetime.date对象并失败了。

我还尝试了pg8000方言，它不会引发Value错误，但是会把BC部分去掉，返回具有年份，月份和日期的datetime.date对象（我的意思是从带有“1990-11-25 BC”值的数据库行中返回datetime.date（1990，11，25），实际上是1990-11-25）。以下是示例：

    import pg8000
    conn = pg8000.connect(user='postgres', host='localhost', port=5432,
                          database='sqlalchemy_test', password='postgres')
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM users')
    values = cursor.fetchall()
    print values
    print [str(val[2]) for val in values]

输出：

    ([1, u'ed', datetime.date(2014, 6, 19)], [2, u'al', datetime.date(1950, 12, 16)], [3, u'Lu', datetime.date(90, 4, 25)])
    ['2014-06-19', '1950-12-16', '0090-04-25']

我还想尝试使用py-postgresql方言，但它只适用于Python 3+，而这不是一个选项（生产环境使用Python 2.7.6进行此项目）。

因此，我的问题的答案是：“不，这在SQLAlchemy中不可能”，因为数据库文字和Python对象之间的类型转换发生在数据库驱动程序中，而不是SQLAlchemy部分。

更新：

虽然这确实无法在SQLAlchemy中完成，但可以在psycopg2中定义自己的类型转换（并覆盖默认行为），并使来自数据库的日期类型返回您想要的内容。以下是示例：

    # Cast PostgreSQL date into string
    # http://initd.org/psycopg/docs/advanced.html#type-casting-from-sql-to-python

    import psycopg2

    BcDate = None

    # This function dafines types cast, and as returned database literal is already a string, no
    # additional logic required.
    def cast_bc_date(value, cursor):
        return value

    def register_bc_date(connection):
        global BcDate
        if not BcDate:
            cursor = connection.cursor()
            cursor.execute('SELECT NULL::date')
            psql_date_oid = cursor.description[0][1]

            BcDate = psycopg2.extensions.new_type((psql_date_oid,), 'DATE', cast_bc_date)
            psycopg2.extensions.register_type(BcDate)

    conn = psycopg2.connect(database='sqlalchemy_test', user='database', password='database', host='localhost', port=5432)
    register_bc_date(conn)

这个问题的例子非常好，而且在SQLAlchemy中开启了额外数据处理的途径。

- pavel_form