{
  "nbformat": 4,
  "nbformat_minor": 5,
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Welcome: your first table\n",
        "\n",
        "Install Asqueel in a virtual environment, then create, query and change a product table in a Python console. No database files needed.\n",
        "\n",
        "[Watch lesson 1](https://asqueel.org/learn/01-what-is-asqueel/)\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": "l1-c0-399ea0de155a"
    },
    {
      "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": "l1-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": "l1-c2-a53efa980848"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%pip install -q \"asqueel[migration]==0.6.0\""
      ],
      "id": "l1-c3-5283bc5543e7",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "%config InteractiveShell.ast_node_interactivity = \"all\""
      ],
      "id": "l1-c4-7a5eb4f63b61",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 1 \u2014 What we'll learn in this lesson"
      ],
      "id": "l1-c5-0faae73bfca2"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Welcome! Today I'm introducing Asqueel, a Python adapter for SQL."
      ],
      "id": "l1-c6-8d9fefd5108e"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "```text\n",
        "Welcome to Asqueel\n",
        "A Python adapter for SQL\n",
        "```"
      ],
      "id": "l1-c7-9652208075f0"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Here's what we'll learn in this lesson. Install Asqueel, create a product table, write and query data, add columns, and import CSV. We'll work in one Python terminal with SQLite in memory. Closing it discards the data."
      ],
      "id": "l1-c8-b512c913f5b7"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "```text\n",
        "What we'll learn in this lesson\n",
        "Install Asqueel\n",
        "Create a product table in memory\n",
        "Insert, query and update\n",
        "Add columns and migrate\n",
        "Import CSV data\n",
        "\n",
        "One Python session \u00b7 temporary data\n",
        "```"
      ],
      "id": "l1-c9-3eadb227d510"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 2 \u2014 Create a virtual environment"
      ],
      "id": "l1-c10-b2777b0ca0dd"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's create an isolated environment. Python puts its packages in this dot venv folder."
      ],
      "id": "l1-c11-62b67f8921f9"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "The terminal step below is already covered by the notebook setup above:\n",
        "\n",
        "```text\n",
        "$ python3 -m venv .venv\n",
        "```"
      ],
      "id": "l1-c12-4a643cdb256a"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's activate it. These are the macOS and Linux commands."
      ],
      "id": "l1-c13-605db1e27437"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "The terminal step below is already covered by the notebook setup above:\n",
        "\n",
        "```text\n",
        "$ source .venv/bin/activate\n",
        "```"
      ],
      "id": "l1-c14-303ee1925397"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's install Asqueel. The migration extra includes the engine that creates and changes our tables."
      ],
      "id": "l1-c15-8493c3dd68a7"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "The terminal step below is already covered by the notebook setup above:\n",
        "\n",
        "```text\n",
        "(.venv) $ python -m pip install \"asqueel[migration]\"\n",
        "```"
      ],
      "id": "l1-c16-22a3f291ac17"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 3 \u2014 Start Python"
      ],
      "id": "l1-c17-18e15b8f3fd9"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's open Python. We'll keep this console open for the whole example."
      ],
      "id": "l1-c18-062c5781c436"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "The terminal step below is already covered by the notebook setup above:\n",
        "\n",
        "```text\n",
        "(.venv) $ python\n",
        "```"
      ],
      "id": "l1-c19-8511ea56a065"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's import our two tools. One describes the model; the other connects it to a database."
      ],
      "id": "l1-c20-b00e4382a80e"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Import our two tools ########\n",
        "\n",
        "from asqueel import AsqueelDb, SqlDatabaseConfig"
      ],
      "id": "l1-c21-18c34dc35bf2",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 4 \u2014 Describe a product table"
      ],
      "id": "l1-c22-3094ff0e8b48"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's choose SQLite in memory. This model lives right here in Python. No model file or class is needed."
      ],
      "id": "l1-c23-e6687aa9b1f6"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Choose SQLite in memory ########\n",
        "\n",
        "model = SqlDatabaseConfig()\n",
        "root = model.source.db(implementation=\"sqlite\")\n",
        "_ = root.connection(name=\":memory:\", implementation=\"sqlite\")"
      ],
      "id": "l1-c24-c26dd6565096",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's describe a product table. It needs a code and a name. Code is the unique key; both columns contain text."
      ],
      "id": "l1-c25-183123b8cd35"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## 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\")"
      ],
      "id": "l1-c26-5e3f5e0cd50f",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 5 \u2014 Create the table"
      ],
      "id": "l1-c27-3562348e57e4"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's create that table. Migrate compares the model with the database and runs the missing SQL. Here is the create table statement."
      ],
      "id": "l1-c28-df0c0c9dd13d"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Create that table ########\n",
        "\n",
        "db = AsqueelDb(model)\n",
        "print(db.migrate().commands)"
      ],
      "id": "l1-c29-21b107963bd1",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 6 \u2014 Insert a product"
      ],
      "id": "l1-c30-460da89d48ce"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's add our first product. Insert tea, then commit the transaction. The data stays in memory, not on disk."
      ],
      "id": "l1-c31-973bbfe1b285"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Add our first product ########\n",
        "\n",
        "products = db.table(\"shop.product\")\n",
        "products.insert({\"code\": \"TEA\", \"name\": \"Tea\"})\n",
        "db.commit()"
      ],
      "id": "l1-c32-abc6c72bbce4",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 7 \u2014 Read and update"
      ],
      "id": "l1-c33-1ce9323dcef5"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's read our product. Select its code and name, then fetch the result."
      ],
      "id": "l1-c34-2e7fa20718a4"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Read our product ########\n",
        "\n",
        "products.query(columns=\"$code, $name\").fetch()"
      ],
      "id": "l1-c35-5d4cbc69050d",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's change its name. Find the row by its key, set Green tea, and commit."
      ],
      "id": "l1-c36-dd93235b234d"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Change its name ########\n",
        "\n",
        "products.update({\"name\": \"Green tea\"}, pkey=\"TEA\")\n",
        "db.commit()"
      ],
      "id": "l1-c37-b256b55bf041",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's check the update. The same query now returns Green tea."
      ],
      "id": "l1-c38-2ab77e58f160"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Check the update ########\n",
        "\n",
        "products.query(columns=\"$code, $name\").fetch()\n",
        "db.commit()"
      ],
      "id": "l1-c39-48fbaf14919a",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 8 \u2014 Add two columns"
      ],
      "id": "l1-c40-acc3146cefa5"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's track category and stock. Add a text column for category and an integer column for stock."
      ],
      "id": "l1-c41-bd6c911f2a80"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Track category and stock ########\n",
        "\n",
        "_ = product.column(\"category\", size=\":30\")\n",
        "_ = product.column(\"stock\", dtype=\"I\")"
      ],
      "id": "l1-c42-380f08259be7",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's load the updated model. Reload changes the running model while keeping our database connection."
      ],
      "id": "l1-c43-12371b50e031"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Load the updated model ########\n",
        "\n",
        "db.reloadModel(model)"
      ],
      "id": "l1-c44-a3910e44f31d",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 9 \u2014 Migrate again"
      ],
      "id": "l1-c45-78bf3551216d"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's add those database columns. Migrate now generates two add column statements, without recreating the table."
      ],
      "id": "l1-c46-7f4f0e55eebf"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Add those database columns ########\n",
        "\n",
        "print(db.migrate().commands)"
      ],
      "id": "l1-c47-43fe2479b85c",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's check our original row. Get a fresh table handle. Green tea is still there; stock has no value yet."
      ],
      "id": "l1-c48-52588f1cdedd"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Check our original row ########\n",
        "\n",
        "products = db.table(\"shop.product\")\n",
        "products.query(columns=\"$name, $stock\").fetch()\n",
        "db.commit()"
      ],
      "id": "l1-c49-de52b77d2e07",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 10 \u2014 Write the new fields"
      ],
      "id": "l1-c50-ad08a7eec48e"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's give tea its stock. Set its category to Drinks and its stock to eight, then commit."
      ],
      "id": "l1-c51-355fbedd5354"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Give tea its stock ########\n",
        "\n",
        "products.update({\"category\": \"Drinks\", \"stock\": 8}, pkey=\"TEA\")\n",
        "db.commit()"
      ],
      "id": "l1-c52-df9bbbba1a42",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's read the new values. The query includes our two new columns."
      ],
      "id": "l1-c53-ee6d7c5920dc"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Read the new values ########\n",
        "\n",
        "products.query(columns=\"$name, $category, $stock\").fetch()\n",
        "db.commit()"
      ],
      "id": "l1-c54-49fad74345c4",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 11 \u2014 Import CSV data"
      ],
      "id": "l1-c55-500bcdb6865a"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's add products from CSV. For this example, keep the CSV in a Python string too. Its headers match the table columns."
      ],
      "id": "l1-c56-43eb6fad10b1"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## 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",
        ")"
      ],
      "id": "l1-c57-0d6d1b39664b",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's import those rows. Pass the stream to SQL insert and specify CSV. Asqueel converts stock using the model and returns the imported row count. Then commit."
      ],
      "id": "l1-c58-d1cf6444a841"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## Import those rows ########\n",
        "\n",
        "products.sql_insert(data, format=\"csv\")\n",
        "db.commit()"
      ],
      "id": "l1-c59-d3714ee09e6b",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 12 \u2014 Query the imported rows"
      ],
      "id": "l1-c60-7486bfd32e46"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Let's list just the drinks. Use a parameter for the category and sort by stock, largest first. Coffee from the CSV, then our Green tea."
      ],
      "id": "l1-c61-b566755714b7"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "######## 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()"
      ],
      "id": "l1-c62-4fb59c9b9076",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 13 \u2014 Thank you \u2014 next: customers"
      ],
      "id": "l1-c63-dbd5339cb7ca"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Thanks for watching! Next, we'll add a customer table and follow the relationships in a small invoicing example."
      ],
      "id": "l1-c64-25754a74168e"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "```text\n",
        "Thank you for following along!\n",
        "Next lesson: add a customer table\n",
        "Build invoices and follow relationship paths\n",
        "```"
      ],
      "id": "l1-c65-f7743219b10b"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Check your result\n",
        "\n",
        "Run this final cell to check the exercise."
      ],
      "id": "l1-c66-062cfb81c9cc"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "assert products.query().count() == 3\n",
        "assert products.query(columns=\"$stock\", where=\"$code = :code\", code=\"TEA\").fetch()[0][\"stock\"] == 8\n",
        "db.commit()\n",
        "print(\"Lesson checks passed.\")\n"
      ],
      "id": "l1-c67-bdde5004927a",
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3",
      "language": "python"
    },
    "language_info": {
      "name": "python"
    },
    "asqueel": {
      "version": "0.6.0",
      "lesson": 1
    }
  }
}