Querying and changing data

Once weather readings are on disk, your Python code needs to ask useful questions about them. What is the latest Dublin reading? Which rows are newer than an hour ago? Which old rows can be cleaned up safely?

Different ways to query data in SQLite

In SQLite, SELECT is the SQL statement you use to read data from a table. An INSERT changes the database by adding rows. A SELECT asks the database a question and returns matching rows.

Querying starts simple: choose the columns, choose the table, and optionally add filters, sorting, limits, or summaries. Each extra clause narrows or shapes the answer SQLite gives back.

The next examples are plain SQL statements against this chapter's weather_readings table. Later on this page, you'll run the same patterns from Python with sqlite3.

Basic SELECT

The simplest query reads every column from every row in a table.

SELECT *
FROM weather_readings;

The * means "all columns". This is useful while exploring a table, but it is not always the best habit in application code. Real programs usually ask for the specific columns they need.

Selecting specific columns

Instead of asking for every column, you can name only the columns you want returned.

SELECT location, temperature, conditions
FROM weather_readings;

This is clearer than SELECT *. The query shows exactly what the application expects to receive from the database.

Filtering rows with WHERE

A WHERE clause filters the table and returns only the rows that match a condition.

SELECT location, temperature, conditions
FROM weather_readings
WHERE location = 'Dublin';

This query asks SQLite for weather readings where the location column equals Dublin.

The key idea is that FROM chooses the table, and WHERE chooses which rows from that table are allowed through.

Filtering with comparisons

A WHERE clause can also use comparison operators such as >, <, >=, and <=.

SELECT location, temperature, conditions
FROM weather_readings
WHERE temperature > 20;

This returns only the rows where the temperature is greater than 20.

Combining conditions with AND

You can combine conditions when one filter is not specific enough.

SELECT location, temperature, conditions
FROM weather_readings
WHERE location = 'Dublin'
  AND temperature > 20;

This query returns rows where both conditions are true. The location must be Dublin, and the temperature must be greater than 20.

Matching one of several values with IN

IN is useful when you want to match against a list of possible values.

SELECT location, temperature, conditions
FROM weather_readings
WHERE location IN ('Dublin', 'Cork', 'Galway');

This is easier to read than writing three separate OR conditions.

Searching text with LIKE

LIKE is used for simple text matching. The percent sign, %, means "any characters".

SELECT location, temperature, conditions
FROM weather_readings
WHERE conditions LIKE '%rain%';

This returns rows where the conditions column contains the word rain. It could match values such as light rain, heavy rain, or rain showers.

Sorting results with ORDER BY

A query result does not have a meaningful order unless you ask for one. ORDER BY tells SQLite how to sort the returned rows.

SELECT location, temperature, conditions, recorded_at
FROM weather_readings
ORDER BY recorded_at DESC;

DESC means descending order, so the newest rows appear first. Use ASC for ascending order, which puts the oldest or smallest values first.

Limiting results with LIMIT

LIMIT caps how many rows SQLite returns. This is useful when you only need the latest row, the top few results, or a small preview.

SELECT location, temperature, conditions, recorded_at
FROM weather_readings
ORDER BY recorded_at DESC
LIMIT 5;

This query returns the five most recent weather readings.

Getting one latest row

Sorting and limiting are often used together when you want the most recent value.

SELECT location, temperature, conditions, recorded_at
FROM weather_readings
WHERE location = 'Dublin'
ORDER BY recorded_at DESC
LIMIT 1;

This query filters the table to Dublin readings, sorts them newest first, and returns only the first row. In plain English: "Give me the latest reading for Dublin."

Counting rows with COUNT

Aggregate functions calculate a result from multiple rows. COUNT tells you how many rows match the query.

SELECT COUNT(*) AS reading_count
FROM weather_readings
WHERE location = 'Dublin';

This does not return the individual weather readings. It returns one value: the number of matching rows.

Calculating averages with AVG

AVG calculates the average value for a numeric column.

SELECT AVG(temperature) AS average_temperature
FROM weather_readings
WHERE location = 'Dublin';

This asks SQLite to calculate the average temperature for all Dublin readings.

Grouping rows with GROUP BY

GROUP BY lets you calculate summary values per group. For example, instead of calculating one average for the whole table, you can calculate one average per location.

SELECT location, AVG(temperature) AS average_temperature
FROM weather_readings
GROUP BY location;

This returns one row per location. Each row contains the location and its average temperature.

The main teaching distinction

The easiest way to understand querying is to separate each clause by its job.

• SELECT chooses columns. It decides what information comes back.
• FROM chooses the table. It decides where SQLite reads from.
• WHERE filters rows. It decides which records are allowed through.
• ORDER BY sorts results. It decides what order the rows appear in.
• LIMIT caps results. It decides how many rows come back.
• GROUP BY summarises rows. It turns many rows into grouped results.

SELECT location, temperature, conditions
FROM weather_readings
WHERE location = 'Dublin'
ORDER BY recorded_at DESC
LIMIT 1;

