SQL from Python

Ask SQLite a real question and close its connection

The browser loads SQLite’s optional standard-library module on the first Run. connect(":memory:") creates a temporary database, not a cloud or local disk file. CREATE TABLE defines column names; these exercises supply valid rows with approved equal to integer zero or one. Parameter placeholders bind values separately from SQL text, including names containing quotes.

execute returns a cursor. fetchone returns one row tuple, so [0] reads the first column; fetchall returns a list of row tuples. COUNT(*) counts rows, WHERE filters rows and GROUP BY makes one group per model. SUM(approved) counts approvals for typed 0/1 flags and retains a zero-approved model; filtering away fails before grouping could hide that model. ORDER BY makes display order explicit.

Keep the supplied executemany table loader in these tasks while changing the query or its display. Behavioral checks substitute new typed rows, execute your actual SQL and include empty/zero-approved cases. Explicitly close the connection after reading the cursor. A SQLite connection context manager commits or rolls back transactions but does not itself close; the complete example uses contextlib.closing.

# Bind data values; close the in-memory connection.
import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:")) as db:
    db.execute("CREATE TABLE evals (model, approved)")
    db.executemany(
        "INSERT INTO evals VALUES (?, ?)",
        [("Canal", 1), ("Canal", 0), ("Museum", 0)],
    )
    query = (
        "SELECT model, SUM(approved) FROM evals "
        "GROUP BY model ORDER BY model"
    )
    for model, count in db.execute(query):
        print(f"{model}: {count}")

This browser database is temporary; the final report’s explicit download is how you keep a file. Values use placeholders, not string interpolation.

Official reference for this lesson