Skip to content
Learn Python with BNHR

Share: a data portal with Datasette

On this page
  1. Overview
  2. 2.1 · The portal's metadata, from the codebook
  3. 3.1 · People by province and sex, for one region
  4. 5.1 · Every page of a Datasette table

Every exercise in 32_datasette.py, with the solution and why it works. Each answer stays closed until you swear you tried it first. Opening one changes nothing in your notebook; your check still has to go green from your own code. Read the whole notebook, with its lessons.

Exercise 2.1. The portal's metadata, from the codebook

The task

Why it matters: a visitor reads the descriptions on the page, not your notebook. Taken from the codebook, they are the same words the contract enforces, and they change when it changes.

Input → output: a round ID and its codebook → a dictionary in the shape Datasette reads. The database is called release (from the file name release.sqlite):

{
    "title": "Labour survey release, round 2024Q2",
    "source": "Course training data (synthetic)",
    "databases": {
        "release": {
            "tables": {
                "release_table": {
                    "description": "Persons by province, sex and age group, round 2024Q2",
                    "columns": {"province": "Province code (10 characters, ...", "sex": "Sex, from the questionnaire", ...},
                },
                "codebook": {"description": "What each column of release_table means, its type, and its rules"},
            },
        },
    },
}

The round ID goes into the title and the table's description with an f-string (f"... round {round_id}"). columns has one entry per row of the codebook: dict(zip(book["column"], book["description"])) pairs each column with its description.

If the check is red: common causes

what you see the usual cause look at
KeyError: 'description' The codebook was read without the description column, or the column name is misspelt. book["description"]
The title shows {round_id} The f before the quotes is missing. f"... round {round_id}"
Right content, still red A key is spelt or nested differently: "tables" inside "release", inside "databases". the shape above

Still stuck after ten minutes? Ask your table, then raise your hand.

The solution, and why it works

Try it yourself first: the check names the input that failed, and the common causes above cover most red checks. Open this when you have been stuck for ten minutes, or when your check is green and you want to compare.

Exercise 3.1. People by province and sex, for one region

The task

Input → output: no input; the function gives back the SQL text. Run on release_table with a value for :region, the query gives one row per province of that region and sex, sorted by province then sex, with three columns:

column what it is
province, sex the group
weighted_persons SUM(weighted_persons): suppressed cells are empty, and a sum skips them
cells_suppressed SUM(suppressed): how many cells are missing from that sum. A reader must see that a total is incomplete.

A province belongs to a region when its first two characters are the region: substr(province, 1, 2) = :region. You met SELECT, WHERE and GROUP BY in the Get module (10_get_round.py, section 2); ORDER BY province, sex sorts the rows, and AS names a column. SQLite writes them as DuckDB does.

If the check is red: common causes

what you see the usual cause look at
OperationalError: near "…": syntax error A missing comma between columns, or a keyword out of order. SELECT … FROM … WHERE … GROUP BY … ORDER BY …
ProgrammingError: You did not supply a value for binding parameter :region The name after : is not region. :region
Every province of every region The WHERE line is missing. substr(province, 1, 2) = :region
One row per cell, not per province and sex GROUP BY is missing, or groups by more columns. GROUP BY province, sex
Right numbers, still red A column is named differently. AS weighted_persons, AS cells_suppressed

Still stuck after ten minutes? Ask your table, then raise your hand.

The solution, and why it works

Try it yourself first: the check names the input that failed, and the common causes above cover most red checks. Open this when you have been stuck for ten minutes, or when your check is green and you want to compare.

Exercise 5.1. Every page of a Datasette table

The task

Input → output: a function fetch (give it an address, it gives back the answer as a dictionary) and the first page's address → one table with the rows of every page.

  • follow page.get("next_url") until it is None;
  • when a page says "ok": false, stop with raise RuntimeError(page["error"]);
  • when a page says "truncated": true, stop with raise RuntimeError("truncated").

In both cases, a half-read table must not look complete.

fetch is an input, so the check can give your function pretend pages, and the live cell below can give it the real portal.

If the check is red: common causes

what you see the usual cause look at
Only the first page's rows The loop does not move to the next address. url = page["next_url"] inside the loop
The cell never finishes url never becomes None: it is set before the loop but not inside it. the last line of the loop
The error page gives a table ok is not checked before using rows. if not page["ok"]: raise RuntimeError(page["error"])
KeyError: 'next_url' on a canned query A canned query's answer has no next_url. page.get("next_url")
The cut answer gives a table truncated is not checked. if page.get("truncated"): raise RuntimeError("truncated")
Rows inside rows rows.append(page["rows"]) adds the list as one item. rows.extend(...) adds each row

Still stuck after ten minutes? Ask your table, then raise your hand.

The solution, and why it works

Try it yourself first: the check names the input that failed, and the common causes above cover most red checks. Open this when you have been stuck for ten minutes, or when your check is green and you want to compare.

Something new

Identify results
LayerExplore a module
ModuleCleaning and time
AboutModule 5 of Arrays & DataFrames: missing values and not-stated codes, the right types and one unit, dates read day-first, an introduction to time series, and checks you can rerun. Its sections, its notebooks to read on the web, and the companion's solutions and explanations.
CourseArrays & DataFrames, free