{
  "nbformat": 4,
  "nbformat_minor": 5,
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Writes and transactions\n",
        "\n",
        "Insert, update and delete invoice rows, then use commit and rollback to control changes in the same in-memory database.\n",
        "\n",
        "[Watch lesson 9](https://asqueel.org/learn/06-aggregations/)\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": "l9-c0-3f2c927c6c78"
    },
    {
      "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": "l9-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": "l9-c2-a53efa980848"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%pip install -q \"asqueel[migration]==0.6.0\""
      ],
      "id": "l9-c3-5283bc5543e7",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%config InteractiveShell.ast_node_interactivity = \"all\""
      ],
      "id": "l9-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": "l9-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": "l9-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": "l9-c7-7d4b62695246",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 1 \u2014 What we'll learn in this lesson"
      ],
      "id": "l9-c8-0faae73bfca2"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Welcome back! Here's what we'll learn. Insert an invoice row, change it, and delete it. Then use commit and rollback to decide which changes remain. We use the same database in memory, with no custom Python triggers yet."
      ],
      "id": "l9-c9-1f7934c28289"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "```text\n",
        "What we'll learn in this lesson\n",
        "insert \u2192 add an invoice row\n",
        "update \u2192 change a record or selection\n",
        "delete \u2192 remove selected rows\n",
        "commit / rollback \u2192 keep or undo changes\n",
        "\n",
        "Same SQLite database in memory\n",
        "```"
      ],
      "id": "l9-c10-fc675dd884ab"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 2 \u2014 Insert a row and commit it"
      ],
      "id": "l9-c11-ebd4e00c14c8"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's add two teas and commit the row. We supply the stored total ourselves: two times three fifty is seven. Insert adds the row; commit closes this transaction. Because SQLite is in memory, commit keeps data only while this connection exists. It does not create a database file."
      ],
      "id": "l9-c12-51e8a0248b18"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Add two teas and commit the row ########\n",
        "\n",
        "from decimal import Decimal\n",
        "rows = db.table(\"shop.invoice_row\")\n",
        "_ = rows.insert({\n",
        "    \"id\": 901, \"invoice_id\": 101, \"product_code\": \"TEA\",\n",
        "    \"quantity\": 2, \"unit_price\": Decimal(\"3.50\"),\n",
        "    \"total\": Decimal(\"7.00\"),\n",
        "})\n",
        "rows.query(\n",
        "    columns=\"$id, $quantity, $unit_price, $total\",\n",
        "    where=\"$id = :id\", id=901,\n",
        ").fetchRecord()\n",
        "db.commit()"
      ],
      "id": "l9-c13-055513c1e02e",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 3 \u2014 Update a complete record"
      ],
      "id": "l9-c14-61c69d74ecbb"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's change the quantity to three. Read the physical fields, copy the record and update quantity and total together. Table update does not read or merge previous values for us. Passing old record makes those previous values available to the write cycle. We leave this change uncommitted."
      ],
      "id": "l9-c15-4ff0547fe16a"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Change the quantity to three ########\n",
        "\n",
        "old = dict(rows.query(where=\"$id = :id\", id=901).fetchRecord())\n",
        "changed = dict(old, quantity=3, total=Decimal(\"10.50\"))\n",
        "_ = rows.update(changed, old_record=old)\n",
        "rows.query(\n",
        "    columns=\"$quantity, $unit_price, $total\",\n",
        "    where=\"$id = :id\", id=901,\n",
        ").fetchRecord()"
      ],
      "id": "l9-c16-ea741fb3898f",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 4 \u2014 Update the selected row directly"
      ],
      "id": "l9-c17-8134d17f4453"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's update the selection to four teas. Query update reads the selected records, merges our changes, and calls table update with their previous values. We pass only the changed fields. Its result is a list of updated primary keys. Our filter selects just row nine hundred and one."
      ],
      "id": "l9-c18-bc3a22588131"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Update the selection to four teas ########\n",
        "\n",
        "rows.query(where=\"$id = :id\", id=901).update({\n",
        "    \"quantity\": 4, \"total\": Decimal(\"14.00\"),\n",
        "})\n",
        "rows.query(\n",
        "    columns=\"$quantity, $total\", where=\"$id = :id\", id=901,\n",
        ").fetchRecord()"
      ],
      "id": "l9-c19-0c3d90e92f51",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 5 \u2014 Delete, then roll back the transaction"
      ],
      "id": "l9-c20-7cddde4cb7b6"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's delete the row, then undo our pending work. Query delete returns the removed records. The empty fetch confirms the row is gone. Rollback restores its last committed state: quantity two and total seven. It undoes both updates and the deletion together, not just the last statement."
      ],
      "id": "l9-c21-89914ee0a94a"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Delete the row, then undo our pending work ########\n",
        "\n",
        "deleted = rows.query(where=\"$id = :id\", id=901).delete()\n",
        "len(deleted)\n",
        "rows.query(where=\"$id = :id\", id=901).fetch()\n",
        "db.rollback()\n",
        "rows.query(\n",
        "    columns=\"$quantity, $total\", where=\"$id = :id\", id=901,\n",
        ").fetchRecord()"
      ],
      "id": "l9-c22-b241de919f78",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 6 \u2014 Remove the demonstration row for good"
      ],
      "id": "l9-c23-516bb773e606"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's clean up and commit the deletion. This time we commit the delete. The original thirty-six rows remain. The final commit closes the count query's read transaction, ready for the next lesson."
      ],
      "id": "l9-c24-6f3102a1331b"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Clean up and commit the deletion ########\n",
        "\n",
        "_ = rows.query(where=\"$id = :id\", id=901).delete()\n",
        "db.commit()\n",
        "rows.query().count()\n",
        "db.commit()"
      ],
      "id": "l9-c25-f6a26e7289ce",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 7 \u2014 Thank you \u2014 next, Python triggers"
      ],
      "id": "l9-c26-95fc806a7509"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Thanks for following along! We inserted, updated and deleted a row, and separated committed changes from pending work. We supplied every stored total ourselves. In the next lesson, Python triggers will calculate and validate those totals for us."
      ],
      "id": "l9-c27-9d05bbf0f444"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "```text\n",
        "Thank you!\n",
        "insert / update / delete \u2192 change rows\n",
        "query(...).update(...) \u2192 merge selected records\n",
        "commit \u2192 keep this transaction\n",
        "rollback \u2192 undo all pending changes\n",
        "\n",
        "Next: Python triggers\n",
        "```"
      ],
      "id": "l9-c28-9fc9f2fa599e"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Check your result\n",
        "\n",
        "Run this final cell to check the exercise."
      ],
      "id": "l9-c29-062cfb81c9cc"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "from math import isclose\n",
        "original_rows = [{'id': '1', 'invoice_id': '101', 'product_code': 'TEA', 'quantity': '2', 'unit_price': '3.50', 'total': '7.00'}, {'id': '2', 'invoice_id': '101', 'product_code': 'COF', 'quantity': '1', 'unit_price': '6.90', 'total': '6.90'}, {'id': '3', 'invoice_id': '101', 'product_code': 'MUG', 'quantity': '4', 'unit_price': '5.25', 'total': '21.00'}, {'id': '4', 'invoice_id': '102', 'product_code': 'BTEA', 'quantity': '2', 'unit_price': '3.20', 'total': '6.40'}, {'id': '5', 'invoice_id': '102', 'product_code': 'HTEA', 'quantity': '3', 'unit_price': '4.10', 'total': '12.30'}, {'id': '6', 'invoice_id': '102', 'product_code': 'HON', 'quantity': '4', 'unit_price': '7.50', 'total': '30.00'}, {'id': '7', 'invoice_id': '103', 'product_code': 'SUG', 'quantity': '3', 'unit_price': '2.30', 'total': '6.90'}, {'id': '8', 'invoice_id': '103', 'product_code': 'CHO', 'quantity': '4', 'unit_price': '3.90', 'total': '15.60'}, {'id': '9', 'invoice_id': '103', 'product_code': 'BIS', 'quantity': '1', 'unit_price': '2.80', 'total': '2.80'}, {'id': '10', 'invoice_id': '104', 'product_code': 'JAM', 'quantity': '4', 'unit_price': '4.60', 'total': '18.40'}, {'id': '11', 'invoice_id': '104', 'product_code': 'OAT', 'quantity': '1', 'unit_price': '2.40', 'total': '2.40'}, {'id': '12', 'invoice_id': '104', 'product_code': 'JUI', 'quantity': '2', 'unit_price': '2.70', 'total': '5.40'}, {'id': '13', 'invoice_id': '105', 'product_code': 'WAT', 'quantity': '1', 'unit_price': '0.90', 'total': '0.90'}, {'id': '14', 'invoice_id': '105', 'product_code': 'LEM', 'quantity': '2', 'unit_price': '1.80', 'total': '3.60'}, {'id': '15', 'invoice_id': '105', 'product_code': 'KET', 'quantity': '3', 'unit_price': '32.90', 'total': '98.70'}, {'id': '16', 'invoice_id': '106', 'product_code': 'POT', 'quantity': '2', 'unit_price': '18.50', 'total': '37.00'}, {'id': '17', 'invoice_id': '106', 'product_code': 'PLT', 'quantity': '3', 'unit_price': '6.40', 'total': '19.20'}, {'id': '18', 'invoice_id': '106', 'product_code': 'BWL', 'quantity': '4', 'unit_price': '7.80', 'total': '31.20'}, {'id': '19', 'invoice_id': '107', 'product_code': 'SPO', 'quantity': '3', 'unit_price': '9.50', 'total': '28.50'}, {'id': '20', 'invoice_id': '107', 'product_code': 'TRY', 'quantity': '4', 'unit_price': '14.90', 'total': '59.60'}, {'id': '21', 'invoice_id': '107', 'product_code': 'JAR', 'quantity': '1', 'unit_price': '8.20', 'total': '8.20'}, {'id': '22', 'invoice_id': '108', 'product_code': 'NAP', 'quantity': '4', 'unit_price': '12.00', 'total': '48.00'}, {'id': '23', 'invoice_id': '108', 'product_code': 'TWL', 'quantity': '1', 'unit_price': '5.60', 'total': '5.60'}, {'id': '24', 'invoice_id': '108', 'product_code': 'FLT', 'quantity': '2', 'unit_price': '2.10', 'total': '4.20'}, {'id': '25', 'invoice_id': '109', 'product_code': 'TIN', 'quantity': '1', 'unit_price': '6.70', 'total': '6.70'}, {'id': '26', 'invoice_id': '109', 'product_code': 'MAT', 'quantity': '2', 'unit_price': '11.40', 'total': '22.80'}, {'id': '27', 'invoice_id': '109', 'product_code': 'CAF', 'quantity': '3', 'unit_price': '29.90', 'total': '89.70'}, {'id': '28', 'invoice_id': '110', 'product_code': 'BOT', 'quantity': '2', 'unit_price': '13.50', 'total': '27.00'}, {'id': '29', 'invoice_id': '110', 'product_code': 'STR', 'quantity': '3', 'unit_price': '4.80', 'total': '14.40'}, {'id': '30', 'invoice_id': '110', 'product_code': 'SCL', 'quantity': '4', 'unit_price': '24.90', 'total': '99.60'}, {'id': '31', 'invoice_id': '111', 'product_code': 'TEA', 'quantity': '3', 'unit_price': '3.50', 'total': '10.50'}, {'id': '32', 'invoice_id': '111', 'product_code': 'COF', 'quantity': '4', 'unit_price': '6.90', 'total': '27.60'}, {'id': '33', 'invoice_id': '111', 'product_code': 'MUG', 'quantity': '1', 'unit_price': '5.25', 'total': '5.25'}, {'id': '34', 'invoice_id': '112', 'product_code': 'BTEA', 'quantity': '4', 'unit_price': '3.20', 'total': '12.80'}, {'id': '35', 'invoice_id': '112', 'product_code': 'HTEA', 'quantity': '1', 'unit_price': '4.10', 'total': '4.10'}, {'id': '36', 'invoice_id': '112', 'product_code': 'HON', 'quantity': '2', 'unit_price': '7.50', 'total': '15.00'}]\n",
        "actual = rows.query(columns=\"$id,$quantity,$unit_price,$total\", order_by=\"$id\").fetch()\n",
        "assert len(actual) == len(original_rows) == 36\n",
        "for actual_row, source in zip(actual, sorted(original_rows, key=lambda r: int(r[\"id\"]))):\n",
        "    assert actual_row[\"id\"] == int(source[\"id\"])\n",
        "    assert actual_row[\"quantity\"] == int(source[\"quantity\"])\n",
        "    assert isclose(float(actual_row[\"unit_price\"]), float(source[\"unit_price\"]))\n",
        "    assert isclose(float(actual_row[\"total\"]), float(source[\"total\"]))\n",
        "\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",
        "db.commit()\n",
        "rows.insert(dict(id=999, invoice_id=101, product_code=\"TEA\", quantity=2, unit_price=Decimal(\"3.50\"), total=Decimal(\"7\")))\n",
        "assert rows.query(where=\"$id=999\").update({\"quantity\": 3, \"total\": Decimal(\"10.50\")}) == [999]\n",
        "assert rows.query(where=\"$id=999\").fetchRecord()[\"total\"] == Decimal(\"10.50\")\n",
        "assert len(rows.query(where=\"$id=999\").delete()) == 1\n",
        "db.rollback()\n",
        "assert rows.query().count() == 36\n",
        "db.commit()\n",
        "\n",
        "db.commit()\n",
        "db.commit()\n",
        "print(\"Lesson checks passed.\")\n"
      ],
      "id": "l9-c30-fa871d538a54",
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3",
      "language": "python"
    },
    "language_info": {
      "name": "python"
    },
    "asqueel": {
      "version": "0.6.0",
      "lesson": 9
    }
  }
}