Schema basics

Your weather script now has data worth keeping, but SQLite needs to know what a saved reading should look like. On this page, you'll define a weather_readings table and create the database file that gives the script somewhere to store its results.

API data in rows and columns

If an API returns JSON objects over time, a database table gives those records a consistent structure on disk. Each row stores one reading, while each column stores one kind of value from that reading.

Here's a slimmed-down view of the weather_readings table after a few API responses have landed:

The id column uniquely identifies each row, and the database will assign it automatically for you. The location, temperature, and conditions columns store values returned by the API. recorded_at stores the moment the reading was saved. Every database operation, including inserts, queries, filters, and sorts, works against this rectangular structure.

Once you know what each row should contain, the next job is to make that shape official.

Defining the table with CREATE TABLE

Before you can save API responses, you must explicitly define the table schema: which columns exist, what type of data they store, and which rules each column follows.

SQL tables have a fixed schema by design. Unlike JSON, where one object can contain completely different fields from the next, every row in a database table follows the same structure. That consistency is what makes databases reliable for searching, sorting, filtering, and analysing large amounts of data.

The generic CREATE TABLE statement follows one shape: the table name, followed by a parenthesised list where each line defines one column by its name, data type, and optional constraints. In the abstract, that shape looks like this:

CREATE TABLE IF NOT EXISTS table_name (
    column_1_name  DATA_TYPE  CONSTRAINTS,
    column_2_name  DATA_TYPE  CONSTRAINTS
);

Data types

Every column in a table has a data type. For API work, you will repeatedly use the same four SQLite types:

• INTEGER. Whole numbers such as counts, IDs, and years.
• REAL. Decimal values such as temperatures, prices, and percentages.
• TEXT. Strings such as names, descriptions, and API responses.
• DATETIME. Dates and timestamps.

Saving the schema as weather_schema.sql

We will save the schema in a SQL file and then use it to create the database using the same SQLite mechanism you saw in the chapter introduction: sqlite3.connect("weather.db") creates the database file if it does not already exist.

The difference is where the CREATE TABLE SQL lives. In the chapter introduction, the SQL was embedded directly inside the Python file. Here, the SQL lives in its own weather_schema.sql file, and Python reads that file and runs it against the database.

Keeping the schema in a separate file is better once the schema becomes a project artifact. It gives the SQL its own place to live, makes it easier to review, and lets editors highlight the database structure as SQL instead of as a string inside Python.

Create a new file in your project root and save it as weather_schema.sql. This file will define the structure and rules your database tables must follow. In the next section, you will run this file once to create weather.db.

One formatting note before you start: use spaces rather than tabs for indentation. SQL schemas are often formatted so column names and data types line up vertically, and spaces keep that alignment consistent across editors, terminals, and GitHub diffs.

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
);

Constraints

Constraints are optional rules attached to columns. They decide what values a column accepts, and what happens when one isn't supplied. The schema above uses three constraints:

• INTEGER PRIMARY KEY marks id as the unique identifier for each row. In SQLite, the database automatically assigns a new integer ID when your insert leaves this column out.
• NOT NULL makes location required. An insert without a location is rejected instead of saving an incomplete reading.
• DEFAULT CURRENT_TIMESTAMP tells SQLite to record the insertion time automatically when your Python code leaves recorded_at out.

Running the schema from Python

Saving weather_schema.sql creates the schema file, but it does not create the table yet. You still need to run that SQL against weather.db.

Save this helper as create_weather_db.py in the same folder as weather_schema.sql:

import sqlite3

with open("weather_schema.sql", encoding="utf-8") as f:
    schema = f.read()

with sqlite3.connect("weather.db") as conn:
    conn.executescript(schema)

print("Created weather.db with the weather_readings table.")

Run it once from the project root:

python create_weather_db.py

You should now see a new weather.db file beside your script. That database contains the weather_readings table, so the weather_with_memory.py script from the previous page, and the insert/query examples on the next pages, have somewhere to write rows.

With the table defined, the next move is filling it.