Databases with sqlite3

What you'll learn

• Create a database file and a table with the sqlite3 module, no server needed
• Insert, query, update, and delete rows with the four core SQL statements
• Pass values as parameters so user input can never rewrite your query
• Know when a file of JSON stops being enough and a database is the right tool

Concept

A database is where data goes when more than one part of a program, or more than one program, needs to read and change it reliably. Files of JSON work until you need to find one record among a million, update it without rewriting the file, or let two processes write at once. SQLite is a complete relational database that lives in a single file, ships inside Python as the sqlite3 module, and is used in production more than any other database in the world, because it is inside every phone and browser.

Tables are the unit of storage. A table has named columns with types, and each row is one record; it is the CSV shape with rules attached. SQL is the language you talk to it in, and four statements do most of the work: CREATE TABLE defines the shape, INSERT adds rows, SELECT reads them with WHERE to filter and ORDER BY to sort, and UPDATE and DELETE change and remove. SQL keywords are conventionally written in upper case so they stand out from your own names.

The rule that matters most is how values reach the query. Never build SQL with f-strings or concatenation from anything a user typed. f"... WHERE name = '{name}'" lets a name like x' OR '1'='1 rewrite the query, which is SQL injection, the oldest and still most common web vulnerability. Instead, put a ? where each value goes and pass the values as a tuple: the database driver inserts them safely, and it is also faster because the query text is reused.

Changes are grouped into transactions. Nothing you insert or update is permanent until commit(); if something fails halfway, rollback() discards the partial work, so the database never holds half of an operation. Using the connection as a context manager, with conn:, commits on success and rolls back on an exception. Closing the connection when you are done releases the file.

When is a database the right tool rather than JSON or CSV? When you need to look records up by a field quickly, change one record without touching the rest, keep related tables consistent, or have several readers and writers. When the data is small, read once, and written by one script, a JSON file is simpler and fine. SQLite sits between: one file, no server, but full SQL, which makes it the right first database for almost every project.

Code

Create a database and a table, insert rows with parameters, and read them back. An in-memory database is used so the example leaves no file; replace ":memory:" with a filename to persist.

# create_insert.py
import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("""
    CREATE TABLE expenses (
        id INTEGER PRIMARY KEY,
        description TEXT NOT NULL,
        amount REAL NOT NULL,
        day TEXT NOT NULL
    )
""")

rows = [("rent", 950.0, "2026-09-01"), ("coffee", 3.5, "2026-09-02"), ("internet", 40.0, "2026-09-03")]
conn.executemany("INSERT INTO expenses (description, amount, day) VALUES (?, ?, ?)", rows)
conn.commit()

for row in conn.execute("SELECT id, description, amount FROM expenses ORDER BY amount DESC"):
    print(row)
print(conn.execute("SELECT COUNT(*), ROUND(SUM(amount), 2) FROM expenses").fetchone())
conn.close()

(1, 'rent', 950.0)
(3, 'internet', 40.0)
(2, 'coffee', 3.5)
(3, 993.5)

Filtering with WHERE, rows as dictionaries via row_factory, and update and delete inside a with conn: transaction.

# query_update.py
import sqlite3

conn = sqlite3.connect(":memory:")
conn.row_factory = sqlite3.Row
conn.execute("CREATE TABLE expenses (id INTEGER PRIMARY KEY, description TEXT, amount REAL, day TEXT)")
conn.executemany("INSERT INTO expenses (description, amount, day) VALUES (?, ?, ?)",
                 [("rent", 950.0, "2026-09-01"), ("coffee", 3.5, "2026-09-02"), ("internet", 40.0, "2026-09-03")])

big = conn.execute("SELECT description, amount FROM expenses WHERE amount > ? ORDER BY day", (10,)).fetchall()
print([(r["description"], r["amount"]) for r in big])

with conn:
    conn.execute("UPDATE expenses SET amount = ? WHERE description = ?", (45.0, "internet"))
    conn.execute("DELETE FROM expenses WHERE description = ?", ("coffee",))

for r in conn.execute("SELECT description, amount FROM expenses ORDER BY id"):
    print(dict(r))

[('rent', 950.0), ('internet', 40.0)]
{'description': 'rent', 'amount': 950.0}
{'description': 'internet', 'amount': 45.0}

Why parameters, demonstrated. The f-string version lets a crafted input return every row; the parameter version treats the same input as a plain, harmless string.

# injection.py
import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE users (name TEXT, secret TEXT)")
conn.executemany("INSERT INTO users VALUES (?, ?)", [("ada", "s3cret"), ("linus", "hunter2")])

user_input = "nobody' OR '1'='1"

unsafe = conn.execute(f"SELECT name FROM users WHERE name = '{user_input}'").fetchall()
print("f-string:", unsafe)

safe = conn.execute("SELECT name FROM users WHERE name = ?", (user_input,)).fetchall()
print("parameter:", safe)

f-string: [('ada',), ('linus',)]
parameter: []

A transaction that fails halfway rolls back, so the database is left exactly as it was. The CHECK constraint makes the second statement fail on purpose.

# transaction.py
import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE stock (item TEXT PRIMARY KEY, qty INTEGER CHECK (qty >= 0))")
conn.execute("CREATE TABLE orders (item TEXT, qty INTEGER)")
conn.execute("INSERT INTO stock VALUES ('pen', 2)")
conn.commit()

try:
    with conn:
        conn.execute("INSERT INTO orders VALUES ('pen', 5)")
        conn.execute("UPDATE stock SET qty = qty - 5 WHERE item = 'pen'")
except sqlite3.IntegrityError as err:
    print("rolled back:", err)

print(conn.execute("SELECT COUNT(*) FROM orders").fetchone()[0], "orders")
print(conn.execute("SELECT qty FROM stock WHERE item = 'pen'").fetchone()[0], "pens in stock")

rolled back: CHECK constraint failed: qty >= 0
0 orders
2 pens in stock

Try it yourself

Create notes_db.py that connects to notes.db on disk, creates a notes table if it does not exist, and offers functions add(title, body), search(word) using WHERE body LIKE ? with f"%{word}%" as the parameter value, and delete(note_id). Add three notes, search for a word, delete one, and print the remaining count. Run the script twice and notice the notes persist between runs.

Common mistakes

• Building SQL with f-strings. Any value that came from outside your code goes in through ? parameters; the f-string version is an injection vulnerability.
• Forgetting to commit. Inserts and updates are invisible to other connections and lost on close until commit(); use with conn: to make it automatic.
• Reaching for a database server first. SQLite handles most projects' needs in one file; move to PostgreSQL when you have many concurrent writers or a network of services.

What's next

• sqlite3 is a full database in one file, built into Python; connect, execute, fetchall, commit.
• CREATE TABLE, INSERT, SELECT ... WHERE ... ORDER BY, UPDATE, and DELETE cover most work; always pass values as ? parameters.
• Transactions keep related changes together; with conn: commits on success and rolls back on error.

You can store and query data properly. Next you will pick up the standard library's specialized containers and iteration tools, collections and itertools, which shorten a surprising amount of everyday code.