Asqueel

Course / Orientation / Lesson 04

Lesson 04 · Orientation

Aliases and formula columns

Give a relationship path a short name, calculate each row's amount and see it change when the quantity changes.

1:37 · video6 scenesPrerequisites: the in-memory Python session from lesson 3

Asqueel 0.6.0 · Python console demonstration · API compatibility

Course contents
Orientation
  1. 01 Welcome: your first table
  2. 02 Customers, invoices and relationship paths
  3. 03 Filter and sort through relationships
  4. 04 Aliases and formula columns
  5. 05 Aggregate related records
  6. 06 Group by and HAVING
  7. 07 Fetch and export
  8. 08 Optional: find the best buyer
  9. 09 Writes and transactions
  10. 10 Python triggers
The model
  1. 11 virtualRelation
  2. 12 Derived columns: aliasColumn, formulaColumn
  3. 13 Advanced formulas
  4. 14 subQueryColumn and bagItemColumn
  5. 15 Layered recipes
  6. 16 Extending the grammar
  7. 17 The model hosts external metadata
Reading data
  1. 18 query(...): syntax and clauses
  2. 19 Parameters and macros
  3. 20 Fetch terminals
  4. 21 The environment
  5. 22 Row policies
  6. 23 Locking on read: for_update
Writing data
  1. 24 insert, update, delete
  2. 25 The order of the write cycle
  3. 26 Field triggers
  4. 27 Relation actions: Python and SQL
  5. 28 Writes without triggers: sql_*, table.lock
  6. 29 sys_fields, hierarchical, auto_counter
  7. 30 recordToUpdate and batchUpdate
Transactions & operations
  1. 31 Transactions and connections
  2. 32 Deferred callbacks
  3. 33 Direct SQL
  4. 34 Import and migrations
  5. 35 The command line
  6. 36 Integration in a host application
  7. 37 The complete example
Other Python ORMs
  1. 38 asqueel, SQLAlchemy, Django and Peewee
WATCH THE LESSON1:37 · English
Download MP4 ↓English captions ↓

Follow the chapters, then try the examples yourself. The complete transcript and code are below.

Try it yourself

Run this lesson in Google Colab, with the starting data included. A Google account is required to execute code. You can also download the notebook for local Jupyter.

Try it yourself ↗Download notebook ↓

Download this example ↓ · Includes four CSV files and console commands. Put the CSV files in your working folder. If Python was closed, use start-here.py to recreate lessons one, two and three.

Download individual CSV files: customers.csv · invoice_rows.csv · invoices.csv · products.csv

Scene 01

What we'll learn in this lesson

Welcome back! Here's what we'll learn in this lesson. Give a relationship path a short name, calculate each invoice row's amount, and watch that formula respond to a quantity change. One row at a time, in our existing database.

Scene 02

Give the city path a short name

Let's call this path city. Alias column gives our two-hop customer city a reusable name. Reload the model and get a fresh table handle. Let's read and filter with that name. Dollar city follows the original relationship path. It does not store another copy of the city.

Example & screen notes
>>> 
>>> ######## Call this path city ########
>>> 
>>> _ = invoice_row.aliasColumn(
...     "city", relation_path="@invoice_id.@customer_id.city",
... )
>>> db.reloadModel(model)
>>> rows = db.table("shop.invoice_row")

>>> 
>>> ######## Read and filter with that name ########
>>> 
>>> rows.query(
...     columns="$city, @product_code.name",
...     where="$city = :city", city="Florence", limit=3,
... ).fetch()
[[city=Florence,_product_code_name=Green tea], [city=Florence,_product_code_name=Coffee], [city=Florence,_product_code_name=Mug]]
>>> db.commit()

Scene 03

Calculate the amount of each row

Let's calculate quantity times price. Formula column declares an SQL expression. Round it to two decimal places, and describe the result as numeric. Let's read the calculated amounts. Two teas at three fifty make seven. The database evaluates the formula when we query it.

Example & screen notes
>>> 
>>> ######## Calculate quantity times price ########
>>> 
>>> _ = invoice_row.formulaColumn(
...     "calculated_total",
...     sql_formula="ROUND($quantity * $unit_price, 2)",
...     dtype="N",
... )
>>> db.reloadModel(model)
>>> rows = db.table("shop.invoice_row")

>>> 
>>> ######## Read the calculated amounts ########
>>> 
>>> rows.query(
...     columns="$quantity, $unit_price, $calculated_total",
...     where="$invoice_id = :invoice", invoice=101,
...     order_by="$id",
... ).fetch()
[[quantity=2,unit_price=3.5,calculated_total=7.0], [quantity=1,unit_price=6.9,calculated_total=6.9], [quantity=4,unit_price=5.25,calculated_total=21.0]]
>>> db.commit()

Scene 04

No new stored columns

Let's check whether the database needs changing. True means no migration is needed. Aliases and formulas add no physical columns.

Example & screen notes
>>> 
>>> ######## Check whether the database needs changing ########
>>> 
>>> db.migration_plan().empty
True

Scene 05

Watch the formula respond to a change

Let's temporarily change the quantity to three. The calculated amount becomes ten fifty immediately. The old total imported from CSV stays seven because it is an ordinary stored column. Let's undo that change. Rollback restores the original quantity, so the rest of our examples use the same dataset.

Example & screen notes
>>> 
>>> ######## Temporarily change the quantity to three ########
>>> 
>>> rows.update({"quantity": 3}, pkey=1)
{'quantity': 3}
>>> rows.query(
...     columns="$quantity, $total, $calculated_total",
...     where="$id = :id", id=1,
... ).fetch()
[[quantity=3,total=7,calculated_total=10.5]]

>>> 
>>> ######## Undo that change ########
>>> 
>>> db.rollback()

Scene 06

Thank you — next steps

Thanks for following along! We named a path and calculated a row amount without storing another column. Next, we will summarize related records: invoice, customer and product totals.