Data Sources and APIs
Open in the Padhaiko app2.1 Reading and writing structured/unstructured data (CSV, JSON, Excel, text)
Data science workflows ingest data from heterogeneous sources: plain logs, delimited flat files, nested API payloads, and multi-sheet spreadsheets. Choosing the right parser, encoding (utf-8), and context manager (with open(...)) prevents resource leaks and character corruption.
| Dimension | Structured Data | Semi-Structured Data | Unstructured Data |
|---|---|---|---|
| Schema Model | Rigid tabular schema (fixed columns and types) | Self-describing tags/keys; schema can vary per record | No predefined schema or field boundaries |
| Examples | CSV tables, SQL tables, Excel sheets | JSON documents, XML payloads, YAML configs | Raw text logs, emails, PDFs, images, audio |
| Parsing Strategy | csv module, pandas.read_csv(), read_excel() | json module, xml.etree.ElementTree | Line-by-line text scanning, regex, NLP tokenizers |
Plain Text Files
Always open text files using a with context manager and explicit encoding="utf-8" so file descriptors close automatically even when exceptions occur:
from pathlib import Path
log_path = Path("events.log")
log_path.write_text("2026-11-18 INFO pipeline_started\n", encoding="utf-8")
with log_path.open("r", encoding="utf-8") as fh:
for line in fh:
parts = line.strip().split(maxsplit=2)Delimited Files (CSV)
Python's built-in csv module handles quoting, embedded commas, and header mapping via DictReader and DictWriter (always pass newline="" on Windows/cross-platform writes to prevent blank rows):
import csv
rows = [{"id": 1, "city": "Kathmandu", "temp": 22.5},
{"id": 2, "city": "Pokhara", "temp": 24.0}]
with open("weather.csv", "w", newline="", encoding="utf-8") as f:
writer = csv.DictWriter(f, fieldnames=["id", "city", "temp"])
writer.writeheader()
writer.writerows(rows)
with open("weather.csv", "r", newline="", encoding="utf-8") as f:
records = list(csv.DictReader(f))For analytical work, pandas.read_csv("weather.csv", usecols=["city", "temp"], dtype={"city": "string"}) parses numeric columns and missing tokens (NA, NaN) directly into a DataFrame.
JSON (JavaScript Object Notation)
| Function | Input / Output | Use Case |
|---|---|---|
json.load(fp) | File-like object → Python dict/list | Reading a .json file from disk |
json.loads(s) | JSON str → Python dict/list | Parsing an HTTP API response body |
json.dump(obj, fp, indent=2) | Python object → .json file | Saving structured configuration or results |
json.dumps(obj) | Python object → JSON str | Serializing a payload for an API POST request |
For line-delimited JSON logs (JSONL / NDJSON), iterate line by line and call json.loads(line) rather than loading one huge JSON array, or use pandas.read_json("events.jsonl", lines=True).
Excel Workbooks (.xlsx)
Excel files support multiple worksheets, formulas, and formatting. In Python:
pandas.read_excel("sales.xlsx", sheet_name="Q1"): Quickly loads a sheet into a DataFrame (engine="openpyxl").openpyxl: Reads and writes cell values, styles, formulas, and multiple sheets programmatically usingpd.ExcelWriter("out.xlsx", engine="openpyxl")oropenpyxl.load_workbook().
2.2 Database access with relational database and non-relational database
Production datasets rarely live only in flat files; they reside in relational (SQL) and non-relational (NoSQL) databases for concurrent access, indexing, and persistence.
| Aspect | Relational Database (RDBMS) | Non-Relational Database (NoSQL) |
|---|---|---|
| Data Model | Normalized tables with rows, columns, and foreign keys | Document (JSON/BSON), Key-Value, Wide-Column, or Graph |
| Schema | Fixed schema enforced upfront (schema-on-write) | Flexible/dynamic fields per document (schema-on-read) |
| Query Language | Declarative SQL (SELECT, JOIN, GROUP BY) | Vendor APIs / JSON filter queries (collection.find()) |
| Scaling & Consistency | Vertical scaling, strong ACID transactions | Horizontal sharding, eventual or tunable consistency |
Relational Database Access (sqlite3 and Parameterized Queries)
Python ships with sqlite3 (implementing PEP 249 — DB-API 2.0), while libraries such as psycopg (PostgreSQL) and SQLAlchemy follow the same connection/cursor workflow:
- Open a connection (
conn = sqlite3.connect("analytics.db")). - Obtain a cursor (
cur = conn.cursor()) or usepandas.read_sql_query(...). - Execute parameterized queries using placeholders (
?insqlite3,%sinpsycopg)—never string interpolation (f"..."), which opens SQL injection vulnerabilities. - Commit write transactions (
conn.commit()) or usewith conn:for automatic commit/rollback, then close the connection.
import sqlite3
with sqlite3.connect("students.db") as conn:
cur = conn.cursor()
cur.execute(
"CREATE TABLE IF NOT EXISTS scores (id INTEGER PRIMARY KEY, name TEXT, gpa REAL)"
)
cur.execute("INSERT INTO scores (id, name, gpa) VALUES (?, ?, ?)", (1, "Aarav", 3.85))
rows = cur.execute("SELECT name, gpa FROM scores WHERE gpa >= ?", (3.5,)).fetchall()For larger applications, SQLAlchemy provides an engine connection pool (create_engine("postgresql+psycopg://...")) and seamless integration with pandas.read_sql(query, engine).
Non-Relational Database Access (Document & Key-Value Stores)
- Document Store (
pymongofor MongoDB): Stores BSON/JSON documents in collections without rigid columns. Queries pass Python dictionaries as filter predicates and projections:
from pymongo import MongoClient
client = MongoClient("mongodb://localhost:27017/")
db = client["ecommerce"]
orders = db["orders"]
high_value = list(orders.find({"total": {"$gte": 5000}}, {"_id": 0, "customer": 1, "total": 1}))- Key-Value Store (
redis): Stores in-memory cached feature vectors, session tokens, or rate-limit counters viar.set("user:42", val, ex=3600)andr.get("user:42").
2.3 Accessing and processing data from APIs (REST, SOAP)
Modern applications expose live datasets (weather, finance, social feeds, government portals) over HTTP Web APIs.
REST vs. SOAP Web Services
- REST (Representational State Transfer): Lightweight architectural style over HTTP using standard verbs (
GETretrieve,POSTcreate,PUT/PATCHupdate,DELETEremove), resource URLs (/api/v1/stations/42), status codes (200 OK,201 Created,400 Bad Request,401 Unauthorized,404 Not Found,429 Too Many Requests,5xx Server Error), and typically JSON payloads. - SOAP (Simple Object Access Protocol): Strict enterprise protocol exchanging XML envelopes (
<soap:Envelope>,<soap:Header>,<soap:Body>) described by a formal WSDL (Web Services Description Language) contract. Supports built-in WS-Security and ACID-like enterprise extensions, often found in banking and legacy telecom systems.
| Aspect | REST API | SOAP API |
|---|---|---|
| Message Format | JSON (most common), XML, or plain text | Strictly XML inside SOAP Envelope |
| Contract Definition | Informal or OpenAPI / Swagger spec | Formal WSDL XML schema |
| Transport & Caching | HTTP/HTTPS; GET responses are cacheable | HTTP, SMTP, JMS; rarely HTTP-cached |
| Payload Overhead | Compact, fast to parse in Python (resp.json()) | Verbose XML tags, heavier parsing (zeep / lxml) |
Consuming REST APIs Safely in Python
Best practices when fetching API data with requests:
- Always set a
timeout=(connect_s, read_s)so hung sockets never stall a pipeline forever. - Check HTTP status codes with
resp.raise_for_status(). - Pass query parameters via
params={...}instead of manual URL concatenation. - Handle authentication (API keys,
Authorization: Bearer <token>headers stored in environment variables—never hardcoded) and pagination (page,offset, ornextcursor URLs) plus rate-limit backoff on HTTP429.
import requests
def fetch_all_pages(base_url: str, token: str, limit: int = 100) -> list[dict]:
headers = {"Authorization": f"Bearer {token}", "Accept": "application/json"}
results = []
page = 1
while True:
resp = requests.get(
base_url,
headers=headers,
params={"page": page, "limit": limit},
timeout=(5, 15),
)
resp.raise_for_status()
payload = resp.json()
batch = payload.get("items", [])
if not batch:
break
results.extend(batch)
if len(batch) < limit:
break
page += 1
return resultsConsuming SOAP APIs
In Python, SOAP endpoints are consumed either with zeep (which parses the service's ?wsdl URL and auto-generates Python client methods) or by posting a SOAP XML envelope via requests.post(..., headers={"Content-Type": "text/xml"}) and parsing the XML reply with xml.etree.ElementTree.
2.4 Web scraping using requests and BeautifulSoup
When a website publishes public data in HTML pages without an official API, web scraping extracts structured records from the DOM tree.
Ethical & Legal Scraping Checklist
- Check if an official API or bulk CSV download already exists first.
- Inspect
https://example.com/robots.txtand respectDisallowrules andCrawl-delay. - Identify your scraper with a polite
User-Agentheader and throttle requests (time.sleep(1.0)) to avoid overloading servers. - Never bypass authentication, CAPTCHAs, or copyright restrictions.
Scraping Workflow with requests and BeautifulSoup (bs4)
Key BeautifulSoup Parsing Methods
BeautifulSoup(html_text, "html.parser")(or"lxml"for speed) parses raw HTML into a navigable tag tree.soup.find(tag, attrs={...}): returns the first matching element (orNone).soup.find_all(tag, attrs={...}): returns a list of all matching elements.soup.select(css_selector)/soup.select_one(css_selector): matches elements using CSS selectors (e.g.,"table.results tr","div.price > span").tag.get_text(strip=True): extracts clean inner text;tag.get("href")safely retrieves attributes.
import requests
from bs4 import BeautifulSoup
url = "https://example.org/courses"
resp = requests.get(url, headers={"User-Agent": "AcademicScraper/1.0"}, timeout=10)
resp.raise_for_status()
soup = BeautifulSoup(resp.text, "html.parser")
courses = []
for row in soup.select("table#catalog tbody tr"):
cells = [td.get_text(strip=True) for td in row.find_all("td")]
if len(cells) >= 3:
courses.append({"code": cells[0], "title": cells[1], "credits": int(cells[2])})Note that requests + BeautifulSoup fetches static server-rendered HTML; pages whose content is rendered client-side by JavaScript require inspecting network XHR/JSON endpoints or using headless browser automation (e.g., Playwright/Selenium).
2.5 Handling large datasets with chunking and lazy evaluation
Loading a 10 GB CSV file into memory at once (pd.read_csv("huge.csv") or f.read()) exhausts RAM and triggers an Out-Of-Memory (MemoryError / OOM kill) crash. Two complementary patterns keep memory footprint bounded: chunking and lazy evaluation.
| Aspect | Chunking (Bounded Batch Processing) | Lazy Evaluation (Deferred Execution) |
|---|---|---|
| Mechanism | Splits input into fixed-size batches (chunksize=50_000) processed sequentially | Builds a computation graph/iterator; evaluates only when output is materialized |
| Memory Profile | O(chunk_size) — peak RAM fixed regardless of total file rows | O(1) for streaming generators; optimized batch memory in lazy DataFrames |
| Python / DS Tools | pd.read_csv(..., chunksize=N), batched SQL fetchmany(N) | Python generators (yield), itertools.islice, polars.scan_csv(), dask.dataframe |
| Typical Operation | Running sum/count, filtering rows, writing cleaned batches to Parquet/SQLite | Predicate pushdown, projection pushdown, chained filter/map pipelines |
Chunking with pandas and sqlite3
When computing aggregate statistics or filtering a multi-gigabyte CSV:
import pandas as pd
total_revenue = 0.0
row_count = 0
for chunk in pd.read_csv("transactions_10gb.csv", chunksize=100_000, usecols=["amount", "status"]):
valid = chunk[chunk["status"] == "completed"]
total_revenue += float(valid["amount"].sum())
row_count += len(valid)
avg_order = total_revenue / row_count if row_count else 0.0Streaming & Lazy Evaluation in Pure Python
Combining generator functions with csv.DictReader streams arbitrarily large files in constant memory:
import csv
def stream_high_amounts(path: str, threshold: float):
with open(path, "r", newline="", encoding="utf-8") as fh:
for row in csv.DictReader(fh):
val = float(row["amount"])
if val >= threshold:
yield row["tx_id"], valIn modern data engineering, lazy DataFrame engines (Polars scan_csv().filter(...).select(...).collect() and Dask) go further by applying projection pushdown (reading only needed columns from disk) and predicate pushdown (skipping row groups that fail filter bounds).
Worked Examples
2.1
A CSV file has 850,000 rows, each occupying roughly 64 bytes in memory when parsed. Compare the peak data memory of loading the entire file at once versus reading it with pd.read_csv(..., chunksize=50_000). How many chunks are processed? Also write the chunked aggregation code to compute the mean of "price" where "valid" == 1.
Calculating Batch Count and Memory Usage:
- Total number of chunks =
850,000 / 50,000 = 17full chunks (with zero remainder). - Full-load data size =
850,000 * 64 bytes = 54,400,000 bytes ≈ 51.88 MiB(before pandas object/index overhead, which often multiplies raw size 3–5x). - Single-chunk data size =
50,000 * 64 bytes = 3,200,000 bytes ≈ 3.05 MiB, reducing peak memory by a factor of 17x.
Chunked Aggregation Code: Accumulate running numerator (total_price) and denominator (valid_count) across chunks rather than averaging chunk averages (which would weight unequal chunks incorrectly):
import pandas as pd
total_price = 0.0
valid_count = 0
for chunk in pd.read_csv("listings.csv", chunksize=50_000, usecols=["price", "valid"]):
filtered = chunk[chunk["valid"] == 1]
total_price += float(filtered["price"].sum())
valid_count += len(filtered)
overall_mean = total_price / valid_count if valid_count > 0 else 0.017 chunks of 50,000 rows are processed. Peak row buffer drops from 51.88 MiB to 3.05 MiB (a 17x reduction). Accumulate total_price and valid_count across chunks and compute total_price / valid_count once after the loop finishes.
2.2
Write a Python script that inserts three product records [("Mouse", 120), ("Keyboard", 80), ("Cable", 50)] safely into a SQLite table using parameterized queries, queries items priced above 60, and computes their average price across all three items.
Inserting Safely with Parameterized Queries (executemany): Use ? placeholders so values are never interpolated into SQL strings:
import sqlite3
products = [("Mouse", 120.0), ("Keyboard", 80.0), ("Cable", 50.0)]
with sqlite3.connect(":memory:") as conn:
cur = conn.cursor()
cur.execute("CREATE TABLE products (name TEXT, price REAL)")
cur.executemany("INSERT INTO products (name, price) VALUES (?, ?)", products)
above_60 = cur.execute(
"SELECT name, price FROM products WHERE price > ? ORDER BY price DESC", (60.0,)
).fetchall()
avg_all = cur.execute("SELECT AVG(price) FROM products").fetchone()[0]Tracing Query Output and Arithmetic:
above_60returns[("Mouse", 120.0), ("Keyboard", 80.0)]("Cable"at50.0is excluded byprice > 60).AVG(price)across all three rows =(120 + 80 + 50) / 3 = 250 / 3 ≈ 83.33.
above_60 returns [("Mouse", 120.0), ("Keyboard", 80.0)], and the average price across all three rows is 83.33 (250 / 3).
2.3
How should a student answer an exam question asking: "Compare REST and SOAP APIs, and explain when to choose an API versus BeautifulSoup web scraping for data collection"?
Part 1 — Structured Comparison of REST vs. SOAP: Present a crisp 4-row table covering format (JSON vs. XML Envelope), contract (OpenAPI vs. WSDL), transport/caching (HTTP verbs + cacheable GET vs. protocol-agnostic/non-cached), and parsing complexity in Python (requests + .json() vs. zeep/XML parsing).
Part 2 — API vs. Web Scraping Decision Criteria:
- Always prefer an official REST/GraphQL API when available: structured JSON schemas rarely break, authentication and rate limits are explicit, and bandwidth is minimal.
- Fall back to web scraping (
requests+BeautifulSoup) only when public HTML pages have no API or downloadable dump, while honoringrobots.txt, throttling requests, and checking whether data is static HTML or dynamically loaded via XHR.
Contrast REST (lightweight JSON over HTTP verbs, cacheable, requests) with SOAP (strict XML envelopes, WSDL contracts, zeep) in a comparison table, then justify preferring official APIs for schema stability and legal clarity over HTML scraping (BeautifulSoup) with robots.txt and rate-limiting safeguards.
Exercises
Practice Exercises — Data Sources and APIs
Compare CSV, JSON, and Excel (.xlsx) formats for storing and exchanging datasets in Python, mentioning which Python modules/functions read and write each format and one pitfall to avoid for CSV and JSON.
Format Comparison:
- CSV: Flat, row-oriented plain text; minimal storage overhead but lacks native types (everything parses as strings unless cast) and cannot represent nested structures. Read/written via
csv.DictReader/DictWriterorpandas.read_csv()/to_csv(). Pitfall: forgettingnewline=""on Windows writes extra blank lines, or unquoted commas shift columns. - JSON: Hierarchical key-value/array text format preserving types (
str,int,bool,null,list,dict); ideal for REST APIs and nested records, though verbose for large flat tables. Read/written viajson.load/loads/dump/dumps. Pitfall: loading a huge multi-GB JSON array exhausts RAM (prefer JSON Lines.jsonl). - Excel (
.xlsx): Binary zip-XML workbook supporting multiple sheets, formulas, and formatting; slower to parse and limited to ~1,048,576 rows per sheet. Read/written viapandas.read_excel()oropenpyxl.
CSV suits flat tabular text (csv, pandas.read_csv); JSON suits nested/API records (json.load/loads/dump/dumps); Excel suits multi-sheet formatted workbooks (openpyxl, pandas.read_excel). Avoid missing newline="" in Python CSV writers and avoid loading giant single-array JSON files into memory instead of line-delimited JSONL.
Why must you never use Python f-strings to build SQL queries in sqlite3? Show an unsafe query example, explain the risk, and rewrite it safely using parameterized placeholders.
Risk of String Formatting (SQL Injection): Interpolating user input directly into SQL—such as cur.execute(f"SELECT * FROM users WHERE name = '{user_input}'")—allows malicious input like ' OR '1'='1 or '; DROP TABLE users; -- to alter the query's syntax and execute unintended SQL commands.
Safe Parameterized Rewrite: Passing parameters as a tuple in the second argument lets the database driver bind values safely as data literals, never executable SQL:
cur.execute("SELECT * FROM users WHERE name = ?", (user_input,))String interpolation allows SQL injection, where crafted input alters the SQL statement itself. Always use parameterized queries (cur.execute("SELECT * FROM users WHERE name = ?", (user_input,))) so the database driver treats input strictly as data.
A paginated REST API returns at most limit = 100 items per request and reports total = 375 records.
- How many
GETrequests are needed to fetch all records, and how many items are in the final page? - Write a Python snippet using
requestswith atimeout,raise_for_status(), andparamsto fetch one page safely.
Calculating Page Count and Final Page Size:
- With
375total items and100items per page:- Pages 1, 2, 3 each return
100items (3 * 100 = 300items). - Page 4 returns the remaining
375 - 300 = 75items. - Total requests needed =
ceil(375 / 100) = 4requests (or5if the loop checks for an empty page rather thanlen(batch) < limit). Using thelen(batch) < limitstop condition takes exactly 4 requests, with 75 items on the final page.
- Pages 1, 2, 3 each return
Safe requests.get Call:
import requests
resp = requests.get(
"https://api.example.com/v1/records",
params={"page": 1, "limit": 100},
headers={"Accept": "application/json"},
timeout=(5, 10),
)
resp.raise_for_status()
data = resp.json()- 4
GETrequests are required (stopping whenlen(batch) < 100), with 75 items returned on the 4th page. - Pass
params={"page": 1, "limit": 100},timeout=(5, 10), callresp.raise_for_status(), and parse viaresp.json().
Given an HTML snippet containing <div class="card"><span class="name">Sensor A</span><span class="val">1200</span></div> (and two more cards with values 850 and 450), write a BeautifulSoup script to extract all values as integers and compute their average. Also state two ethical rules for web scraping.
Extracting and Averaging Values with BeautifulSoup:
from bs4 import BeautifulSoup
soup = BeautifulSoup(html_doc, "html.parser")
vals = [int(el.get_text(strip=True)) for el in soup.select("div.card span.val")]
avg_val = sum(vals) / len(vals)For [1200, 850, 450]: sum = 2500, count = 3, avg = 2500 / 3 ≈ 833.33.
Two Ethical Scraping Rules:
- Respect
robots.txtdirectives and Terms of Service, and prefer official APIs when provided. - Throttle request frequency (e.g.,
time.sleeprate limiting) and set an honestUser-Agentheader without bypassing login gates or CAPTCHAs.
soup.select("div.card span.val") extracts [1200, 850, 450], giving an average of 833.33 (2500 / 3). Two ethical rules: honor robots.txt/site policies and throttle requests with a clear User-Agent.
Explain chunking and lazy evaluation when processing datasets larger than available RAM. If a 2.4-million-row file is processed with chunksize=150_000, how many chunks are read, and why is generator-based streaming (yield) memory-efficient?
Chunking vs. Lazy Evaluation:
- Chunking reads bounded slices (batches) of rows into RAM, processes/aggregates each slice, and discards it before loading the next slice (
O(chunksize)memory). - Lazy evaluation defers computation until values are actually requested (e.g., Python generator functions using
yield, generator expressions, orpolars.scan_csv()), avoiding intermediate lists and enabling pushdown optimizations.
Chunk Count Calculation: Number of chunks = 2,400,000 / 150,000 = 16 chunks. A yield-based generator holds only one row/frame at a time in O(1) memory regardless of whether the source has 2.4 million or 2.4 billion rows.
Chunking bounds peak RAM to O(chunksize), while lazy evaluation defers execution and streams one item/plan step at a time. A 2.4-million-row file at chunksize=150_000 reads 16 chunks, and a yield generator uses O(1) memory by yielding one record per iteration without storing the full dataset.