Module 3 — Real-world I/O

🧭 ◀ Data Science · 🏠 Course home · Web Scraping ▶

Goal: Move beyond toy data. Make polite, retry-aware HTTP calls against live APIs and query a SQL database from Python — the two ways real business data reaches your code (with an optional appendix for the API-less web: scraping).

Estimated time: 4–6 hours of focused study.

Prerequisites: Modules 1–2 (especially NB 4 — dictionaries and JSON — and NB 7 — pandas; the ETL and SQL examples build DataFrames).

   Public APIs               Your databases
   (NB 12 — HTTP)   ──►   (NB 13 — SQL/SQLite)
        │                        │
        └───────────┬────────────┘
                    ▼
   Now you can pull real data from anywhere —
   appendix A1 covers pages with no API (scraping),
   and A2 covers data that outgrew CSV+pandas
          (Parquet, DuckDB, Polars).

Notebooks at a glance

#	Notebook	⏱ Time	Difficulty	What you’ll learn / build
12	12_apis_and_http.ipynb	~45–60 min	Beginner / Intermediate	GET/POST with requests, status codes, auth headers, retry + backoff, pagination — capped by a weather ETL pipeline that writes forecast.csv
13	13_sql_fundamentals.ipynb	~50–70 min	Beginner / Intermediate	The six core SQL clauses, aggregations, JOINs, CTEs, and a window-function tour on an in-memory SQLite database — plus when SQL beats pandas

Optional appendices at a glance

Appendix	Notebook	⏱ Time	Difficulty	Focus
A1	A1_web_scraping_firecrawl.ipynb	~50–70 min	Intermediate	Optional, demo/reference style. DIY scraping with requests + BeautifulSoup (robots.txt, polite-scraper habits), then Firecrawl for LLM-ready markdown and structured extraction — runs fully offline via a local HTML fixture and a built-in Firecrawl mock
A2	A2_duckdb_polars.ipynb	~60–75 min	Intermediate	Optional, demo/reference style. When pandas isn’t enough: Parquet vs CSV measured honestly (columnar layout, pushdown), DuckDB as NB 13’s SQL pointed at files (globs, out-of-core, .df() interop), the Polars lazy API with explain(), an honest 4-engine benchmark, and the pandas → Parquet → engine → warehouse escalation ladder. Parquet sections run on the core stack; DuckDB/Polars cells skip gracefully if not installed

Notebook guides

12 · APIs, HTTP, and Real-World Data Fetching — 12_apis_and_http.ipynb

One question threads the whole notebook: “What’s the weather going to do this week?” You start by asking the Open-Meteo API for the current temperature in Berlin, and every new idea — status codes, headers, retries, pagination — earns its keep by making that same weather question more robust. By the last section the pieces snap together into a real Extract–Transform–Load pipeline that pulls a 7-day forecast for four cities (Berlin, London, New York, Tokyo) into a tidy CSV. A second, fake API (JSONPlaceholder) is borrowed for two detours weather can’t demonstrate: POST and pagination.

The mental model throughout: talking to an API is like mailing a letter and waiting for the reply — you format an envelope (method + URL + headers, plus a body for POST), requests is the courier, the server mails back a status code, headers, and body. The same patterns transfer directly to calling OpenAI, Anthropic, Stripe, Salesforce, or your internal data warehouse. Both APIs are free and keyless, and every networked cell degrades gracefully to a recorded response when there is no internet.

Learning objectives:

• Send GET and POST requests with the requests library

• Read HTTP status codes and handle errors gracefully

• Add query parameters, headers, and bearer-token auth

• Parse JSON responses into Python dicts and pandas DataFrames

• Handle rate limits and flaky networks with retry + backoff, and paginate multi-page result sets

• Build a small ETL pipeline that fetches API data and saves it locally

Sections:

1. The shape of every HTTP request

2. Setup

3. Your first GET request (incl. the request/response lifecycle, status codes, and headers up close)

4. HTTP status codes — what the server is telling you

5. Headers, authentication, and User-Agent

6. POST — sending data to a server

7. Retry with exponential backoff

8. Pagination — when one request isn’t enough

9. Putting it together — a real ETL pipeline

