skip to content

sqlite3 in Applications

13 roadmaps4 questions

A whole database in one file, opened with connect and queried through the same cursors as any driver. Interviewers ask about placeholders vs f-strings, transaction control, WAL and locking errors.

part ofPythonoverview, primer and where to startread it →
on this pageshow

explore

questions

4

In Python's sqlite3, why pass values as ? placeholders instead of building the SQL with an f-string?

level: juniorimportance: must knowfreq 75%

answer

  1. Two arguments, not one string
  2. Compile first, bind afterwards
  3. Values only, never identifiers
  4. One value still needs a trailing comma
  5. qmark ? or named :name

basics

~20 s

sqlite3 compiles the statement once and binds each Python value as a typed parameter, so no quoting or escaping ever happens. An f-string splices the value into the statement text, where it can change what the SQL means.

solid answer

~50 s

`Cursor.execute` takes a second argument: a sequence for qmark style (`?`) or a mapping for named style (`:name`). sqlite3 hands the SQL text to SQLite to compile, then binds the parameters into the compiled statement, so a value is never parsed as SQL - it arrives as INTEGER, REAL, TEXT, BLOB or NULL, and the conversion from `int`, `float`, `str`, `bytes` or `None` is done for you. With an f-string the value becomes part of the statement text, so a quote character or an appended clause changes the statement's structure, and `None`, `bytes` and floats need hand-written quoting you will eventually get wrong. Two traps worth naming: a single value still needs a one-element tuple, `(name,)`, and placeholders bind **values only** - table and column names cannot be parameterized, so those must come from a whitelist you control.

code

python · 8 lines
python
import sqlite3

con = sqlite3.connect(':memory:')
con.execute('CREATE TABLE place(name TEXT, lat REAL)')
con.execute('INSERT INTO place VALUES (?, ?)', ('Oslo', 59.91))
con.execute('INSERT INTO place VALUES (:name, :lat)', {'name': 'Lima', 'lat': -12.05})
print(con.execute('SELECT name FROM place WHERE lat > ?', (0,)).fetchall())
con.close()

go deeper

for a junior

Recall the shape: SQL with ? in one argument, a tuple of values in the second, and the trailing comma when there is only one value. Be ready to rewrite an f-string query as a parameterized one on the spot.

for a middle

Explain the mechanics: SQLite compiles the text, then values are bound into the compiled statement as typed parameters. Know that identifiers cannot be bound and be able to show the whitelist pattern for a dynamic column.

for a senior

Show the habit is codebase-wide: a review rule that no SQL string is ever f-formatted with data, a helper for variable-length IN lists, and adapters registered once for the domain types your rows carry.

for a principal

Own the enforcement story rather than the rule: a linting gate on string-formatted SQL, one data-access layer that owns query construction, and a decision about whether raw sqlite3 or a query builder is the right floor for your team.

