What does a DB-API driver's paramstyle attribute declare, and why does it hurt portability?

middleshould knowfreq 38%

answer

The module describes itself to you
Five legal placeholder spellings, one per driver
Positional wants a sequence, named wants a mapping
It is a module global, not a connection setting
Swap drivers and every query string changes

basics

~20 s

PEP 249 makes every driver module expose a paramstyle string naming the placeholder syntax it accepts: qmark, numeric, named, format or pyformat. Because the driver fixes the style, statements written for one driver do not run on another.

solid answer

~50 s

PEP 249 requires each database module to expose a module-level paramstyle constant with one of five values: qmark (?), numeric (:1), named (:name), format (%s) and pyformat (%(name)s). The stdlib sqlite3 module, for example, reports qmark. It is a module attribute, not a per-connection setting, so the placeholder syntax is fixed by whichever driver you imported — swap drivers and every parameterised statement in the codebase has to be rewritten, even when the SQL dialect itself is identical. That is why teams either standardise on one driver, generate SQL through a layer that knows the style, or keep the handful of styles behind a small helper. Whichever style is in play, the values are passed as a second argument to execute() and bound by the driver; they are never substituted into the string by Python.

PEP 249 requires each database module to expose a module-level `paramstyle` constant with one of five values: `qmark` (`?`), `numeric` (`:1`), `named` (`:name`), `format` (`%s`) and `pyformat` (`%(name)s`). The stdlib sqlite3 module, for example, reports `qmark`. It is a *module* attribute, not a per-connection setting, so the placeholder syntax is fixed by whichever driver you imported — swap drivers and every parameterised statement in the codebase has to be rewritten, even when the SQL dialect itself is identical. That is why teams either standardise on one driver, generate SQL through a layer that knows the style, or keep the handful of styles behind a small helper. Whichever style is in play, the values are passed as a second argument to `execute()` and bound by the driver; they are never substituted into the string by Python.

code

python · 12 lines

python

import sqlite3

print(sqlite3.paramstyle, sqlite3.apilevel, sqlite3.threadsafety)
# qmark 2.0 3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE plan(name TEXT, cents INTEGER)")
cur.execute("INSERT INTO plan VALUES (?, ?)", ("team", 1100))
cur.execute("SELECT cents FROM plan WHERE name = :name", {"name": "team"})
print(cur.fetchone())  # (1100,)
con.close()

go deeper

for a junior

Know that values go in the second argument to execute() and never into the statement string, and that a single value is passed as a one-element tuple with its trailing comma.

for a middle

Name the five styles and what each looks like, say that paramstyle is a module global fixed by the driver, and explain why format/pyformat are placeholders rather than Python's % operator.

for a senior

Show what a driver swap really costs: every parameterised statement rewritten with no compiler help, and the mitigations — one data-access layer, generated SQL, or introspecting module.paramstyle in portable library code.

for a principal

Own the choice of whether the codebase writes raw driver SQL at all. Weigh a hand-rolled access layer against an ORM in terms of driver lock-in, review load and the blast radius of a future engine migration.

What the attribute is

PEP 249 asks every conforming database module for three module-level globals describing itself: apilevel (the spec version it implements, "2.0" for anything modern), threadsafety (an integer 0–3 saying how far a connection may be shared between threads), and paramstyle (a string naming the placeholder syntax the driver accepts). The stdlib sqlite3 module reports "2.0", 3 and "qmark" respectively — sqlite3.threadsafety became 3 in Python 3.11, having previously been 1.

The five legal paramstyle values are:

value	placeholder	parameters passed as
`qmark`	`... WHERE id = ?`	a sequence
`numeric`	`... WHERE id = :1`	a sequence
`named`	`... WHERE id = :sub_id`	a mapping
`format`	`... WHERE id = %s`	a sequence
`pyformat`	`... WHERE id = %(sub_id)s`	a mapping

Positional styles take a sequence of values in placeholder order; named styles take a mapping keyed by placeholder name.

Why it is a portability problem

