{ "cells": [ { "cell_type": "markdown", "metadata": { "role": "brief" }, "source": [ "# LAB 1: Python & SQLite Database Connectivity Lab\n", "**Assignment Version: eda-lab-1.0 \u00b7 4 Tasks \u00b7 18 Rubric Points \u00b7 Estimated Time: 40 min**\n", "\n", "Practice secure SQLite operations, bound parameterized queries, connection context manager transaction rollbacks, SQLAlchemy engine queries, and chunked streaming with Pandas.\n", "\n", "### How Marks Are Awarded\n", "| Task | Deliverable | Points |\n", "| :--- | :--- | ---: |\n", "| T05 \u00b7 Secure SQLite CRUD and cursor retrieval | lab_conn, find_note, CRUD output, Cursor fetch output | 5 |\n", "| T06 \u00b7 Prove transaction rollback | rollback_before, rollback_after, rollback_observed | 4 |\n", "| T07 \u00b7 SQLAlchemy and pandas round trip | engine, sql_customers, sql_extract, SQL text and bound parameters | 5 |\n", "| T08 \u00b7 Process SQL in chunks and plan production access | stream_rows, stream_revenue, chunk_count, Production connection note | 4 |\n", "\n", "**Total Points Available: 18 points.** (Pass mark: 80%)\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Environment Setup\n", "Run unchanged. This initializes your local exercise workspace.\n" ] }, { "cell_type": "code", "metadata": { "role": "setup" }, "source": [ "import sys, json, sqlite3, hashlib\n", "from pathlib import Path\n", "from itertools import combinations\n", "from collections import Counter\n", "import numpy as np\n", "import pandas as pd\n", "import matplotlib.pyplot as plt\n", "import seaborn as sns\n", "from sqlalchemy import create_engine, text, inspect\n", "from IPython.display import display\n", "ASSIGNMENT_VERSION = \"eda-capstone-2.0\"\n", "SNAPSHOT_DATE = pd.Timestamp(\"2024-07-01\")\n", "ROOT = Path.cwd() / \"topfolio_eda_capstone_v2\"\n", "DATA_DIR, OUTPUT_DIR = ROOT / \"monthly_exports\", ROOT / \"outputs\"\n", "for directory in (DATA_DIR, OUTPUT_DIR):\n", " directory.mkdir(parents=True, exist_ok=True)\n", "sns.set_theme(style=\"whitegrid\", palette=\"colorblind\")\n", "plt.rcParams.update({\"figure.figsize\": (10, 5), \"font.size\": 10})\n", "print(\"Python\", sys.version.split()[0], \"| pandas\", pd.__version__, \"| seaborn\", sns.__version__)\n", "print(\"Workspace:\", ROOT)\n" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Data Setup & Baselines\n", "Run unchanged. This prepares the exercise environment and data structures.\n" ] }, { "cell_type": "code", "metadata": { "role": "data_setup" }, "source": [ "# Lab 1 Baseline Data Setup\n", "rng = np.random.default_rng(42)\n", "products = pd.DataFrame([\n", " (\"P01\", \"Laptop\", \"Electronics\", 899.0), (\"P02\", \"Headphones\", \"Electronics\", 89.0),\n", " (\"P03\", \"USB Cable\", \"Electronics\", 12.0), (\"P04\", \"Desk\", \"Furniture\", 249.0),\n", " (\"P05\", \"Chair\", \"Furniture\", 149.0), (\"P06\", \"Notebook\", \"Office Supplies\", 5.0),\n", " (\"P07\", \"Pen Pack\", \"Office Supplies\", 8.0), (\"P08\", \"T-Shirt\", \"Apparel\", 24.0),\n", " (\"P09\", \"Jacket\", \"Apparel\", 79.0), (\"P10\", \"Kettle\", \"Home & Kitchen\", 39.0),\n", " (\"P11\", \"Mug\", \"Home & Kitchen\", 15.0), (\"P12\", \"Lamp\", \"Furniture\", 49.0),\n", "], columns=[\"product_id\", \"product\", \"product_category\", \"list_price\"])\n", "cities = [(\"Springfield\", \"IL\", \"Midwest\"), (\"Springfield\", \"MA\", \"Northeast\"),\n", " (\"Austin\", \"TX\", \"South\"), (\"Portland\", \"OR\", \"West\")]\n", "customers = pd.DataFrame([(f\"C{i:03}\", *cities[(i-1)%4], [\"Bronze\",\"Silver\",\"Gold\"][(i-1)%3])\n", " for i in range(1,121)], columns=[\"customer_id\",\"city\",\"state\",\"region\",\"customer_tier\"])\n", "\n", "# Generate clean sales dataframe for SQL round-trip and chunking\n", "rows = []\n", "product_lookup = products.set_index('product_id')\n", "customer_lookup = customers.set_index('customer_id')\n", "for i in range(720):\n", " cid = f\"C{(i+1 if i < 120 else int(rng.integers(1,121))):03}\"\n", " customer = customer_lookup.loc[cid]\n", " month = i // 120 + 1\n", " timestamp = pd.Timestamp(2024,month,int(rng.integers(1,28)),int(rng.choice([8,10,12,13,17,18,19,20])))\n", " status = \"completed\" if i < 120 or i % 11 else (\"cancelled\" if i % 22 else \"refunded\")\n", " selected = ['P01','P02','P03'] if i%7 == 0 else list(rng.choice(products.product_id,size=int(rng.integers(1,4)),replace=False))\n", " for pid in selected:\n", " product = product_lookup.loc[pid]\n", " quantity = 60 if i == 250 and pid == selected[0] else int(rng.integers(1,5))\n", " rows.append(dict(line_id=f\"L{len(rows)+1:05}\",order_id=f\"O{i+1:04}\",customer_id=cid,\n", " order_date=timestamp,product_id=pid,product=product['product'],\n", " product_category=product.product_category,quantity=quantity,unit_price=float(product.list_price),\n", " status=status,purchase_address=f\"{i+1} Market St, {customer.city}, {customer.state} 12345\",\n", " customer_tier=customer.customer_tier,region=customer.region))\n", "sales = pd.DataFrame(rows)\n", "sales = sales.loc[sales.status.eq('completed')].copy()\n", "sales['line_revenue'] = sales.quantity * sales.unit_price\n", "sales['month'] = sales.order_date.dt.month\n", "sales['hour'] = sales.order_date.dt.hour\n", "sales['city_state'] = sales.purchase_address.str.split(',').str[1].str.strip() + ', ' + sales.purchase_address.str.split(',').str[2].str.strip().str[:2]\n", "print(f\"Environment ready: {len(customers)} customers, {len(sales)} completed sales lines.\")\n" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": { "task_id": "T05", "role": "task_brief" }, "source": [ "## T05 \u00b7 Secure SQLite CRUD and cursor retrieval\n", "\n", "**5 points \u00b7 about 20 minutes**\n", "\n", "Create `lab_conn`, an in-memory sqlite3 connection, and an analyst_notes table (note_id INTEGER PRIMARY KEY, body TEXT). Use bound values for INSERT, SELECT, UPDATE and DELETE. Insert IDs 1,2,3 with bodies \"customer's first order\", 'review pending', and the literal `attack_input = \"x' OR 1=1 --\"`; update ID 1 to \"reviewed customer's order\" and delete ID 2. Commit. Implement `find_note(body)` returning matching (note_id, body) tuples using a bound SELECT. Demonstrate fetchone(), fetchmany(1) and fetchall() advancing a cursor over the remaining IDs. Never format a user value into SQL.\n", "\n", "\n", "**How this task earns marks:**\n", "- 2 points: Correct parameterised insert/update/delete and persistence.\n", "- 2 points: Bound SELECT returns exact literal including apostrophe/injection-shaped text.\n", "- 1 points: Cursor retrieval methods and lifecycle demonstrated.\n" ] }, { "cell_type": "code", "metadata": { "task_id": "T05", "role": "implementation" }, "source": [ "# T05: Secure SQLite CRUD and cursor retrieval\n", "# Replace the placeholder with your implementation.\n", "raise NotImplementedError('Complete T05')\n" ], "execution_count": null, "outputs": [] }, { "cell_type": "code", "metadata": { "task_id": "T05", "role": "public_checks" }, "source": [ "# Public self-checks: keep these; the rubric also checks reasoning and source.\n", "assert find_note(attack_input) == [(3,attack_input)], 'Treat input as data, not SQL.'\n", "assert find_note(\"reviewed customer's order\") == [(1,\"reviewed customer's order\")]\n", "assert find_note('not present') == []\n", "assert lab_conn.execute('SELECT COUNT(*) FROM analyst_notes WHERE note_id=2').fetchone()[0] == 0\n", "assert fetch_results == [(1,),[(3,)],[]]\n", "\n", "print('T05 self-checks passed')\n" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": { "task_id": "T06", "role": "task_brief" }, "source": [ "## T06 \u00b7 Prove transaction rollback\n", "\n", "**4 points \u00b7 about 15 minutes**\n", "\n", "Using only analyst_notes, demonstrate atomic rollback: record `rollback_before` as the full sorted row list, start a `with lab_conn:` block, insert ID 10, then intentionally insert ID 10 again to violate the primary key. Catch sqlite3.IntegrityError outside the block. Set `rollback_after` from a new SELECT and `rollback_observed` to True only when the expected error occurs. ID 10 must not remain. Show that the connection is still usable. Never induce this error against a production database.\n", "\n", "\n", "**How this task earns marks:**\n", "- 3 points: Intentional constraint error rolls back both writes atomically.\n", "- 1 points: Explains transaction scope versus connection lifetime.\n" ] }, { "cell_type": "code", "metadata": { "task_id": "T06", "role": "implementation" }, "source": [ "# T06: Prove transaction rollback\n", "# Replace the placeholder with your implementation.\n", "raise NotImplementedError('Complete T06')\n" ], "execution_count": null, "outputs": [] }, { "cell_type": "code", "metadata": { "task_id": "T06", "role": "public_checks" }, "source": [ "# Public self-checks: keep these; the rubric also checks reasoning and source.\n", "assert rollback_observed and rollback_before == rollback_after\n", "assert lab_conn.execute('SELECT COUNT(*) FROM analyst_notes WHERE note_id=10').fetchone()[0] == 0\n", "assert lab_conn.execute('SELECT 1').fetchone() == (1,)\n", "\n", "print('T06 self-checks passed')\n" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": { "task_id": "T06", "role": "written_response" }, "source": [ "### T06 \u00b7 Written response\n", "\n", "Why must the error be caught outside the transaction block? Does with conn close the connection? Explain what would happen if the first insert were committed early.\n", "\n", "**Your response:**\n", "\n", "_Write your answer here, citing your actual results._\n" ] }, { "cell_type": "markdown", "metadata": { "task_id": "T07", "role": "task_brief" }, "source": [ "## T07 \u00b7 SQLAlchemy and pandas round trip\n", "\n", "**5 points \u00b7 about 20 minutes**\n", "\n", "Create `engine` for a local SQLite file at ROOT / 'analytics.sqlite'. Write sales to warehouse_sales and customers to warehouse_customers with index=False. For these two disposable local exercise tables only, replace is allowed on rerun. Read selected columns from warehouse_customers using pd.read_sql_table into `sql_customers`. Use pd.read_sql_query with SQLAlchemy text(), bound status='completed' and minimum=250, and an explicit JOIN on customer_id to produce `sql_extract` containing line_id, order_id, customer_id, line_revenue and customer_region. Keep raw files unchanged. Show connection context management.\n", "\n", "\n", "**How this task earns marks:**\n", "- 2 points: Engine plus correct table reads/writes with index=False.\n", "- 2 points: Bound filtered JOIN returns correct rows and customer_region.\n", "- 1 points: Connections managed; replace restricted to disposable exercise tables.\n" ] }, { "cell_type": "code", "metadata": { "task_id": "T07", "role": "implementation" }, "source": [ "# T07: SQLAlchemy and pandas round trip\n", "# Replace the placeholder with your implementation.\n", "raise NotImplementedError('Complete T07')\n" ], "execution_count": null, "outputs": [] }, { "cell_type": "code", "metadata": { "task_id": "T07", "role": "public_checks" }, "source": [ "# Public self-checks: keep these; the rubric also checks reasoning and source.\n", "assert len(sql_customers) == 120 and sql_customers.customer_id.is_unique\n", "assert set(sql_extract.line_id) == set(sales.loc[sales.line_revenue.ge(250),'line_id'])\n", "assert sql_extract.line_id.is_unique\n", "assert sql_extract.customer_region.equals(sql_extract.customer_id.map(customers.set_index('customer_id').region))\n", "assert 'index' not in [c['name'] for c in inspect(engine).get_columns('warehouse_sales')]\n", "\n", "print('T07 self-checks passed')\n" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": { "task_id": "T08", "role": "task_brief" }, "source": [ "## T08 \u00b7 Process SQL in chunks and plan production access\n", "\n", "**4 points \u00b7 about 15 minutes**\n", "\n", "Read line_id and line_revenue from warehouse_sales with pd.read_sql_query(..., chunksize=200). Iterate without concatenating chunks into another full table. Compute `stream_rows`, `stream_revenue` and `chunk_count`; compare with sales. In the written response give PostgreSQL and MySQL dialect/driver names, environment-variable credential handling, connection pooling/cleanup, and why server-side filtering reduces transfer. No external database account is required.\n", "\n", "\n", "**How this task earns marks:**\n", "- 2 points: Chunk iterator used without full-result concatenation.\n", "- 1 points: Streamed row/revenue totals reconcile.\n", "- 1 points: Accurate driver, secret, pool and resource plan.\n" ] }, { "cell_type": "code", "metadata": { "task_id": "T08", "role": "implementation" }, "source": [ "# T08: Process SQL in chunks and plan production access\n", "# Replace the placeholder with your implementation.\n", "raise NotImplementedError('Complete T08')\n" ], "execution_count": null, "outputs": [] }, { "cell_type": "code", "metadata": { "task_id": "T08", "role": "public_checks" }, "source": [ "# Public self-checks: keep these; the rubric also checks reasoning and source.\n", "assert stream_rows == len(sales) and np.isclose(stream_revenue,sales.line_revenue.sum())\n", "assert chunk_count == int(np.ceil(len(sales)/200))\n", "\n", "print('T08 self-checks passed')\n" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": { "task_id": "T08", "role": "written_response" }, "source": [ "### T08 \u00b7 Written response\n", "\n", "Write a production handoff covering postgresql+psycopg2, mysql+pymysql, an environment variable for the URI, .env exclusion from Git, least-privilege access, pooled connections and dispose(). Do not include real credentials.\n", "\n", "**Your response:**\n", "\n", "_Write your answer here, citing your actual results._\n" ] }, { "cell_type": "markdown", "metadata": { "role": "handoff" }, "source": [ "## Submission Checklist\n", "- All 4 tasks attempted; kernel restarted and all cells run cleanly from top to bottom.\n", "- Visual outputs, tables, and written responses retained in the notebook.\n", "- Save and download your completed `.ipynb` file.\n", "- Upload and submit on the corresponding course lab page.\n" ] } ], "metadata": { "language_info": { "name": "python", "version": "3.10" }, "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "topfolio_assignment": { "version": "eda-lab-1.0", "course_slug": "python-eda-data-viz", "title": "LAB 1: Python & SQLite Database Connectivity Lab", "role": "student_starter", "task_count": 4, "max_points": 18 } }, "nbformat": 4, "nbformat_minor": 4 }