Asqueel

Course / Orientation / Lesson 02

Lesson 02 · Orientation

Customers, invoices and relationship paths

Import a product catalogue, customers and dated invoices from CSV. Import quantities, prices and totals, then read customer and product fields through relationship paths.

3:51 · video13 scenesPrerequisites: the in-memory Python session from lesson 1; rerun its commands if the console was closed

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 LESSON3:51 · 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 lesson one.

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

Scene 01

What we'll learn in this lesson

Welcome back! Here's what we'll learn in this lesson. Create customers, invoices and invoice rows, import our CSV files, and read fields through relationship paths. Keep the same Python console open. If you closed it, rerun lesson one. The database is still in memory. Let's check our starting point. We already have three products.

Example & screen notes
>>> 
>>> ######## Check our starting point ########
>>> 
>>> products.query(columns="$code, $name").fetch()
[[code=TEA,name=Green tea], [code=COF,name=Coffee], [code=MUG,name=Mug]]
>>> db.commit()

Scene 02

Add customers and their city

Let's describe our customers. Alongside the identifier and name, add their city. Let's create the customer table. Reload the model and migrate, keeping the existing products.

Example & screen notes
>>> 
>>> ######## Describe our customers ########
>>> 
>>> customer = shop.table("customer", pkey="id")
>>> _ = customer.column("id", dtype="I")
>>> _ = customer.column("name", size=":60")
>>> _ = customer.column("city", size=":60")

>>> 
>>> ######## Create the customer table ########
>>> 
>>> db.reloadModel(model)
>>> print(db.migrate().commands)
CREATE TABLE "shop"."shop_customer"(
 "id" integer NOT NULL,
 "name" character varying(60),
 "city" character varying(60),
 PRIMARY KEY ("id")
);

Scene 03

Give each invoice a date and customer

Let's describe a dated invoice. D means a date column. An invoice has its own date and number. Let's connect invoices to customers. The stored customer identifier refers to the customer key. The inverse name invoices lets a customer reach its invoices.

Example & screen notes
>>> 
>>> ######## Describe a dated invoice ########
>>> 
>>> invoice = shop.table("invoice", pkey="id")
>>> _ = invoice.column("id", dtype="I")
>>> _ = invoice.column("number", size=":20")
>>> _ = invoice.column("invoice_date", dtype="D")

>>> 
>>> ######## Connect invoices to customers ########
>>> 
>>> customer_id = invoice.column("customer_id", dtype="I")
>>> _ = customer_id.relation(
...     "shop.customer.id", foreign_key=True, relation_name="invoices",
... )

Scene 04

Give each row a quantity, price and total

Let's describe an invoice row. It needs quantity, unit price, and total. N declares numeric columns; twelve comma two describes precision and scale. Let's connect the row at both ends. The identifiers point to the invoice and product. Each inverse is called rows: the rows of one invoice, or the rows selling one product.

Example & screen notes
>>> 
>>> ######## Describe an invoice row ########
>>> 
>>> invoice_row = shop.table("invoice_row", pkey="id")
>>> _ = invoice_row.column("id", dtype="I")
>>> _ = invoice_row.column("quantity", dtype="I")
>>> _ = invoice_row.column("unit_price", dtype="N", size="12,2")
>>> _ = invoice_row.column("total", dtype="N", size="12,2")

>>> 
>>> ######## Connect the row at both ends ########
>>> 
>>> invoice_id = invoice_row.column("invoice_id", dtype="I")
>>> _ = invoice_id.relation(
...     "shop.invoice.id", foreign_key=True, relation_name="rows",
... )
>>> product_code = invoice_row.column("product_code", size=":10")
>>> _ = product_code.relation(
...     "shop.product.code", foreign_key=True, relation_name="rows",
... )

Scene 05

Create the connected tables

Let's apply the new model. The migration creates invoices and rows, including their foreign keys. Let's get fresh table handles. We will populate these four tables from CSV files.

Example & screen notes
>>> 
>>> ######## Apply the new model ########
>>> 
>>> db.reloadModel(model)
>>> print(db.migrate().commands)
CREATE TABLE "shop"."shop_invoice"(
 "id" integer NOT NULL,
 "number" character varying(20),
 "invoice_date" date,
 "customer_id" integer,
 PRIMARY KEY ("id"),
 CONSTRAINT "fk_9e89d3b2" FOREIGN KEY ("customer_id") REFERENCES "shop_customer" ("id") ON UPDATE CASCADE
);
CREATE INDEX "shop"."idx_bf6aec23" ON "shop_invoice" ("customer_id");
CREATE TABLE "shop"."shop_invoice_row"(
 "id" integer NOT NULL,
 "quantity" integer,
 "unit_price" numeric(12,2),
 "total" numeric(12,2),
 "invoice_id" integer,
 "product_code" character varying(10),
 PRIMARY KEY ("id"),
 CONSTRAINT "fk_3f48b098" FOREIGN KEY ("invoice_id") REFERENCES "shop_invoice" ("id") ON UPDATE CASCADE,
 CONSTRAINT "fk_0cadb045" FOREIGN KEY ("product_code") REFERENCES "shop_product" ("code") ON UPDATE CASCADE
);
CREATE INDEX "shop"."idx_0d71220a" ON "shop_invoice_row" ("invoice_id");
CREATE INDEX "shop"."idx_657cb92e" ON "shop_invoice_row" ("product_code");

>>> 
>>> ######## Get fresh table handles ########
>>> 
>>> products = db.table("shop.product")
>>> customers = db.table("shop.customer")
>>> invoices = db.table("shop.invoice")
>>> rows = db.table("shop.invoice_row")

Scene 06

Download the sample CSV files