The value is attached to the module, not to a connection or a cursor. You cannot ask a connection to accept a different style, and there is nothing in the spec that translates between styles. So the placeholder syntax is decided the moment you choose a driver, and it leaks into every single statement string in the codebase. Migrating a service from one driver to another — even between two drivers for the same database engine, which is common — means touching every parameterised query, not just the connection setup, and the compiler will not help you: a query written with %s placeholders and handed to a qmark driver fails at runtime, inside whatever code path happens to run it.

The practical responses are all forms of not writing raw placeholders everywhere. A thin data-access layer can hold the queries in one place, so a style change is one file. Query builders and object-relational mappers render the statement for whichever driver is configured, which is a large part of why they exist. And generic library code that must support several drivers reads module.paramstyle at import time and renders its own SQL accordingly — the attribute exists precisely so that portable tooling can introspect it, not so that application code can print it.

The traps around the placeholders themselves

A single parameter still needs a sequence. With a positional style, cur.execute("... WHERE name = ?", "team") is a bug, not a shortcut: a string is a sequence, so the driver sees four bindings for a one-placeholder statement and raises a ProgrammingError. The 1-tuple ("team",) — trailing comma included — is the fix, and the missing comma is one of the most common defects in hand-written database code.

format and pyformat look like Python's own % operator, and are not. Those two styles use %s and %(name)s purely because the drivers that pioneered them borrowed the notation. Writing cur.execute("... WHERE id = %s" % sub_id) performs Python string formatting and hands the database a fully-built statement with no parameters at all, which is both an injection hole and a defeat of statement caching. The value belongs in the second argument to execute().

A placeholder stands for one value, never for an identifier. No style lets you parameterise a table name, a column name, an ORDER BY direction or a whole IN list. An IN clause needs as many placeholders as you have values, generated to match the length of the sequence you are binding; identifiers have to be validated against an allow-list in Python instead.

Some drivers accept more than they declare. The stdlib sqlite3 module declares qmark but also documents support for named placeholders bound with a dict. That is a documented extension, not a licence to assume it elsewhere: relying on an undeclared style is how portable-looking code turns out to be driver-specific.

Where the value is bound

With any of the five styles the parameters travel to the database separately from the statement text. The driver either sends a prepared statement plus its arguments, or escapes and types the values itself; either way the database parses the statement once and treats each parameter as a typed value rather than as SQL. That is what makes the parameterised form both safer and, on repeated execution, faster — executemany() exists to lean on exactly that, running one statement against a sequence of parameter sequences or mappings.

