Talking to a Database
sqlite3 from the standard library: parameterised queries, real transactions, and closing what you opened.
conn.execute("SELECT * FROM orders WHERE sku = ?", (sku,)) # never an f-stringsqlite3 ships with Python, needs no server, and implements the same DB-API 2.0 interface as the PostgreSQL and MySQL drivers, so what you learn here transfers. sqlite3.connect(database, timeout=5.0, detect_types=0, isolation_level='DEFERRED', check_same_thread=True, factory=sqlite3.Connection, cached_statements=128, uri=False, *, autocommit=sqlite3.LEGACY_TRANSACTION_CONTROL) opens it; those positional parameters are deprecated as of 3.13 and become keyword-only in 3.15, so pass them by name.
Placeholders, not formatting
Values go in as parameters. Cursor.execute(sql, parameters=(), /) accepts qmark style, WHERE sku = ? with a sequence, or named style, WHERE sku = :sku with a dict. The driver sends the statement and the values separately, so a value containing ' or -- is only ever a value. Build the same query with an f-string and a crafted value changes the statement — which is how data gets copied out of databases. The standard library documentation is explicit: always use placeholders instead of string formatting. Placeholders bind values only; a table or column name cannot be parameterised, so validate those against a list you wrote.
Transactions and closing
with conn: commits on success and rolls back if the block raises — but it does not close the connection, and that catches people. Since 3.13 a connection garbage-collected without close() emits a ResourceWarning. Use contextlib.closing, or a try/finally, around the with.
Set conn.row_factory = sqlite3.Row and rows become mappings you can index by column name, which survives someone adding a column in the middle of a SELECT *.
Example
When to use it
- Giving a small service durable storage without running a database server.
- Reviewing a query built with an f-string and knowing exactly what to say about it.
- Making a multi-step write all-or-nothing.
- Returning rows by column name so a schema change does not reshuffle your tuples.
More examples
The injection, side by side
Run this once and the rule stops being abstract. The same mistake with a DELETE or an UPDATE does not just read data, it destroys it — and note that executescript, used above for setup, deliberately accepts multiple statements, so it must never see untrusted input.
import sqlite3
conn = sqlite3.connect(":memory:")
conn.executescript("CREATE TABLE users(name TEXT, email TEXT);"
"INSERT INTO users VALUES('ada','[email protected]');")
hostile = "x' OR 1=1 --" # whatever the client typed into a search box
# WRONG. The value becomes part of the statement, the WHERE is always true,
# and every row in the table leaves the building.
sql = f"SELECT * FROM users WHERE name = '{hostile}'"
print("formatted:", conn.execute(sql).fetchall()) # all rows
# RIGHT. Statement and data travel separately; nothing in the value can be code.
print("bound:", conn.execute(
"SELECT * FROM users WHERE name = ?", (hostile,)).fetchall()) # no rows
conn.close()All of it or none of it
The pattern for any multi-statement change. Without the transaction you can decrement stock for an order that was never written — and the foreign_keys pragma is worth knowing about, because SQLite ignores foreign key constraints unless you switch them on per connection.
import sqlite3
from contextlib import closing
# closing() guarantees the close; `with conn` handles the transaction. Two
# different jobs, which is why you often want both.
with closing(sqlite3.connect("app.db")) as conn:
conn.execute("PRAGMA foreign_keys = ON") # sqlite does NOT enforce FKs by default
try:
with conn: # BEGIN ... COMMIT, or ROLLBACK on raise
cur = conn.execute(
"INSERT INTO orders(sku, qty) VALUES(:sku, :qty)",
{"sku": "A-1", "qty": 2}, # named style needs a dict
)
order_id = cur.lastrowid
conn.execute("UPDATE stock SET qty = qty - :qty WHERE sku = :sku",
{"sku": "A-1", "qty": 2})
conn.execute("INSERT INTO ledger(order_id) VALUES(?)", (order_id,))
except sqlite3.IntegrityError as exc:
print("rolled back, nothing was written:", exc)Explicit transaction control (Python 3.12+)
Use this when a transaction's boundaries matter and you would rather not reason about sqlite3's legacy implicit-BEGIN rules. It also makes the code read the same as it would against PostgreSQL.
import sqlite3
# autocommit was added in 3.12. False means PEP 249 behaviour: a transaction is
# always open and you commit or roll back yourself -- no implicit BEGIN based on
# what kind of statement you happened to run.
conn = sqlite3.connect("app.db", autocommit=False)
try:
conn.execute("UPDATE accounts SET cents = cents - 500 WHERE id = ?", (1,))
conn.execute("UPDATE accounts SET cents = cents + 500 WHERE id = ?", (2,))
conn.commit()
except Exception:
conn.rollback()
raise
finally:
conn.close()
# The default is still LEGACY_TRANSACTION_CONTROL, which the docs say will
# change to False in a future release -- so being explicit now is free.
Discussion