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.
The idea
Datasette reads its page texts from a nested dictionary. Build that dictionary from the codebook, so a visitor reads the same descriptions the contract enforces, and they change when it does.
The function, solved
Copy it with the button on the code's frame, and paste it over all the code in the exercise's cell, so the indentation stays right.
def metadata_for(round_id: str, book: pd.DataFrame) -> dict: """Datasette's metadata for a release: a title, the source, and a description of each table and column.""" # Exercise 2.1: the task and the shape are in the text above this cell. columns = dict(zip(book["column"], book["description"])) return { "title": f"Labour survey release, round {round_id}", "source": "Course training data (synthetic)", "databases": { "release": { "tables": { "release_table": {"description": f"Persons by province, sex and age group, round {round_id}", "columns": columns}, "codebook": {"description": "What each column of release_table means, its type, and its rules"}, }, }, }, }Why each part is there
dict(zip(book["column"], book["description"]))pairs each column name with its description, one entry per codebook row:{"province": "Province code ...", "sex": "Sex, from the questionnaire", ...}.- The dictionary's shape is Datasette's, key by key:
databases, then the database's namerelease(fromrelease.sqlite), thentables, then each table'sdescriptionandcolumns. A key spelt or nested differently is simply ignored by Datasette, which is why the check compares the whole shape. - The round ID goes into the title and the table's description with f-strings, so each round's portal says which round it shows; without the
f, the page would read{round_id}.
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.
The idea
One query that sums the release's cells by province and sex for one region, and says, beside each total, how many cells were missing from it: a reader must see when a total is incomplete.
The function, solved
Copy it with the button on the code's frame, and paste it over all the code in the exercise's cell, so the indentation stays right.
def region_sql() -> str: """The canned query: one region's provinces by sex, with how many suppressed cells each total is missing.""" # Exercise 3.1: the task is in the text above this cell. Write the query between the triple quotes. return """ SELECT province, sex, SUM(weighted_persons) AS weighted_persons, SUM(suppressed) AS cells_suppressed FROM release_table WHERE substr(province, 1, 2) = :region GROUP BY province, sex ORDER BY province, sex """Why each part is there
SUM(weighted_persons) AS weighted_persons: suppressed cells are empty, and SQL'sSUMskips empty values, so the total adds only the published cells.SUM(suppressed) AS cells_suppressed:suppressedis stored as 1 or 0, so its sum counts the hidden cells in each group. A total with a count above 0 is incomplete, and says so.WHERE substr(province, 1, 2) = :regionkeeps the provinces of one region;:regionis a named parameter that Datasette fills from the address, so the same query serves every region without the value ever being pasted into the SQL.GROUP BY province, sexmakes one row per province and sex, andORDER BY province, sexfixes their order;ASgives each sum the name the check, and the portal's visitors, see.
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 isNone; - when a page says
"ok": false, stop withraise RuntimeError(page["error"]); - when a page says
"truncated": true, stop withraise 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.
The idea
A Datasette table comes in pages, each saying where the next one is. Follow them until there is none, and stop with an error the moment a page is an error or was cut short, so a half-read table never looks complete.
The function, solved
Copy it with the button on the code's frame, and paste it over all the code in the exercise's cell, so the indentation stays right.
def read_datasette(fetch, url: str) -> pd.DataFrame: """Every row of a Datasette table, following next_url from page to page.""" # Exercise 5.1: the task is in the text above this cell. rows = [] while url: page = fetch(url) if not page["ok"]: raise RuntimeError(page["error"]) if page.get("truncated"): raise RuntimeError("truncated") rows.extend(page["rows"]) url = page.get("next_url") return pd.DataFrame(rows)Why each part is there
while url:runs while there is an address to read;url = page.get("next_url")at the end moves on, and isNoneon the last page, which ends the loop..getalso covers a canned query's answer, which has nonext_urlat all.if not page["ok"]: raise RuntimeError(page["error"])stops on an error page, passing on Datasette's own message, instead of reading its missing rows as an empty table.if page.get("truncated"): raise RuntimeError("truncated")stops when Datasette cut an answer at its row limit: the rows that arrived are real, but not all of them.rows.extend(page["rows"])adds each row of the page to the list (appendwould add the whole list as one item), andpd.DataFrame(rows)makes the table.fetchis an input, not a fixed web request, so the check can give the function pretend pages, three of them holding five rows, and the live cell can give it the real portal.