### What execute actually receives `Cursor.execute(sql, parameters)` - and `Connection.execute`, which creates a cursor for you and returns it - takes the statement text and the values as two separate arguments. sqlite3 passes the text to SQLite, which parses and compiles it into a prepared statement, and only then binds each Python object into a numbered slot of that compiled statement. The parser has finished its work before your data arrives. That ordering is the whole point: no value can close a quote, append a clause, or terminate the statement, because by the time the value exists inside SQLite the shape of the statement is already fixed. The module advertises its default style as `sqlite3.paramstyle`, which is `"qmark"`. ### The two placeholder styles **qmark style** puts a bare `?` in the SQL and passes a sequence of values, matched positionally: ```python cur.execute('SELECT lat, lon FROM place WHERE name = ?', (name,)) ``` **named style** puts `:name` in the SQL and passes a mapping: ```python cur.execute('SELECT lat, lon FROM place WHERE name = :name', {'name': name}) ``` Named style is worth reaching for when the same value appears more than once in a statement, and when a long INSERT would otherwise turn into a column-counting exercise where a swapped pair of arguments produces a silently wrong row rather than an error. Supplying a *sequence* where the SQL uses named placeholders was deprecated in Python 3.12; pass a mapping. ### The one-value trap The most common beginner error is passing the bare value instead of a one-element tuple. `(name)` is just `name` - the parentheses are grouping, not a tuple - so the trailing comma in `(name,)` is load-bearing. When the number of supplied bindings does not match the number of placeholders in the compiled statement, sqlite3 raises `sqlite3.ProgrammingError` rather than guessing. ### What placeholders cannot stand in for A placeholder is a *value* slot in the compiled statement, so it can never be an identifier or a piece of syntax. `SELECT * FROM ?` does not compile; SQLite rejects it and sqlite3 surfaces `sqlite3.OperationalError`. The same applies to a column name in an ORDER BY, a sort direction, and the number of items in an `IN` list. The correct patterns are: - **identifiers**: map the caller's input through a dict or a tuple of allowed names, and use the value you looked up, never the string you were given; - **IN lists**: build exactly as many `?` marks as you have values, `','.join('?' * len(values))`, and pass the values as the parameter sequence; - **LIKE patterns**: bind the *whole* pattern as one value - build `'%' + term + '%'` in Python and pass it as a parameter, leaving `LIKE ?` in the SQL. ### Types, not text Binding is typed. `None` becomes NULL, `int` becomes INTEGER, `float` becomes REAL, `str` becomes TEXT and `bytes` becomes BLOB. Anything else raises unless you register an adapter with `sqlite3.register_adapter` or the object implements the module's conform protocol. This is the reason the argument for placeholders is not only a security argument: an f-string forces you to invent a text representation for every type, and the representations for `None`, for a float that round-trips, and for binary data are exactly the ones people get wrong. A parameterized statement sidesteps the whole question. ### Batching and statement reuse `executemany` takes the same SQL plus an iterable of parameter sequences, one per row, and reuses a single compiled statement across all of them. Connections also keep a small cache of compiled statements, sized by `connect`'s `cached_statements` argument. Neither mechanism can help a query built with an f-string: every distinct value produces a distinct statement text, so every call pays a fresh compile and the cache fills with single-use entries. Parameterization is the faster habit as well as the correct one. ### A word on what this is not Parameterization is often taught only as an injection defence, and that framing undersells it. The mechanism is a *typing and parsing* boundary that happens to close an injection class as a consequence. Even in a program where every value is generated internally and no attacker exists, an apostrophe in a place name, a `None` where a value was expected, or a float rendered with a locale-dependent separator will break an f-string query and will not break a parameterized one. That is why the rule is unconditional rather than risk-assessed: there is no category of value for which splicing is the better choice, so there is no judgement call to make at the call site. ### The honest summary Placeholders are how you tell SQLite which parts of the request are *structure* and which parts are *data*. f-strings erase that distinction, and everything downstream - correctness of quoting, type fidelity, statement reuse, and the ability to reason about what a query can possibly do - depends on it being preserved.

  • You need the sort column chosen at runtime. How do you do it, given placeholders cannot bind identifiers?
    Keep a mapping from the caller's token to a column name you wrote yourself, look the token up, and fail closed if it is missing. The SQL is then assembled from strings that only ever came from your own dictionary, and the remaining values still travel as parameters. Never interpolate the caller's string, even after checking it looks harmless.
  • How do you filter on a list of values whose length is not known until runtime?
    Build the placeholder run to match: `marks = ','.join('?' * len(values))`, then execute `f'SELECT ... WHERE id IN ({marks})'` with `values` as the parameter sequence. The f-string here interpolates only question marks that you generated, never data. Watch SQLite's limit on the number of bound parameters for very large lists, and chunk the query if you approach it.
  • What happens when you bind a value whose Python type sqlite3 does not know, such as a Decimal?
    It raises rather than guessing a representation. You either convert at the call site to `str`, `int`, `float` or `bytes`, or register a conversion once with `sqlite3.register_adapter` so every bind of that class goes through the same function. Registering an adapter is the better choice when the type appears across a codebase, because one place decides the storage representation.

A prepared statement is a form printed with blank boxes: the printing happens first, then someone fills the boxes. An f-string lets the person filling in a box also rewrite the questions on the form.

saying these in an interview costs you the question

  • Says escaping quotes in an f-string is equivalent
  • Thinks a placeholder can hold a table or column name
  • Passes a bare value instead of a one-element tuple
  • Believes sqlite3 parses the parameters as SQL
  • Claims placeholders only matter for untrusted input

context

resolving links…

open as a page

What does Python's sqlite3 do about transactions by default, and how does the autocommit attribute change it?

level: middleimportance: must knowfreq 60%

basics

~20 s

By default sqlite3 opens a transaction implicitly before an INSERT, UPDATE, DELETE or REPLACE and leaves it open until you call Connection.commit; nothing else commits for you. Setting Connection.autocommit to True or False replaces that legacy behaviour with explicit modes.

open as a page

How do you make Python's sqlite3 return rows as something other than plain tuples?

level: middleimportance: should knowfreq 45%

basics

~20 s

Assign a callable to Connection.row_factory, most often the built-in sqlite3.Row, which yields rows addressable by column name as well as index. A custom factory receives the cursor and the raw tuple and can return a dict, a namedtuple or any object.

open as a page

A geocoding batch writing to one SQLite file intermittently raises sqlite3.OperationalError 'database is locked' - how do you diagnose and fix it?

level: seniorimportance: should knowfreq 40%

basics

~20 s

SQLite allows one writer at a time, and the error means another connection held the write lock for longer than sqlite3.connect's timeout, which defaults to five seconds. Shorten transactions, switch the file to WAL, raise the timeout, and give each worker its own connection.

open as a page