How to read datetime back from sqlite as datetime instead of a string in Python?

Question

I am using the sqlite3 module in Python 2.6.4 to store datetime in a SQLite database. Inserting is very simple because sqlite automatically converts the date to a string. The problem is that when reading it returns as a string, but I need to restore the original datetime object. How to do it?

Answer 1

If you declare your column as a timestamp type, you are in clover:

>>> db = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES) >>> c = db.cursor() >>> c.execute('create table foo (bar integer, baz timestamp)') <sqlite3.Cursor object at 0x40fc50> >>> c.execute('insert into foo values(?, ?)', (23, datetime.datetime.now())) <sqlite3.Cursor object at 0x40fc50> >>> c.execute('select * from foo') <sqlite3.Cursor object at 0x40fc50> >>> c.fetchall() [(23, datetime.datetime(2009, 12, 1, 19, 31, 1, 40113))] 

Cm? both int (for a declared integer) and datetime (for a column declared with a timestamp) survive a round with an intact type.

Answer 2

It turns out that sqlite3 can do this, and is even documented , sort of - but it's pretty easy to miss or misunderstand.

I needed to do the following:

• Pass the sqlite3.PARSE_COLNAMES parameter in the .connect () call, for example.

 conn = sqlite3.connect(dbFilePath, detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES) 

• Put the type that I need in the query - and for datetime it is not actually "datetime", but "timestamp":

   sql = 'SELECT jobid, startedTime as "[timestamp]" FROM job' cursor = conn.cursor() try: cursor.execute(sql) return cursor.fetchall() finally: cursor.close() 

If I go to "datetime", instead, it is silently ignored, and I still return the string. Same thing if I omit the quotation marks.