Chapter 15: Database Fundamentals with SQLite

Introduction to SQL and SQLite

Up to now, most of your API projects have probably been stateless: run the script, fetch some data, print the result, then exit. The moment the Python process stops, everything disappears with it. That works for experiments and one-run utilities, but real applications often need state: a way to store data permanently so it can be queried, updated, and reused across runs.

Persistent storage solves that problem, and SQLite is the fastest path to it, with no server to install, no configuration files, no ports, and no separate database service to manage.

SQLite

SQLite is embedded. It stores data in a single file on the host device, and the database engine runs inside the application process. Python includes built-in support for SQLite through the standard-library sqlite3 module. To use it, you import sqlite3 and connect to a database file.

Because the database is embedded, your application can read and write local data directly, without sending queries to a separate database server. That makes SQLite simple to set up and very fast for local workloads, especially small applications, prototypes, and single-user tools.

SQLite is often misunderstood as a tool meant only for small, temporary scripts. Think of it instead as the database you reach for when an application needs its own local store on the same device. That is why SQLite shows up in mobile apps, desktop software, developer tools, browsers, and embedded devices: not because those systems are unserious, but because local storage is exactly the right shape for the problem.

When you create a new SQLite database, you specify a filename, and that file becomes the container for your data. In this chapter, that file is weather.db. Inside it, you can define one or more tables to organise data into rows and columns.

Each table has a schema: the column names, data types, and rules that define what each row should contain. This chapter's main project starts with a single weather_readings table. The tree below also shows two small teaching databases introduced later: one for cache-style upserts and one for foreign keys and JOINs.

weather.db
└── weather_readings

weather_demo.db
├── locations
└── weather_readings

weather_cache_demo.db
└── weather_cache

weather.db is this chapter's main project with one table weather_readings. weather_demo.db appears on the foreign keys and JOINs page, and weather_cache_demo.db on the inserting rows page, both for teaching purposes.

SQL is a language for working with data

SQL (Structured Query Language) is the standard language used to work with relational databases such as SQLite, PostgreSQL, and MySQL. The details vary depending on which database system you're using, but the core ideas remain the same.

SQL lets you define tables, insert data, search for rows, update records, delete records, and combine information across multiple tables. Python can control the overall program, while SQL focuses on the database: storing data, organising it, and retrieving exactly the pieces you need.

Here's a preview of the kind of statement you'll write yourself on the schema basics page:

CREATE TABLE IF NOT EXISTS weather_readings (
    id           INTEGER PRIMARY KEY,
    location     TEXT NOT NULL,
    temperature  REAL,
    conditions   TEXT,
    humidity     INTEGER,
    wind_speed   REAL,
    recorded_at  DATETIME DEFAULT CURRENT_TIMESTAMP
);

You can run SQL statements like the one above directly from a database CLI in your terminal, or you can embed them as strings inside your Python code and execute them with the sqlite3 module.

Creating a database and a table

In the script below, the SQL statement sits inside a multiline Python string passed to conn.execute(): two languages in one call, Python on the outside and SQL inside the string.

The Python on the outside opens a connection to weather.db with sqlite3.connect(), creating the file on disk if it doesn't already exist. The with block is Python's standard idiom for using a connection; the mechanics get a fuller treatment later in the chapter.

import sqlite3

with sqlite3.connect("weather.db") as conn:
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS weather_readings (
            id           INTEGER PRIMARY KEY,
            location     TEXT NOT NULL,
            temperature  REAL,
            conditions   TEXT,
            humidity     INTEGER,
            wind_speed   REAL,
            recorded_at  DATETIME DEFAULT CURRENT_TIMESTAMP
        );
        """
    )

Each call to conn.execute() ships a SQL string to the engine and hands the result back to Python. The rest of the chapter builds up the patterns around that single call: parameter binding, transaction control, and row factories.

What you'll learn

This chapter turns a "one-run" API script into a small database-backed application. The project is a weather cache: Python fetches current weather from an API, SQLite stores each reading on disk, and SQL lets your code reuse fresh data, inspect history, and calculate simple trends.

The chapter starts with the smallest useful idea: a script forgets everything when it exits. From there, each page adds one database habit until the final WeatherCache class can decide whether to return a stored reading or call the API again.