skip to content

Which placeholder styles does sqlite3 accept, and what does the DB-API paramstyle global tell you?

level: middleimportance: should knowfreq 42%

answer

  1. PEP 249 makes drivers describe themselves
  2. Five possible values, two accepted here
  3. Question mark wants a sequence
  4. Colon-name wants a mapping
  5. Mismatch became an error in 3.14

basics

~20 s

sqlite3 accepts qmark placeholders (a bare ?, filled from a sequence) and named placeholders (:name, filled from a mapping). sqlite3.paramstyle is the PEP 249 global naming a driver's expected spelling; for sqlite3 it reads 'qmark'.

solid answer

~40 s

PEP 249 requires each driver module to publish `paramstyle`, one of `'qmark'`, `'numeric'`, `'named'`, `'format'` or `'pyformat'`. `sqlite3.paramstyle` is `'qmark'`, but the module in fact accepts two spellings: `?` placeholders bound positionally from a tuple or list, and `:name` placeholders bound from a dict. The statement and the container must agree — a named statement given a sequence raises `sqlite3.ProgrammingError` on Python 3.14, where 3.12 and 3.13 only issued a DeprecationWarning, and a qmark statement given a dict raises too. You also cannot mix the two styles in one statement. With qmark the count must match exactly; with named, extra keys in the mapping are ignored and a missing key is the error, which is why named placeholders are easier to keep correct once a statement has several parameters.

code

python · 15 lines
python
import sqlite3

print(sqlite3.paramstyle)

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE job(doc TEXT, pages INTEGER)")
con.execute("INSERT INTO job VALUES(?, ?)", ("a.pdf", 3))
con.execute("INSERT INTO job VALUES(:doc, :pages)", {"doc": "b.pdf", "pages": 5})

print(con.execute("SELECT pages FROM job WHERE doc = :doc", {"doc": "b.pdf"}).fetchone())
try:
    con.execute("SELECT pages FROM job WHERE doc = :doc", ("b.pdf",))
except sqlite3.ProgrammingError as exc:
    print("mismatch:", exc)
con.close()

go deeper

for a junior

Recall the two spellings sqlite3 accepts and which container each one needs: a question mark goes with a tuple, a colon-name goes with a dict. Remember the trailing comma in a one-element tuple.

for a middle

Explain that paramstyle is PEP 249 self-description with five defined values, and that a container mismatch is a ProgrammingError on 3.14 where older versions warned. Know why named placeholders resist transposition bugs.

for a senior

Demonstrate that you would settle one style as a house convention and enforce it, and that you treat the 3.12-to-3.14 deprecation as a concrete upgrade task — code running with warnings filtered off starts failing at runtime, not at import.

for a principal

Own the question of whether a shared query layer should abstract over drivers at all. Be able to say what such an abstraction genuinely buys, where it leaks, and why placeholder generation is the part of it that most often becomes a defect source.

