Python sqlite3 Tutorial: Tables, Transactions, JSON and Upserts with the Built-in Database (2026)

Data Manipulation Nov 18, 2026

Watch on YouTube

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 autocommit parameter and attribute. It is now the recommended way to control transactions; the old isolation_level approach still works but is legacy. 3.12 also deprecated the default datetime adapters and added the python -m sqlite3 command-line shell.
  • Python 3.13 emits a ResourceWarning if you forget to call close() on a connection.
  • Python 3.14 raises ProgrammingError when you mix named placeholders with a plain sequence of parameters, and removed the misleading sqlite3.version constants (use sqlite3.sqlite_version).
  • Python 3.15 makes every connect() parameter except the database keyword-only. sqlite3.connect("app.db", 5) now fails with TypeError: connect() takes at most 1 positional arguments (2 given); write timeout=5 instead.

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

TaskCode
Open with explicit transactionssqlite3.connect("app.db", autocommit=False)
Rows as dict-like objectscon.row_factory = sqlite3.Row
Safe valuescon.execute("... WHERE id = ?", (id_,))
Many rowscon.executemany(sql, rows)
All-or-nothing writeswith con: ...
UpsertINSERT ... ON CONFLICT(col) DO UPDATE SET ...
Concurrent readersPRAGMA journal_mode = WAL
Wait for lockssqlite3.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 a timeout.
  • Changes disappear after the script ends: with autocommit=False you must call commit() or use with con:.
  • ProgrammingError about placeholders (3.14+): named placeholders like :id need a dict, not a tuple.
  • TypeError from connect() (3.15+): pass timeout, autocommit and the rest as keywords.
  • ResourceWarning (3.13+): close connections, or use contextlib.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.

Share:
Last updated: November 2026
Built-in Functions in Python 2026 str() in Python 2026: String Conversion + Modern Formatting & Best Practices memoryview with JAX in Python 2026: Zero-Copy NumPy → JAX Array Interop + Efficient ML Examples memoryview with TensorFlow in Python 2026: Zero-Copy NumPy → Tensor Interop + GPU Pinning & ML Examples memoryview with TensorFlow in Python 2026: Zero-Copy NumPy → Tensor Interop + ML Examples memoryview with NumPy & PyTorch in Python 2026: Zero-Copy Views, Efficient Slicing & ML Interop Examples memoryview with NumPy in Python 2026: Zero-Copy Views, Efficient Slicing & Real ML Examples memoryview() in Python 2026: Zero-Copy Magic for Large Binary Data + Real Examples import() in Python 2026: How It Works, Security Risks & Modern Alternatives (importlib) zip() in Python 2026: How to Use It, strict=True Behavior & Real-World Examples vars() in Python 2026: Accessing Object Namespace + Modern Introspection Patterns type() in Python 2026: Dynamic Type Inspection & Object Creation + Modern Patterns tuple() in Python 2026: Immutable Sequences + Modern Patterns & Best Practices super() in Python 2026: Method Resolution & Modern Inheritance Patterns sum() in Python 2026: Summing Iterables + Modern Numeric Patterns & Best Practices staticmethod() in Python 2026: Static Methods, Utility Functions & Modern Best Practices sorted() in Python 2026: Sorting Iterables + Modern Patterns & Best Practices slice() in Python 2026: Creating Slice Objects + Modern Patterns & Best Practices setattr() in Python 2026: Dynamic Attribute Setting + Modern Patterns & Safety set() in Python 2026: Mutable Sets Creation + Modern Patterns & Best Practices