lesson 149 Packaging & Databases · advanced

SQLite: store and query rows

sqlite3 comes with Python: a whole SQL database in one file, or in memory. You create tables, insert rows and ask questions with SELECT, passing values through ? placeholders.

You'll be able to

Open a database with sqlite3.connect, create a table and insert rows with executemany
Query with execute(...).fetchall() / fetchone(), including JOIN and GROUP BY
Pass values with ? placeholders and explain why building SQL text from data breaks

The idea

A list of dictionaries is fine for a few hundred rows that live as long as the program. When data must outlive the program, be searched in different ways, or be shared, use a database. SQLite is a complete SQL database in a single file, and the sqlite3 module is part of Python.

sqlite3.connect("shop.db") opens (or creates) a database file; ":memory:" makes a database that lives only while the program runs. conn.execute(sql, values) runs one statement and returns a cursor; fetchall() on it gives the result rows as a list of tuples, fetchone() the next row (or None). executemany runs the same statement once per row of data.

Run each example to see what it prints. Running examples doesn't count toward progress.

example-1.py · Create, insert, select

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE products (sku TEXT PRIMARY KEY, name TEXT, category TEXT, price REAL, stock INTEGER)")
rows = [
    ("SPO-11681", "Ultra Bike Light", "sports", 7.49, 12),
    ("HOM-22847", "Deluxe Vase", "home", 31.0, 0),
    ("SPO-61044", "Travel Yoga Mat", "sports", 50.99, 7),
    ("ELE-24781", "Organic Speaker", "electronics", 171.99, 4),
    ("SPO-65611", "Studio Tent", "sports", 45.0, 0),
]
conn.executemany("INSERT INTO products VALUES (?, ?, ?, ?, ?)", rows)

cursor = conn.execute("SELECT sku, price FROM products WHERE category = ? ORDER BY price DESC", ("sports",))
print(cursor.fetchall())
print(conn.execute("SELECT COUNT(*) FROM products WHERE stock = 0").fetchone())

Why not write the value straight into the SQL with an f-string? Because data is not code. A customer's name containing a quote ends the SQL string early:

example-2.py · Building SQL from data

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE customers (customer_id TEXT, last_name TEXT)")
conn.execute("INSERT INTO customers VALUES (?, ?)", ("C00412", "O'Brien"))

last_name = "O'Brien"
query = f"SELECT customer_id FROM customers WHERE last_name = '{last_name}'"
print(conn.execute(query).fetchall())

Queries are where a database earns its keep. A JOIN matches rows of two tables on a shared key; GROUP BY collects one result row per group, with COUNT, SUM or AVG over each group. A function that takes the connection as a parameter can run against any database: the real file, or a small one built for a test.

example-3.py · Revenue per state, with a join

import sqlite3

def revenue_by_state(conn, status):
    sql = """
        SELECT customers.state, COUNT(*), ROUND(SUM(orders.total), 2)
        FROM orders JOIN customers ON customers.customer_id = orders.customer_id
        WHERE orders.status = ?
        GROUP BY customers.state
        ORDER BY 3 DESC
    """
    return conn.execute(sql, (status,)).fetchall()

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE customers (customer_id TEXT PRIMARY KEY, state TEXT)")
conn.execute("CREATE TABLE orders (order_id TEXT PRIMARY KEY, customer_id TEXT, status TEXT, total REAL)")
conn.executemany("INSERT INTO customers VALUES (?, ?)", [("C00038", "NY"), ("C00065", "CA"), ("C00077", "TX"), ("C01338", "NY")])
conn.executemany("INSERT INTO orders VALUES (?, ?, ?, ?)", [
    ("O100282", "C00077", "delivered", 35.84),
    ("O100376", "C00065", "returned", 91.47),
    ("O100918", "C01338", "returned", 55.72),
    ("O101219", "C00077", "delivered", 246.6),
    ("O101341", "C00038", "delivered", 46.41),
    ("O101723", "C01338", "delivered", 54.55),
])
print(revenue_by_state(conn, "delivered"))
print(revenue_by_state(conn, "returned"))

When to use it: data that must be kept, searched by different fields, or joined; reports over many rows. Mistakes to avoid: f-strings or + to put values into SQL (use ?); ("toys") instead of ("toys",) for a single value (without the comma it's just text in brackets); and forgetting that every row comes back as a tuple.

Review

sqlite3.connect(path or ":memory:"), execute, executemany, then fetchall() / fetchone(): rows are tuples.
Values always go through ? placeholders, in a tuple: (value,) for one.
JOIN, GROUP BY, ORDER BY and LIMIT let the database answer the question instead of a Python loop.
Next: what happens to your changes when the program stops, and how transactions keep related changes together.

Practise this