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: 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.