In plain English, this says: "From the weather readings table, find Dublin readings, show the useful columns, put the newest result first, and return only one row."

That is the core mental model. A query is not magic. It is a structured question: what do you want, where should SQLite look, which rows qualify, and how should the answer be shaped?

Running SELECT from Python

The SQL patterns above are the database side of the story. In Python, you send those queries through sqlite3, then loop over the returned rows or fetch one result when the query should return a single answer.

Keep that table in mind as you read the examples. A query is just a precise instruction for which columns you want back, which rows qualify, and what order the answer should arrive in. The Terminal blocks below show output for these illustrative rows; your own weather.db reflects whatever you inserted on the earlier pages, so your exact rows and counts will differ.

Reading rows with SELECT

SELECT is the read statement. It does not change the database; it asks SQLite to return rows. The simplest version asks for every column from every row in the table:

import sqlite3

with sqlite3.connect("weather.db") as conn:
    cursor = conn.execute("SELECT * FROM weather_readings")
    for row in cursor:
        print(row)

(1, 'Dublin', 12.5, 'cloudy', 75, 4.5, '2026-05-05 14:30:22')
(2, 'London', 15.2, 'rain', 85, 6.2, '2026-05-05 14:35:10')
(3, 'Dublin', 13.1, 'partly cloudy', 72, 3.8, '2026-05-06 09:15:44')
(4, 'Paris', 18.0, 'sunny', 60, 3.1, '2026-05-06 10:22:33')

SELECT * means "every column." The FROM weather_readings part names the table to read from. Each row comes back as a tuple, in the column order the table was created with. That is useful for a quick peek, but it is rarely the best shape for application code because your Python now depends on every column staying in the same position forever.

The more deliberate pattern is to name only the columns you need. That gives the result a stable shape you can unpack clearly:

import sqlite3

with sqlite3.connect("weather.db") as conn:
    cursor = conn.execute("""
        SELECT location, temperature, conditions
        FROM weather_readings
    """)
    for location, temp, conditions in cursor:
        print(f"{location}: {temp}°C, {conditions}")

This prints a friendlier version of each row:

Dublin: 12.5°C, cloudy
London: 15.2°C, rain
Dublin: 13.1°C, partly cloudy
Paris: 18.0°C, sunny

