{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "cell-000",
   "metadata": {},
   "source": [
    "# Class 10 activity — Look at the data before you build the server\n",
    "\n",
    "**~15 minutes, then B4 on your own after class.** Then we close this notebook and go build the API in a\n",
    "terminal, from files.\n",
    "\n",
    "That order is deliberate. Lab 5 asks you to put an HTTP server in front of a\n",
    "database. It is very hard to tell whether a server is returning the *right*\n",
    "answer if you have never seen the data by hand. So: SQL first, by hand, with no\n",
    "Python in the way. Then the same query through an ORM, watching what it writes\n",
    "for you.\n",
    "\n",
    "Notebooks are good for exactly this — poking at data, seeing tables, changing\n",
    "one thing and re-running. They are not good for servers, which is why your\n",
    "actual Lab 5 will be `.py` files and a terminal."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-001",
   "metadata": {},
   "source": [
    "---\n",
    "## Part A — SQL, before any Python\n",
    "\n",
    "Run this cell. It builds a small seeded database and connects a SQL engine to it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-002",
   "metadata": {},
   "outputs": [],
   "source": [
    "# This notebook needs SQLModel and jupysql. They're there if JupyterLab was\n",
    "# started with `uv run jupyter lab` in the folder where you ran `uv add`.\n",
    "try:\n",
    "    import sqlmodel, sql  # noqa: F401\n",
    "except ImportError:\n",
    "    raise SystemExit(\n",
    "        \"Missing packages. Close JupyterLab, then in lecture-activities/lecture-10 run \"\n",
    "        \"`uv add sqlmodel jupysql pandas jupyterlab` and `uv run jupyter lab`. \"\n",
    "        \"(See 'Start here' at the top of the build-along.)\"\n",
    "    )\n",
    "\n",
    "import sqlite3, pathlib\n",
    "pathlib.Path(\"practice.db\").unlink(missing_ok=True)\n",
    "con = sqlite3.connect(\"practice.db\")\n",
    "con.executescript('''\n",
    "CREATE TABLE idea (id INTEGER PRIMARY KEY, title TEXT, author TEXT);\n",
    "CREATE TABLE vote (id INTEGER PRIMARY KEY, idea_id INTEGER, voter TEXT);\n",
    "INSERT INTO idea (title, author) VALUES\n",
    "  ('Automatic plant waterer','ada'),\n",
    "  ('Laundry that folds itself','grace'),\n",
    "  ('Bike lock that texts you','alan'),\n",
    "  ('A better course scheduler','ada');\n",
    "INSERT INTO vote (idea_id, voter) VALUES (1,'grace'),(1,'alan'),(2,'ada'),(1,'ada'),(4,'grace');\n",
    "''')\n",
    "con.commit(); con.close()\n",
    "\n",
    "import warnings  # jupysql emits a harmless SyntaxWarning on import\n",
    "warnings.filterwarnings(\"ignore\", category=SyntaxWarning)\n",
    "%load_ext sql\n",
    "%sql sqlite:///practice.db\n",
    "print(\"practice.db ready\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-003",
   "metadata": {},
   "source": [
    "A cell starting with `%%sql` is **SQL, not Python** — no quotes, no string\n",
    "escaping, results come back as a table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-004",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT * FROM idea;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-005",
   "metadata": {},
   "source": [
    "**A1.** Every idea written by `ada`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-006",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- YOUR SQL HERE"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-007",
   "metadata": {},
   "source": [
    "**A2.** Each idea with **how many votes it has**, most-voted first.\n",
    "\n",
    "You will need `JOIN`, `GROUP BY`, `COUNT` and `ORDER BY`. Take your time — this\n",
    "is the query behind Lab 5's `GET /ideas`, which returns every idea with its\n",
    "`vote_count`. It is much easier to get right here, where it is only SQL.\n",
    "\n",
    "Watch out for *Bike lock that texts you*. It has no votes. Does it appear in\n",
    "your results? Should it?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-008",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "-- YOUR SQL HERE"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-009",
   "metadata": {},
   "source": [
    "**A3.** Why does A2 need a `JOIN` at all? What goes wrong if you query only `vote`?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-010",
   "metadata": {},
   "source": [
    "> **✏️ Your answer:**\n",
    ">\n",
    "> *Double-click this cell, replace this line with your answer, then press Shift+Enter.*"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-011",
   "metadata": {},
   "source": [
    "---\n",
    "## Part B — The same queries, through an ORM\n",
    "\n",
    "In Lab 5 you will not write that SQL. You will write Python classes, and an\n",
    "**ORM** (Object-Relational Mapper) will write the SQL for you.\n",
    "\n",
    "That is a good deal, right up until the moment the SQL it writes is not what you\n",
    "expected. So today we watch it work. `echo=True` prints every statement the ORM\n",
    "sends to the database — and at the Lab 5 check-in you'll be asked to read yours."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-012",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlmodel import SQLModel, Field, Session, create_engine, select, func\n",
    "\n",
    "class Idea(SQLModel, table=True):\n",
    "    __tablename__ = \"idea\"\n",
    "    id: int | None = Field(default=None, primary_key=True)\n",
    "    title: str\n",
    "    author: str = \"\"\n",
    "\n",
    "class Vote(SQLModel, table=True):\n",
    "    __tablename__ = \"vote\"\n",
    "    id: int | None = Field(default=None, primary_key=True)\n",
    "    idea_id: int = Field(foreign_key=\"idea.id\")\n",
    "    voter: str\n",
    "\n",
    "# echo=True is the whole point of this cell\n",
    "engine = create_engine(\"sqlite:///practice.db\", echo=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-013",
   "metadata": {},
   "source": [
    "**B1.** Your A1 again, through the ORM: every idea by `ada`, printed by title.\n",
    "\n",
    "Use a `Session`, `select(Idea)`, and `.where(Idea.author == \"ada\")`. Then look at\n",
    "what got printed *above* your output — that is the SQL the ORM wrote."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-014",
   "metadata": {},
   "outputs": [],
   "source": [
    "with Session(engine) as sess:\n",
    "    pass  # YOUR CODE HERE"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-015",
   "metadata": {},
   "source": [
    "**B2.** Compare the SQL the ORM emitted with what you wrote in A1.\n",
    "Name **two** differences. Where did `\"ada\"` go?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-016",
   "metadata": {},
   "source": [
    "> **✏️ Your answer:**\n",
    ">\n",
    "> *Double-click this cell, replace this line with your answer, then press Shift+Enter.*"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-017",
   "metadata": {},
   "source": [
    "**B3.** Now A2 — the one that matters for Lab 5. Here it is through the ORM,\n",
    "with one thing wrong. **Run it as-is**, then compare the output with your A2."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-018",
   "metadata": {},
   "outputs": [],
   "source": [
    "stmt = (\n",
    "    select(Idea.title, func.count(Vote.id))\n",
    "    .join(Vote)\n",
    "    .group_by(Idea.id)\n",
    "    .order_by(func.count(Vote.id).desc())\n",
    ")\n",
    "with Session(engine) as sess:\n",
    "    for title, n in sess.exec(stmt):\n",
    "        print(n, title)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-019",
   "metadata": {},
   "source": [
    "Which idea is missing, and why? Fix it in the cell below: copy the statement and\n",
    "change **one** thing. (Look up `.join` in the SQLModel/SQLAlchemy docs, or read\n",
    "the SQL that got printed and ask which word your A2 had that this doesn't.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-020",
   "metadata": {},
   "outputs": [],
   "source": [
    "# YOUR FIX HERE"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-021",
   "metadata": {},
   "source": [
    "> **✏️ Your answer:**\n",
    ">\n",
    "> *Double-click this cell, replace this line with your answer, then press Shift+Enter.*"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-022",
   "metadata": {},
   "source": [
    "**B4 (after class, 5 minutes).** Here is another way to get the same numbers, and it\n",
    "is the one most people (and most AI assistants) write first. Run it, then\n",
    "**count the `SELECT` statements** in the log."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cell-023",
   "metadata": {},
   "outputs": [],
   "source": [
    "with Session(engine) as sess:\n",
    "    for idea in sess.exec(select(Idea)).all():\n",
    "        votes = sess.exec(select(Vote).where(Vote.idea_id == idea.id)).all()\n",
    "        print(len(votes), idea.title)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-024",
   "metadata": {},
   "source": [
    "How many `SELECT`s was that? How many would it be with 1,000 ideas? How many did B3 take?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-025",
   "metadata": {},
   "source": [
    "> **✏️ Your answer:**\n",
    ">\n",
    "> *Double-click this cell, replace this line with your answer, then press Shift+Enter.*"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-026",
   "metadata": {},
   "source": [
    "> **✅ Check yourself** — these are the kinds of questions you'll be asked at the Lab 5 check-in. Can you answer them out loud, in your own words?\n",
    ">\n",
    "> - Why is this data in two tables instead of one?\n",
    "> - What is the difference between a `JOIN` and a `LEFT JOIN` here? Which idea tells them apart?\n",
    "> - Your ORM wrote SQL for you. What did it write for B3, and why is there a `?` in B1's?\n",
    "> - How many queries does your way of counting votes send, for 4 ideas? For 1,000?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-027",
   "metadata": {},
   "source": [
    "---\n",
    "## Next: close this notebook\n",
    "\n",
    "The rest of class is in a terminal, in files, in your own repo. You'll start the\n",
    "API that serves this data over HTTP — and Lab 5 is where you finish it.\n",
    "\n",
    "Your Lab 5 database has these same two tables, and `GET /ideas` has to return\n",
    "B3's answer as JSON: every idea, with a `vote_count`."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}