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

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.

Proper way to do it as of Python 3.12+ is to register a converter, see docs. Example:

from contextlib import closing
from datetime import datetime, UTC
import sqlite3

# https://docs.python.org/3/library/sqlite3.html#sqlite3-adapter-converter-recipes
def adapt_datetime_iso(val):
    return val.isoformat()

def convert_datetime(val):
    return datetime.fromisoformat(val.decode())

sqlite3.register_adapter(datetime, adapt_datetime_iso)
sqlite3.register_converter("datetime", convert_datetime)

with (
    sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES) as conn,
    closing(conn.cursor()) as cursor
):
    cursor.execute("create table if not exists test (date datetime);")
    cursor.execute("insert into test values (:date);", {"date": datetime.now(tz=UTC)})
    cursor.execute("select * from test;")
    print(cursor.fetchall())

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