{ "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": 1, "id": "cell-002", "metadata": {}, "outputs": [ { "data": { "text/html": [ "The 'toml' package isn't installed. To load settings from pyproject.toml or ~/.jupysql/config, install with: pip install toml" ], "text/plain": [ "The 'toml' package isn't installed. To load settings from pyproject.toml or ~/.jupysql/config, install with: pip install toml" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "Connecting to 'sqlite:///practice.db'" ], "text/plain": [ "Connecting to 'sqlite:///practice.db'" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "practice.db ready\n" ] } ], "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": 2, "id": "cell-004", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'sqlite:///practice.db'" ], "text/plain": [ "Running query in 'sqlite:///practice.db'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idtitleauthor
1Automatic plant watererada
2Laundry that folds itselfgrace
3Bike lock that texts youalan
4A better course schedulerada
" ], "text/plain": [ "+----+---------------------------+--------+\n", "| id | title | author |\n", "+----+---------------------------+--------+\n", "| 1 | Automatic plant waterer | ada |\n", "| 2 | Laundry that folds itself | grace |\n", "| 3 | Bike lock that texts you | alan |\n", "| 4 | A better course scheduler | ada |\n", "+----+---------------------------+--------+" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "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": 3, "id": "cell-006", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'sqlite:///practice.db'" ], "text/plain": [ "Running query in 'sqlite:///practice.db'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
idtitleauthor
1Automatic plant watererada
4A better course schedulerada
" ], "text/plain": [ "+----+---------------------------+--------+\n", "| id | title | author |\n", "+----+---------------------------+--------+\n", "| 1 | Automatic plant waterer | ada |\n", "| 4 | A better course scheduler | ada |\n", "+----+---------------------------+--------+" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT * FROM idea WHERE author = 'ada';" ] }, { "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": 4, "id": "cell-008", "metadata": {}, "outputs": [ { "data": { "text/html": [ "Running query in 'sqlite:///practice.db'" ], "text/plain": [ "Running query in 'sqlite:///practice.db'" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
titlevotes
Automatic plant waterer3
Laundry that folds itself1
A better course scheduler1
Bike lock that texts you0
" ], "text/plain": [ "+---------------------------+-------+\n", "| title | votes |\n", "+---------------------------+-------+\n", "| Automatic plant waterer | 3 |\n", "| Laundry that folds itself | 1 |\n", "| A better course scheduler | 1 |\n", "| Bike lock that texts you | 0 |\n", "+---------------------------+-------+" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT i.title, COUNT(v.id) AS votes\n", "FROM idea i LEFT JOIN vote v ON v.idea_id = i.id\n", "GROUP BY i.id, i.title\n", "ORDER BY votes DESC;" ] }, { "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": [ "**Solution:**\n", "\n", "`vote` rows carry an `idea_id`, not a title — so on their own they tell you how\n", "many votes each *id* has, not which idea that is. You would have to look up\n", "every id by hand, which is exactly the work a `JOIN` does for you.\n", "\n", "Worse: an idea with zero votes has no rows in `vote` at all, so it would vanish\n", "from the results entirely. That is the difference between `JOIN` and\n", "`LEFT JOIN` — the `LEFT` keeps every row of `idea` even when the right-hand side\n", "has nothing to match, which is why *Bike lock that texts you* still shows up,\n", "with a count of 0." ] }, { "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": 5, "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": 6, "id": "cell-014", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2026-10-01 17:13:16,263 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2026-10-01 17:13:16,266 INFO sqlalchemy.engine.Engine SELECT idea.id, idea.title, idea.author \n", "FROM idea \n", "WHERE idea.author = ?\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2026-10-01 17:13:16,267 INFO sqlalchemy.engine.Engine [generated in 0.00049s] ('ada',)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "['Automatic plant waterer', 'A better course scheduler']\n", "2026-10-01 17:13:16,267 INFO sqlalchemy.engine.Engine ROLLBACK\n" ] } ], "source": [ "with Session(engine) as sess:\n", " ideas = sess.exec(select(Idea).where(Idea.author == \"ada\")).all()\n", " print([i.title for i in ideas])" ] }, { "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": [ "**Solution:**\n", "\n", "1. **It names every column explicitly.** The ORM emits\n", " `SELECT idea.id, idea.title, idea.author FROM idea` rather than `SELECT *`.\n", " It knows the class, so it asks for exactly the columns it can map back onto\n", " attributes — and it keeps working if someone adds a column later.\n", "\n", "2. **The value is a `?` placeholder, not pasted into the string.** The SQL says\n", " `WHERE idea.author = ?`, and `('ada',)` is sent separately on the next line.\n", " That separation is what stops a value like `'; DROP TABLE idea; --` from\n", " being *executed* as SQL instead of *compared* as text. It is the single most\n", " important habit in this entire unit, and the ORM gives it to you for free.\n", "\n", "Also worth noticing: the ORM opened a transaction you never asked for —\n", "`BEGIN (implicit)` — and closed it with **`ROLLBACK`**, not `COMMIT`. Nothing\n", "was written, so there was nothing to commit and rolling back is cheaper. When\n", "you write `POST /ideas` in class, watch this line turn into `COMMIT`." ] }, { "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": 7, "id": "cell-018", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2026-10-01 17:13:16,272 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2026-10-01 17:13:16,275 INFO sqlalchemy.engine.Engine SELECT idea.title, count(vote.id) AS count_1 \n", "FROM idea JOIN vote ON idea.id = vote.idea_id GROUP BY idea.id ORDER BY count(vote.id) DESC\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2026-10-01 17:13:16,275 INFO sqlalchemy.engine.Engine [generated in 0.00066s] ()\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "3 Automatic plant waterer\n", "1 A better course scheduler\n", "1 Laundry that folds itself\n", "2026-10-01 17:13:16,276 INFO sqlalchemy.engine.Engine ROLLBACK\n" ] } ], "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": 8, "id": "cell-020", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2026-10-01 17:13:16,281 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2026-10-01 17:13:16,282 INFO sqlalchemy.engine.Engine SELECT idea.title, count(vote.id) AS count_1 \n", "FROM idea LEFT OUTER JOIN vote ON idea.id = vote.idea_id GROUP BY idea.id ORDER BY count(vote.id) DESC\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2026-10-01 17:13:16,283 INFO sqlalchemy.engine.Engine [generated in 0.00044s] ()\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "3 Automatic plant waterer\n", "1 Laundry that folds itself\n", "1 A better course scheduler\n", "0 Bike lock that texts you\n", "2026-10-01 17:13:16,284 INFO sqlalchemy.engine.Engine ROLLBACK\n" ] } ], "source": [ "stmt = (\n", " select(Idea.title, func.count(Vote.id))\n", " .join(Vote, isouter=True) # LEFT OUTER JOIN: keep ideas with no votes\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-021", "metadata": {}, "source": [ "**Solution:**\n", "\n", "*Bike lock that texts you* is missing. `.join(Vote)` emits a plain (inner)\n", "`JOIN`, which only keeps an idea if some vote matches it — and nobody has voted\n", "for the bike lock. The fix is `.join(Vote, isouter=True)`, which emits\n", "`LEFT OUTER JOIN` (the same thing as `LEFT JOIN`) and keeps it, with a count\n", "of 0.\n", "\n", "Why 0 and not 1? Because we count `Vote.id`, which is `NULL` in the row the\n", "`LEFT JOIN` makes up for the bike lock, and `COUNT(column)` skips `NULL`s.\n", "`func.count()` with nothing inside becomes `COUNT(*)`, which counts rows — and\n", "would say the bike lock has one vote.\n", "\n", "This is what Lab 5's `GET /ideas` has to send. `check.py` checks the bike-lock\n", "case: a brand new idea has no votes, so a plain `JOIN` makes it vanish from\n", "your board." ] }, { "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": 9, "id": "cell-023", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2026-10-01 17:13:16,288 INFO sqlalchemy.engine.Engine BEGIN (implicit)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2026-10-01 17:13:16,289 INFO sqlalchemy.engine.Engine SELECT idea.id, idea.title, idea.author \n", "FROM idea\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2026-10-01 17:13:16,289 INFO sqlalchemy.engine.Engine [generated in 0.00044s] ()\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2026-10-01 17:13:16,291 INFO sqlalchemy.engine.Engine SELECT vote.id, vote.idea_id, vote.voter \n", "FROM vote \n", "WHERE vote.idea_id = ?\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2026-10-01 17:13:16,291 INFO sqlalchemy.engine.Engine [generated in 0.00039s] (1,)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "3 Automatic plant waterer\n", "2026-10-01 17:13:16,292 INFO sqlalchemy.engine.Engine SELECT vote.id, vote.idea_id, vote.voter \n", "FROM vote \n", "WHERE vote.idea_id = ?\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2026-10-01 17:13:16,293 INFO sqlalchemy.engine.Engine [cached since 0.001711s ago] (2,)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "1 Laundry that folds itself\n", "2026-10-01 17:13:16,293 INFO sqlalchemy.engine.Engine SELECT vote.id, vote.idea_id, vote.voter \n", "FROM vote \n", "WHERE vote.idea_id = ?\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2026-10-01 17:13:16,294 INFO sqlalchemy.engine.Engine [cached since 0.002821s ago] (3,)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "0 Bike lock that texts you\n", "2026-10-01 17:13:16,295 INFO sqlalchemy.engine.Engine SELECT vote.id, vote.idea_id, vote.voter \n", "FROM vote \n", "WHERE vote.idea_id = ?\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "2026-10-01 17:13:16,295 INFO sqlalchemy.engine.Engine [cached since 0.003932s ago] (4,)\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "1 A better course scheduler\n", "2026-10-01 17:13:16,295 INFO sqlalchemy.engine.Engine ROLLBACK\n" ] } ], "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": [ "**Solution:**\n", "\n", "**Five**: one for the ideas, then one more *per idea* to fetch its votes.\n", "With 1,000 ideas it would be 1,001 round trips to the database; B3 is **one**,\n", "whatever the number of ideas. This is called the **N+1 query problem**.\n", "\n", "It gives the right answer, and it passes every Lab 5 check — `check.py` can only\n", "see your responses, not how many queries made them. But `echo=True` shows it\n", "immediately, and it is exactly what the check-in's SQL question is looking\n", "at: *show me the SQL your ORM sends for `GET /ideas`, and read it to me.*" ] }, { "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": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.13.15" } }, "nbformat": 4, "nbformat_minor": 5 }