Python: Parameterised query with dict produces results deviating from standard library sqlite driver #3262
Description
What happened?
Given that adbc_driver_manager.dbapi.Cursor is a A DB-API 2.0 (PEP 249) cursor, I would expect the ADBC driver behaviour to match the standard library sqlite driver.
The current issues include not raising errors where the standard library sqlite3 does, and failing to bind a dict parameter where the standard library sqlite3 does.
Stack Trace
No response
How can we reproduce the bug?
import sqlite3
import adbc_driver_sqlite.dbapi
query = "SELECT 'foo' WHERE {} = 1"
for param_style in ("?", "?1", ":n", "$n", "@n"):
for param_value in (
{"n": 1}, # matching key
{"x": 1}, # incorrect key
):
q = query.format(param_style)
print(f"{q = }")
print(f"{param_value = }")
with adbc_driver_sqlite.dbapi.connect() as adbc_conn:
adbc_cursor = adbc_conn.cursor()
try:
adbc_cursor.execute(q, param_value)
adbc_res = adbc_cursor.fetchone()
print(f"{adbc_res = }")
except Exception as e:
print(f"adbc_res = {str(e)}")
adbc_cursor.close()
with sqlite3.connect(":memory:") as sqlite_conn:
sqlite_cursor = sqlite_conn.cursor()
try:
sqlite_cursor.execute(q, param_value)
sqlite_res = sqlite_cursor.fetchone()
print(f"{sqlite_res = }", end="\n\n")
except Exception as e:
print(f"sqlite_res = {str(e)}", end="\n\n")
sqlite_cursor.close()If only certain styles of binding parameters intends to be support (as seems to be the case with PostgreSQL, noted here), it would be great to have this documented and/or raise errors when used incorrectly.
I'm unsure if "?1", "$name", and "@name", intend to be support, but the DBAPI supported "?" and ":name" also have issues with dicts (shown below)
Output
q = "SELECT 'foo' WHERE ? = 1"
param_value = {'n': 1}
adbc_res = None
sqlite_res = Binding 1 has no name, but you supplied a dictionary (which has only names).
q = "SELECT 'foo' WHERE ? = 1"
param_value = {'x': 1}
adbc_res = None
sqlite_res = Binding 1 has no name, but you supplied a dictionary (which has only names).
q = "SELECT 'foo' WHERE ?1 = 1"
param_value = {'n': 1}
adbc_res = None
sqlite_res = You did not supply a value for binding parameter :1.
q = "SELECT 'foo' WHERE ?1 = 1"
param_value = {'x': 1}
adbc_res = None
sqlite_res = You did not supply a value for binding parameter :1.
q = "SELECT 'foo' WHERE :n = 1"
param_value = {'n': 1}
adbc_res = None
sqlite_res = ('foo',)
q = "SELECT 'foo' WHERE :n = 1"
param_value = {'x': 1}
adbc_res = None
sqlite_res = You did not supply a value for binding parameter :n.
q = "SELECT 'foo' WHERE $n = 1"
param_value = {'n': 1}
adbc_res = None
sqlite_res = ('foo',)
q = "SELECT 'foo' WHERE $n = 1"
param_value = {'x': 1}
adbc_res = None
sqlite_res = You did not supply a value for binding parameter :n.
q = "SELECT 'foo' WHERE @n = 1"
param_value = {'n': 1}
adbc_res = None
sqlite_res = ('foo',)
q = "SELECT 'foo' WHERE @n = 1"
param_value = {'x': 1}
adbc_res = None
sqlite_res = You did not supply a value for binding parameter :n.
Environment/Setup
Python 3.11.8 on WSL. Latest version of all packages. Repro executed with uv.
uv run --no-project --with 'adbc-driver-sqlite' --with 'pyarrow' repro.py