Currently, sqlite3
disallows using nameless (aka qmark) placeholders with params supplied as dict
s:
>>> import sqlite3
>>> cx = sqlite3.connect(":memory:")
>>> cx.execute("select ?", {"a": 42})
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
sqlite3.ProgrammingError: Binding 1 has no name, but you supplied a dictionary (which has only names).
OTOH, named placeholders combined with sequences are, unfortunately, silently allowed:
>>> cx.execute("select :name", [42]).fetchall()
[(42,)]
This is possible, because when you supply a sequence, sqlite3
simply maps the items in the sequence onto the placeholders by index.
Now, let’s introduce a common source of confusion: numeric placeholders. PEP-249 uses the following format for numeric placeholders: :<number>
. SQLite, OTOH, uses the following format: ?<number>
. What happens if you pass PEP-249 numeric placeholders to the SQLite library? The placeholders are interpreted by SQLite as named placeholders:
>>> cx.execute("select :1", {"1": 42}).fetchall()
[(42,)]
Unfortunately, if one misses the fact that these are named, and not numeric, placeholders, bugs may sneak in:
>>> cx.execute("select :1", ("first",)).fetchall()
[('first',)]
>>> cx.execute("select :1, :2", ("first", "second")).fetchall()
[('first', 'second')]
>>> cx.execute("select :2, :1", ("first", "second")).fetchall() # Unexpected result follows
[('first', 'second')]
IMO, it would better if sqlite3
disallowed combining named placeholders with sequences of params, similar to how it disallows nameless placeholders and dicts. Of course, we’d have to introduce a deprecation warning, wait a couple of releases, and only then start raising an exception.
Now, this might break some code out there (Hyrum’s Law), but there is value in preventing new bugs to sneak into people’s code bases.
Thoughts?