The essentials
Quick reference
One focused task per row. Jump to the related section for complete, working examples.
| Use | Syntax | Examples |
|---|---|---|
| Open an in-memory database | con = sqlite3.connect(':memory:') | View examples |
| Open a database file | con = sqlite3.connect('app.db', timeout=5.0) | View examples |
| Create a table safely | con.execute('CREATE TABLE IF NOT EXISTS task(id INTEGER PRIMARY KEY, title TEXT NOT NULL)') | View examples |
| Bind positional values | con.execute('SELECT * FROM task WHERE id = ?', (task_id,)) | View examples |
| Bind named values | con.execute('UPDATE task SET done = :done WHERE id = :id', {'done': 1, 'id': task_id}) | View examples |
| Insert a batch | con.executemany('INSERT INTO task(title) VALUES(?)', [(title,) for title in titles]) | View examples |
| Commit or roll back a unit | with con: con.execute('UPDATE account SET balance = balance - ? WHERE id = ?', (amount, account_id)) | View examples |
| Select PEP 249 control | con = sqlite3.connect('app.db', autocommit=False) | View examples |
| Inspect transaction state | if con.in_transaction: con.rollback() | View examples |
| Enforce foreign keys | con.execute('PRAGMA foreign_keys = ON') | View examples |
| Handle constraint failures | except sqlite3.IntegrityError as error: con.rollback() | View examples |
| Read columns by name | con.row_factory = sqlite3.Row | View examples |
| Fetch one result | row = con.execute('SELECT title FROM task WHERE id = ?', (task_id,)).fetchone() | View examples |
| Create a nested recovery point | con.execute('SAVEPOINT before_optional_work') | View examples |
| Undo to a savepoint | con.execute('ROLLBACK TO SAVEPOINT before_optional_work') | View examples |
| Copy a live database | source.backup(destination, pages=100) | View examples |
Python's sqlite3 module provides an embedded transactional database through the standard DB-API interface. Treat the database as a durable boundary: bind every value, define constraints in the schema, make transaction ownership explicit, close connections deliberately, and test failure paths as carefully as successful queries.
Step by step
Detailed examples
Own the connection and encode invariants in the schema
A Connection represents one database session and should be closed explicitly; since Python 3.13, abandoning an unclosed connection can emit ResourceWarning. Use :memory: for isolated tests and a path for persistence. Declare PRIMARY KEY, NOT NULL, UNIQUE, CHECK, and foreign-key rules in SQL so every writer—not only one Python call site—shares the same guarantees.
import sqlite3
con = sqlite3.connect(':memory:')
try:
con.execute('CREATE TABLE score(name TEXT PRIMARY KEY, points INTEGER NOT NULL CHECK(points >= 0))')
con.executemany('INSERT INTO score VALUES(?, ?)', [('Ada', 3), ('Lin', 5)])
con.commit()
print(con.execute('SELECT COUNT(*), SUM(points) FROM score').fetchone())
finally:
con.close()(2, 8)Bind values; allow-list identifiers
Never interpolate values into SQL with f-strings, percent formatting, or concatenation. Pass a sequence for ? placeholders or a dictionary for :name placeholders; the driver quotes and adapts each value safely. Placeholders represent values only, not table names, column names, directions, or SQL keywords. Choose those structural fragments from an application-owned allow-list before composing SQL.
import sqlite3
con = sqlite3.connect(':memory:')
con.execute('CREATE TABLE note(body TEXT NOT NULL)')
supplied = "'); DROP TABLE note; --"
con.execute('INSERT INTO note(body) VALUES(?)', (supplied,))
con.executemany('INSERT INTO note(body) VALUES(:body)', [{'body': 'safe'}, {'body': 'also safe'}])
rows = con.execute('SELECT body FROM note ORDER BY rowid').fetchall()
print(len(rows))
print(rows[0][0])
con.close()3
'); DROP TABLE note; --Make transaction boundaries visible
Connection as a context manager commits a pending transaction when its block succeeds and rolls back when the block raises, but it neither opens nor closes the connection by itself. Python 3.12 added the autocommit parameter; autocommit=False provides recommended PEP 249 behavior, while LEGACY_TRANSACTION_CONTROL is still the default in Python 3.14 and is documented to change in a future release. Set the mode deliberately instead of relying on that moving default.
import sqlite3
con = sqlite3.connect(':memory:')
con.execute('CREATE TABLE account(name TEXT PRIMARY KEY, balance INTEGER NOT NULL)')
con.executemany('INSERT INTO account VALUES(?, ?)', [('checking', 100), ('savings', 50)])
con.commit()
try:
with con:
con.execute('UPDATE account SET balance = balance - 30 WHERE name = ?', ('checking',))
raise RuntimeError('credit service unavailable')
except RuntimeError:
pass
print(con.execute('SELECT name, balance FROM account ORDER BY name').fetchall())
con.close()[('checking', 100), ('savings', 50)]Let SQLite reject invalid state
Constraint violations raise sqlite3.IntegrityError, a narrower exception than DatabaseError. SQLite foreign-key enforcement must be enabled separately for each connection, normally immediately after connecting and before a transaction begins. Catch an integrity failure only where the application can translate or recover from that expected conflict; do not turn programming, disk, or locking failures into misleading validation messages.
import sqlite3
con = sqlite3.connect(':memory:')
con.execute('PRAGMA foreign_keys = ON')
con.execute('CREATE TABLE project(id INTEGER PRIMARY KEY)')
con.execute('CREATE TABLE task(project_id INTEGER NOT NULL REFERENCES project(id))')
con.execute('INSERT INTO project VALUES(1)')
try:
con.execute('INSERT INTO task VALUES(?)', (99,))
except sqlite3.IntegrityError as error:
print(type(error).__name__)
print(con.execute('SELECT COUNT(*) FROM task').fetchone()[0])
con.close()IntegrityError
0Choose a row shape and deterministic ordering
Rows are tuples by default. Assign sqlite3.Row to Connection.row_factory before creating cursors to support both numeric and case-insensitive named lookup with low overhead. Select only required columns, handle fetchone returning None, and include ORDER BY whenever order matters—SQL does not promise insertion order without it.
import sqlite3
con = sqlite3.connect(':memory:')
con.row_factory = sqlite3.Row
con.execute('CREATE TABLE item(id INTEGER PRIMARY KEY, label TEXT NOT NULL)')
con.executemany('INSERT INTO item(label) VALUES(?)', [('gamma',), ('alpha',), ('beta',)])
rows = con.execute('SELECT id, label FROM item ORDER BY label').fetchall()
print([row['label'] for row in rows])
print(rows[0].keys())
con.close()['alpha', 'beta', 'gamma']
['id', 'label']Use savepoints for partial recovery and backup for durability
BEGIN transactions do not nest in SQLite. SAVEPOINT, ROLLBACK TO, and RELEASE provide nested recovery points inside a larger transaction; ROLLBACK TO keeps the savepoint active until RELEASE. Connection.backup copies a consistent snapshot and can operate while the source is in use. A backup is not verified disaster recovery: store copies separately and regularly test that they open and contain the expected data.
import sqlite3
source = sqlite3.connect(':memory:')
source.execute('CREATE TABLE event(name TEXT NOT NULL)')
source.execute("INSERT INTO event VALUES('kept')")
source.execute('SAVEPOINT optional')
source.execute("INSERT INTO event VALUES('discarded')")
source.execute('ROLLBACK TO optional')
source.execute('RELEASE optional')
source.commit()
copy = sqlite3.connect(':memory:')
source.backup(copy)
print(copy.execute('SELECT name FROM event').fetchall())
source.close()
copy.close()[('kept',)]Local code tester
Query a safe in-memory database
Edit the parameterized inserts and query while keeping values separate from SQL syntax.
Press Run to load Python locally.
Sources and further reading
References
Authoritative documentation used to verify and expand this cheat sheet.
Help us improve
Found a typo or missing example?
Tell us what would make this cheat sheet clearer, more complete, or more useful.