Scene 01
What we'll learn in this lesson
Welcome back! Here's what we'll learn. In lesson nine we supplied totals ourselves. Now Python hooks will calculate and validate them when we write. We will also see which direct SQL operations bypass this behavior. These are Python hooks in Asqueel, not SQL triggers installed in SQLite. We keep our database in memory.
Scene 02
Give invoice rows their Python behavior
Let's define the write behavior. Our shared calculation checks quantity and price, then stores their product rounded to cents. The inserting and updating hooks run before the SQL write. The updated hook prints the old and new quantities afterwards. Afterwards means after the statement, not after commit. Let's attach it to invoice rows. X table class gives this existing table its Python behavior. Reload the model and get a fresh table handle. True means no migration is needed: we added Python hooks, not columns. Existing rows are not recalculated.
Example & screen notes
>>>
>>> ######## Define the write behavior ########
>>>
>>> from asqueel import SqlTable
>>> from decimal import Decimal
>>>
>>> class InvoiceRowTable(SqlTable):
... def calculate_total(self, record):
... if record["quantity"] <= 0:
... raise ValueError("Quantity must be positive")
... price = Decimal(str(record["unit_price"]))
... if price < 0:
... raise ValueError("Price must not be negative")
... record["total"] = (
... record["quantity"] * price
... ).quantize(Decimal("0.01"))
... def trigger_onInserting(self, record):
... self.calculate_total(record)
... def trigger_onUpdating(self, record, old_record=None):
... self.calculate_total(record)
... def trigger_onUpdated(self, record, old_record=None):
... if old_record is not None:
... print("Quantity:", old_record["quantity"],
... "->", record["quantity"])
...
>>>
>>> ######## Attach it to invoice rows ########
>>>
>>> _ = shop.table("invoice_row", x_table_class=InvoiceRowTable)
>>> db.reloadModel(model)
>>> rows = db.table("shop.invoice_row")
>>> db.migration_plan().empty
True
Scene 03
Calculate totals on insert and update
Let's insert two teas. We supply quantity and price, but no total. The inserting hook stores seven. The formula column from lesson four also reads seven. The stored total is written by our hook; the formula is calculated when we query. This change is still uncommitted. Let's change only the quantity. Query update supplies the complete record and its previous values, as in lesson nine. This time our updating hook recalculates ten fifty automatically. The updated hook prints two to three before commit.
Example & screen notes
>>>
>>> ######## Insert two teas ########
>>>
>>> _ = rows.insert({
... "id": 901, "invoice_id": 101, "product_code": "TEA",
... "quantity": 2, "unit_price": Decimal("3.50"),
... })
>>> rows.query(
... columns="$id, $quantity, $unit_price, $total, $calculated_total",
... where="$id = :id", id=901,
... ).fetchRecord()
[id=901,quantity=2,unit_price=3.5,total=7,calculated_total=7.0]
>>>
>>> ######## Change only the quantity ########
>>>
>>> rows.query(where="$id = :id", id=901).update({"quantity": 3})
Quantity: 2 -> 3
[901]
>>> rows.query(
... columns="$quantity, $total, $calculated_total",
... where="$id = :id", id=901,
... ).fetchRecord()
[quantity=3,total=10.5,calculated_total=10.5]
Scene 04
Direct SQL skips the Python hooks
Let's compare a direct SQL update. SQL update changes one selected row and returns one. It skips our Python hooks: no message appears, and the stored total stays ten fifty. The formula now reads fourteen. Database constraints still apply. The direct SQL insert used for CSV imports also skips these Python hooks. Let's run the normal write cycle again. Normal query update runs our hooks again. Both totals become seventeen fifty, and the result contains the updated primary key.
Example & screen notes
>>>
>>> ######## Compare a direct SQL update ########
>>>
>>> rows.query(where="$id = :id", id=901).sql_update({"quantity": 4})
1
>>> rows.query(
... columns="$quantity, $total, $calculated_total",
... where="$id = :id", id=901,
... ).fetchRecord()
[quantity=4,total=10.5,calculated_total=14.0]
>>>
>>> ######## Run the normal write cycle again ########
>>>
>>> rows.query(where="$id = :id", id=901).update({"quantity": 5})
Quantity: 4 -> 5
[901]
>>> rows.query(
... columns="$quantity, $total, $calculated_total",
... where="$id = :id", id=901,
... ).fetchRecord()
[quantity=5,total=17.5,calculated_total=17.5]
Scene 05
Undo the uncommitted examples
Let's undo our demonstration writes. Rollback removes our new row and its updates together, returning us to thirty-six original rows. It does not erase messages already printed by a Python hook. Commit here closes the verification read; it does not save the rolled-back examples.
Example & screen notes
>>>
>>> ######## Undo our demonstration writes ########
>>>
>>> db.rollback()
>>> rows.query(where="$id >= :first_demo", first_demo=901).fetch()
[]
>>> rows.query().count()
36
>>> db.commit()
Scene 06
Reject an invalid write and recover
Let's reject a zero quantity. Our inserting hook raises a value error before SQL insertion. After a failed write cycle, roll back before continuing. That rollback affects all pending work in the transaction, not just this row. We began from a clean boundary, so no earlier work is lost. The empty result confirms that row nine hundred and three was never stored.
Example & screen notes
>>>
>>> ######## Reject a zero quantity ########
>>>
>>> try:
... rows.insert({
... "id": 903, "invoice_id": 101, "product_code": "TEA",
... "quantity": 0, "unit_price": Decimal("3.50"),
... })
... except ValueError as error:
... print(str(error))
... db.rollback()
...
Quantity must be positive
>>> rows.query(where="$id = :id", id=903).fetch()
[]
>>> db.commit()
Scene 07
What to remember
Thanks for following along! Use normal writes for Python validation and calculations. Use direct SQL deliberately, with complete valid data. Keep transaction boundaries explicit. We have added automatic write behavior to the operations from lesson nine.