Description
Bug report
Bug description:
Starting in python 3.12, the following snippet generates a deprecation warning:
import sqlite3
db = sqlite3.connect(':memory:')
db.execute('CREATE TABLE a (b, c)')
db.execute('INSERT INTO a (b, c) VALUES (?2, ?1)', [3, 4])
# This line isn't necessary to reproduce the warning, it's just to show that
# the insert did in fact put "4" in column "b" and "3" in column "c".
print(db.execute('SELECT * FROM a').fetchall())Here's the warning for the first placeholder (there's another identical one for the second):
DeprecationWarning: Binding 1 ('?1') is a named parameter, but you supplied a sequence which requires nameless (qmark) placeholders. Starting with Python 3.14 an sqlite3.ProgrammingError will be raised.
I'll admit to not having a great understanding of how databases are supposed to work in python, but I don't think this warning should be issued. The sqlite docs specify that the ?<number> syntax is used to specify a parameter index, not a parameter name. So this kind of placeholder is meant to be used with sequence-style parameters like [3, 4]. I think the above warning should be issued only when the user tries to use :<word> placeholders with sequence-style parameters.
The above example is very simplified, so I think it might also be helpful to show the real-life query that triggered this warning for me. The goal is to insert key/value pairs from a dictionary, updating any keys that are already in the table. The query requires referring to the value in two places. ?<number> placeholders seem like the right syntax to use here, because they allow the metadata.items() to be used directly:
def upsert_metadata(db: sqlite3.Connection, metadata: dict[str, Any]):
db.executemany(
'''\
INSERT INTO metadata (key, value)
VALUES (?1, ?2)
ON CONFLICT (key)
DO UPDATE SET value=?2
''',
metadata.items(),
)CPython versions tested on:
3.11, 3.12
Operating systems tested on:
Linux