Python SQLite
Tutorial 51 of 65 · pythondeck.com Python course
The standard library bundles sqlite3, an embedded SQL database in a single file - perfect for desktop apps, prototypes and tests.
SQLite embeds a full SQL engine in a single file—zero server setup, perfect for prototypes, mobile backends, and edge caches. Python ships sqlite3 in the standard library.
Mastering transactions, parameter binding, and schema migrations avoids corruption and injection bugs in local apps.
Desktop tools, mobile apps, and CI test databases all reuse the same file-based mental model—copy the .db file to reproduce a bug.
Connection context — with sqlite3.connect(...) as conn commits on success.
Parameter binding — ?` or `:name` placeholders; never interpolate user strings.
Row factory — sqlite3.Row for dict-like access.
PRAGMA — foreign_keys=ON, journal_mode, synchronous levels for durability trade-offs.
Types — dynamic affinity; be explicit in CREATE TABLE.
Concurrency — one writer; readers concurrent; WAL mode for many readers.
ATTACH / DETACH — join multiple database files in one query for sharded local archives.
SQLite excels under read-heavy, single-writer workloads. For web apps, enable WAL and keep writes short. Use application-level migrations (Alembic can target SQLite for dev). Backup is file copy while checkpointing or via Online Backup API.
When data outgrows single-machine limits or you need concurrent writers, plan a move to PostgreSQL—but keep SQLite for tests and offline mode.
String-formatting SQL with f-strings, opening SQL injection paths.
Sharing connections across threads without serializing writes.
Storing timezone-naive timestamps without documenting convention.
Huge blobs in rows without considering page size and vacuum needs.
Enable foreign keys and use explicit transactions for multi-statement updates.
Index columns used in WHERE and JOIN; EXPLAIN QUERY PLAN slow statements.
Use pathlib for DB path; single directory per environment.
Test migrations on a copy of production schema snapshots.
Use executemany for bulk inserts inside a single transaction to maximize throughput.
Re-read the examples below with these ideas in mind; change variable names and inputs to match your own project.
The program below demonstrates connect + create. Read the comments on each line, run the code, then change names or values to see how the output shifts.
# Example: Connect + create
# Run in the REPL or save as a .py file and execute with python.
import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE user (id INTEGER PRIMARY KEY, name TEXT)")
con.executemany("INSERT INTO user(name) VALUES (?)",
[("Ada",), ("Grace",), ("Linus",)])
con.commit()This sample walks through query in a small, runnable script. Paste it into the REPL or save it as a .py file before you continue to the next block.
# Example: Query
# Run in the REPL or save as a .py file and execute with python.
import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE t(x INTEGER)")
con.executemany("INSERT INTO t VALUES(?)", [(i,) for i in range(5)])
for row in con.execute("SELECT x*x FROM t WHERE x > 2"):
print(row)Here is a hands-on illustration of row factory. Follow the inline comments first; only then execute the snippet and compare the result with what you expected.
# Example: Row factory
# Run in the REPL or save as a .py file and execute with python.
import sqlite3
con = sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row
cur = con.execute("SELECT 1 AS one, 'hi' AS msg")
row = cur.fetchone()
print(row["one"], row["msg"])The program below demonstrates sqlite crud. Read the comments on each line, run the code, then change names or values to see how the output shifts.
# sqlite3 ships with Python — file DB needs no server
import sqlite3 # DB-API driver
conn = sqlite3.connect("app.db") # file-backed database
conn.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)") # schema
conn.execute("INSERT INTO users(name) VALUES (?)", ("Ada",)) # parameterized insert
conn.commit() # persist transaction
rows = conn.execute("SELECT id, name FROM users").fetchall() # read all
print(rows) # list of tuples
conn.close() # release connectionThis sample walks through row factory in a small, runnable script. Paste it into the REPL or save it as a .py file before you continue to the next block.
# row_factory exposes columns by name
import sqlite3 # sqlite3
conn = sqlite3.connect("app.db") # reopen database
conn.row_factory = sqlite3.Row # dict-like rows
cur = conn.execute("SELECT id, name FROM users LIMIT 1") # query
row = cur.fetchone() # single row
print(row["name"], row["id"]) # named access
conn.execute("UPDATE users SET name=? WHERE id=?", ("Augusta", row["id"])) # update
conn.commit() # save
conn.close() # close