PHASE 15Intermediate ~28 min· topic 6 of 10

Topic 15.6

Databases from Python: sqlite3 and Beyond

In one line

Python can store data in a database through a driver. The built-in sqlite3 module needs no server; whatever the database, always pass values to SQL as parameters (placeholders), never by pasting text, and group changes in transactions so they succeed or fail together.

Think of it like this

A school office with filing cabinets. Papers (data) are kept in labelled drawers (tables) with a fixed form (columns). You do not wander through the drawers yourself: you hand a clerk a written request, such as "bring me every form where the name is Asha". A database is the cabinet, SQL is the language of the written request, and a driver is the messenger from your Python program to the clerk.

Words you'll meet

New words in this topic, in plain English. Come back here whenever one feels fuzzy.

Database: Organised storage that programs query instead of reading raw files.
SQL: The language used to ask a relational database for data and to change it.
Table, row, column: A table holds rows (one record each); each row has values in columns.
Driver: A library that lets Python talk to a particular database. sqlite3 is built in.
Parameterised query: A SQL statement with placeholders (?) and the values passed separately.
SQL injection: An attack where input is treated as SQL code because it was pasted into a query.
Transaction: A group of changes that all succeed or are all undone.
Commit / rollback: Commit saves the transaction's changes; rollback undoes them.
Constraint: A rule the database enforces, such as NOT NULL, UNIQUE or CHECK.
ORM: A library that maps database tables to Python classes. SQLAlchemy is the best known.

Step by step

01Connect, create a table, insert

sqlite3.connect(":memory:") makes a database that lives only while the program runs. executemany runs one statement for each tuple in a list. Changes are not saved until you commit (or use with conn:).

main.pywhole filepython

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, plan TEXT NOT NULL)")
conn.executemany("INSERT INTO users (name, plan) VALUES (?, ?)",
                 [("Asha", "free"), ("Ben", "pro")])
conn.commit()

02Query with placeholders

The values go in a tuple (note the comma in (name,) for a single value). The statement text never changes, whatever the value is.

main.pywhole filepython

row = conn.execute("SELECT id, name FROM users WHERE name = ?", ("Ben",)).fetchone()
print(row)    # (2, 'Ben')

03Why pasting values is dangerous

With an f-string, the value becomes part of the SQL text. A name with a quote ("O'Brien") already breaks the statement with a syntax error. A hostile value would not just break it, it could change its meaning, so the rule is simple and absolute: user-controlled values go in as parameters, always.

Why pasting values is dangerousdiagram

Rendering diagram…

04Transactions with the connection as a context manager

with conn: wraps the block in a transaction. If the block finishes, the changes are committed. If an exception escapes, they are rolled back and the exception continues. It does not close the connection; call conn.close() yourself.

main.pywhole filepython

def transfer(conn, src, dst, amount):
    with conn:
        conn.execute("UPDATE accounts SET balance = balance - ? WHERE name = ?", (amount, src))
        conn.execute("UPDATE accounts SET balance = balance + ? WHERE name = ?", (amount, dst))

05Let the database refuse bad data

A CHECK (balance >= 0) constraint means no code path can create a negative balance. The failed UPDATE raises sqlite3.IntegrityError, the with conn: block rolls back the first half of the transfer, and both accounts are left as they were.

06Rows by name

Set conn.row_factory = sqlite3.Row to read columns by name. This makes code that survives column reordering, and dict(row) converts a row for JSON output.

07Beyond sqlite3

With PostgreSQL you use a server, a connection string from the environment (Topic 15.3), a pool of connections, and often SQLAlchemy. The ideas are the same; the placeholder style differs (%s for psycopg). The block in the code section shows SQLAlchemy's style: the text() query with a named :name placeholder.

Try it yourself

1
Find a user
In the first example add a lookup for "Asha" with a placeholder and print only the id. Then try passing the value without the tuple comma (("Asha") instead of ("Asha",)) and read the error you get: a string is a sequence of characters, so sqlite3 sees four values (A, s, h, a) for one placeholder and raises ProgrammingError: Incorrect number of bindings supplied.
2
Provoke an IntegrityError
In the second example insert a row with a negative balance, or insert "asha" twice (the primary key rejects the duplicate). Predict the message first.
3
Remove the with block
Replace with conn: with a plain block and no commit. The failed transfer now leaves the first UPDATE applied inside an open transaction. Check conn.in_transaction and balances(conn), then call conn.rollback().

