Collecting data: SQL, APIs & web sources

Real projects rarely begin with a neat CSV. You will pull data from a company database, a partner API, a government portal or occasionally a web page. Each source has its own habits, and a good analyst makes collection repeatable rather than a one-off copy-paste.

Source 1: databases with SQL

Company data usually lives in MySQL, PostgreSQL or a warehouse such as BigQuery. Let the database do the heavy lifting (filtering, joining, aggregating) and bring only what you need into Python:

import pandas as pd
from sqlalchemy import create_engine, text

engine = create_engine("mysql+pymysql://analyst:secret@db-host/retail")

query = text("""
    SELECT o.order_id, o.order_date, s.city, o.net_amount
    FROM orders o
    JOIN stores s ON s.store_id = o.store_id
    WHERE o.order_date >= :start
""")
orders = pd.read_sql(query, engine, params={"start": "2025-04-01"})

Use parameters (:start) instead of string formatting. It prevents SQL injection and handles quoting for you. Keep passwords in environment variables, never in the notebook.

Source 2: REST APIs

APIs return JSON over HTTP. Always set a timeout, check the status code and handle pagination:

import requests

rows, page = [], 1
while True:
    resp = requests.get(
        "https://api.example.com/v1/products",
        params={"page": page, "per_page": 100},
        headers={"Authorization": "Bearer <token>"},
        timeout=10,
    )
    resp.raise_for_status()
    batch = resp.json()["results"]
    if not batch:
        break
    rows.extend(batch)
    page += 1

products = pd.json_normalize(rows)   # flattens nested JSON into columns

Respect rate limits (add time.sleep between calls if the docs require it) and cache raw responses to disk so you do not hit the API again every time you rerun a cell.

Source 3: public and open data

India has good open-data sources worth bookmarking:

Source	What you get
data.gov.in	Datasets and APIs from central ministries
RBI DBIE	Interest rates, inflation, banking statistics
MoSPI	CPI, IIP, survey reports
Census / NFHS reports	Population, health and household indicators

Read the licence and the methodology notes: definitions often change between survey rounds.

Source 4: web pages

If a site offers no API, pandas.read_html can pull simple HTML tables, and libraries such as BeautifulSoup can parse pages. Before scraping, check the site's terms and robots.txt, go slowly, identify yourself in the User-Agent, and never collect personal data you have no right to process.

Make collection reproducible

• Save raw data untouched in a data/raw/ folder, with the date of extraction in the filename.
• Put collection code in a script or function, not scattered notebook cells.
• Log row counts and date ranges each time you pull, so silent drops are noticed.

Common mistakes

• SELECT * on a huge table and filtering later in pandas.
• No timeout in requests, so a hung server freezes your pipeline.
• Forgetting pagination and analysing only the first 100 records.
• Hard-coding API keys and then pushing the notebook to GitHub.