sqlalchemy BC dates with postgresql

Question

I am working on postgresql database through sqlalchemy. I need to work with BC dates with postgresql "date" column type (it supports BC dates http://www.postgresql.org/docs/9.2/static/datatype-datetime.html). But while I can insert BC dates in form of string (for example '2000-01-01 BC'), I can't retrieve them. Trying to get BC dates from database raises Value error: year is out of range. At first I thought the reason was that sqlalchemy in its type Date uses python datetime.date module, which does not support BC dates, but I'm not entirely sure. I was trying to find a way to map "date" type in database to custom python class (to bypass usage of datetime.date module) but I wasn't successful yet (I found ways to add logic to various processing steps in sqlalchemy, like result_processor or bind_expression with column_expression, but I didn't manage to make it work).

Here is example to reproduce problem.

    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]

And output with trace:

    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

I'm using postgresql 9.1.11, sqlalchemy 0.9.4, python 2.7

My question is: is there a way to work with BC dates in postgresql "date" type column through sqlalchemy (at least retrieve it as string, but perspectively map it to some module that can work with BC dates, like FlexiDate or astropy.time)?

Answer 1

I was digging this problem and I found out that this Value error exception is thrown not by sqlalchemy, but by database driver (psycopg2 in this case). Here, if performed with the database, created from above, this code will raise same Value error exception.

    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()

Output:

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

Looks like it tries to create datetime.date object from value retrieved from database and fails.

I tried also pg8000 dialect, it doesn't raise Value error, but it eats BC parts, returning datetime.date object with year, month and day (I mean from database row with value '1990-11-25 BC' it returns datetime.date(1990, 11, 25), which is 1990-11-25 actually). Here example:

    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]

Output:

    ([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']

Also I wanted to try py-postgresql dialect, but it's only for python 3+, which in not an option (production uses python 2.7.6 for this project).

So, answer for my question is: "No, it is not possible in sqlalchemy.", because type convertion between database literal and python object happens in database driver, not in sqlalchemy part.

UPDATE:

While this is indeed impossible to do in sqlalchemy, it is possible to define your own type cast in psycopg2 (and override default behavior), and make date type from database return watever you want. Here is an example:

    # 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)

With this an example from question works like a charm. And open a ways for additional data processing in sqlalchemy.