### What paramstyle is PEP 249, Python's Database API 2.0 specification, requires every conforming driver module to expose a handful of module-level globals describing itself. `sqlite3.paramstyle` is one of them: a string naming the placeholder spelling that module's `execute` methods accept. For the stdlib driver it reads `'qmark'`. Its siblings `sqlite3.apilevel` (`'2.0'`) and `sqlite3.threadsafety` round out the self-description. The specification defines five possible values: - `'qmark'` — a bare `?`, filled positionally from a sequence. - `'numeric'` — `:1`, `:2`, numbered positions. - `'named'` — `:name`, filled from a mapping. - `'format'` — `%s`, positional, borrowed from `%`-formatting syntax. - `'pyformat'` — `%(name)s`, filled from a mapping. A module reports exactly one value even when it accepts more than one style — and `sqlite3` is precisely that case. It reports `'qmark'`, yet it also accepts `named` placeholders. ### The two styles sqlite3 accepts ```python cur.execute("INSERT INTO job VALUES(?, ?)", ("a.pdf", 3)) cur.execute("INSERT INTO job VALUES(:doc, :pages)", {"doc": "b.pdf", "pages": 5}) ``` The rule is that the *statement* and the *parameters container* must agree. Qmark placeholders need a sequence — a tuple or list. Named placeholders need a mapping. Mismatch either way is an error on 3.14: - named text, sequence supplied → `sqlite3.ProgrammingError: Binding 1 (':doc') is a named parameter, but you supplied a sequence which requires nameless (qmark) placeholders.` - qmark text, mapping supplied → `sqlite3.ProgrammingError: Binding 1 has no name, but you supplied a dictionary (which has only names).` **This is version-sensitive and worth stating in an interview.** Feeding a sequence to named placeholders was merely deprecated in Python 3.12 and 3.13 — it emitted a `DeprecationWarning` that literally announced the deadline — and it became a hard `sqlite3.ProgrammingError` in **3.14**. Code that ran with warnings suppressed on 3.13 fails at runtime after the upgrade, so it is a real migration item rather than trivia. You cannot mix the two styles inside one statement either: whichever container you pass, every placeholder must match it, so `"... WHERE doc = ? AND pages = :p"` fails whether you supply a tuple or a dict. ### Counting matters, and only for qmark With qmark placeholders the arity must match exactly. Supplying too few or too many raises `sqlite3.ProgrammingError: Incorrect number of bindings supplied.` Named placeholders are looked up by key, so the mapping may carry extra keys the statement does not use; a *missing* key is the error. That difference is the practical argument for the named style once a statement has more than three or four parameters: positional tuples silently transpose two arguments of the same type, and a mapping cannot. ### Why paramstyle does not give you portability The instinctive reading is that you can read `paramstyle` at import time and generate the right spelling, making one query layer work across drivers. In practice that is a trap for two reasons. First, the placeholder spelling is the *smallest* of the differences between drivers. Type adaptation, transaction control, returning-clause support, the meaning of `rowcount`, and the SQL dialect itself all differ; a layer that abstracts only the placeholder has abstracted the easy part. Second, generating placeholder text by string manipulation reintroduces the very construction that parameters exist to avoid. Building `"WHERE id IN (%s)" % ",".join(["?"] * len(ids))` is defensible because the interpolated text is derived from a *length*, not from user data — but it is the shape that review has to look at closely every time, and it is where mistakes cluster. The honest use of `paramstyle` is diagnostic: it tells you what a module you have just imported expects, which is genuinely useful when you are writing generic tooling, a test harness or a thin adapter, and it makes the "is this `?` or `%s`?" question answerable at runtime instead of from memory. ### What to say when asked Name the two styles `sqlite3` accepts and show that the container decides which one you may use. Say that `sqlite3.paramstyle` is the PEP 249 self-description and that its five possible values exist because drivers disagree. Then make the sharper point: the placeholder is never a formatting choice you make per value — it is a fixed part of a constant statement string, and the only thing that varies between calls is the parameters argument.

  • Can one sqlite3 statement mix `?` and `:name` placeholders?
    No. The parameters container decides how every placeholder is filled, so a statement such as `WHERE doc = ? AND pages = :p` fails either way: pass a tuple and sqlite3 complains that binding 2 is a named parameter needing nameless placeholders; pass a dict and it complains that binding 1 has no name. Pick one style per statement — in practice, per codebase.
  • Why doesn't reading paramstyle at import time make a query layer portable across drivers?
    Because the placeholder spelling is the smallest difference between drivers. Type adaptation, transaction control, the meaning of rowcount, returning-clause support and the SQL dialect all differ, so a layer that abstracts only the placeholder has abstracted the easy part. Worse, generating placeholder text by string manipulation reintroduces exactly the construction that parameters exist to avoid. paramstyle is best used diagnostically — to answer "what does this module I just imported expect?".
  • What does sqlite3 do when a qmark statement is given the wrong number of parameters?
    It raises `sqlite3.ProgrammingError: Incorrect number of bindings supplied`, naming both counts. Named placeholders behave differently: they are looked up by key, so a mapping with extra keys is fine and only a missing key is an error. That asymmetry is the practical argument for named placeholders in statements with many parameters — a positional tuple will happily transpose two same-typed arguments and produce a silently wrong row.

saying these in an interview costs you the question

  • Says sqlite3 uses %s placeholders like some other drivers
  • Thinks paramstyle can be read to make SQL portable
  • Mixes ? and :name placeholders in one statement
  • Assumes a named statement still accepts a tuple on 3.14
  • Believes extra keys in a parameter mapping cause an error

context

resolving links…