Before the questions, make sure you can: write SQL queries that filter, group, join and find unmatched rows, and run them from pandas; explain parameterized queries; call a REST API, follow pagination, respect rate limits and handle HTTP errors; parse an HTML table with BeautifulSoup using robust selectors; read a robots.txt file and decide what may be crawled; weigh the legal and ethical questions of scraping (terms of service, copyright, personal data); and design an acquisition pipeline that is reproducible and documented.
A visualization is only as good as its data, and in practice most of the work is getting that data: querying a database, calling an API page by page, or extracting a table from a web page. These steps fail silently — a query that joins on the wrong key, an API that returns page 1 only, a scraper that breaks when a class name changes — and AI-written acquisition code fails in exactly these ways. This chapter turns data acquisition into an engineering task with tests, limits and ethics.
Sources of data
| Source | Access | Typical issues |
|---|---|---|
| Files (CSV, Excel, JSON, Parquet) | download, pd.read_* |
versions, encodings, types |
| Relational databases (MySQL, PostgreSQL, SQLite) | SQL queries | joins, permissions, load on production systems |
| APIs (REST/JSON) | HTTP requests | authentication, pagination, rate limits, changing schemas |
| Web pages | scraping HTML | legality, fragile selectors, JavaScript rendering |
| Streams (sensors, message queues) | subscriptions | volume, late or missing events (Chapter 13) |
| Open-data portals | files and APIs | licences, documentation quality |
Prefer, in this order: an official dataset or API → a database export → scraping as a last resort.
SQL for visualization
Most business data lives in relational databases. The queries you need most:
SELECT s.district, COUNT(*) AS trips, ROUND(AVG(t.duration_min), 1) AS avg_min
FROM trips t JOIN stations s ON t.start_station = s.station_id
WHERE t.duration_min BETWEEN 1 AND 180
GROUP BY s.district
HAVING COUNT(*) >= 2
ORDER BY trips DESC;
WHEREfilters rows before grouping;HAVINGfilters groups after aggregation.JOIN(inner) keeps only matching rows;LEFT JOIN … WHERE right.key IS NULLfinds rows without a match (stations with no trips).- Aggregate in the database when data is large: sending 10 million trips to Python to compute 6 district counts is wasteful.
From Python: pd.read_sql_query(sql, connection) returns a DataFrame; df.to_sql("table", connection) writes one. SQLite (sqlite3, built into Python) is ideal for labs; MySQL/PostgreSQL use a driver (mysql-connector, psycopg) or SQLAlchemy.
Never build SQL by pasting user input into a string (f"... WHERE district = '{name}'"): an input such as x' OR '1'='1 changes the query (SQL injection). Use parameters: con.execute("SELECT * FROM trips WHERE start_station = ?", (station,)) (sqlite3) or %s placeholders (MySQL drivers).
APIs
A REST API returns data (usually JSON) at a URL:
r = requests.get("https://api.example.org/v1/readings",
params={"district": "Lucheng", "page": 1},
headers={"Authorization": f"Bearer {os.environ['CITYPULSE_KEY']}"},
timeout=10)
r.raise_for_status() # raises for 4xx / 5xx
data = r.json()
- Status codes: 200 OK, 301/302 redirect, 400 bad request, 401 unauthenticated, 403 forbidden, 404 not found, 429 too many requests (slow down; often with a
Retry-Afterheader), 500/503 server errors (retry later with exponential back-off). - Pagination: results come in pages (
page/nextlinks, cursors or offsets). Loop until there is no next page — but guard against loops and missing pages, and deduplicate, because items can shift between pages while you read. - Rate limits: at 30 requests per minute, one request every 2 s; 500 pages take at least 998 s ≈ 17 min. Plan, cache and download incrementally.
- Secrets: API keys go in environment variables or a secrets manager — never in code, notebooks or repositories (and never in prompts to public AI tools).
- Standards help: bike-share systems worldwide publish GBFS (General Bikeshare Feed Specification) feeds such as
station_information.jsonandstation_status.json.
requests has no default timeout: without timeout=, a dead server can hang your pipeline forever.
Web scraping
When there is no API, data can be extracted from HTML:
soup = BeautifulSoup(html, "html.parser")
table = soup.find("table", id="stations")
rows = [[td.get_text(strip=True) for td in tr.find_all("td")]
for tr in table.find("tbody").find_all("tr")]
Robust scrapers:
- select by stable attributes (ids, semantic tags, headers), not by generated layout classes such as
x7f2that change with every redesign; - validate every row (wrong number of cells, "n/a" values) instead of crashing or storing garbage;
- set a descriptive User-Agent, a timeout, and a delay between requests; cache pages you already fetched;
- for pages built by JavaScript, look for the underlying JSON API in the browser's network tab, or use a browser-automation tool (Playwright, Selenium) as a last resort.
pd.read_html can parse simple tables directly, but you still need validation.
Legal and ethical rules
- robots.txt (the Robots Exclusion Protocol, standardized as RFC 9309 in 2022) tells crawlers which paths they may fetch and sometimes a crawl delay. It is a convention, not an access-control system — respecting it is part of professional conduct.
- Terms of service may forbid scraping or reuse; copyright may protect content; personal data (names, faces, locations) is regulated (e.g., China's PIPL, the EU's GDPR). Collect only what you need, anonymize, and store securely.
- Do no harm: do not overload small servers; identify yourself; stop when asked.
- Licences: open data comes with licences (CC BY, ODbL) that require attribution — cite your sources on the visualization.
For example, IMDb publishes official datasets for non-commercial use at datasets.imdbws.com — far better than scraping its web pages, whose layout changes regularly and which its conditions of use restrict.
Reproducible acquisition
A professional pipeline is a script, not a series of clicks: it records when data was fetched and from where, saves raw responses before cleaning, logs counts at each step, and can be re-run. This is what lets you — and reviewers of your project — trust the numbers in the final dashboard.
Data engineers build ELT pipelines (extract–load–transform) with schedulers such as Airflow or Dagster: raw data is loaded unchanged into a warehouse, then transformed with tested SQL (dbt). Dashboards read only from the tested tables — so a broken source shows up as a failed test, not as a wrong chart in a meeting.
Large-scale web data powers both visualization research and AI training, raising open questions about consent, copyright and representativeness. Research datasets now come with datasheets describing how they were collected, by whom, with what permissions and biases — a practice worth copying in your project.
Key takeaways
- Prefer official datasets and APIs; scrape only when necessary and permitted.
- SQL: WHERE filters rows, HAVING filters groups, JOIN combines tables, LEFT JOIN … IS NULL finds unmatched rows; aggregate in the database; always use parameters.
- APIs: check status codes, follow pagination safely, deduplicate, respect rate limits (429, back-off), use timeouts, keep keys out of code.
- Scraping: stable selectors, validate rows, identify yourself, delay requests; robots.txt, terms of service, copyright and privacy apply.
- Make acquisition reproducible: scripts, raw copies, timestamps and counts.
Ready? Close the notes and practise.
30 questions. Predict the output before you check — that is the skill the exam measures.