Run It Twice, Get the Same Table in Python

Loads run twice more often than you'd think: a scheduler retries a job after a crash, or someone reruns a day by hand. If the load only appends with INSERT, each extra run adds the day's rows again, and every report built on the table counts that day twice. A load is idempotent when running it once or five times leaves the same table.

The recipe: first remove whatever an earlier run of this day left, then insert the day again. DELETE FROM table WHERE … removes the rows that match the condition; without a WHERE, it removes every row. stg_orders keeps each row's filename, so one day's rows are WHERE filename = ?.

One trap is left: if the insert fails after the delete ran, the day is gone. A transaction turns the two into one change: after BEGIN, the changes count only once COMMIT saves them all together, and ROLLBACK instead undoes everything since BEGIN.

con.execute("BEGIN")
con.execute("DELETE FROM raw_orders WHERE filename = ?", ["orders/2026-05-07.csv"])
con.execute("ROLLBACK")   # undone: 05-07's 12 rows are back in raw_orders

The starting code makes an empty stg_orders, the statement insert_day and a function load_day(filename) that only inserts; then it loads 05-01, and 05-02 twice, which now gives 24 rows for 05-02. The check prints the rows per file, then adds a bad row to 05-02's file in raw_orders (quantity 'three'), loads the day again, and runs ROLLBACK when that fails. Your program must print:

filename | row_count
orders/2026-05-01.csv | 10
orders/2026-05-02.csv | 12
(2 rows)
A bad row added to 05-02's file, and the day loaded again:
Conversion Error: Could not convert string 'three' to INT32 when casting from source column quantity
filename | row_count
orders/2026-05-01.csv | 10
orders/2026-05-02.csv | 12
(2 rows)

Your task: write delete_day, a DELETE FROM stg_orders for one file's rows; run it in load_day before the insert; then open the function with BEGIN and close it with COMMIT.

Assignment
Follow the Coding Tutorial to make load_day delete the day's rows before inserting them, inside one transaction.

Hint
The delete goes above the insert: the other way round, it would remove the rows you just inserted. BEGIN is the first line of load_day and COMMIT its last; until both are there, the program stops on the second call because a transaction is left open.