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 Store Data with sqlite3 and Parameterized Queries documentation.

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

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 >= 0))")
        con.execute("INSERT INTO expenses VALUES (?, ?, ?) ON CONFLICT(id) DO UPDATE SET name=excluded.name, amount=excluded.amount", ("e1", "lunch", 8000))
    rows = con.execute("SELECT name, amount FROM expenses ORDER BY id").fetchall()
    print(rows)
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.

Check rejection, reruns and cleanup

Run twice and confirm that there is still one row.
Try a negative amount, inspect the constraint failure and verify that the previous valid amount remains.
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