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?