Clarify how sqlite3 maps parameters onto placeholders

[erlend-aasland]

agree; you might want to clarify though that the use of "qmark" or "named" is detected automatically on a per-statement basis (provided my understanding of that is correct).

Yes, we should definitely clarify how parameters are interpreted and mapped to the placeholders.

sqlite3 does not check if you use the "qmark" or "named" style (or any other style FWIW¹); it only looks at the type of the params supplied:

1. If a dict or dict subclass is supplied, the named style is assumed and you'll get an error if a named parameter is not provided by the supplied dict.

2. If an exact tuple, an exact list, or a sequence (that is not a dict or dict subclass) is supplied, the qmark style² is assumed. This means that sqlite3 iterates over the params and blindly assigns placeholder 1³ the first item in the supplied sequence, and so on. This also happens if you use named placeholders and supply, for example, a list. Try it and be surprised. Now, that bug may be too old to be fixed; there's bound to be some code out there that depends on this exact bug. We might be able to introduce a warning and then change the behaviour after a few release cycles, but such a breaking change/bugfix will need a broader discussion.

Originally posted by @erlend-aasland in #100630 (comment)

Linked PRs

• gh-100668: Clarify how sqlite3 maps parameters onto placeholders #100960
• [3.11] gh-100668: Clarify how sqlite3 maps parameters onto placeholders (GH-100960) #101044
• [3.10] gh-100668: Clarify how sqlite3 maps parameters onto placeholders (GH-100960) #101045

Footnotes

1. try for example cx.execute("select ?2, ?1", ['first', 'second']); the SQLite numeric style, which is not PEP-249-compatible, is accepted and correctly applied ↩

2. called nameless in SQLite speak ↩

3. SQLite placeholders use one-based indices ↩

[erlend-aasland]

See also #99953

[erlend-aasland]

We should not put too much details into the existing "placeholders how-to"; perhaps we could add a short explanation for how this stuff actually works.