0%

Create, Import, and Evolve a Schema · practice

Import Every New Row or None

You now have a complete of validated before a database is touched. The next boundary receives that staged list and one caller-owned connection:

import_staged_items(connection, staged_items)

Complete this in catalog_import.py. Put the whole inside one with connection: block and execute the fixed INSERT through five placeholders. Count each successful insert, then return (inserted, 0) after the commits. Do not use executemany, interpolate values into SQL, commit inside the loop, or close the supplied connection.

Run keeps one fixed sequence while you build both versions of this function:

== import staged items ==
First call: import_staged_items(connection, staged_items)
First result: (3, 0)
1 | TL-100 | Cordless drill | tools | 7
2 | EV-200 | Folding table | events | 14
3 | EL-300 | Projector | electronics | 3
Exact-repeat call: import_staged_items(connection, staged_items)
Exact-repeat result: IntegrityError
Changed-row call: import_staged_items(connection, changed_items)
Changed-row result: IntegrityError
Items after reopening: 3

The repeated calls fail for now because this first version accepts only all-new rows. That is an honest intermediate result. The important behavior is what happens when a conflict appears after an earlier insert in the same call: the context manager rolls the whole attempt back. Once the file is reopened, none of that call’s earlier new rows may remain.

Let sqlite3.IntegrityError propagate. This function does not yet have enough rules to explain whether an existing row is an exact retry or a disagreement. It should not hide that missing policy behind a generic success.

A transaction describes one useful outcome. For this function, the outcome is the entire staged batch, not each individual row. Keeping the connection open also keeps ownership clear: the caller supplied it and decides when its lifetime ends.

After success, the connection should be outside a transaction and another connection should see every inserted row.

Task

Complete import_staged_items(connection, items) in catalog_import.py.

Insert each validated with the fixed placeholder statement inside one connection context. Return (inserted_count, 0) after success. A later conflict must roll back every earlier insert from that invocation, and the caller’s connection must remain open.