10. Common pitfalls

Practice: 3 ✋ quick exercises · 4 🧪 practice exercises (⭐–⭐⭐, incl. a Debug me 🐞) · 4 🧠 stretch exercises (⭐⭐⭐) · 1 🎁 bonus mini-project (a weekly weather dashboard built on the §9 fetch_forecast helper).

Files/datasets: Writes forecast.csv (city, date, max/min temperature, precipitation — 7 days × 4 cities). The copy shipped in this folder is the output of a sample run, so you can see what the pipeline should produce. No input files needed.

13 · SQL Fundamentals with pandas — 13_sql_fundamentals.ipynb

Every analyst job description asks for SQL, yet you can read most of the Python data-science internet without seeing a single SELECT. This notebook closes that gap. One dataset runs through everything: support_ops — customer support across five channels (Email, Chat, Phone, Web Form, Social) where an AI bot automates part of the load, with monthly ticket volume, automation rate, latency, satisfaction, and cost. That table is the question machine for the whole lesson: which channels does the bot serve well, and which need help?

The data (60 channel-month rows, built inline with a seeded RNG so the notebook is self-contained) is pushed into a SQLite database that lives entirely in RAM — SQLite ships with Python, so there is nothing to install. The mental model: a SQL query is an assembly line whose stations (WHERE, GROUP BY, HAVING, SELECT, …) don’t run in the order you write them — §5 shows the real logical order, which explains half of SQL’s confusing errors. Along the way you mirror queries in pandas and learn when each tool wins; the same SQL transfers (with minor syntax differences) to PostgreSQL, MySQL, BigQuery, and Snowflake.

Learning objectives:

• Connect to a SQLite database from Python and create tables from a DataFrame

• Write the six SQL clauses that cover ~95% of analytics work: SELECT, FROM, WHERE, GROUP BY, ORDER BY, LIMIT

• Aggregate with COUNT, SUM, AVG, MIN, MAX (and filter buckets with HAVING)

• Join two tables on a key, and break complex queries into readable CTEs (WITH …)

• Move data between SQL and pandas with read_sql and to_sql

• Decide when SQL is the better tool than pandas (and vice versa)

Sections:

1. SQL in one slide

2. Setup — load the data into SQLite (built inline, pushed in via to_sql)

3. Your first SELECT

4. WHERE — filtering rows

5. Aggregations — the part that earns its keep (the logical clause order, HAVING)

6. Grouping by multiple columns and time

7. JOINs — bringing two tables together

8. CTEs — making complex queries readable

9. SQL vs pandas — when to use which

10. Tiny tour of window functions

11. Cleaning up

Practice: 4 ✋ quick exercises (plus a 🔮 predict-the-output checkpoint) · 5 🧪 practice exercises (⭐–⭐⭐, incl. a Debug me 🐞) · 4 🧠 stretch exercises (⭐⭐⭐) · 1 🎁 bonus mini-project (a SQL-driven mini report combining a CTE with a JOIN).

Files/datasets: Nothing on disk — sqlite3.connect(":memory:") creates the database in RAM, holding support_ops plus a channel_meta lookup table (team leads, launch years) and a tiny budgets table for the JOIN section. The notebook shows sqlite3.connect("ops.db") as the one-line change that would persist it to a file.

Appendix guide

A1 · Web Scraping & Firecrawl — A1_web_scraping_firecrawl.ipynb

Notebook 12 pulled data from a clean JSON API — but most of the web has no API, and the data you want is trapped in HTML meant for human eyes. This optional, reference-style appendix has two halves: do it yourself (requests + BeautifulSoup, the rules of the road — robots.txt, ToS, rate limits, GDPR/PII — and the four polite-scraper habits: identify, throttle, cache, retry), then let a service do the hard part — Firecrawl, an API that turns any URL (including JavaScript-heavy, anti-bot pages) into clean, LLM-ready markdown or structured JSON in one call, the modern way to feed a RAG pipeline or an agent.

