SQLAlchemy losing timezone information with SQLite

Viewed 3812

I'm having an issue where SQLAlchemy (versions 0.6.4 & 0.6.8) is losing the timezone information from a datetime object when committed to an SQLite database with the column defined with SQLAlchemy's DateTime class. (I realise that SQLAlchemy is converting this to a string and back, which is what I believe to be half the problem).

As code speaks louder than words, I have the following example/reproducer:

import datetime, pytz, sqlalchemy
from sqlalchemy import create_engine, Column, Integer, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base()

Session = sessionmaker(bind=engine)
session = Session()

class Example(Base):
    __tablename__ = "example"
    id = Column(Integer, primary_key=True)
    date = Column(DateTime(timezone=True))

    def __init__(self, date):
        self.date = date

Base.metadata.create_all(engine)

aucklandtz = pytz.timezone('Pacific/Auckland')
exdatetime = datetime.datetime(2011,8,8,2,23)

print aucklandtz, exdatetime

# inject TZ to exdatetime:
injdatetime = aucklandtz.localize(exdatetime)

print injdatetime

newrecord = Example(injdatetime)
print newrecord.date
session.add(newrecord)
session.commit()
print newrecord.date

From the console output, the main thing is that it is inserting the record into SQLite as the value '2011-08-08 02:23:00.000000'.

Is there an easy way to fix this, or do I need to add the TZ information to a separate column/store everything as timestamps and juggle between the formats?

2 Answers
Related