Skip to content
Learn Python with BNHR

Store: rules that hold in the database

On this page
  1. Overview
  2. 2.1 · One pandera rule, in SQL
  3. 3.1 · Load a round safely
  4. 4.1 · Would the database refuse these rows?

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.

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), then con.execute(f"INSERT INTO {table} BY NAME SELECT * FROM incoming"). BY NAME matches 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.

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.

Something new

Identify results
LayerExplore a module
ModuleGet: Round 1 arrives
AboutModule 2 of Python for Data Management: read Round 1 without losing anything, join it to reference data and compare it with published totals. Its sections, its notebooks to read on the web, and the companion's solutions and explanations.
CoursePython for Data Management, free