Every Python installation ships with a real SQL database. The sqlite3 module needs no server, no Docker container and no pip install: one file on disk holds your tables, indexes and data, and the same file opens on Windows, macOS, Linux and even phones. That makes SQLite the right first choice for scripts, desktop tools, prototypes, test fixtures, caches and many small web apps. In this tutorial we build a tiny shop database step by step: a STRICT table, safe parameter placeholders, transactions with the modern autocommit setting, JSON queries, an upsert with RETURNING, a bulk-insert speed test, and the datetime change that trips people up on new Python versions. Everything was run on Python 3.15.2 with the bundled SQLite 3.53.1 (and re-checked on Python 3.14.10 with identical output), and every output block below is the real output of the script above it.
What changed in Python's sqlite3 recently
A lot of older sqlite3 tutorials still teach the pre-2023 behaviour. These are the changes that matter in 2026:
- Python 3.12 added the
autocommitparameter and attribute. It is now the recommended way to control transactions; the oldisolation_levelapproach still works but is legacy. 3.12 also deprecated the defaultdatetimeadapters and added thepython -m sqlite3command-line shell. - Python 3.13 emits a
ResourceWarningif you forget to callclose()on a connection. - Python 3.14 raises
ProgrammingErrorwhen you mix named placeholders with a plain sequence of parameters, and removed the misleadingsqlite3.versionconstants (usesqlite3.sqlite_version). - Python 3.15 makes every
connect()parameter except the database keyword-only.sqlite3.connect("app.db", 5)now fails withTypeError: connect() takes at most 1 positional arguments (2 given); writetimeout=5instead.
No install is needed. If you want an isolated environment for the examples, uv venv -p 3.15 or python -m venv .venv is enough, because sqlite3 is part of the standard library.
Step 1: create a STRICT table and insert rows safely
We open a connection with autocommit=False. That gives PEP 249 behaviour: a transaction is opened implicitly and nothing is saved until you call commit(). Setting row_factory = sqlite3.Row lets us read columns by name. The STRICT keyword (SQLite 3.37+) makes SQLite enforce column types, so a text value can no longer slip into an INTEGER column.
import sqlite3
con = sqlite3.connect("shop.db", autocommit=False)
con.row_factory = sqlite3.Row
con.execute("""
CREATE TABLE IF NOT EXISTS products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
price REAL NOT NULL CHECK (price >= 0),
stock INTEGER NOT NULL DEFAULT 0
) STRICT
""")
rows = [
("Keyboard", 49.90, 12),
("Mouse", 19.50, 40),
("Monitor", 189.00, 5),
("USB-C Hub", 34.25, 0),
]
con.executemany(
"INSERT INTO products (name, price, stock) VALUES (?, ?, ?)", rows
)
con.commit()
for row in con.execute(
"SELECT id, name, price FROM products WHERE price < :limit ORDER BY price",
{"limit": 50},
):
print(row["id"], row["name"], row["price"])
print("sqlite library:", sqlite3.sqlite_version)
con.close()
Output:
2 Mouse 19.5
4 USB-C Hub 34.25
1 Keyboard 49.9
sqlite library: 3.53.1
Note the two placeholder styles. ? takes a tuple; :limit takes a dict. executemany() runs one statement for a whole list of rows, which is both shorter and much faster than a Python loop.
Step 2: transactions that roll back on errors
A connection is a context manager. with con: commits when the block finishes and rolls back if any exception escapes. It does not close the connection. Below, sell() decrements stock and then, on purpose, inserts a duplicate product to trigger a UNIQUE violation. The second call fails our own stock check.
import sqlite3
con = sqlite3.connect("shop.db", autocommit=False)
def sell(name, qty):
with con: # commits on success, rolls back on any exception
cur = con.execute(
"UPDATE products SET stock = stock - ? WHERE name = ? AND stock >= ?",
(qty, name, qty),
)
if cur.rowcount != 1:
raise ValueError(f"not enough {name} in stock")
con.execute("INSERT INTO products (name, price) VALUES ('Mouse', 1)")
try:
sell("Mouse", 3)
except sqlite3.IntegrityError as err:
print("rolled back:", err)
try:
sell("USB-C Hub", 1)
except ValueError as err:
print("rolled back:", err)
print(con.execute("SELECT name, stock FROM products WHERE name IN ('Mouse', 'USB-C Hub')").fetchall())
con.close()
Output:
rolled back: UNIQUE constraint failed: products.name
rolled back: not enough USB-C Hub in stock
[('Mouse', 40), ('USB-C Hub', 0)]
Mouse stock is still 40, not 37: the UPDATE ran, but the whole transaction was rolled back when the INSERT failed. This all-or-nothing behaviour is the main reason to group related writes in one with con: block. Checking cursor.rowcount after an UPDATE is also a simple way to detect "nothing matched" without a separate SELECT.
Step 3: never build SQL with f-strings
This is the most important rule in the tutorial. Here is what happens when user input is pasted into the SQL text, compared with passing the same value as a parameter:
import sqlite3
con = sqlite3.connect("shop.db", autocommit=False)
user_input = "x' OR '1'='1"
unsafe = f"SELECT count(*) FROM products WHERE name = '{user_input}'"
print("f-string query :", con.execute(unsafe).fetchone()[0], "rows")
safe = "SELECT count(*) FROM products WHERE name = ?"
print("placeholder query:", con.execute(safe, (user_input,)).fetchone()[0], "rows")
con.close()
Output:
f-string query : 4 rows
placeholder query: 0 rows
The f-string version turned the input into OR '1'='1' and matched every row; that is SQL injection. With a ? placeholder, SQLite treats the input purely as a value, and no product has that strange name. Placeholders work for values only. Table and column names cannot be parameters, so if those come from users, check them against an allow-list.
Step 4: JSON columns, upserts and RETURNING
Modern SQLite has built-in JSON support, including the ->> operator to extract a value as SQL text or number. That is handy for flexible data such as order payloads or API responses. The same script shows an upsert: INSERT ... ON CONFLICT(name) DO UPDATE updates the existing row instead of failing, and RETURNING hands back the final values without a second query.
import json, sqlite3
con = sqlite3.connect("shop.db", autocommit=False)
with con:
con.execute("""CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY, customer TEXT NOT NULL, payload TEXT NOT NULL) STRICT""")
con.executemany(
"INSERT INTO orders (customer, payload) VALUES (?, ?)",
[
("ali", json.dumps({"items": ["Mouse", "Keyboard"], "total": 69.4, "city": "Lahore"})),
("sara", json.dumps({"items": ["Monitor"], "total": 189.0, "city": "Karachi"})),
],
)
q = """SELECT customer, payload ->> '$.city' AS city,
payload ->> '$.total' AS total,
json_array_length(payload, '$.items') AS n_items
FROM orders ORDER BY total DESC"""
for row in con.execute(q):
print(row)
with con:
new_stock = con.execute("""
INSERT INTO products (name, price, stock) VALUES ('Mouse', 18.99, 10)
ON CONFLICT(name) DO UPDATE SET stock = stock + excluded.stock,
price = excluded.price
RETURNING name, price, stock""").fetchone()
print("upsert ->", new_stock)
con.close()
Output:
('sara', 'Karachi', 189.0, 1)
('ali', 'Lahore', 69.4, 2)
upsert -> ('Mouse', 18.99, 50)
The upsert hit the existing Mouse row, added 10 to its stock (40 + 10 = 50) and replaced the price, using excluded to refer to the values we tried to insert. RETURNING needs SQLite 3.35+ and the ->> operator needs 3.38+; any Python from the last few years bundles a newer SQLite, and you can check yours with sqlite3.sqlite_version.
Step 5: bulk inserts, WAL and why transactions are fast
SQLite is durable: every commit waits for the data to reach the disk. If you commit after every row you pay that cost every time. Here we compare 2,000 inserts in autocommit mode (one transaction each) with 20,000 rows inside a single explicit transaction, using WAL journal mode, which lets readers keep working while one writer commits.
import sqlite3, time
con = sqlite3.connect("speed.db", autocommit=True)
con.execute("PRAGMA journal_mode = WAL")
con.execute("CREATE TABLE IF NOT EXISTS log (id INTEGER PRIMARY KEY, msg TEXT) STRICT")
rows = [(f"event {i}",) for i in range(20_000)]
t = time.perf_counter()
for r in rows[:2_000]:
con.execute("INSERT INTO log (msg) VALUES (?)", r) # one transaction per row
print(f"2,000 rows, autocommit each : {time.perf_counter() - t:.3f}s")
t = time.perf_counter()
con.execute("BEGIN")
con.executemany("INSERT INTO log (msg) VALUES (?)", rows) # one transaction
con.execute("COMMIT")
print(f"20,000 rows, one transaction: {time.perf_counter() - t:.3f}s")
print("total rows:", con.execute("SELECT count(*) FROM log").fetchone()[0])
con.close()
Output (timings will differ on your machine):
2,000 rows, autocommit each : 0.186s
20,000 rows, one transaction: 0.008s
total rows: 22000
Ten times more rows finished more than twenty times faster, simply because they shared one transaction. For imports, batch rows with executemany() inside one transaction. autocommit=True is fine for interactive tools and for running your own BEGIN/COMMIT as shown.
Step 6: dates and the deprecated default adapter
Since Python 3.12, passing a datetime straight into a query uses a deprecated built-in adapter and prints a warning. The fix is to register your own adapter so you control the stored format, and convert back with datetime.fromisoformat():
import sqlite3, warnings
from datetime import datetime
warnings.simplefilter("always")
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE t (at TEXT)")
con.execute("INSERT INTO t VALUES (?)", (datetime(2026, 11, 18, 8, 0),))
# the fix: register your own adapter (and convert back yourself)
sqlite3.register_adapter(datetime, lambda d: d.isoformat())
con.execute("INSERT INTO t VALUES (?)", (datetime(2026, 11, 18, 9, 30),))
for (at,) in con.execute("SELECT at FROM t"):
print(repr(at), "->", datetime.fromisoformat(at))
con.close()
Output (file path shortened):
step6_dates.py:7: DeprecationWarning: The default datetime adapter is deprecated as of Python 3.12; see the sqlite3 documentation for suggested replacement recipes
con.execute("INSERT INTO t VALUES (?)", (datetime(2026, 11, 18, 8, 0),))
'2026-11-18 08:00:00' -> 2026-11-18 08:00:00
'2026-11-18T09:30:00' -> 2026-11-18 09:30:00
Storing ISO 8601 text keeps dates sortable and readable in any SQLite tool. Store timezone-aware UTC values if your app spans time zones.
Bonus: the built-in sqlite3 shell
Python 3.12+ includes a small command-line shell, handy on machines where the sqlite3 binary is not installed:
python -m sqlite3 shop.db "SELECT name, price, stock FROM products ORDER BY id"
('Keyboard', 49.9, 12)
('Mouse', 18.99, 50)
('Monitor', 189.0, 5)
('USB-C Hub', 34.25, 0)
Run python -m sqlite3 shop.db without a query to get an interactive prompt.
Quick reference
| Task | Code |
|---|---|
| Open with explicit transactions | sqlite3.connect("app.db", autocommit=False) |
| Rows as dict-like objects | con.row_factory = sqlite3.Row |
| Safe values | con.execute("... WHERE id = ?", (id_,)) |
| Many rows | con.executemany(sql, rows) |
| All-or-nothing writes | with con: ... |
| Upsert | INSERT ... ON CONFLICT(col) DO UPDATE SET ... |
| Concurrent readers | PRAGMA journal_mode = WAL |
| Wait for locks | sqlite3.connect("app.db", timeout=10) |
Common errors and fixes
sqlite3.OperationalError: database is locked: another connection is holding a write transaction. Keep transactions short, enable WAL, and pass atimeout.- Changes disappear after the script ends: with
autocommit=Falseyou must callcommit()or usewith con:. ProgrammingErrorabout placeholders (3.14+): named placeholders like:idneed a dict, not a tuple.TypeErrorfromconnect()(3.15+): passtimeout,autocommitand the rest as keywords.ResourceWarning(3.13+): close connections, or usecontextlib.closing(sqlite3.connect(...)).
When to move beyond sqlite3
SQLite handles many readers and one writer at a time very well, and databases of many gigabytes are fine. Move to PostgreSQL or MySQL when many processes or servers must write at the same moment, or when you need users and network access. If you want models and migrations on top of SQLite, see our SQLAlchemy 2.0 ORM guide, which uses the same database file. For heavy analytical queries over large files, an in-process column store is faster; see DuckDB and Polars together in Python pipelines. And to export tables to columnar files for data work, read PyArrow tables and Parquet in Python.
Summary
Python's built-in sqlite3 gives you a real SQL database in one file. Use autocommit=False with with con: for safe transactions, always pass values through placeholders, make tables STRICT, use JSON operators and upserts to keep code short, batch inserts in one transaction, and register your own date adapters. Those habits cover most of what small apps and data scripts need.