## What the attribute is PEP 249 asks every conforming database module for three module-level globals describing itself: `apilevel` (the spec version it implements, `"2.0"` for anything modern), `threadsafety` (an integer 0–3 saying how far a connection may be shared between threads), and `paramstyle` (a string naming the placeholder syntax the driver accepts). The stdlib sqlite3 module reports `"2.0"`, `3` and `"qmark"` respectively — `sqlite3.threadsafety` became `3` in Python 3.11, having previously been `1`. The five legal `paramstyle` values are: | value | placeholder | parameters passed as | |---|---|---| | `qmark` | `... WHERE id = ?` | a sequence | | `numeric` | `... WHERE id = :1` | a sequence | | `named` | `... WHERE id = :sub_id` | a mapping | | `format` | `... WHERE id = %s` | a sequence | | `pyformat` | `... WHERE id = %(sub_id)s` | a mapping | Positional styles take a sequence of values in placeholder order; named styles take a mapping keyed by placeholder name. ## Why it is a portability problem The value is attached to the **module**, not to a connection or a cursor. You cannot ask a connection to accept a different style, and there is nothing in the spec that translates between styles. So the placeholder syntax is decided the moment you choose a driver, and it leaks into every single statement string in the codebase. Migrating a service from one driver to another — even between two drivers for the *same* database engine, which is common — means touching every parameterised query, not just the connection setup, and the compiler will not help you: a query written with `%s` placeholders and handed to a `qmark` driver fails at runtime, inside whatever code path happens to run it. The practical responses are all forms of not writing raw placeholders everywhere. A thin data-access layer can hold the queries in one place, so a style change is one file. Query builders and object-relational mappers render the statement for whichever driver is configured, which is a large part of why they exist. And generic library code that must support several drivers reads `module.paramstyle` at import time and renders its own SQL accordingly — the attribute exists precisely so that portable tooling can introspect it, not so that application code can print it. ## The traps around the placeholders themselves **A single parameter still needs a sequence.** With a positional style, `cur.execute("... WHERE name = ?", "team")` is a bug, not a shortcut: a string *is* a sequence, so the driver sees four bindings for a one-placeholder statement and raises a `ProgrammingError`. The 1-tuple `("team",)` — trailing comma included — is the fix, and the missing comma is one of the most common defects in hand-written database code. **`format` and `pyformat` look like Python's own `%` operator, and are not.** Those two styles use `%s` and `%(name)s` purely because the drivers that pioneered them borrowed the notation. Writing `cur.execute("... WHERE id = %s" % sub_id)` performs Python string formatting and hands the database a fully-built statement with no parameters at all, which is both an injection hole and a defeat of statement caching. The value belongs in the second argument to `execute()`. **A placeholder stands for one value, never for an identifier.** No style lets you parameterise a table name, a column name, an `ORDER BY` direction or a whole `IN` list. An `IN` clause needs as many placeholders as you have values, generated to match the length of the sequence you are binding; identifiers have to be validated against an allow-list in Python instead. **Some drivers accept more than they declare.** The stdlib sqlite3 module declares `qmark` but also documents support for `named` placeholders bound with a dict. That is a documented extension, not a licence to assume it elsewhere: relying on an undeclared style is how portable-looking code turns out to be driver-specific. ## Where the value is bound With any of the five styles the parameters travel to the database separately from the statement text. The driver either sends a prepared statement plus its arguments, or escapes and types the values itself; either way the database parses the statement once and treats each parameter as a typed value rather than as SQL. That is what makes the parameterised form both safer and, on repeated execution, faster — `executemany()` exists to lean on exactly that, running one statement against a sequence of parameter sequences or mappings.

How would you write library code that has to work against drivers with different paramstyles?
Read the driver module's `paramstyle` at import time and render your statements for it, keeping the SQL in one place — a small function that emits the right placeholder for the declared style and packs the parameters as a sequence or a mapping accordingly. Application code should not do this per query; it should go through a data-access layer, a query builder or an ORM that already does. What you must never do is assume a driver accepts a style it does not declare.
Can you use a placeholder for a table name or for the values of an IN clause?
No. A placeholder stands for a single value, so identifiers — tables, columns, sort direction — can never be bound; validate them in Python against an allow-list of known-good names and interpolate only from that list. For an `IN` clause you generate as many placeholders as you have values and bind the sequence, so a three-element list produces three markers. Both are the standard shape of the problem, in every paramstyle.
The paramstyle is 'pyformat' and someone writes execute("... WHERE id = %s" % user_id). What is wrong?
The `%` operator formats the string in Python before the driver ever sees it, so the statement arrives with the value baked into the SQL text and no parameters at all. That reintroduces injection, blocks the database's statement cache from reusing a plan, and mangles quoting and types. The `%s` in pyformat is the driver's placeholder notation, not Python formatting; the value belongs in the second argument to `execute()`.

saying these in an interview costs you the question

Thinks paramstyle can be set per connection or per cursor
Assumes ? placeholders work with every Python database driver
Uses the % operator because the style is called pyformat
Passes a bare string as the parameters for one placeholder
Believes a placeholder can stand in for a table or column name
Assumes an undeclared placeholder style will work anyway

What does a DB-API driver's paramstyle attribute declare, and why does it hurt portability?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

What the attribute is

Why it is a portability problem

The traps around the placeholders themselves

Where the value is bound

▾common follow-ups3 questions

▾red flags6 flags

§ context

▾related questions3 questions

▾related topics4 topics

What the attribute is

Why it is a portability problem

The traps around the placeholders themselves

Where the value is bound

answer

go deeper

context