{
  "nbformat": 4,
  "nbformat_minor": 5,
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Fetch and export\n",
        "\n",
        "Reuse the city report to choose Python result formats, retrieve records or keys, read batches and export CSV or JSON.\n",
        "\n",
        "[Watch lesson 7](https://asqueel.org/learn/07-fetch-and-export/)\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": "l7-c0-dd182e22db6a"
    },
    {
      "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": "l7-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": "l7-c2-a53efa980848"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%pip install -q \"asqueel[migration]==0.6.0\""
      ],
      "id": "l7-c3-5283bc5543e7",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%config InteractiveShell.ast_node_interactivity = \"all\""
      ],
      "id": "l7-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": "l7-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": "l7-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",
        "\n",
        "# Continue with lesson two.\n",
        "\n",
        "######## Check our starting point ########\n",
        "\n",
        "products.query(columns=\"$code, $name\").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## 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\")\n",
        "\n",
        "\n",
        "######## Create the customer table ########\n",
        "\n",
        "db.reloadModel(model)\n",
        "print(db.migrate().commands)\n",
        "\n",
        "\n",
        "######## 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\")\n",
        "\n",
        "\n",
        "######## 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",
        ")\n",
        "\n",
        "\n",
        "######## 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\")\n",
        "\n",
        "\n",
        "######## 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",
        ")\n",
        "\n",
        "\n",
        "######## Apply the new model ########\n",
        "\n",
        "db.reloadModel(model)\n",
        "print(db.migrate().commands)\n",
        "\n",
        "\n",
        "######## 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\")\n",
        "\n",
        "\n",
        "######## Inspect the row file ########\n",
        "\n",
        "from pathlib import Path\n",
        "print(\"\\n\".join(Path(\"invoice_rows.csv\").read_text().splitlines()[:4]))\n",
        "\n",
        "\n",
        "######## 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()\n",
        "\n",
        "\n",
        "######## Count the catalogue ########\n",
        "\n",
        "products.query().count()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## 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()\n",
        "\n",
        "\n",
        "######## Import the invoices ########\n",
        "\n",
        "with open(\"invoices.csv\", newline=\"\", encoding=\"utf-8\") as source:\n",
        "    invoices.sql_insert(source)\n",
        "\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## 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()\n",
        "\n",
        "\n",
        "######## Check how much we imported ########\n",
        "\n",
        "invoices.query().count()\n",
        "rows.query().count()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## 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()\n",
        "\n",
        "\n",
        "######## 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()\n",
        "\n",
        "\n",
        "######## 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()\n",
        "\n",
        "# Continue with lesson three.\n",
        "\n",
        "######## Find invoices for Florence ########\n",
        "\n",
        "invoices.query(\n",
        "    columns=\"$number\",\n",
        "    where=\"@customer_id.city = :city\", city=\"Florence\",\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Sort invoices by city ########\n",
        "\n",
        "invoices.query(\n",
        "    columns=\"$number, @customer_id.city\",\n",
        "    order_by=\"@customer_id.city\", limit=4,\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Show the newest rows first ########\n",
        "\n",
        "rows.query(\n",
        "    columns=\"@invoice_id.invoice_date, $total\",\n",
        "    order_by=\"@invoice_id.invoice_date DESC\", limit=3,\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Find rows for Florence ########\n",
        "\n",
        "rows.query(\n",
        "    columns=\"@product_code.name, $quantity\",\n",
        "    where=\"@invoice_id.@customer_id.city = :city\",\n",
        "    city=\"Florence\", limit=3,\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Combine the conditions ########\n",
        "\n",
        "from datetime import date\n",
        "rows.query(\n",
        "    columns=\"@product_code.name, $total\",\n",
        "    where=\"@invoice_id.@customer_id.city = :city \"\n",
        "          \"AND @invoice_id.invoice_date >= :since\",\n",
        "    city=\"Florence\", since=date(2026, 10, 1),\n",
        "    order_by=\"@invoice_id.invoice_date DESC\", limit=4,\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Close this read transaction ########\n",
        "\n",
        "db.commit()\n",
        "\n",
        "# Continue with lesson 4.\n",
        "\n",
        "######## Call this path city ########\n",
        "\n",
        "_ = invoice_row.aliasColumn(\n",
        "    \"city\", relation_path=\"@invoice_id.@customer_id.city\",\n",
        ")\n",
        "db.reloadModel(model)\n",
        "rows = db.table(\"shop.invoice_row\")\n",
        "\n",
        "\n",
        "######## Read and filter with that name ########\n",
        "\n",
        "rows.query(\n",
        "    columns=\"$city, @product_code.name\",\n",
        "    where=\"$city = :city\", city=\"Florence\", limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Calculate quantity times price ########\n",
        "\n",
        "_ = invoice_row.formulaColumn(\n",
        "    \"calculated_total\",\n",
        "    sql_formula=\"ROUND($quantity * $unit_price, 2)\",\n",
        "    dtype=\"N\",\n",
        ")\n",
        "db.reloadModel(model)\n",
        "rows = db.table(\"shop.invoice_row\")\n",
        "\n",
        "\n",
        "######## Read the calculated amounts ########\n",
        "\n",
        "rows.query(\n",
        "    columns=\"$quantity, $unit_price, $calculated_total\",\n",
        "    where=\"$invoice_id = :invoice\", invoice=101,\n",
        "    order_by=\"$id\",\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Check whether the database needs changing ########\n",
        "\n",
        "db.migration_plan().empty\n",
        "\n",
        "\n",
        "######## Temporarily change the quantity to three ########\n",
        "\n",
        "rows.update({\"quantity\": 3}, pkey=1)\n",
        "rows.query(\n",
        "    columns=\"$quantity, $total, $calculated_total\",\n",
        "    where=\"$id = :id\", id=1,\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Undo that change ########\n",
        "\n",
        "db.rollback()\n",
        "\n",
        "# Continue with lesson 5.\n",
        "\n",
        "######## Give each invoice a calculated total ########\n",
        "\n",
        "_ = invoice.formulaColumn(\n",
        "    \"total\", dtype=\"N\",\n",
        "    select=dict(\n",
        "        table=\"shop.invoice_row\",\n",
        "        columns=\"ROUND(SUM($calculated_total), 2)\",\n",
        "        where=\"$invoice_id = #THIS.id\",\n",
        "    ),\n",
        ")\n",
        "db.reloadModel(model)\n",
        "invoices = db.table(\"shop.invoice\")\n",
        "\n",
        "\n",
        "######## Read those totals as ordinary fields ########\n",
        "\n",
        "invoices.query(\n",
        "    columns=\"$number, $total\", order_by=\"$id\", limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Total the invoices of each customer ########\n",
        "\n",
        "_ = customer.formulaColumn(\n",
        "    \"total\", dtype=\"N\",\n",
        "    select=dict(\n",
        "        table=\"shop.invoice\",\n",
        "        columns=\"ROUND(SUM($total), 2)\",\n",
        "        where=\"$customer_id = #THIS.id\",\n",
        "    ),\n",
        ")\n",
        "db.reloadModel(model)\n",
        "customers = db.table(\"shop.customer\")\n",
        "\n",
        "\n",
        "######## Read each customer's sales total ########\n",
        "\n",
        "customers.query(\n",
        "    columns=\"$name, $total\", order_by=\"$id\", limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Total the sales of each product ########\n",
        "\n",
        "_ = product.formulaColumn(\n",
        "    \"total\", dtype=\"N\",\n",
        "    select=dict(\n",
        "        table=\"shop.invoice_row\",\n",
        "        columns=\"ROUND(SUM($calculated_total), 2)\",\n",
        "        where=\"$product_code = #THIS.code\",\n",
        "    ),\n",
        ")\n",
        "db.reloadModel(model)\n",
        "products = db.table(\"shop.product\")\n",
        "rows = db.table(\"shop.invoice_row\")\n",
        "\n",
        "\n",
        "######## Read the product totals ########\n",
        "\n",
        "products.query(\n",
        "    columns=\"$code, $name, $total\",\n",
        "    where=\"$code IN :codes\", codes=[\"TEA\", \"COF\", \"MUG\"],\n",
        "    order_by=\"$code\",\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Summarize each customer's invoices ########\n",
        "\n",
        "customers = db.table(\"shop.customer\")\n",
        "customers.query(\n",
        "    columns=\"$name, @invoices.aggr(\"\n",
        "            \"sum($total), count(), avg($total))\",\n",
        "    order_by=\"$id\", limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Count the rows of each invoice too ########\n",
        "\n",
        "invoices = db.table(\"shop.invoice\")\n",
        "invoices.query(\n",
        "    columns=\"$number, $total, @rows.aggr(count())\",\n",
        "    order_by=\"$id\", limit=3,\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Average the recorded selling prices ########\n",
        "\n",
        "products.query(\n",
        "    columns=\"$code, @rows.aggr(count(), avg($unit_price))\",\n",
        "    where=\"$code IN :codes\", codes=[\"TEA\", \"COF\", \"MUG\"],\n",
        "    order_by=\"$code\",\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "\n",
        "######## Name the average price so we can filter it ########\n",
        "\n",
        "_ = product.formulaColumn(\n",
        "    \"average_price\", dtype=\"N\",\n",
        "    select=dict(\n",
        "        table=\"shop.invoice_row\", columns=\"AVG($unit_price)\",\n",
        "        where=\"$product_code = #THIS.code\",\n",
        "    ),\n",
        ")\n",
        "db.reloadModel(model)\n",
        "products = db.table(\"shop.product\")\n",
        "rows = db.table(\"shop.invoice_row\")\n",
        "\n",
        "\n",
        "######## Filter by average price ########\n",
        "\n",
        "products.query(\n",
        "    columns=\"$code, $name, $average_price\",\n",
        "    where=\"$average_price BETWEEN :min_price AND :max_price\",\n",
        "    min_price=3.2, max_price=4.8,\n",
        "    order_by=\"$average_price, $code\",\n",
        ").fetch()\n",
        "db.commit()\n",
        "\n",
        "# Continue with lesson 6.\n",
        "\n",
        "######## Total sales by city ########\n",
        "\n",
        "rows = db.table(\"shop.invoice_row\")\n",
        "products = db.table(\"shop.product\")\n",
        "rows.query(\n",
        "    columns=\"$city, SUM($calculated_total)\",\n",
        "    group_by=\"$city\", order_by=\"$city\", limit=3,\n",
        ").fetch()\n",
        "\n",
        "\n",
        "######## Name the report columns ########\n",
        "\n",
        "report = rows.query(\n",
        "    columns=\"$city, ROUND(SUM($calculated_total), 2) AS sales, \"\n",
        "            \"COUNT(*) AS row_count\",\n",
        "    group_by=\"$city\", order_by=\"$city\",\n",
        ")\n",
        "report.fetch()\n",
        "\n",
        "\n",
        "######## Filter rows and groups ########\n",
        "\n",
        "selected = rows.query(\n",
        "    columns=\"$city, ROUND(SUM($calculated_total), 2) AS sales\",\n",
        "    where=\"$unit_price >= :min_price\", min_price=5,\n",
        "    group_by=\"$city\",\n",
        "    having=\"SUM($calculated_total) >= :min_sales\", min_sales=100,\n",
        "    order_by=\"$city\",\n",
        ")\n",
        "selected.fetch()\n",
        "db.commit()\n"
      ],
      "id": "l7-c7-7d4b62695246",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 1 \u2014 What we'll learn in this lesson"
      ],
      "id": "l7-c8-0faae73bfca2"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Welcome back! Here's what we'll learn. Use the city report we built in lesson six. We will read named rows, index or group records in Python, fetch one record or keys, read batches, and export CSV and JSON. The query is already ready; today we focus on its results."
      ],
      "id": "l7-c9-2c048c48b7bd"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "```text\n",
        "What we'll learn in this lesson\n",
        "Same city report from lesson 6\n",
        "fetch \u2192 rows, dictionaries and groups\n",
        "fetchRecord / fetchPkeys \u2192 one row or keys\n",
        "fetchAsJson / iterfetch \u2192 JSON text and batches\n",
        "fetch(destination) \u2192 CSV and JSON files\n",
        "\n",
        "Same SQLite database in memory\n",
        "```"
      ],
      "id": "l7-c10-8964121043f2"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 2 \u2014 Fetch rows and read their fields"
      ],
      "id": "l7-c11-29138cd4856a"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's inspect a fetched row. Fetch returns a list of named rows. Read a field by its name, or turn a row into an ordinary Python dictionary with dict. Each fetch executes the query again."
      ],
      "id": "l7-c12-c70c69390a4e"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Inspect a fetched row ########\n",
        "\n",
        "records = report.fetch()\n",
        "records[0]\n",
        "records[0][\"sales\"]\n",
        "dict(records[0])"
      ],
      "id": "l7-c13-636644d671e9",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 3 \u2014 Index the summaries by city"
      ],
      "id": "l7-c14-421b2b6753b1"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's look up a city directly. Fetch as dict indexes the result rows by the selected key. Our grouped cities are unique, so none are lost. The values are still named rows. With a repeated key, a later row would replace the earlier one."
      ],
      "id": "l7-c15-9ab25125cd79"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Look up a city directly ########\n",
        "\n",
        "by_city = report.fetchAsDict(key=\"city\")\n",
        "by_city[\"Florence\"]\n",
        "by_city[\"Florence\"][\"sales\"]"
      ],
      "id": "l7-c16-eec160a7698f",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 4 \u2014 Group detail rows in Python"
      ],
      "id": "l7-c17-bdd63122b5db"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's keep the detail rows too. Fetch grouped creates Python lists, one for each city. It does not calculate SQL aggregates. Florence keeps all twelve detail rows. Use this when you want groups of records rather than summary totals."
      ],
      "id": "l7-c18-91dbdda60f1d"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Keep the detail rows too ########\n",
        "\n",
        "details = rows.query(\n",
        "    columns=\"$id, $city, $quantity, $calculated_total\",\n",
        "    order_by=\"$id\",\n",
        ")\n",
        "groups = details.fetchGrouped(key=\"city\")\n",
        "[(city, len(items)) for city, items in groups.items()]\n",
        "groups[\"Florence\"][0]"
      ],
      "id": "l7-c19-bbe6bfa1ac13",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 5 \u2014 Fetch one record or just the keys"
      ],
      "id": "l7-c20-1ba45db4b880"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's fetch one known product. Fetch record returns a single row. This filter uses a unique key. A missing row raises an error unless ignoreMissing allows None. Multiple rows raise an error unless explicit order by selects the first ordered row."
      ],
      "id": "l7-c21-0837485d39e6"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Fetch one known product ########\n",
        "\n",
        "products.query(\n",
        "    columns=\"$code, $name, $stock\",\n",
        "    where=\"$code = :code\", code=\"TEA\",\n",
        ").fetchRecord()"
      ],
      "id": "l7-c22-befcdf29d0bf",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's fetch just the product keys. Fetch pkeys returns a list of primary-key values in query order. Our limit selects the first three drink codes."
      ],
      "id": "l7-c23-e7afa410e2bf"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Fetch just the product keys ########\n",
        "\n",
        "products.query(\n",
        "    where=\"$category = :category\", category=\"Drinks\",\n",
        "    order_by=\"$code\", limit=3,\n",
        ").fetchPkeys()"
      ],
      "id": "l7-c24-a69cfcb29e00",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 6 \u2014 Get JSON text"
      ],
      "id": "l7-c25-7960aa1c9027"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's get the report as JSON. Fetch as JSON returns text. Decode it to inspect the first object. This creates no file. We will export JSON to a file shortly."
      ],
      "id": "l7-c26-98ea8bfe9678"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Get the report as JSON ########\n",
        "\n",
        "import json\n",
        "payload = report.fetchAsJson()\n",
        "type(payload).__name__\n",
        "json.loads(payload)[0]"
      ],
      "id": "l7-c27-5359a9c09f15",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 7 \u2014 Read larger results in batches"
      ],
      "id": "l7-c28-153e77b56f21"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's fetch the details in batches. Iterfetch yields batches of rows. Here they contain ten, ten, ten and six records. We skip the final empty batch. This example records only the sizes; real code can process each batch as it arrives."
      ],
      "id": "l7-c29-72d0fd395e3b"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Fetch the details in batches ########\n",
        "\n",
        "[len(batch) for batch in details.iterfetch(arraysize=10) if batch]"
      ],
      "id": "l7-c30-b82e4612c6e5",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 8 \u2014 Export the report directly to CSV"
      ],
      "id": "l7-c31-0e52172f08d2"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's export the CSV report. Pass a destination to fetch. The file extension chooses the format, and the return value is seven: the number of rows written. The headings use our optional aliases. Only the report file is saved; SQLite is still in memory."
      ],
      "id": "l7-c32-ece192d7276b"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Export the CSV report ########\n",
        "\n",
        "report.fetch(\"sales-by-city.csv\")\n",
        "from pathlib import Path\n",
        "print(Path(\"sales-by-city.csv\").read_text())"
      ],
      "id": "l7-c33-e2bad336c864",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 9 \u2014 Export the same report to JSON"
      ],
      "id": "l7-c34-92d6aaa89698"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's export JSON as well. The same query writes a JSON array when the destination ends in dot json. Again, fetch returns a row count. Existing destination files are replaced. The notebook includes a way to download both reports."
      ],
      "id": "l7-c35-24650e751d71"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Export JSON as well ########\n",
        "\n",
        "report.fetch(\"sales-by-city.json\")\n",
        "json.loads(Path(\"sales-by-city.json\").read_text())[0]\n",
        "db.commit()"
      ],
      "id": "l7-c36-facc57d009ee",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 10 \u2014 Thank you \u2014 use and export the results"
      ],
      "id": "l7-c37-76b55ad1cbed"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Thanks for following along! We reused one report query, chose how to receive its results in Python, and exported two files. You can now try the optional best-buyer exercise, or go straight to lesson nine for writes and transactions."
      ],
      "id": "l7-c38-0aa1cfcdab55"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "```text\n",
        "Thank you!\n",
        "fetch / fetchAsDict / fetchGrouped \u2192 Python results\n",
        "fetchRecord / fetchPkeys \u2192 one record or keys\n",
        "fetchAsJson / iterfetch \u2192 text and batches\n",
        "fetch(destination) \u2192 CSV and JSON reports\n",
        "\n",
        "Optional next: best buyer\n",
        "Or skip to lesson 9: writes and transactions\n",
        "```"
      ],
      "id": "l7-c39-ab58018f268a"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Download your reports\n",
        "\n",
        "The exported files are in the notebook's temporary folder. In Colab, run the cell below to download them before ending the session. In local Jupyter, it prints their full paths so you can open or copy them."
      ],
      "id": "l7-c40-bbb612995cb0"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "try:\n",
        "    from google.colab import files\n",
        "except ImportError:\n",
        "    print(Path(\"sales-by-city.csv\").resolve())\n",
        "    print(Path(\"sales-by-city.json\").resolve())\n",
        "else:\n",
        "    files.download(\"sales-by-city.csv\")\n",
        "    files.download(\"sales-by-city.json\")\n"
      ],
      "id": "l7-c41-2d0ca994911a",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Check your result\n",
        "\n",
        "Run this final cell to check the exercise."
      ],
      "id": "l7-c42-062cfb81c9cc"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "from math import isclose\n",
        "import csv\n",
        "import json\n",
        "from pathlib import Path\n",
        "expected = {'Amsterdam': {'sales': 26.2, 'row_count': 3, 'ids': [10, 11, 12]}, 'Boston': {'sales': 57.8, 'row_count': 3, 'ids': [22, 23, 24]}, 'Florence': {'sales': 300.65, 'row_count': 12, 'ids': [1, 2, 3, 13, 14, 15, 25, 26, 27, 31, 32, 33]}, 'Helsinki': {'sales': 57.2, 'row_count': 6, 'ids': [7, 8, 9, 34, 35, 36]}, 'London': {'sales': 189.7, 'row_count': 6, 'ids': [4, 5, 6, 28, 29, 30]}, 'Manchester': {'sales': 87.4, 'row_count': 3, 'ids': [16, 17, 18]}, 'New York': {'sales': 96.3, 'row_count': 3, 'ids': [19, 20, 21]}}\n",
        "records = report.fetch()\n",
        "assert [r[\"city\"] for r in records] == sorted(expected)\n",
        "assert all(isclose(float(r[\"sales\"]), expected[r[\"city\"]][\"sales\"]) and r[\"row_count\"] == expected[r[\"city\"]][\"row_count\"] for r in records)\n",
        "assert all(dict(report.fetchAsDict(key=\"city\")[r[\"city\"]]) == dict(r) for r in records)\n",
        "assert json.loads(report.fetchAsJson()) == [dict(r) for r in records]\n",
        "assert {city: [r[\"id\"] for r in items] for city, items in details.fetchGrouped(key=\"city\").items()} == {city: item[\"ids\"] for city, item in expected.items()}\n",
        "csv_rows = list(csv.DictReader(Path(\"sales-by-city.csv\").open()))\n",
        "assert len(csv_rows) == 7\n",
        "assert all(r[\"city\"] == src[\"city\"] and isclose(float(r[\"sales\"]), src[\"sales\"]) and int(r[\"row_count\"]) == src[\"row_count\"] for r, src in zip(csv_rows, records))\n",
        "assert json.loads(Path(\"sales-by-city.json\").read_text()) == [dict(r) for r in records]\n",
        "assert db.table(\"shop.product\").query().count() == 30\n",
        "assert db.table(\"shop.customer\").query().count() == 8\n",
        "assert db.table(\"shop.invoice\").query().count() == 12\n",
        "assert rows.query().count() == 36\n",
        "db.commit()\n",
        "\n",
        "db.commit()\n",
        "db.commit()\n",
        "print(\"Lesson checks passed.\")\n"
      ],
      "id": "l7-c43-164e6ab11e8b",
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3",
      "language": "python"
    },
    "language_info": {
      "name": "python"
    },
    "asqueel": {
      "version": "0.6.0",
      "lesson": 7
    }
  }
}