Code & diagrams

Parameterised queries and the quote that breaks pasted SQL New tab

A users table is a small version of a real app's customer list. Look at the last two queries: a value that merely looks like SQL is just a name nobody has, so it matches nothing.

Expected output

count: 3
safe lookup: (3, "O'Brien")
pasted SQL failed: near "Brien": syntax error
odd name matches: []
1 Asha {'id': 1, 'name': 'Asha', 'plan': 'free'}
3 O'Brien {'id': 3, 'name': "O'Brien", 'plan': 'free'}

A transaction that rolls back New tab

The second transfer fails halfway. Because of with conn:, the first UPDATE is undone too.

Expected output

after good transfer: {'asha': 70, 'ben': 50}
rejected: CHECK constraint failed: balance >= 0
after failed transfer: {'asha': 70, 'ben': 50}
in a transaction? False

SQLAlchemy and PostgreSQL (need pip install and a server)python

Not runnable here: it needs packages and a running PostgreSQL server. The connection string comes from the environment, never from the code. In this ORM-free core style the query still uses a named placeholder.

# pip install sqlalchemy "psycopg[binary]"
import os
from sqlalchemy import create_engine, text

engine = create_engine(os.environ["DATABASE_URL"])   # e.g. postgresql+psycopg://user:pass@host/db

with engine.begin() as conn:                          # begins a transaction, commits at the end
    rows = conn.execute(
        text("SELECT id, name FROM users WHERE plan = :plan"),
        {"plan": "free"},
    ).all()

Break it on purpose

Errors are the best teachers. Make each change, read the error, guess what went wrong, then reveal the answer.

Break #1

Wrong number of placeholders

Pass two values for one placeholder.

main.pywhole filepython

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE t (a TEXT)")
conn.execute("SELECT * FROM t WHERE a = ?", ("x", "y"))

terminal

$ python main.py

── what you'll see ──

Traceback (most recent call last):

File "main.py", line 5, in <module>

conn.execute("SELECT * FROM t WHERE a = ?", ("x", "y"))

~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 2 supplied.

Myth vs fact

Myth

Escaping quotes myself is just as safe as parameters.

Fact

Hand-made escaping is easy to get wrong and differs between databases. Parameters are built into the driver and are the standard defence.

Myth

SQL injection only matters for big websites.

Fact

Any program that builds a query from text it did not write is at risk, including small scripts, internal tools and AI agents that turn model output into SQL.

Myth

SQLite is only a toy.

Fact

It is one of the most widely deployed databases in the world, used in phones, browsers and applications. Its limits are about many writers at once and network access, not about correctness.

Pro corner

Extra depth for experienced readers. New to this? Skip it for now and come back later.

▸
Defence in depth: parameters stop injection, and the database account should also have only the rights it needs (read-only for reporting code, no DROP rights for the web app). If an AI agent can run SQL, give it a read-only role and a restricted set of tables.
▸
Open one connection per task or use a pool; sqlite3 connections should not be shared between threads without care (check_same_thread). With server databases a pool avoids paying connection set-up per request.
▸
Transaction control in sqlite3 has two modes. The old default (autocommit=sqlite3.LEGACY_TRANSACTION_CONTROL, which is -1) quietly opens a transaction before INSERT, UPDATE and DELETE and leaves it open until you commit(), so forgetting the commit loses the writes when the connection closes. Since Python 3.12 the connection has an autocommit attribute: autocommit=False means a transaction is always open (a new one starts right after each commit or rollback), and autocommit=True means every statement commits on its own and you manage BEGIN yourself. The docs recommend connect(..., autocommit=False) for new code. In every mode, with conn: commits on success and rolls back on an exception, and it does not close the connection (contextlib.closing(conn) does).
▸
SQLite has two defaults that surprise people. Foreign keys are not enforced until you run PRAGMA foreign_keys = ON on each new connection, so a REFERENCES clause alone lets you insert a child row whose parent does not exist. And columns have type affinity, not strict types: a TEXT value can be stored in an INTEGER column unless you add a CHECK or create the table with STRICT (SQLite 3.37+). For concurrency, a writer locks the whole database file. PRAGMA journal_mode=WAL lets readers continue while one writer works, and a timeout= on connect makes a blocked writer wait instead of failing at once with database is locked.
▸
Schema changes in real projects are tracked as migrations (tools such as Alembic). Never edit the production schema by hand. See the Database Design course (/database-design) for indexes, normalisation and query plans.

