Talking to a database from Python: sqlite3 and parameterized queries

The Python side of a database

The SQL course on this platform teaches the query language: SELECT, joins, aggregation, window functions. This lesson teaches the other half, the part that lives in your Python file. Writing a correct query is not enough if the code around it splices user input into the statement, forgets to commit, or hands the rest of the program a tuple when it expected a record.

sqlite3 is in the standard library, so there is nothing to install. Every driver you will meet later (psycopg for Postgres, mysqlclient, pyodbc) follows the same DB-API shape, so the four moves below transfer unchanged.

Connect, execute, fetch

import sqlite3

conn = sqlite3.connect("app.db")   # a file, or ":memory:" for a throwaway database
cur = conn.cursor()                # a cursor holds one statement and its result rows

cur.execute("SELECT id, name FROM users WHERE active = 1")
rows = cur.fetchall()              # [(1, 'Ada'), (2, 'Sam')]
conn.close()

The connection is the session, the cursor is the thing that runs a statement and walks its result. fetchall() returns every remaining row at once, fetchone() returns the next row or None, and iterating the cursor streams rows one at a time (the right choice for a large table).

Never build the query text out of values

This is the single most important habit in the lesson. Here is the version that looks fine:

name = "Ada"
cur.execute(f"SELECT id FROM users WHERE name = '{name}'")   # WRONG

It works. It keeps working. It ships. Then a customer named O'Brien signs up, and the statement the database receives is WHERE name = 'O'Brien', which closes the string literal after the O and fails to parse. The same hole that an apostrophe trips by accident is the hole an attacker types on purpose: a name of '; DROP TABLE users; -- ends your statement early and starts a new one.

The fix is to send the statement and the values as two separate things. A ? marks a slot, and the values ride alongside in a sequence:

cur.execute("SELECT id FROM users WHERE name = ?", (name,))
cur.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Ada", "ada@example.com"))

The SQL text is now a constant. It does not change when name changes, so nothing a user types can alter the shape of the statement. The driver ships the values over separately and the database treats them as data, never as syntax. As a bonus, the database can cache the plan for a statement it has already seen.

Note the trailing comma in (name,). Without it (name) is just a parenthesized string, and the driver reads each character as a separate parameter.

Table

Parameterizing does not escape the value, it moves the value out of the statement entirely.

what the driver sends	f-string version	parameterized version
statement text	WHERE name = 'O'Brien'	WHERE name = ?
values	(none, they are in the text)	('O'Brien',)
result	syntax error, or an injection	one row, every time

One limit worth knowing: a placeholder can only stand in for a value, never for a table name, a column name, or a keyword like ASC. SELECT * FROM ? is not valid SQL. When an identifier really has to vary, check it against a fixed allowlist of names your own code owns, then interpolate that checked name.

Writes are transactions

Every write runs inside a transaction. Nothing is durable until you commit:

cur.execute("INSERT INTO users (name) VALUES (?)", ("Ada",))
conn.commit()      # without this, the row disappears when the connection closes

Better, let the connection manage it for you. Used as a context manager it commits when the block exits cleanly and rolls back when an exception escapes:

with conn:
    cur.execute("INSERT INTO users (name) VALUES (?)", ("Ada",))
    cur.execute("INSERT INTO audit (action) VALUES (?)", ("created user",))
# both rows landed, or neither did

For many rows, executemany runs one statement against a sequence of parameter tuples in a single transaction, which is far faster than a loop of execute calls.

What runs where

sqlite3 is stdlib in CPython, but it is not bundled into the Python build that powers this browser sandbox, so import sqlite3 fails here. Open a terminal in the environment you set up in "Running Python & installing packages" and every snippet above runs as written, against a real file. The exercises below grade the skills that surround the connection, which is where the bugs actually live: building the statement and its parameters as two separate things, auditing query text that was built by formatting, and turning the driver's tuples into records the rest of your program can read.

Interview nuance: "why parameterize?" has a weak answer and a strong one. The weak answer is "to stop SQL injection". The strong answer is that a parameterized statement and its values are sent to the database as two different things, so user input is never parsed as syntax. Injection is what that prevents; escaping is a different and worse strategy that tries to neutralize dangerous characters in text you have already merged. Say the second one, then add that placeholders bind values only, so a varying table or column name needs an allowlist instead.