sqlite3 Cheat Sheet

1Connect & cursoropen a database

import sqlite3 con = sqlite3.connect("app.db")★
Opens (or creates) a database file. Use ":memory:" for a throwaway in-RAM DB — ideal for tests.
cur = con.cursor() cur.execute("SELECT 1"); cur.fetchone()★
A cursor runs statements and holds results. Or skip it: con.execute(...) is a shortcut that returns a temporary cursor.
con = sqlite3.connect("file:app.db?mode=ro", uri=True)
URI filenames unlock options like read-only (mode=ro) and shared cache.
con.close() # release the file handle★
Always close when done. Note: with con: does NOT close (card 5) — it's a transaction block.

2Execute & parametersnever format SQL by hand

cur.execute("SELECT * FROM user WHERE id = ?", (uid,))★
qmark placeholders. Pass a tuple of values — even for one, note the trailing comma (uid,). SQLite substitutes safely.
cur.execute("... WHERE name = :name", {"name": n})★
Named placeholders take a dict — clearer for many params.
cur.executescript(""" CREATE TABLE t(id INTEGER PRIMARY KEY, name TEXT); CREATE INDEX ix ON t(name); """)
Run multiple statements at once (schema setup). Note: executescript issues an implicit COMMIT first.
# NEVER: f-strings / .format / % into SQL — injection cur.execute(f"... WHERE id = {uid}") # WRONG
String-building SQL is an injection hole. Always use ?/named params for values.

3Fetch & Rowread results

row = cur.fetchone() # one tuple or None rows = cur.fetchall() # list of tuples some = cur.fetchmany(100)★
Pull results after an execute. fetchone returns None when exhausted.
for row in cur.execute("SELECT id, name FROM user"): ... # iterate lazily, memory-friendly★
A cursor is iterable — the idiomatic way to stream rows without loading all of them.
con.row_factory = sqlite3.Row row = con.execute("SELECT * FROM user").fetchone() row["name"] · row.keys()★
Access columns by name (and still by index). sqlite3.Row is the go-to row factory; set a plain dict factory for JSON output.

4Insert, update, bulkand commit

cur.execute("INSERT INTO user(name) VALUES(?)", ("Ada",)) con.commit() # writes are NOT saved without this★
Commit to persist. In the default (legacy) mode an open transaction holds your changes until commit().
cur.executemany("INSERT INTO user(name) VALUES(?)", [("Ada",), ("Alan",), ("Grace",)])★
Bulk insert from a sequence (or generator) of param tuples — far faster than a Python loop.
cur.lastrowid # id of the row just inserted cur.rowcount # rows affected by UPDATE/DELETE★
Grab the new primary key, or how many rows an UPDATE/DELETE touched.
con.total_changes # rows changed since connect
A running total across the connection — handy in tests.

5Transactions & autocommit3.12+ control

with con: con.execute("INSERT INTO user(name) VALUES(?)", ("x",)) # commits on success, ROLLS BACK on exception★
The clean pattern. The connection as a context manager wraps a transaction. Remember: it commits/rolls back but does not close the connection.
con.commit() · con.rollback()★
Manual control — save or discard the current transaction.
con = sqlite3.connect("app.db", autocommit=False)3.12+
Modern PEP 249 transaction control. autocommit=False = explicit transactions you end with commit()/rollback(); True = every statement auto-commits. Prefer this over the old isolation_level.
# default is sqlite3.LEGACY_TRANSACTION_CONTROL con.autocommit # will default to False in a future Python
Until then, legacy mode auto-opens transactions before DML and needs an explicit commit().

6Types & convertersPython ↔ SQLite

# native: None→NULL int→INTEGER float→REAL str→TEXT bytes→BLOB★
SQLite has five storage classes. Everything else needs an adapter (Python→DB) and converter (DB→Python).
sqlite3.register_adapter(MyType, lambda v: str(v)) sqlite3.register_converter("mytype", lambda b: parse(b))
Teach sqlite3 your own types. Enable converters at connect time (below).
con = sqlite3.connect("app.db", detect_types=sqlite3.PARSE_DECLTYPES)
Turns on converter lookup by declared column type. PARSE_COLNAMES uses AS "x [mytype]" hints instead.
# 3.12: default date/datetime adapters are DEPRECATED3.12
The built-in datetime adapters/converters are going away — register your own, or store ISO strings / Unix timestamps explicitly. JSON → store as TEXT via json.dumps/loads.

7Functions & extensionsPython inside SQL

con.create_function("shout", 1, lambda s: s.upper(), deterministic=True) con.execute("SELECT shout(name) FROM user")★
Call Python from SQL. deterministic=True lets it be used in indexes/WHERE optimizations.
con.create_aggregate("stdev", 1, StdevClass) con.create_collation("nocase_x", my_cmp)
Custom aggregate functions and sort collations, backed by Python classes/callables.
con.enable_load_extension(True) con.load_extension("./fts5", entrypoint="sqlite3_fts_init")3.12: entrypoint
Load compiled C extensions (e.g. full-text search, spatial). The entrypoint= keyword arrived in 3.12.
con.set_trace_callback(print) # log every SQL statement
Great for debugging what actually runs.

8Backup, dump, WAL, CLIoperate the DB

dst = sqlite3.connect("backup.db") con.backup(dst) # online, consistent copy★
Live backup of an in-use database to another connection (even :memory:→disk), no locking the app out.
for line in con.iterdump(filter="user%"): f.write(line)3.13: filter
Dump the schema+data as SQL text. The filter= LIKE pattern (3.13) limits which tables you export.
con.execute("PRAGMA journal_mode=WAL") con.execute("PRAGMA foreign_keys=ON")★
WAL boosts read/write concurrency. FK enforcement is OFF by default — turn it on every connection.
python -m sqlite3 app.db3.12+
A SQLite shell shipped with Python — inspect a DB on any machine that has Python, no separate sqlite3 binary needed.

!Common gotchasread before shipping

parameterize, never string-build
The #1 rule: ?/named params for values. Placeholders can't parametrize identifiers (table/column names) — validate those against an allow-list.
commit() or you lose writes
In legacy mode, uncommitted changes vanish on close/crash. Use with con: or commit explicitly (or autocommit=True).
with con: does NOT close
It ends the transaction, not the connection. Still call con.close() (or use contextlib.closing).
foreign keys OFF by default
SQLite ignores FK constraints unless you run PRAGMA foreign_keys=ON on each connection.
connections are thread-bound
By default a connection may only be used in its creating thread. Pass check_same_thread=False (and serialize access yourself) or open one connection per thread.
3.13: pass connect() args by keyword3.13
Positional use of timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements, uri is deprecated — name them.
use sqlite3.sqlite_version
The old sqlite3.version/version_info (the pysqlite binding version) are deprecated — check the engine with sqlite_version.

sqlite3 cheat sheet