Applications need state

Most backend features that make a script feel like an application, such as caching, history, freshness checks, dashboards, and audit logs, depend on one thing: data that stays on disk between runs. The rest of this chapter is the toolkit for making that happen.

Our project: a weather cache

In this chapter, you'll turn a weather script into a weather cache by adding a database.

Here's a weather script you could have written after Chapter 3. It works perfectly on a good day: fetch current conditions, print them, exit. Save this script as weather_no_memory.py at the project root:

import os

import requests
from dotenv import load_dotenv


load_dotenv()

API_KEY = os.getenv("OPENWEATHER_API_KEY")

if not API_KEY:
    raise SystemExit("Missing OPENWEATHER_API_KEY in .env")

def get_weather(location):
    response = requests.get(
        "https://api.openweathermap.org/data/2.5/weather",
        params={"q": location, "appid": API_KEY, "units": "metric"},
        timeout=10,
    )
    response.raise_for_status()
    return response.json()

weather = get_weather("Dublin")
print(f"Temperature: {weather['main']['temp']}°C")
print(f"Conditions:  {weather['weather'][0]['description']}")

# Script ends. Data disappears.

Run it and you get one snapshot of the weather. Run it again tomorrow and you get one snapshot of tomorrow's weather. Nothing connects the two. That's fine for a one-off check, but useless for an application that wants to compare readings, calculate averages, spot trends, or reuse recent results.

Adding memory to the script

Save this version shown below as weather_with_memory.py.

This script keeps the same fetching logic as weather_no_memory.py, then adds one block to persist each reading and two more that ask questions: what was yesterday's reading? and what's the 7-day average?.

These three SQL statements turn a script that forgets into a script that remembers. Don't run it just yet: we need to create the table on the next page first.

Some of the connection-handling details will make more sense later in the chapter; for now, focus on the shape: fetch, insert, query.

import os
import sqlite3

import requests
from dotenv import load_dotenv


load_dotenv()

API_KEY = os.getenv("OPENWEATHER_API_KEY")

if not API_KEY:
    raise SystemExit("Missing OPENWEATHER_API_KEY in .env")

def get_weather(location):
    response = requests.get(
        "https://api.openweathermap.org/data/2.5/weather",
        params={"q": location, "appid": API_KEY, "units": "metric"},
        timeout=10,
    )
    response.raise_for_status()
    data = response.json()

    # Persist the reading. `recorded_at` is filled by the column's
    # DEFAULT CURRENT_TIMESTAMP so we don't pass it ourselves.
    with sqlite3.connect("weather.db") as conn:
        conn.execute(
            """
            INSERT INTO weather_readings (location, temperature, conditions)
            VALUES (?, ?, ?)
            """,
            (
                location,
                data["main"]["temp"],
                data["weather"][0]["description"],
            ),
        )

    return data

# Today's reading (from the API).
weather = get_weather("Dublin")
print(f"Today: {weather['main']['temp']}°C, {weather['weather'][0]['description']}")

# Yesterday's reading (from the database).
with sqlite3.connect("weather.db") as conn:
    cursor = conn.execute(
        """
        SELECT temperature, conditions
        FROM weather_readings
        WHERE location = ? AND date(recorded_at) = date('now', '-1 day')
        """,
        ("Dublin",),
    )
    yesterday = cursor.fetchone()
    if yesterday:
        print(f"Yesterday: {yesterday[0]}°C, {yesterday[1]}")

# Last seven days, daily average (from the database).
with sqlite3.connect("weather.db") as conn:
    cursor = conn.execute(
        """
        SELECT date(recorded_at), AVG(temperature)
        FROM weather_readings
        WHERE location = ? AND recorded_at >= date('now', '-7 days')
        GROUP BY date(recorded_at)
        ORDER BY date(recorded_at)
        """,
        ("Dublin",),
    )
    print("\nLast 7 days:")
    for day, avg_temp in cursor.fetchall():
        print(f"  {day}: {avg_temp:.1f}°C")

Your script can now answer questions about the past, calculate trends, and build context from history. The difference is a handful of lines of INSERT code plus a couple of SELECTs; everything else is the same fetch you wrote in Chapter 3.

• The API call still gets today's data. That part did not change.
• The INSERT stores a copy. The reading now survives after Python exits.
• The SELECT queries ask history questions. Once data exists on disk, Python can compare today with previous rows.

The conditions column stores OpenWeather's description field verbatim, which comes back lowercase (light rain, broken clouds, clear sky). The illustrative tables and INSERT examples in this chapter use the same form so they match what the script actually stores.

Why SQLite fits this project

A local weather cache is an excellent fit for SQLite: one application can keep its data in a single file without requiring a separate database server. Because weather.db is just a file, you can copy it to back up, send it to a teammate, or delete it to start fresh. The trade-off only shows up when many writers need the database at once.

The SQL and schema habits you build here transfer directly to PostgreSQL the day a workload outgrows a single file.

Next, you'll define the table that gives this weather script somewhere reliable to store those readings.