[Python] How to properly store a date time value in a sqlite3 database with python

I wanted to store a timestamp from Python in a sqlite3 database. For that I used this kind of code

from datetime import datetime, date, timedelta, time
import sqlite3
import os

def main():

script_dir = os.path.abspath(os.path.dirname(__file__))
database_file = os.path.join(script_dir, "sqlite.db")
print(database_file)

con = sqlite3.connect(database_file, detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES)
cur = con.cursor()
cur.execute("""CREATE TABLE if not exists timstmp
(DateCrt timestamp, DateSupp timestamp)"""
)
con.commit()

cur.execute("SELECT * FROM timstmp")
print(cur.fetchall())

print(datetime.now())
con = sqlite3.connect(database_file) , detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES)
cur = con.cursor()
cur.execute("INSERT into timstmp values (?, ?)", (datetime.now(), None))
con.commit()

cur.execute("SELECT * FROM timstmp")
print(cur.fetchall())

con.close()

if __name__ == '__main__':
main()


It works well, I can store and retrieve values, they are timestamps in sqlite, and in Python too

But when executing, I have the warning

DeprecationWarning: The default datetime adapter is deprecated as of Python 3.12; see the sqlite3 documentation for suggested replacement recipes

So, I wanted to correct my code, and i added this lines of code (found in https://docs.python.org/3/library/sqlite3.html#sqlite3-adapter-converter-recipes)

def adapt_date_iso(val):
"""Adapt datetime.date to ISO 8601 date."""
return val.isoformat()

def adapt_datetime_iso(val):
"""Adapt datetime.datetime to timezone-naive ISO 8601 date."""
return val.isoformat()

def adapt_datetime_epoch(val):
"""Adapt datetime.datetime to Unix timestamp."""
return int(val.timestamp())

sqlite3.register_adapter(date, adapt_date_iso)
sqlite3.register_adapter(datetime, adapt_datetime_iso)
sqlite3.register_adapter(datetime, adapt_datetime_epoch)

def convert_date(val):
"""Convert ISO 8601 date to datetime.date object."""
return date.fromisoformat(val.decode())

def convert_datetime(val):
"""Convert ISO 8601 datetime to datetime.datetime object."""
return datetime.fromisoformat(val.decode())

def convert_timestamp(val):
"""Convert Unix epoch timestamp to datetime.datetime object."""
return datetime.fromtimestamp(int(val))

sqlite3.register_converter("date", convert_date)
sqlite3.register_converter("datetime", convert_datetime)
sqlite3.register_converter("timestamp", convert_timestamp)


It seems to work (from the python program), but the values stored in the database aren't timestamps anymore, they are integers (and now the precision is only one second).

Did I misunderstood the goal of the adapters and converters? Is this a way to have again a timestamp in the sqlite3 database, without potential problem with future versions of Python?

Continue reading...