First, get the sample files. Download the lesson bundle and put its four CSV files in the current working folder. They contain fictional sample data. Let's inspect the row file. The CSV contains the keys, quantities, unit prices, and row totals. Each total is quantity times unit price.

Example & screen notes
Download the CSV bundle below this video
products.csv       27 new products
customers.csv       8 customers
invoices.csv       12 invoices
invoice_rows.csv   36 rows

>>> 
>>> ######## Inspect the row file ########
>>> 
>>> from pathlib import Path
>>> print("\n".join(Path("invoice_rows.csv").read_text().splitlines()[:4]))
id,invoice_id,product_code,quantity,unit_price,total
1,101,TEA,2,3.50,7.00
2,101,COF,1,6.90,6.90
3,101,MUG,4,5.25,21.00

Scene 07

Import the catalogue and customers

Let's expand the product catalogue. Pass the open file to SQL insert. The extension identifies CSV, and the model supplies the types. This bulk import skips Python triggers. Commit when finished. Let's count the catalogue. Twenty-seven imported products plus our original three makes thirty. Let's import customers and their cities. Use the same direct import for customers. Integer keys are converted automatically. Here are three customers, including Ada in Florence.

Example & screen notes
>>> 
>>> ######## Expand the product catalogue ########
>>> 
>>> with open("products.csv", newline="", encoding="utf-8") as source:
...     products.sql_insert(source)
... 
27
>>> db.commit()

>>> 
>>> ######## Count the catalogue ########
>>> 
>>> products.query().count()
30
>>> db.commit()

>>> 
>>> ######## Import customers and their cities ########
>>> 
>>> with open("customers.csv", newline="", encoding="utf-8") as source:
...     customers.sql_insert(source)
... 
8
>>> db.commit()
>>> customers.query(columns="$name, $city", limit=3).fetch()
[[name=Ada,city=Florence], [name=Grace,city=London], [name=Linus,city=Helsinki]]
>>> db.commit()

Scene 08

Import dated invoices

Let's import the invoices. Their customer keys must already exist. The model converts the ISO date strings automatically.

Example & screen notes
>>> 
>>> ######## Import the invoices ########
>>> 
>>> with open("invoices.csv", newline="", encoding="utf-8") as source:
...     invoices.sql_insert(source)
... 
12
>>> db.commit()

Scene 09

Import prices and totals

Let's import the invoice rows. SQL insert converts quantities to integers and amounts to Decimal. Totals are already in this CSV; importing does not calculate them. Let's check how much we imported. Twelve invoices and thirty-six invoice rows.

Example & screen notes
>>> 
>>> ######## Import the invoice rows ########
>>> 
>>> with open("invoice_rows.csv", newline="", encoding="utf-8") as source:
...     rows.sql_insert(source)
... 
36
>>> db.commit()

>>> 
>>> ######## Check how much we imported ########
>>> 
>>> invoices.query().count()
12
>>> rows.query().count()
36
>>> db.commit()

Scene 10

Read the customer city and invoice date

Let's read invoices with their customers. Dollar invoice date is local. At customer I D follows the relation. Asqueel automatically names the returned columns after their paths.

Example & screen notes
>>> 
>>> ######## Read invoices with their customers ########
>>> 
>>> invoices.query(
...     columns="$number, $invoice_date, "
...             "@customer_id.name, @customer_id.city",
...     limit=3,
... ).fetch()
[[number=INV-001,invoice_date=2026-09-28,_customer_id_name=Ada,_customer_id_city=Florence], [number=INV-002,invoice_date=2026-09-29,_customer_id_name=Grace,_customer_id_city=London], [number=INV-003,invoice_date=2026-09-30,_customer_id_name=Linus,_customer_id_city=Helsinki]]
>>> db.commit()

Scene 11

Read products, quantities and amounts

Let's inspect the first invoice. Product names come through the relation. Quantity, unit price, and total belong to the row: two teas at three fifty make seven.

Example & screen notes
>>> 
>>> ######## Inspect the first invoice ########
>>> 
>>> rows.query(
...     columns="@product_code.name, "
...             "$quantity, $unit_price, $total",
...     where="$invoice_id = :invoice", invoice=101,
... ).fetch()
[[_product_code_name=Green tea,quantity=2,unit_price=3.5,total=7], [_product_code_name=Coffee,quantity=1,unit_price=6.9,total=6.9], [_product_code_name=Mug,quantity=4,unit_price=5.25,total=21]]
>>> db.commit()

Scene 12

Follow two relationships

Now follow a row to the customer. Go through the invoice, then the customer. The same path also reaches their city. Let's read across both connections. A single query returns invoice number, customer city, product name, and row total.

Example & screen notes
invoice_row → invoice → customer
@invoice_id.@customer_id.city

invoice_row → invoice
@invoice_id.invoice_date

invoice_row → product
@product_code.name

>>> 
>>> ######## Read across both connections ########
>>> 
>>> rows.query(
...     columns="@invoice_id.number, "
...             "@invoice_id.@customer_id.city, "
...             "@product_code.name, $total",
...     limit=3,
... ).fetch()
[[_invoice_id_number=INV-001,_invoice_id__customer_id_city=Florence,_product_code_name=Green tea,total=7], [_invoice_id_number=INV-001,_invoice_id__customer_id_city=Florence,_product_code_name=Coffee,total=6.9], [_invoice_id_number=INV-001,_invoice_id__customer_id_city=Florence,_product_code_name=Mug,total=21]]
>>> db.commit()

Scene 13

Thank you — a complete small dataset

Thanks for following along! We imported a catalogue and dated invoices, imported row totals, and read fields through relationships. Next, we will use those same paths to filter and sort.