sqlite3 seems to consider ?1 a named placeholder

[kalekundert]

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

Linked PRs

• gh-117995: Don't raise DeprecationWarnings for indexed nameless params #118001
• [3.12] gh-117995: Don't raise DeprecationWarnings for indexed nameless params (GH-118001) #118142

[erlend-aasland]

Thanks for the report! I overlooked the ?NNNN case when I implemented the warning last year. Currently, we use the return value of the SQLite C API sqlite3_bind_parameter_name() to decide if a parameter is named or not. This is tricky; I'll get back to you (hopefully this evening) with a proposed solution¹.

Footnotes

1. ping me if you don't hear from me until the weekend. ↩

[kalekundert]

Thanks for putting together a PR so quickly!

[erlend-aasland]

Resolved by:

• gh-117995: Don't raise DeprecationWarnings for indexed nameless params #118001
• [3.12] gh-117995: Don't raise DeprecationWarnings for indexed nameless params (GH-118001) #118142

[erlend-aasland]

Thanks for the report and the reproducer, @kalekundert :)