"ValueError: Couldn't parse time string", sqlite + Time column data type

[Former user]

When a table is created (using sqlite backend) with a timestamp column (data type is explicitly sqlalchemy.Time), an exception is thrown when trying to access the data from the row object.

See test code below for bug reproduction. Python 2.7.1, SQLAlchemy 0.7.1 on win32.

Test code:

#!/usr/bin/env python

import sqlalchemy

engine = sqlalchemy.create_engine("sqlite:///:memory:", poolclass=sqlalchemy.pool.StaticPool)
meta = sqlalchemy.MetaData()
meta.bind = engine

t = sqlalchemy.Table("testtable", meta,
                      sqlalchemy.Column("pkid", sqlalchemy.Integer, autoincrement=True, primary_key=True),
                      sqlalchemy.Column("name", sqlalchemy.Text),
                      sqlalchemy.Column("timestamp", sqlalchemy.Time, default=sqlalchemy.func.now()))

meta.create_all(bind=engine)

t.insert(values={'name':'foobar'}).execute()
cur = engine.execute(sqlalchemy.select([t](t)).where(t.c.name == "foobar"))
row = cur.fetchone()

print(row['timestamp']('timestamp'))

Result:

Traceback (most recent call last):
  File ".\bug-repr.py", line 23, in <module>
    print(row['timestamp']('timestamp'))
ValueError: Couldn't parse time string.

[Mike Bayer]

You're using the wrong SQL function to create data for the TIME type, CURRENT_TIMESTAMP (i.e. func.now()). which generates date and time, such as:

2011-08-04 19:13:01

whereas SQLAlchemy's Time type on SQLite (which note does not store dates/times natively, only as strings) documents the format here as:

12:05:57.10558

SQLite itself provides the TIME function as documented at http://sqlite.org/lang_datefunc.html in order to produce just the time portion, so the correct default should be:

sqlalchemy.Column("timestamp", sqlalchemy.Time, default=sqlalchemy.func.time(sqlalchemy.func.now()))

Some related fixes here: Fixed the links for DATE/TIME/DATETIME in the docs in 0eb06660ce838b23e8d12c460f3f322ba3f23ab5 b32c2001029d70233d032bab37cde3c5debe216e, ensured the identical ValueError is raised regardless of C extensions in place or not in 757400f82ff079f029cf2e11536c13d6199f832d.