Scene 01
Continue from our products
Welcome back! Let us turn our product catalogue into an invoicing example with customers, dates, quantities, and prices. 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
From products to invoices
customer ← invoice ← invoice_row → product
Continue in the same Python session
Starting fresh? Rerun lesson one first
>>> 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
>>> customer = shop.table("customer", pkey="id")
>>> _ = customer.column("id", dtype="I")
>>> _ = customer.column("name", size=":60")
>>> _ = customer.column("city", size=":60")
>>> db.reload_model(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 key in the customer table.
Example & screen notes
>>> invoice = shop.table("invoice", pkey="id")
>>> _ = invoice.column("id", dtype="I")
>>> _ = invoice.column("number", size=":20")
>>> _ = invoice.column("invoice_date", dtype="D")
>>> customer_id = invoice.column("customer_id", dtype="I")
>>> _ = customer_id.relation("shop.customer.id", foreign_key=True)
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 invoice identifier points to its invoice. The product code points to the original catalogue.
Example & screen notes
>>> 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")
>>> invoice_id = invoice_row.column("invoice_id", dtype="I")
>>> _ = invoice_id.relation("shop.invoice.id", foreign_key=True)
>>> product_code = invoice_row.column("product_code", size=":10")
>>> _ = product_code.relation("shop.product.code", foreign_key=True)
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
>>> db.reload_model(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");
>>> 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, and unit prices. We will calculate the total ourselves.
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
>>> import csv
>>> from datetime import date
>>> from decimal import Decimal
>>> from pathlib import Path
>>> print("\n".join(Path("invoice_rows.csv").read_text().splitlines()[:4]))
id,invoice_id,product_code,quantity,unit_price
1,101,TEA,2,3.50
2,101,COF,1,6.90
3,101,MUG,4,5.25
Scene 07
Import the catalogue and customers
Let's expand the product catalogue. Read the new products, convert stock to an integer, insert each dictionary, and commit. Let's count the catalogue. Twenty-seven imported products plus our original three makes thirty. Let's import customers and their cities. Convert their keys to integers. Here are three customers, including Ada in Florence.
Example & screen notes
>>> with open("products.csv", newline="", encoding="utf-8") as source:
... for row in csv.DictReader(source):
... row["stock"] = int(row["stock"])
... _ = products.insert(row)
...
>>> db.commit()
>>> products.query().count()
30
>>> db.commit()
>>> with open("customers.csv", newline="", encoding="utf-8") as source:
... for row in csv.DictReader(source):
... row["id"] = int(row["id"])
... _ = customers.insert(row)
...
>>> 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. Convert each year-month-day string to a Python date before inserting.
Example & screen notes
>>> with open("invoices.csv", newline="", encoding="utf-8") as source:
... for row in csv.DictReader(source):
... row["id"] = int(row["id"])
... row["customer_id"] = int(row["customer_id"])
... row["invoice_date"] = date.fromisoformat(row["invoice_date"])
... _ = invoices.insert(row)
...
>>> db.commit()
Scene 09
Import prices and calculate totals
Let's import the invoice rows. Convert each price directly from its text to Decimal. Multiply by quantity, round to two decimal places, and store the total. This calculation happens during import. Let's check how much we imported. Twelve invoices and thirty-six invoice rows.
Example & screen notes
>>> with open("invoice_rows.csv", newline="", encoding="utf-8") as source:
... for row in csv.DictReader(source):
... for key in ("id", "invoice_id", "quantity"):
... row[key] = int(row[key])
... row["unit_price"] = Decimal(row["unit_price"])
... row["total"] = (row["quantity"] * row["unit_price"]).quantize(Decimal("0.01"))
... _ = rows.insert(row)
...
>>> db.commit()
>>> 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
>>> 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
>>> 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
>>> 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, calculated row totals, and read fields through relationships. Next, we will use those same paths to filter and sort.
Example & screen notes
Thank you!
30 products · 8 customers · 12 invoices · 36 rows
Row total = quantity × unit price
Next: filter and sort through relationships