Every exercise in 22_store_round.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. One pandera rule, in SQL
The task
Why it matters: this small translation is what keeps the rule in one place. With it, the database's rules are generated from your contract, and change when it changes.
Input → output: a column name and a pandera check → the SQL condition that says the same thing, or None for a rule SQL cannot say (a rule written as a Python lambda).
check.name |
check.statistics |
SQL condition for the column age, sex, … |
|---|---|---|
greater_than |
{'min_value': 0} |
weight > 0 (already written) |
greater_than_or_equal_to |
{'min_value': 1} |
member_no >= 1 (already written) |
in_range |
{'min_value': 0, 'max_value': 120, ...} |
age BETWEEN 0 AND 120 |
isin |
{'allowed_values': ['1', '2']} |
sex IN ('1', '2') (text in single quotes) |
str_matches |
{'pattern': '^\d{10}$'} |
regexp_full_match(area_code, '^\d{10}$') |
| anything else | None |
For isin, build the list with a join: ", ".join(f"'{v}'" for v in values) puts each value in single quotes, separated by commas.
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
| SQL error: … column "1" not found | Text values in double quotes: in SQL that names a column. | single quotes around each value |
SQL error near [ |
The Python list was put into the SQL as it is: IN (['1', '2']). |
", ".join(...) |
| The lambda rule gives an SQL error | The last line, return None, is missing, so the function falls through. |
return None for anything else |
| 113 hours passes | BETWEEN was given the wrong numbers. |
stats["min_value"], stats["max_value"] |
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
Each kind of pandera check has an SQL twin. Read the check's name to know which, its statistics for the numbers or values, and write the condition as text. A rule SQL cannot say gets None, so it stays in pandera only.
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 check_to_sql(column: str, check: pa.Check) -> str | None: """The SQL condition that says what a pandera check says about a column, or None if SQL cannot say it.""" # Exercise 2.1: the task and examples are in the text above this cell. stats = check.statistics # the check's settings, a dictionary if check.name == "greater_than": return f"{column} > {stats['min_value']}" if check.name == "greater_than_or_equal_to": return f"{column} >= {stats['min_value']}" if check.name == "in_range": return f"{column} BETWEEN {stats['min_value']} AND {stats['max_value']}" if check.name == "isin": return f"{column} IN ({', '.join(repr(str(v)) for v in stats['allowed_values'])})" if check.name == "str_matches": return f"regexp_full_match({column}, '{stats['pattern']}')" return NoneWhy each part is there
in_rangebecomesf"{column} BETWEEN {stats['min_value']} AND {stats['max_value']}". SQL'sBETWEENincludes both ends, as pandera'sin_rangedoes by default, soage BETWEEN 0 AND 120refuses exactly what the contract refuses.isinbecomessex IN ('1', '2'). Each value goes in single quotes, because in SQL double quotes name a column;repr(str(v))gives'1', quotes included, and', '.join(...)puts commas between. Pasting the Python list as it is givesIN (['1', '2']), an SQL error.str_matchesbecomesregexp_full_match(area_code, '^\d{10}$'): DuckDB's function for a pattern that must match the whole text, as pandera's does.return Nonefor anything else, such as a rule written as a lambda. Without it the function would give back nothing by accident and the generated table would be wrong; with it, the rule is knowingly left to pandera, which the layer table shows.
Exercise 3.1. Load a round safely
The task
Why it matters: a pipeline is run again and again; a load that doubles rows, or leaves half a round, quietly corrupts every total after it.
Input → output: a DuckDB connection, a table name, a round ID, and the round's rows → the number of rows of that round in the table after the load. If anything fails, the table is left exactly as it was, and the error is raised again so the caller knows.
con.execute("BEGIN TRANSACTION")
try:
... # 1. delete the round's rows
... # 2. insert the new rows
con.execute("COMMIT")
except Exception:
con.execute("ROLLBACK") # undo everything since BEGIN
raise # and let the error go on to the caller
- Delete:
con.execute(f"DELETE FROM {table} WHERE round = ?", [round_id]). The?is filled in by DuckDB with the value in the list, which is safer than putting it into the text yourself. - Insert: make the DataFrame visible to DuckDB with
con.register("incoming", rows), thencon.execute(f"INSERT INTO {table} BY NAME SELECT * FROM incoming").BY NAMEmatches columns by name, not by position. - Count:
con.execute(f"SELECT count(*) FROM {table} WHERE round = ?", [round_id]).fetchall()[0][0].
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
| 6 rows after the second load | The round's rows were not deleted first. | the DELETE in step 1 |
| The bad load was accepted, or the table changed | There is no transaction, or no ROLLBACK on error. |
BEGIN, and ROLLBACK in except |
| TransactionContext Error: cannot start a transaction within a transaction | An earlier failed run left a transaction open, because ROLLBACK was missing. |
add the except part, then run the cell again |
| The error disappears, but the caller never hears of it | raise is missing after ROLLBACK. |
re-raise the error |
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
Loading a round means "replace this round's rows". Doing it inside a transaction makes it all or nothing: if any step fails, the table goes back to exactly how it was. Deleting the round first makes running it again safe.
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 load_round(con: duckdb.DuckDBPyConnection, table: str, round_id: str, rows: pd.DataFrame) -> int: """Replace a round's rows in a table, all or nothing. Returns how many rows the round has afterwards.""" # Exercise 3.1: the task and examples are in the text above this cell. con.execute("BEGIN TRANSACTION") try: con.execute(f"DELETE FROM {table} WHERE round = ?", [round_id]) con.register("incoming", rows) con.execute(f"INSERT INTO {table} BY NAME SELECT * FROM incoming") con.execute("COMMIT") except Exception: con.execute("ROLLBACK") raise finally: con.unregister("incoming") return con.execute(f"SELECT count(*) FROM {table} WHERE round = ?", [round_id]).fetchall()[0][0]Why each part is there
BEGIN TRANSACTIONopens the transaction; nothing done after it is final untilCOMMIT.DELETE FROM {table} WHERE round = ?removes the round's earlier rows, so a second load gives the same 3 rows, not 6. The?is filled in by DuckDB with the round ID, so the value can never change the query.con.register("incoming", rows)lets DuckDB read the DataFrame as a table, andINSERT INTO {table} BY NAME SELECT * FROM incomingmatches the columns by name, so their order in the DataFrame does not matter.- On any error,
ROLLBACKundoes the delete and the insert together, andraisepasses the error on, so the caller knows the load failed. WithoutROLLBACKa failed run leaves the transaction open, the third cause in the table above.finally: con.unregister("incoming")tidies up either way. - The count runs after the transaction, so it reports what the table really holds.
Exercise 4.1. Would the database refuse these rows?
The task
Why it matters: knowing what the database alone refuses tells you what the earlier layers must catch. The rules it cannot see are the ones that depend on pandera.
Input → output: a connection, a table name and some rows → "accepted", or the first line of the database's error. Either way, the table is left exactly as it was.
con.execute("BEGIN TRANSACTION")
try:
... # register the rows and insert them BY NAME, as in Exercise 3.1
result = "accepted"
except duckdb.Error as error:
result = str(error).splitlines()[0]
finally:
con.execute("ROLLBACK") # always: this is a test, nothing is kept
return result
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
| The table holds 2 rows after a trial | The insert was kept: no ROLLBACK, or COMMIT instead. |
finally: con.execute("ROLLBACK") |
| Everything is "accepted" | The error is caught too widely, or result is set after the except. |
set result = "accepted" right after the insert |
| cannot start a transaction within a transaction | An earlier run left a transaction open. | the finally part; then run the cell again |
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
Ask the database itself: try the insert inside a transaction, note whether it was accepted or what error it gave, and always roll back. It is a test, so the table must end exactly as it began.
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 refused_by_database(con: duckdb.DuckDBPyConnection, table: str, rows: pd.DataFrame) -> str: """Try inserting rows, keep nothing: "accepted", or the first line of the database's error.""" # Exercise 4.1: the task and examples are in the text above this cell. con.execute("BEGIN TRANSACTION") try: con.register("trial", rows) con.execute(f"INSERT INTO {table} BY NAME SELECT * FROM trial") result = "accepted" except duckdb.Error as error: result = str(error).splitlines()[0] finally: con.execute("ROLLBACK") con.unregister("trial") return resultWhy each part is there
- The insert is Exercise 3.1's,
registerandINSERT ... BY NAME, insideBEGIN TRANSACTION. result = "accepted"comes right after the insert, so it is only set when the insert succeeded.except duckdb.Errorcatches only the database's own refusals, andstr(error).splitlines()[0]keeps the first line, which names the rule that refused.finally: con.execute("ROLLBACK")runs whether the insert worked or not, so even an accepted row is never kept. Each time the check looks, the table still holds exactly its one row.- Which rows the database alone refuses, and which it lets through (the rules across two columns), is the layer table's lesson: those are the ones only pandera catches.