Chapter review

You started this chapter with a script that forgot every weather reading as soon as it exited. You finished with a SQLite-backed cache: a small persistence layer that stores API responses, skips unnecessary network calls, and lets Python ask questions about previous runs. That is the big shift databases give your API projects: memory.

The chapter in one pass

The chapter built the database layer in stages. Each stage answered one practical question:

• Why store anything? Application state lets a script remember previous API responses, compare readings, calculate trends, and avoid repeated network calls.
• What shape should the data have? A schema defines tables, columns, data types, defaults, and constraints before Python starts writing rows.
• How do rows get in? INSERT adds data; parameterised inserts keep Python values separate from SQL; batch inserts and upserts serve different application shapes.
• How do rows come back out? SELECT, WHERE, ORDER BY, LIMIT, aggregates, and GROUP BY turn stored rows into useful answers.
• How does the table stay fast? Indexes earn their keep when they match real query shapes, especially repeated filters and sorts.
• When should data split across tables? Foreign keys and JOINs let repeated entity data live once while related rows point back to it.
• How does Python run the SQL safely? sqlite3 gives you connections, cursors, parameters, row factories, transactions, and explicit connection cleanup.
• How does this become application code? WeatherCache wraps the schema, freshness check, API fetch, storage write, and history queries behind a small public interface.
• What happens after the first version ships? Schema design, migrations, query plans, backups, foreign-key enforcement, and batching are the habits that keep the database trustworthy.

Patterns to carry forward

Most SQLite-backed API projects reuse a small set of patterns. These are the shapes worth keeping close.

from contextlib import closing
import sqlite3


with closing(sqlite3.connect("weather.db")) as conn:
    conn.row_factory = sqlite3.Row

    row = conn.execute(
        """
        SELECT location, temperature, conditions, recorded_at
        FROM weather_readings
        WHERE location = ?
        ORDER BY recorded_at DESC
        LIMIT 1
        """,
        ("Dublin",),
    ).fetchone()

if row is not None:
    print(row["temperature"])

from contextlib import closing
import sqlite3


reading = {
    "location": "Dublin",
    "temperature": 12.5,
    "conditions": "cloudy",
    "humidity": 75,
    "wind_speed": 4.5,
}

with closing(sqlite3.connect("weather.db")) as conn:
    with conn:
        conn.execute(
            """
            INSERT INTO weather_readings
                (location, temperature, conditions, humidity, wind_speed)
            VALUES
                (:location, :temperature, :conditions, :humidity, :wind_speed)
            """,
            reading,
        )

CREATE INDEX IF NOT EXISTS idx_weather_location_recorded_at
    ON weather_readings(location, recorded_at DESC);

The details change from project to project, but the shape stays familiar: open the database, run parameterised SQL, fetch or write deliberately, and add indexes only when a real query earns them.

Chapter review quiz

Use these questions as a quick pressure test. If one answer feels vague, that is the section to revisit before moving on.

WHERE location = ?
ORDER BY recorded_at DESC
LIMIT 1

Before moving on

You are ready for the next chapter if you can explain these choices in plain language:

• Why the weather project keeps historical rows instead of only the latest value.
• Why the schema lets SQLite fill id and recorded_at.
• Why every Python value goes through a parameter.
• Why reads and writes have different sqlite3 patterns.
• Why indexes follow real queries, not guesses.
• Why SQLite is a strong local database but not the answer for many simultaneous writers across multiple servers.

Looking forward

Chapter 16 takes the same database thinking into the Spotify Music Time Machine. OAuth gets permission to call Spotify. The API provides current listening data. SQLite gives the project memory.

The model becomes richer, but the habits stay the same: store entities once when they have their own meaning, append snapshots when history matters, index the questions the app asks repeatedly, and use Python database code that keeps SQL readable and values safe.

That is the quiet power of this chapter. You are no longer just calling APIs and printing responses; you can build applications that remember.