SQLlite converter recipes result in TypeError

[naglis]

The date/datetime SQLite converters in the Adapter and converter recipes produce TypeErrors as they seem to expect the value passed to be str, however as noted in the documentation, the converters are always passed a bytes object. In the convert_timestamp case, a cast to int (IIUC not float, as int is used in the adapt_datetime_epoch adapter recipe) is missing.

Below is an example based on the recipe to demonstrate the errors.

import datetime
import sqlite3
import traceback


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


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


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


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


con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)

d = datetime.date.today()
dt = datetime.datetime.now()

# Test `convert_date`.
sqlite3.register_adapter(datetime.date, adapt_date_iso)
sqlite3.register_converter("date", convert_date)

cur = con.execute("CREATE TABLE test_date(d date)")
cur.execute("INSERT INTO test_date(d) VALUES(?)", (d,))
try:
    cur.execute("SELECT d FROM test_date")
except Exception as e:
    traceback.print_exception(e)
else:
    print(f"Value from DB: {cur.fetchone()[0]}")

# Test `convert_datetime`.
sqlite3.register_adapter(datetime.datetime, adapt_datetime_iso)
sqlite3.register_converter("datetime", convert_datetime)

cur = con.execute("CREATE TABLE test_datetime(dt datetime)")
cur.execute("INSERT INTO test_datetime(dt) VALUES(?)", (dt,))
try:
    cur.execute("SELECT dt FROM test_datetime")
except Exception as e:
    traceback.print_exception(e)
else:
    print(f"Value from DB: {cur.fetchone()[0]}")


# Test `convert_timestamp`.
sqlite3.register_adapter(datetime.datetime, adapt_datetime_epoch)
sqlite3.register_converter("timestamp", convert_timestamp)

cur = con.execute("CREATE TABLE test_timestamp(ts timestamp)")
cur.execute("INSERT INTO test_timestamp(ts) VALUES(?)", (dt,))
try:
    cur.execute("SELECT ts FROM test_timestamp")
except Exception as e:
    traceback.print_exception(e)
else:
    print(f"Value from DB: {cur.fetchone()[0]}")

cur.close()
con.close()

Output:

Traceback (most recent call last):
  File "/tmp/sqlite_converter_recipe_test.py", line 48, in <module>
    cur.execute("SELECT d FROM test_date")
  File "/tmp/sqlite_converter_recipe_test.py", line 23, in convert_date
    return datetime.date.fromisoformat(val)
TypeError: fromisoformat: argument must be str
Traceback (most recent call last):
  File "/tmp/sqlite_converter_recipe_test.py", line 61, in <module>
    cur.execute("SELECT dt FROM test_datetime")
  File "/tmp/sqlite_converter_recipe_test.py", line 28, in convert_datetime
    return datetime.datetime.fromisoformat(val)
TypeError: fromisoformat: argument must be str
Traceback (most recent call last):
  File "/tmp/sqlite_converter_recipe_test.py", line 75, in <module>
    cur.execute("SELECT ts FROM test_timestamp")
  File "/tmp/sqlite_converter_recipe_test.py", line 33, in convert_timestamp
    return datetime.datetime.fromtimestamp(val)
TypeError: 'bytes' object cannot be interpreted as an integer

• PR: gh-99392: Fix SQLite converter recipes #99393

• PR: [3.11] gh-99392: Fix sqlite3 converter recipes (GH-99393) #99419

• PR: [3.10] gh-99392: Fix sqlite3 converter recipes (GH-99393) #99420

[Questionsdenombres]

Still about Adapter and converter recipes

Converters convert_date and convert_datetime that move data from SQLite to python now receive and decode bytes. I understand that this was the documentation fix.

However the just above and symmetric corresponding Adapters named adapt_date_iso and adapt_datetime_iso, which move data from python to SQLite, generate strings, not bytes.

Maybe this is correct since they were executed without reported error in the reported naglis test. But it looks inconsistent and adding to confusion, the `point' example, just a few lines above in the documentation use bytes for the Converter and for the Adapter;

def adapt_point(point):
    return f"{point.x};{point.y}".encode("utf-8")

def convert_point(s):
    x, y = list(map(float, s.split(b";")))
    return Point(x, y)

Thank you for the great product and documentation.
Patrick.

[erlend-aasland]

However the just above and symmetric corresponding Adapters named adapt_date_iso and adapt_datetime_iso, which move data from python to SQLite, generate strings, not bytes.

Maybe this is correct since they were executed without reported error in the reported naglis test. But it looks inconsistent and adding to confusion, the `point' example, just a few lines above in the documentation use bytes for the Converter and for the Adapter;

Yeah, it is slightly confusing that we're storing strings when adapting and decoding bytes when converting, but the docs explicitly say (visually highlighted as a "note", IIRC) that SQLite values are always passed to converters as bytes objects¹. If we were to change sqlite3's behaviour regarding converters, it would be a breaking change, so that's not gonna happen. The best we can do is to make sure the docs do a very good job in telling the user what to expect from sqlite3 :)

Now to the "point example"; thanks for pointing out that inconsistency. It's not a bug (the doctests would have caught that), but it is definitely inconsistent with the previous "point example"² (yes, there are two) and inconsistent with the prose. I'll change fix this with a docs update PR.

Thanks for the report, but in the future, please create a new issue instead of reusing a (related) old issue :)

Footnotes

1. https://docs.python.org/3/library/sqlite3.html#how-to-convert-sqlite-values-to-custom-python-types ↩

2. https://docs.python.org/3/library/sqlite3.html#how-to-register-adapter-callables ↩