Python Tutorial / Store Data with sqlite3 and Parameterized Queries

Python Tutorial

sqlite3 stores SQL data in a file without a separate database server. Pass input values as parameters instead of interpolating SQL, and make transaction success, failure and connection cleanup explicit.

Choose a transaction policy

SQLite is an embedded database engine. It is convenient for a local file, but its concurrency and operating conditions differ from a server database. Opening a connection alone does not mean every subsequent change is durably committed.

This example requires Python 3.12 or later and explicitly uses autocommit=False. A connection context manager commits on success or rolls back on failure; it does not close the connection, so finally closes it separately.

For the relevant APIs and platform requirements, see Python: sqlite3.

Rollback and a command-line storage tool build on the rules demonstrated in Handle Exceptions with try, except, else, finally and raise.

Run a complete insert and query

Use a fresh practice folder so ledger-demo.db is a disposable database for this example.

Save the complete code below as sqlite_demo.py in a separate practice folder, using UTF-8. Run python sqlite_demo.py in a terminal with your project virtual environment selected. Outside a virtual environment on macOS or Ubuntu, use python3 when that is your interpreter command.

import sqlite3
MAX_AMOUNT = 1_000_000_000_000
amount = 8000
if type(amount) is not int or not 0 <= amount <= MAX_AMOUNT:
    raise ValueError("amount outside practice range")
con = sqlite3.connect("ledger-demo.db", autocommit=False)
try:
    with con:
        con.execute("CREATE TABLE IF NOT EXISTS expenses (id TEXT PRIMARY KEY, name TEXT NOT NULL, amount INTEGER NOT NULL CHECK(amount BETWEEN 0 AND 1000000000000))")
        con.execute("INSERT INTO expenses VALUES (?, ?, ?) ON CONFLICT(id) DO UPDATE SET name=excluded.name, amount=excluded.amount", ("e1", "lunch", amount))
    print(con.execute("SELECT name, amount FROM expenses ORDER BY id").fetchall())
finally:
    con.close()

Expected output:

[('lunch', 8000)]

Separate value parameters from SQL structure

A question mark is a value placeholder; the second execute argument supplies a tuple of values. A one-item tuple needs a comma, as in (value,). Table names and sort expressions are SQL structure, so select them from a controlled allowlist when they must vary.

PRIMARY KEY, NOT NULL and CHECK apply rules in storage as well as in Python. Both layers matter when other paths can write data. The upsert updates the row with the same id on reruns rather than accumulating copies of the expense.

Python int supports arbitrarily large values within memory limits, while SQLite INTEGER is a signed 64-bit integer. This practice contract accepts integers from 0 through 1,000,000,000,000 and validates before binding. A transaction groups writes for commit or rollback; closing the connection is a separate responsibility. CREATE TABLE IF NOT EXISTS does not alter an existing CHECK constraint. Use a fresh practice folder; an existing production database needs a backed-up migration.

Official behavior and conditions: SQLite: storage classes and datatypes

Check rejection, reruns and cleanup

Run twice and confirm that there is still one row.
Try a negative amount, inspect the ValueError raised before SQL binding, and verify that the prior amount remains. A direct SQL write bypassing Python validation is still subject to the CHECK constraint.
Add another identifier and inspect the explicit ORDER BY result.

Without ORDER BY, do not rely on the observed query order. For locking errors, check long transactions and unclosed connections. Avoid adding DROP TABLE merely to make reruns convenient on an unknown database file. The final expense application combines these rules with explicit initialization and commands.

Python Tutorial