{
  "nbformat": 4,
  "nbformat_minor": 5,
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Customers, invoices and relationship paths\n",
        "\n",
        "Import a product catalogue, customers and dated invoices from CSV. Import quantities, prices and totals, then read customer and product fields through relationship paths.\n",
        "\n",
        "[Watch lesson 2](https://asqueel.org/learn/02-installation-first-db/)\n",
        "\n",
        "**Run the cells from top to bottom in a fresh Python kernel.** Each notebook includes its own starting state. SQLite runs in memory; restarting the kernel discards the database. To repeat the exercise, restart the kernel and run all cells. Colab execution requires a Google account.\n",
        "\n",
        "\u00a9 2026 Softwell S.r.l. \u00b7 Apache-2.0"
      ],
      "id": "l2-c0-58408d385f2d"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Before the examples: `_ = ...` is optional\n",
        "\n",
        "It assigns a return value we do not need, preventing its automatic display in the Python console or notebook. The call still runs normally. This is a Python convention, not an Asqueel requirement. When you need the result, use a meaningful variable name."
      ],
      "id": "l2-c1-5261ce0eb0bf"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Prepare the notebook\n",
        "\n",
        "The video starts in a terminal. Here, install the same official package in the notebook kernel. No virtual environment or separate Python console is needed in Colab."
      ],
      "id": "l2-c2-a53efa980848"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%pip install -q \"asqueel[migration]==0.6.0\""
      ],
      "id": "l2-c3-5283bc5543e7",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%config InteractiveShell.ast_node_interactivity = \"all\""
      ],
      "id": "l2-c4-7a5eb4f63b61",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Recreate the starting point\n",
        "\n",
        "The previous lessons are rebuilt below. The four fictional CSV files are included in this notebook and written into a new temporary folder. No downloads or existing database are needed."
      ],
      "id": "l2-c5-26bce07fb451"
    },
    {
      "cell_type": "code",
      "metadata": {
        "jupyter": {
          "source_hidden": true
        },
        "cellView": "form"
      },
      "source": [
        "#@title Prepare the sample CSV files\n",
        "from pathlib import Path\n",
        "import os\n",
        "import tempfile\n",
        "_lesson_folder = tempfile.TemporaryDirectory(prefix=\"asqueel-notebook-\")\n",
        "os.chdir(_lesson_folder.name)\n",
        "_csv_files = {'customers.csv': 'id,name,city\\n1,Ada,Florence\\n2,Grace,London\\n3,Linus,Helsinki\\n4,Guido,Amsterdam\\n5,Elena,Florence\\n6,Alan,Manchester\\n7,Margaret,New York\\n8,Barbara,Boston\\n', 'invoice_rows.csv': 'id,invoice_id,product_code,quantity,unit_price,total\\n1,101,TEA,2,3.50,7.00\\n2,101,COF,1,6.90,6.90\\n3,101,MUG,4,5.25,21.00\\n4,102,BTEA,2,3.20,6.40\\n5,102,HTEA,3,4.10,12.30\\n6,102,HON,4,7.50,30.00\\n7,103,SUG,3,2.30,6.90\\n8,103,CHO,4,3.90,15.60\\n9,103,BIS,1,2.80,2.80\\n10,104,JAM,4,4.60,18.40\\n11,104,OAT,1,2.40,2.40\\n12,104,JUI,2,2.70,5.40\\n13,105,WAT,1,0.90,0.90\\n14,105,LEM,2,1.80,3.60\\n15,105,KET,3,32.90,98.70\\n16,106,POT,2,18.50,37.00\\n17,106,PLT,3,6.40,19.20\\n18,106,BWL,4,7.80,31.20\\n19,107,SPO,3,9.50,28.50\\n20,107,TRY,4,14.90,59.60\\n21,107,JAR,1,8.20,8.20\\n22,108,NAP,4,12.00,48.00\\n23,108,TWL,1,5.60,5.60\\n24,108,FLT,2,2.10,4.20\\n25,109,TIN,1,6.70,6.70\\n26,109,MAT,2,11.40,22.80\\n27,109,CAF,3,29.90,89.70\\n28,110,BOT,2,13.50,27.00\\n29,110,STR,3,4.80,14.40\\n30,110,SCL,4,24.90,99.60\\n31,111,TEA,3,3.50,10.50\\n32,111,COF,4,6.90,27.60\\n33,111,MUG,1,5.25,5.25\\n34,112,BTEA,4,3.20,12.80\\n35,112,HTEA,1,4.10,4.10\\n36,112,HON,2,7.50,15.00\\n', 'invoices.csv': 'id,number,customer_id,invoice_date\\n101,INV-001,1,2026-09-28\\n102,INV-002,2,2026-09-29\\n103,INV-003,3,2026-09-30\\n104,INV-004,4,2026-10-01\\n105,INV-005,5,2026-10-02\\n106,INV-006,6,2026-10-03\\n107,INV-007,7,2026-10-04\\n108,INV-008,8,2026-10-05\\n109,INV-009,1,2026-10-06\\n110,INV-010,2,2026-10-07\\n111,INV-011,5,2026-10-08\\n112,INV-012,3,2026-10-09\\n', 'products.csv': 'code,name,category,stock\\nBTEA,Black tea,Drinks,30\\nHTEA,Herbal tea,Drinks,18\\nHON,Honey,Food,22\\nSUG,Cane sugar,Food,40\\nCHO,Dark chocolate,Food,35\\nBIS,Biscuits,Food,28\\nJAM,Strawberry jam,Food,16\\nOAT,Oat drink,Drinks,32\\nJUI,Apple juice,Drinks,26\\nWAT,Mineral water,Drinks,60\\nLEM,Lemonade,Drinks,30\\nKET,Kettle,Kitchen,9\\nPOT,Teapot,Kitchen,14\\nPLT,Plate,Kitchen,36\\nBWL,Bowl,Kitchen,24\\nSPO,Spoon set,Kitchen,20\\nTRY,Serving tray,Kitchen,11\\nJAR,Storage jar,Kitchen,25\\nNAP,Cotton napkins,Kitchen,18\\nTWL,Tea towel,Kitchen,30\\nFLT,Coffee filters,Kitchen,45\\nTIN,Tea tin,Kitchen,15\\nMAT,Table mats,Kitchen,16\\nCAF,Coffee maker,Kitchen,7\\nBOT,Water bottle,Kitchen,21\\nSTR,Tea strainer,Kitchen,23\\nSCL,Kitchen scale,Kitchen,8\\n'}\n",
        "for _name, _text in _csv_files.items():\n",
        "    _ = Path(_name).write_text(_text, encoding=\"utf-8\")\n"
      ],
      "id": "l2-c6-89efb228754e",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "jupyter": {
          "source_hidden": true
        },
        "cellView": "form"
      },
      "source": [
        "#@title Recreate the previous lessons\n",
        "\n",
        "######## Import our two tools ########\n",
        "\n",
        "from asqueel import AsqueelDb, SqlDatabaseConfig\n",
        "\n",
        "\n",
        "######## Choose SQLite in memory ########\n",
        "\n",
        "model = SqlDatabaseConfig()\n",
        "root = model.source.db(implementation=\"sqlite\")\n",
        "_ = root.connection(name=\":memory:\", implementation=\"sqlite\")\n",
        "\n",
        "\n",
        "######## Describe a product table ########\n",
        "\n",
        "shop = root.packages().package(\"shop\", sqlschema=\"shop\")\n",
        "product = shop.table(\"product\", pkey=\"code\")\n",
        "_ = product.column(\"code\", size=\":10\")\n",
        "_ = product.column(\"name\", size=\":60\")\n",
        "\n",
        "\n",
        "######## Create that table ########\n",
        "\n",
        "db = AsqueelDb(model)\n",
        "print(db.migrate().commands)\n",
        "\n",
        "\n",
        "######## Add our first product ########\n",
        "\n",
        "products = db.table(\"shop.product\")\n",
        "products.insert({\"code\": \"TEA\", \"name\": \"Tea\"})\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Read our product ########\n",
        "\n",
        "products.query(columns=\"$code, $name\").fetch()\n",
        "\n",
        "\n",
        "######## Change its name ########\n",
        "\n",
        "products.update({\"name\": \"Green tea\"}, pkey=\"TEA\")\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Check the update ########\n",
        "\n",
        "products.query(columns=\"$code, $name\").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Track category and stock ########\n",
        "\n",
        "_ = product.column(\"category\", size=\":30\")\n",
        "_ = product.column(\"stock\", dtype=\"I\")\n",
        "\n",
        "\n",
        "######## Load the updated model ########\n",
        "\n",
        "db.reloadModel(model)\n",
        "\n",
        "\n",
        "######## Add those database columns ########\n",
        "\n",
        "print(db.migrate().commands)\n",
        "\n",
        "\n",
        "######## Check our original row ########\n",
        "\n",
        "products = db.table(\"shop.product\")\n",
        "products.query(columns=\"$name, $stock\").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Give tea its stock ########\n",
        "\n",
        "products.update({\"category\": \"Drinks\", \"stock\": 8}, pkey=\"TEA\")\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Read the new values ########\n",
        "\n",
        "products.query(columns=\"$name, $category, $stock\").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Add products from CSV ########\n",
        "\n",
        "from io import StringIO\n",
        "data = StringIO(\n",
        "    \"code,name,category,stock\\n\"\n",
        "    \"COF,Coffee,Drinks,24\\n\"\n",
        "    \"MUG,Mug,Kitchen,12\\n\"\n",
        ")\n",
        "\n",
        "\n",
        "######## Import those rows ########\n",
        "\n",
        "products.sql_insert(data, format=\"csv\")\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## List just the drinks ########\n",
        "\n",
        "products.query(\n",
        "    columns=\"$name, $stock\",\n",
        "    where=\"$category = :category\", category=\"Drinks\",\n",
        "    order_by=\"$stock DESC\",\n",
        ").fetch()\n",
        "db.commit()\n"
      ],
      "id": "l2-c7-910a7aeabf1e",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 1 \u2014 What we'll learn in this lesson"
      ],
      "id": "l2-c8-0faae73bfca2"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "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."
      ],
      "id": "l2-c9-ac5c2ab1918f"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "```text\n",
        "What we'll learn in this lesson\n",
        "Create customers, invoices and invoice rows\n",
        "Import products, dates, quantities and prices\n",
        "Read fields through relationship paths\n",
        "\n",
        "customer \u2190 invoice \u2190 invoice_row \u2192 product\n",
        "```"
      ],
      "id": "l2-c10-7c82f4afc024"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "The setup above has recreated the previous lessons in this notebook's kernel. Continue with the cells below."
      ],
      "id": "l2-c11-7ea3c414bc8c"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's check our starting point. We already have three products."
      ],
      "id": "l2-c12-a6d3384d1d26"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Check our starting point ########\n",
        "\n",
        "products.query(columns=\"$code, $name\").fetch()\n",
        "db.commit()"
      ],
      "id": "l2-c13-d7aa9b8695d5",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 2 \u2014 Add customers and their city"
      ],
      "id": "l2-c14-b5ea677074c9"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's describe our customers. Alongside the identifier and name, add their city."
      ],
      "id": "l2-c15-9707519cc824"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Describe our customers ########\n",
        "\n",
        "customer = shop.table(\"customer\", pkey=\"id\")\n",
        "_ = customer.column(\"id\", dtype=\"I\")\n",
        "_ = customer.column(\"name\", size=\":60\")\n",
        "_ = customer.column(\"city\", size=\":60\")"
      ],
      "id": "l2-c16-27b606dfe46a",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's create the customer table. Reload the model and migrate, keeping the existing products."
      ],
      "id": "l2-c17-08b45819d3a5"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Create the customer table ########\n",
        "\n",
        "db.reloadModel(model)\n",
        "print(db.migrate().commands)"
      ],
      "id": "l2-c18-edd6403be625",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 3 \u2014 Give each invoice a date and customer"
      ],
      "id": "l2-c19-10b3edec536a"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's describe a dated invoice. D means a date column. An invoice has its own date and number."
      ],
      "id": "l2-c20-127966078638"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Describe a dated invoice ########\n",
        "\n",
        "invoice = shop.table(\"invoice\", pkey=\"id\")\n",
        "_ = invoice.column(\"id\", dtype=\"I\")\n",
        "_ = invoice.column(\"number\", size=\":20\")\n",
        "_ = invoice.column(\"invoice_date\", dtype=\"D\")"
      ],
      "id": "l2-c21-08afefbec8a0",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "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."
      ],
      "id": "l2-c22-7a6b5218833b"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Connect invoices to customers ########\n",
        "\n",
        "customer_id = invoice.column(\"customer_id\", dtype=\"I\")\n",
        "_ = customer_id.relation(\n",
        "    \"shop.customer.id\", foreign_key=True, relation_name=\"invoices\",\n",
        ")"
      ],
      "id": "l2-c23-9bb0f3060955",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 4 \u2014 Give each row a quantity, price and total"
      ],
      "id": "l2-c24-b24f47836ad7"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's describe an invoice row. It needs quantity, unit price, and total. N declares numeric columns; twelve comma two describes precision and scale."
      ],
      "id": "l2-c25-8a4c1997d183"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Describe an invoice row ########\n",
        "\n",
        "invoice_row = shop.table(\"invoice_row\", pkey=\"id\")\n",
        "_ = invoice_row.column(\"id\", dtype=\"I\")\n",
        "_ = invoice_row.column(\"quantity\", dtype=\"I\")\n",
        "_ = invoice_row.column(\"unit_price\", dtype=\"N\", size=\"12,2\")\n",
        "_ = invoice_row.column(\"total\", dtype=\"N\", size=\"12,2\")"
      ],
      "id": "l2-c26-1331a5709e18",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "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."
      ],
      "id": "l2-c27-7b8c58cfafde"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Connect the row at both ends ########\n",
        "\n",
        "invoice_id = invoice_row.column(\"invoice_id\", dtype=\"I\")\n",
        "_ = invoice_id.relation(\n",
        "    \"shop.invoice.id\", foreign_key=True, relation_name=\"rows\",\n",
        ")\n",
        "product_code = invoice_row.column(\"product_code\", size=\":10\")\n",
        "_ = product_code.relation(\n",
        "    \"shop.product.code\", foreign_key=True, relation_name=\"rows\",\n",
        ")"
      ],
      "id": "l2-c28-a7083b80ce2d",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 5 \u2014 Create the connected tables"
      ],
      "id": "l2-c29-0b4d49d245e2"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's apply the new model. The migration creates invoices and rows, including their foreign keys."
      ],
      "id": "l2-c30-1602c6108695"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Apply the new model ########\n",
        "\n",
        "db.reloadModel(model)\n",
        "print(db.migrate().commands)"
      ],
      "id": "l2-c31-575805c58cb1",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's get fresh table handles. We will populate these four tables from CSV files."
      ],
      "id": "l2-c32-84e70092f301"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Get fresh table handles ########\n",
        "\n",
        "products = db.table(\"shop.product\")\n",
        "customers = db.table(\"shop.customer\")\n",
        "invoices = db.table(\"shop.invoice\")\n",
        "rows = db.table(\"shop.invoice_row\")"
      ],
      "id": "l2-c33-6c9566b58543",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 6 \u2014 Download the sample CSV files"
      ],
      "id": "l2-c34-fda9a60adfd4"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "The four fictional CSV files are already in the temporary folder created above: `products.csv`, `customers.csv`, `invoices.csv` and `invoice_rows.csv`. Let's inspect and import them."
      ],
      "id": "l2-c35-e080402d509c"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's inspect the row file. The CSV contains the keys, quantities, unit prices, and row totals. Each total is quantity times unit price."
      ],
      "id": "l2-c36-ab3373c4de0b"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Inspect the row file ########\n",
        "\n",
        "from pathlib import Path\n",
        "print(\"\\n\".join(Path(\"invoice_rows.csv\").read_text().splitlines()[:4]))"
      ],
      "id": "l2-c37-52076cf104ab",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 7 \u2014 Import the catalogue and customers"
      ],
      "id": "l2-c38-405e600fb643"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "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."
      ],
      "id": "l2-c39-e0ee9d97019e"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Expand the product catalogue ########\n",
        "\n",
        "with open(\"products.csv\", newline=\"\", encoding=\"utf-8\") as source:\n",
        "    products.sql_insert(source)\n",
        "\n",
        "db.commit()"
      ],
      "id": "l2-c40-d473d52dab99",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's count the catalogue. Twenty-seven imported products plus our original three makes thirty."
      ],
      "id": "l2-c41-84eea7f540b8"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Count the catalogue ########\n",
        "\n",
        "products.query().count()\n",
        "db.commit()"
      ],
      "id": "l2-c42-506091e85c69",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "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."
      ],
      "id": "l2-c43-05127768ac7e"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Import customers and their cities ########\n",
        "\n",
        "with open(\"customers.csv\", newline=\"\", encoding=\"utf-8\") as source:\n",
        "    customers.sql_insert(source)\n",
        "\n",
        "db.commit()\n",
        "customers.query(columns=\"$name, $city\", limit=3).fetch()\n",
        "db.commit()"
      ],
      "id": "l2-c44-cba0ee8bfa42",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 8 \u2014 Import dated invoices"
      ],
      "id": "l2-c45-023b719b4b5f"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's import the invoices. Their customer keys must already exist. The model converts the ISO date strings automatically."
      ],
      "id": "l2-c46-5009cf730ba0"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Import the invoices ########\n",
        "\n",
        "with open(\"invoices.csv\", newline=\"\", encoding=\"utf-8\") as source:\n",
        "    invoices.sql_insert(source)\n",
        "\n",
        "db.commit()"
      ],
      "id": "l2-c47-f1672c88947e",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 9 \u2014 Import prices and totals"
      ],
      "id": "l2-c48-e67f90a6b9b2"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "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."
      ],
      "id": "l2-c49-2db554dedce9"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Import the invoice rows ########\n",
        "\n",
        "with open(\"invoice_rows.csv\", newline=\"\", encoding=\"utf-8\") as source:\n",
        "    rows.sql_insert(source)\n",
        "\n",
        "db.commit()"
      ],
      "id": "l2-c50-52a868f2cd8f",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's check how much we imported. Twelve invoices and thirty-six invoice rows."
      ],
      "id": "l2-c51-abe9737cebea"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Check how much we imported ########\n",
        "\n",
        "invoices.query().count()\n",
        "rows.query().count()\n",
        "db.commit()"
      ],
      "id": "l2-c52-e7a6e3d774e9",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 10 \u2014 Read the customer city and invoice date"
      ],
      "id": "l2-c53-b52d94ed0feb"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "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."
      ],
      "id": "l2-c54-822b75c4c9ad"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Read invoices with their customers ########\n",
        "\n",
        "invoices.query(\n",
        "    columns=\"$number, $invoice_date, \"\n",
        "            \"@customer_id.name, @customer_id.city\",\n",
        "    limit=3,\n",
        ").fetch()\n",
        "db.commit()"
      ],
      "id": "l2-c55-448d82077f19",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 11 \u2014 Read products, quantities and amounts"
      ],
      "id": "l2-c56-6f8886861ed4"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "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."
      ],
      "id": "l2-c57-67268b16f5b6"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Inspect the first invoice ########\n",
        "\n",
        "rows.query(\n",
        "    columns=\"@product_code.name, \"\n",
        "            \"$quantity, $unit_price, $total\",\n",
        "    where=\"$invoice_id = :invoice\", invoice=101,\n",
        ").fetch()\n",
        "db.commit()"
      ],
      "id": "l2-c58-dc6ab223cb50",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 12 \u2014 Follow two relationships"
      ],
      "id": "l2-c59-b17328d8aa2d"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Now follow a row to the customer. Go through the invoice, then the customer. The same path also reaches their city."
      ],
      "id": "l2-c60-1f0d2a0be007"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "```text\n",
        "invoice_row \u2192 invoice \u2192 customer\n",
        "@invoice_id.@customer_id.city\n",
        "\n",
        "invoice_row \u2192 invoice\n",
        "@invoice_id.invoice_date\n",
        "\n",
        "invoice_row \u2192 product\n",
        "@product_code.name\n",
        "```"
      ],
      "id": "l2-c61-c06ee62fdc89"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's read across both connections. A single query returns invoice number, customer city, product name, and row total."
      ],
      "id": "l2-c62-986765d876c7"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Read across both connections ########\n",
        "\n",
        "rows.query(\n",
        "    columns=\"@invoice_id.number, \"\n",
        "            \"@invoice_id.@customer_id.city, \"\n",
        "            \"@product_code.name, $total\",\n",
        "    limit=3,\n",
        ").fetch()\n",
        "db.commit()"
      ],
      "id": "l2-c63-ae45b9b06d37",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 13 \u2014 Thank you \u2014 a complete small dataset"
      ],
      "id": "l2-c64-92f702eb030e"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "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."
      ],
      "id": "l2-c65-ae0d29493fdf"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "```text\n",
        "Thank you!\n",
        "30 products \u00b7 8 customers \u00b7 12 invoices \u00b7 36 rows\n",
        "Row total = quantity \u00d7 unit price\n",
        "Next: filter and sort through relationships\n",
        "```"
      ],
      "id": "l2-c66-8f7ea8503740"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Check your result\n",
        "\n",
        "Run this final cell to check the exercise."
      ],
      "id": "l2-c67-062cfb81c9cc"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "from decimal import Decimal\n",
        "from datetime import date\n",
        "assert products.query().count() == 30\n",
        "assert customers.query().count() == 8\n",
        "assert invoices.query().count() == 12\n",
        "assert rows.query().count() == 36\n",
        "assert all(isinstance(r[\"invoice_date\"], date) for r in invoices.query(columns=\"$invoice_date\").fetch())\n",
        "assert all(r[\"total\"] == r[\"quantity\"] * r[\"unit_price\"] for r in rows.query(columns=\"$quantity, $unit_price, $total\").fetch())\n",
        "db.commit()\n",
        "print(\"Lesson checks passed.\")\n"
      ],
      "id": "l2-c68-93a89a332037",
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3",
      "language": "python"
    },
    "language_info": {
      "name": "python"
    },
    "asqueel": {
      "version": "0.6.0",
      "lesson": 2
    }
  }
}