Everything runs offline: the hands-on BeautifulSoup cells parse a local HTML fixture (a mock bookshop catalogue page) instead of fetching a live site, and the Firecrawl cells use a small built-in mock that mimics the v2 SDK surface — install firecrawl-py and set FIRECRAWL_API_KEY to swap in the real service. The structured-extraction demo validates Firecrawl’s JSON output with Pydantic (falling back to a dataclass if Pydantic isn’t installed), and real SDK calls (scrape / crawl / map / search) are shown as commented reference code.

Sections:

1. When to scrape — and the rules you don’t break

2. The anatomy of a scrape

3. Respect robots.txt

4. Polite scraping — the four habits

5. Where DIY scraping hurts

6. Firecrawl — the LLM-ready web API (incl. structured extraction into typed JSON)

7. Choosing your approach

Practice: 3 ✋ quick exercises · 2 🧪 exercises (unrated — appendices favour demo over drill) · no stretch exercises or mini-project.

Files/datasets: None — the HTML fixture and the Firecrawl mock live inline in the notebook.

A2 · When pandas Isn’t Enough: Parquet, DuckDB & Polars — A2_duckdb_polars.ipynb

The day the data outgrows the notebook. The webshop’s order log is now 6 million rows; the team’s read_csv ritual costs seconds per rerun and a third of a gigabyte of disk. The appendix untangles three dials that get conflated in “pandas is slow” — file format, execution model, memory model — and upgrades them one at a time, measuring at every step.

§1–2 make the case against CSV mechanically (row-oriented text: parsing is the price of everything) and fix the format first: the same pandas code on Parquet lands ~3× smaller files and >20× faster reads, plus the columnar superpower of reading only the columns a question needs. §3 points NB 13’s SQL directly at files with DuckDB — no server, no load step, a glob over monthly partition files as a table, results handed back to pandas via .df(). §4 introduces the Polars lazy grammar (scan → expressions → collect()) and uses explain() to show projection and predicate pushdown actually happening in the plan, with a pandas↔Polars translation table for every move NB 7 taught. §5 races all four setups on the same query and then spends more words on how to read a micro-benchmark honestly than on the numbers — verify the answers agree, don’t credit the engine for the format’s win, remember that in-RAM benchmarks understate the engines’ real advantages. §6 closes with the escalation ladder (pandas → +Parquet → DuckDB/Polars → warehouse) and the two questions that decide: does it fit in RAM, and what shape is the work?

Practice: 3 ✋ quick-exercise checkpoints (why CSV can’t column-prune, AOV in DuckDB SQL, a pandas→Polars translation) and 4 numbered exercises with worked solutions — measuring column pruning, a window-function query that earns the engine, a Debug me 🐞 where a viral “3000× faster” benchmark turns out to have timed the query plan instead of the query, and a verified CSV→Parquet migration function.

Datasets: Synthetic, generated inline — one year of order lines (6 M rows) written to scratch CSV/Parquet/monthly partitions at runtime; nothing committed to the repo. Parquet sections need only the core stack (pandas+pyarrow); pip install duckdb polars turns on the engine sections, and every such cell prints a clear skip note without them.

How these notebooks work

Every notebook runs 100% offline. NB 12 talks to two free, keyless public APIs (Open-Meteo and JSONPlaceholder) when you have internet, and every networked cell wraps its call in try/except with a graceful fallback to a recorded response — plus “offline proof” cells that model the whole request/response lifecycle with plain dicts. NB 13 never touches the network at all (in-memory SQLite), A1 scrapes a local HTML fixture and mocks the Firecrawl SDK, and A2 generates its 6M-row dataset inline and writes only to a scratch directory. Each lesson opens with a Colab badge and a metadata line (estimated time, difficulty), then follows the course rhythm: ✋ Quick exercise (~2 min) checkpoints with collapsible 💡 solutions, end-of-lesson 🧪 practice exercises (⭐-rated, including a “Debug me 🐞”), 🧠 stretch exercises, and a 🎁 bonus mini-project in the core lessons.

Where next

→ Module 4 — Web Scraping (../04_webscraping/) — get data off the web politely when there’s no API, or → Module 5 — Machine Learning (../05_machine_learning/17_sklearn_basics.ipynb)

---

📝 Finished this module? Test yourself with the Module 3 quiz — five questions, ~10 minutes.