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

I’m using the sqlite3 module in Python 2.6.4 to store a datetime in a SQLite database. Inserting it is very easy, because sqlite automatically converts the date to a string. The problem is, when reading it it comes back as a string, but I need to reconstruct the original datetime object. How do I do this?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

If you declare your column with a type of timestamp, you’re 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))]

See? both int (for a column declared integer) and datetime (for a column declared timestamp) survive the round-trip with the type intact.

Method 2

It turns out that sqlite3 can do this and it’s even documented, kind of – but it’s pretty easy to miss or misunderstand.

What I had to do is:

• Pass the sqlite3.PARSE_COLNAMES option in the .connect() call, eg.

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

• Put the type I wanted into the query – and for datetime, it’s 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 pass in “datetime” instead it’s silently ignored and I still get a string back. Same if I omit the quotes.

Method 3

Note: In Python3, I had to change the SQL to something like:

SELECT jobid, startedTime as "st [timestamp]" FROM job

(I had to explicitly name the column.)

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0