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