Scene 01
What we'll learn in this lesson
Welcome back! Here's what we'll learn. Insert an invoice row, change it, and delete it. Then use commit and rollback to decide which changes remain. We use the same database in memory, with no custom Python triggers yet.
Scene 02
Insert a row and commit it
Let's add two teas and commit the row. We supply the stored total ourselves: two times three fifty is seven. Insert adds the row; commit closes this transaction. Because SQLite is in memory, commit keeps data only while this connection exists. It does not create a database file.
Example & screen notes
>>>
>>> ######## Add two teas and commit the row ########
>>>
>>> from decimal import Decimal
>>> rows = db.table("shop.invoice_row")
>>> _ = rows.insert({
... "id": 901, "invoice_id": 101, "product_code": "TEA",
... "quantity": 2, "unit_price": Decimal("3.50"),
... "total": Decimal("7.00"),
... })
>>> rows.query(
... columns="$id, $quantity, $unit_price, $total",
... where="$id = :id", id=901,
... ).fetchRecord()
[id=901,quantity=2,unit_price=3.5,total=7]
>>> db.commit()
Scene 03
Update a complete record
Let's change the quantity to three. Read the physical fields, copy the record and update quantity and total together. Table update does not read or merge previous values for us. Passing old record makes those previous values available to the write cycle. We leave this change uncommitted.
Example & screen notes
>>>
>>> ######## Change the quantity to three ########
>>>
>>> old = dict(rows.query(where="$id = :id", id=901).fetchRecord())
>>> changed = dict(old, quantity=3, total=Decimal("10.50"))
>>> _ = rows.update(changed, old_record=old)
>>> rows.query(
... columns="$quantity, $unit_price, $total",
... where="$id = :id", id=901,
... ).fetchRecord()
[quantity=3,unit_price=3.5,total=10.5]
Scene 04
Update the selected row directly
Let's update the selection to four teas. Query update reads the selected records, merges our changes, and calls table update with their previous values. We pass only the changed fields. Its result is a list of updated primary keys. Our filter selects just row nine hundred and one.
Example & screen notes
>>>
>>> ######## Update the selection to four teas ########
>>>
>>> rows.query(where="$id = :id", id=901).update({
... "quantity": 4, "total": Decimal("14.00"),
... })
[901]
>>> rows.query(
... columns="$quantity, $total", where="$id = :id", id=901,
... ).fetchRecord()
[quantity=4,total=14]
Scene 05
Delete, then roll back the transaction
Let's delete the row, then undo our pending work. Query delete returns the removed records. The empty fetch confirms the row is gone. Rollback restores its last committed state: quantity two and total seven. It undoes both updates and the deletion together, not just the last statement.
Example & screen notes
>>>
>>> ######## Delete the row, then undo our pending work ########
>>>
>>> deleted = rows.query(where="$id = :id", id=901).delete()
>>> len(deleted)
1
>>> rows.query(where="$id = :id", id=901).fetch()
[]
>>> db.rollback()
>>> rows.query(
... columns="$quantity, $total", where="$id = :id", id=901,
... ).fetchRecord()
[quantity=2,total=7]
Scene 06
Remove the demonstration row for good
Let's clean up and commit the deletion. This time we commit the delete. The original thirty-six rows remain. The final commit closes the count query's read transaction, ready for the next lesson.
Example & screen notes
>>>
>>> ######## Clean up and commit the deletion ########
>>>
>>> _ = rows.query(where="$id = :id", id=901).delete()
>>> db.commit()
>>> rows.query().count()
36
>>> db.commit()
Scene 07
Thank you — next, Python triggers
Thanks for following along! We inserted, updated and deleted a row, and separated committed changes from pending work. We supplied every stored total ourselves. In the next lesson, Python triggers will calculate and validate those totals for us.