Remember this

1
A relational database stores data in tables of rows and columns, and you ask for data with SQL (SELECT, INSERT, UPDATE, DELETE). The Database Design course on Hectal (/database-design) teaches tables, keys, normalisation and indexes in depth; this topic covers using a database from Python. Think of a table as a spreadsheet: each row is one record, each column one kind of fact.
2
**sqlite3** is part of Python's standard library. SQLite is a complete SQL database stored in one file (or in memory with ":memory:") with no server to install. It is excellent for learning, tests, small tools and local apps. You open a connection with sqlite3.connect(...), run statements with conn.execute(sql, values), and read rows with fetchone(), fetchall() or by looping over the result.
3
Never build SQL by pasting values into the text (f-strings, +, %). A value that contains a quote breaks the statement, and a value crafted by an attacker can change what the statement *does*: that attack is called SQL injection, one of the oldest and most damaging web vulnerabilities. The defence is a parameterised query: write the SQL with placeholders (? in sqlite3) and pass the values separately: conn.execute("SELECT * FROM users WHERE name = ?", (name,)). The database receives the statement and the values separately, so a value can only ever be data, never SQL. Placeholders work for values only; table and column names cannot be parameters, so build those from a fixed allow-list.
4
A transaction is a group of changes that succeed or fail as one unit, like moving money: subtract here, add there. If anything fails in the middle, rollback undoes all of it. In sqlite3, with conn: (Topic 7.4) commits the transaction when the block ends normally and rolls back if an exception is raised. Constraints (NOT NULL, PRIMARY KEY, CHECK, UNIQUE, and foreign keys, which SQLite enforces only after PRAGMA foreign_keys = ON) make the database itself refuse bad data and raise sqlite3.IntegrityError.
5
By default a row is a tuple, so you read it by position (row[1]). Set conn.row_factory = sqlite3.Row and you can also read by column name (row["name"]) and convert with dict(row). Values come back as Python types (INTEGER as int, TEXT as str, NULL as None); SQLite is loose about types, so use CHECK constraints when you need strictness.
6
For bigger systems you use a server database, most often PostgreSQL, through a driver (psycopg) or an ORM such as SQLAlchemy, which maps tables to Python classes. The DB-API habits transfer: connect, execute with parameters, commit, close (other drivers use %s or named placeholders instead of ?). Async services use async drivers (Topic 12.6). AI applications also store vectors in databases (Postgres with the pgvector extension or dedicated vector stores, Topic 16.8), and still need exactly the safe-query habits taught here.

Explain it without notes

What is SQL injection and how do parameterised queries prevent it?

What is a transaction, and how does with conn: behave in sqlite3?

Why can you not use a placeholder for a table name, and what do you do instead?

What does row_factory = sqlite3.Row change?

A teammate builds a query with an f-string and says "it is only an internal tool". How do you respond?

Practice

Write find_user(conn, name) that returns the row for a name using a placeholder, or None if it does not exist.

Write add_user(conn, name, plan) that inserts a user inside with conn: and returns the new id (cursor.lastrowid).

Write count_by_plan(conn) returning a dictionary such as {'free': 2, 'pro': 1} using GROUP BY.

Trade-offs

↔
Raw SQL versus an ORM. Raw SQL is explicit and fast to reason about for queries, but repetitive. An ORM reduces boilerplate and maps rows to classes, but can hide inefficient queries. Many teams mix both.
↔
SQLite versus a server database. SQLite is zero-setup and perfect for local and embedded use. A server database such as PostgreSQL handles many concurrent writers, network access and richer features, at the cost of running and securing a server.

Done when you can

Done when you can create a table, insert and query rows with sqlite3.
Done when every query with a variable value uses placeholders, and you can explain why.
Done when you can use with conn: for transactions and explain rollback.
Done when you can name the exception raised by a violated constraint.
Done when you can say where an ORM and a server database fit (see /database-design for more).

Command Palette

Words you'll meet

Step by step

01Connect, create a table, insert

02Query with placeholders

03Why pasting values is dangerous

04Transactions with the connection as a context manager

05Let the database refuse bad data

06Rows by name

07Beyond sqlite3

Try it yourself

Find a user

Provoke an IntegrityError

Remove the with block

Code & diagrams

Break it on purpose

Wrong number of placeholders

Myth vs fact

Pro corner

Remember this

Explain it without notes

Practice

Trade-offs

Done when you can