Naming the columns explicitly does two things at once: it limits the result set to what you actually need (cheaper over a network when you're on a server database later), and it lets you unpack the tuple by meaning in the for loop. row[0] / row[1] is harder to read and breaks silently if you reorder the SELECT list.

Asking for specific rows with WHERE

Most application queries don't want the whole table. They want the rows that answer a specific question: Dublin's weather, readings above 15°C, or anything fetched in the last hour. WHERE is the part of the query that decides which rows qualify.

The first example below is the pattern you'll use whenever a value comes from Python. The city name is passed as a parameter with ?, exactly like the safe INSERT pattern from the previous page.

import sqlite3

with sqlite3.connect("weather.db") as conn:
    # Dublin only.
    cursor = conn.execute(
        """
        SELECT temperature, conditions, recorded_at
        FROM weather_readings
        WHERE location = ?
        """,
        ("Dublin",),
    )
    for temp, conditions, recorded_at in cursor:
        print(f"  {recorded_at}: {temp}°C, {conditions}")

    # Combine conditions with AND / OR.
    cursor = conn.execute("""
        SELECT location, temperature
        FROM weather_readings
        WHERE temperature > 15 AND temperature < 20
    """)
    for location, temp in cursor:
        print(f"  Moderate: {location} {temp}°C")

  2026-05-05 14:30:22: 12.5°C, cloudy
  2026-05-06 09:15:44: 13.1°C, partly cloudy
  Moderate: London 15.2°C
  Moderate: Paris 18.0°C

Read each WHERE clause as a sentence: location equals Dublin; temperature is greater than 15 and less than 20. The comparison operators are the obvious ones: =, !=, <, >, <=, >=. Combine multiple conditions with AND and OR. SQLite also has BETWEEN, IN, and LIKE (for wildcard text matching with %); reach for those when the plain = / AND form starts to read awkwardly.

Freshness checks with time filters

Time is where stored API data starts to become useful. A cache does not just need to know whether it has a row for Dublin; it needs to know whether that row is still fresh enough to trust. SQLite ships with built-in date functions that make those time-relative checks readable:

import sqlite3

with sqlite3.connect("weather.db") as conn:
    # Anything fetched in the last 24 hours.
    cursor = conn.execute("""
        SELECT location, temperature, recorded_at
        FROM weather_readings
        WHERE recorded_at > datetime('now', '-24 hours')
    """)
    for location, temp, recorded_at in cursor:
        print(f"{recorded_at} | {location}: {temp}°C")

The four functions you'll use most:

• datetime('now'): current timestamp.
• datetime('now', '-1 hour'): one hour ago. Modifiers like -30 minutes, -7 days, +1 month all work.
• date('now'): today's date, no time.
• date(recorded_at): extract just the date portion of a stored timestamp.

The cache freshness check on the weather cache page boils down to WHERE location = ? AND recorded_at > datetime('now', '-1 hour'). If a row matches for that location, the cache is fresh and the API call can be skipped. If no row matches, the stored data is stale or missing, so Python should fetch a new reading.

Finding the newest rows with ORDER BY and LIMIT

Rows in a database table do not have a natural order you should rely on. If you care which row comes first, ask for it explicitly. ORDER BY sorts the result; LIMIT restricts how many rows come back.

import sqlite3

with sqlite3.connect("weather.db") as conn:
    # Hottest first, top five.
    cursor = conn.execute("""
        SELECT location, temperature
        FROM weather_readings
        ORDER BY temperature DESC
        LIMIT 5
    """)
    for location, temp in cursor:
        print(f"{location}: {temp}°C")

    # The most recent Dublin reading. fetchone() returns one tuple or None.
    cursor = conn.execute(
        """
        SELECT temperature, conditions, recorded_at
        FROM weather_readings
        WHERE location = ?
        ORDER BY recorded_at DESC
        LIMIT 1
        """,
        ("Dublin",),
    )
    latest = cursor.fetchone()
    if latest is not None:
        temp, conditions, recorded_at = latest
        print(f"Latest Dublin: {temp}°C, {conditions} (at {recorded_at})")

DESC is descending (largest or newest first); ASC is ascending and is the default. The "most recent record" pattern, ORDER BY ... DESC LIMIT 1, plus fetchone() on the cursor, comes up so often in cache-freshness checks that it's worth memorising the shape outright. It turns a table full of history into the one row your API logic needs right now.

Summarising rows with aggregates and GROUP BY

Aggregates collapse many rows into one number. Combined with GROUP BY, they're how you build any trends-over-time report: requests per day, tracks per artist, average response time per endpoint. The simplest form runs across a whole filtered result set:

import sqlite3

with sqlite3.connect("weather.db") as conn:
    # Single-row summary across all Dublin readings.
    cursor = conn.execute(
        """
        SELECT AVG(temperature), MIN(temperature), MAX(temperature), COUNT(*)
        FROM weather_readings
        WHERE location = ?
        """,
        ("Dublin",),
    )
    avg_temp, min_temp, max_temp, count = cursor.fetchone()
    print(f"Dublin: {count} readings, avg {avg_temp:.1f}°C, range {min_temp} to {max_temp}°C")

The WHERE clause still runs first: only Dublin rows are included in the summary. COUNT(*) is the number of rows that matched. Aggregates over an empty result set return NULL for AVG/MIN/MAX and 0 for COUNT, worth knowing when you're handling the no-rows case.

Aggregates get more interesting paired with GROUP BY. Without GROUP BY, the whole result set becomes one summary row. With GROUP BY, SQLite makes one bucket per repeated value, then runs the aggregate separately inside each bucket:

import sqlite3

with sqlite3.connect("weather.db") as conn:
    # Reading count per city.
    cursor = conn.execute("""
        SELECT location, COUNT(*) AS reading_count
        FROM weather_readings
        GROUP BY location
        ORDER BY reading_count DESC
    """)
    for location, count in cursor:
        label = "reading" if count == 1 else "readings"
        print(f"{location}: {count} {label}")

    # Daily average temperature for Dublin (the "trend over time" pattern).
    cursor = conn.execute(
        """
        SELECT date(recorded_at) AS day, AVG(temperature) AS avg_temp
        FROM weather_readings
        WHERE location = ?
        GROUP BY date(recorded_at)
        ORDER BY day
        """,
        ("Dublin",),
    )
    for day, avg_temp in cursor:
        print(f"  {day}: {avg_temp:.1f}°C")

The first query above turns many readings into one row per city. The second turns many Dublin readings into one row per day:

Dublin: 2 readings
London: 1 reading
Paris: 1 reading

2026-05-05: 12.5°C
2026-05-06: 13.1°C

The pattern is SELECT [grouping_column], [aggregate] ... GROUP BY [grouping_column]. Rows with the same value in the grouping column collapse into one row in the result. Group by location for one row per city; group by date(recorded_at) for one row per day.

Changing rows with UPDATE and DELETE

Most of the weather-history project is append-only: new readings are inserted, old readings stay as history. Still, real applications sometimes need to correct a row or remove stale data. UPDATE changes existing rows; DELETE removes them.

Both statements look like SELECT with a more dangerous verb, and both demand a careful WHERE clause. Leaving it off updates or deletes every row in the table.

import sqlite3

with sqlite3.connect("weather.db") as conn:
    # Update one row.
    conn.execute(
        """
        UPDATE weather_readings
        SET temperature = ?, conditions = ?
        WHERE id = ?
        """,
        (14.0, "partly cloudy", 1),
    )

    # Delete rows older than 30 days. cursor.rowcount tells you how many.
    cursor = conn.execute("""
        DELETE FROM weather_readings
        WHERE recorded_at < datetime('now', '-30 days')
    """)
    print(f"Deleted {cursor.rowcount} stale readings.")

Next we'll scale this up in two directions: indexes make repeated lookups fast, and joins let separate tables work together.