90

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?

Ciro Santilli OurBigBook.com
  • 347,512
  • 102
  • 1,199
  • 985
EMP
  • 59,148
  • 53
  • 164
  • 220

3 Answers3

124

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.

Alex Martelli
  • 854,459
  • 170
  • 1,222
  • 1,395
  • 5
    Thanks, Alex, it works! I'm surprised by this, because there is no mention of the TIMESTAMP type at all on http://www.sqlite.org/datatype3.html – EMP Dec 02 '09 at 23:11
  • 19
    @Evgeny, see http://docs.python.org/library/sqlite3.html#default-adapters-and-converters -- it's not documented at sqlite.org because it's actually implemented in the Python layer, not in sqlite itself! – Alex Martelli Dec 03 '09 at 01:44
  • 23
    I just find out it is the __detect_types=sqlite3.PARSE_DECLTYPES__ parameter of the connect function that makes fetchall returns a datetime. If you omit it you will have a unicode object. – Ponytech May 11 '12 at 15:39
  • 7
    Seems like this fails for timezone aware values like `u'2012-01-01 18:35:24.617954-02:00'` – Paulo Scardine Jul 26 '12 at 01:58
  • I had a tangential question on this issue, but I've created a new one here: http://stackoverflow.com/questions/11840382/sqlite-python-retrieve-current-timestamp-as-datetime-object. Is the issue related? It sounds like it MIGHT be. – Dev Kanchen Aug 07 '12 at 06:29
  • @AlexMartelli the datetime value returns via SQLITE is being considered as a Tuple when I try to use strftime function. What am I doing wrong? – Volatil3 Nov 11 '12 at 16:32
  • 14
    What is the rationale for TIMESTAMP working but not DATETIME? – Ciro Santilli OurBigBook.com Nov 24 '15 at 09:57
  • 1
    It's not reliable ... File "/opt/local/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/sqlite3/dbapi2.py", line 67, in convert_timestamp datepart, timepart = val.split(b" ") ValueError: not enough values to unpack (expected 2, got 1) – Wolfgang Fahl Apr 08 '20 at 08:00
  • 1
    I'm using python 3.8 and declared my variable as DATE. After using `detect_types=sqlite3.PARSE_DECLTYPES`, sqlite3 returns a datetime.date object. Otherwise, it returns a string. – FreyGeospatial May 11 '22 at 15:48
31

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.

EMP
  • 59,148
  • 53
  • 164
  • 220
  • 9
    note that it's definitely `"[timestamp]"`, `[timestamp]` is silently ignored, and `.fetchone` also won't convert the types. What an odd library. – Adam Baxter Dec 11 '16 at 09:54
  • It seemed what really count is the double quote characters, neither the alias you explicitly specified nor the `as` keyword – John Wang Sep 13 '18 at 00:20
  • I have now filed https://bugs.python.org/issue35145 'sqlite3: "select *" should autoconvert datetime fields'. – Robert Pollak Nov 02 '18 at 12:43
  • 1
    ^bug issue has been migrated here: https://github.com/python/cpython/issues/79326 – FreyGeospatial May 17 '22 at 15:36
1

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.)

Richard Li
  • 528
  • 2
  • 9
  • As @AdamBaxter said, what really count is the double quote characters, neither the alias you explicitly specified nor the `as` keyword. e.g., this works fine: `SELECT jobid, startedTime "[timestamp]" FROM job` – John Wang Sep 06 '18 at 21:54