Getting Data In: Databases and APIs

Most real data lives in a database or behind an API rather than in a file someone emailed you. Both have a pattern worth learning once.

Databases

The connection layer is SQLAlchemy, which gives pandas a consistent interface regardless of which database is underneath.

import pandas as pd
from sqlalchemy import create_engine, text

engine = create_engine('postgresql+psycopg://user:pass@host:5432/dbname')
# sqlite:///local.db
# mysql+pymysql://user:pass@host/dbname

df = pd.read_sql("SELECT * FROM orders WHERE created_at >= '2026-01-01'", engine)

Never put credentials in your code

# Wrong. This ends up in git, and then in a public repository.
engine = create_engine('postgresql://admin:hunter2@prod-db/main')

# Right.
import os
engine = create_engine(os.environ['DATABASE_URL'])

Put the value in a .env file, add .env to .gitignore, and load it:

from dotenv import load_dotenv
load_dotenv()
engine = create_engine(os.environ['DATABASE_URL'])

This is not paranoia. Credential leaks through version control are among the most common security incidents there are, and the fix costs three lines.

Parameterise every query

Never build SQL with string formatting.

# Catastrophically wrong
city = user_input
df = pd.read_sql(f"SELECT * FROM customers WHERE city = '{city}'", engine)

If city contains '; DROP TABLE customers; -- you have just deleted the table. This is SQL injection, it is the oldest vulnerability in the book, and f-strings make it easy to write by accident.

# Right — the driver sends the value separately from the query
df = pd.read_sql(
    text("SELECT * FROM customers WHERE city = :city"),
    engine,
    params={'city': city},
)

Parameterised queries are also faster, because the database can reuse the query plan.

Let the database do the work

The most common beginner mistake is pulling everything and filtering in pandas.

# Wrong: transfers ten million rows to compute twelve numbers
df = pd.read_sql("SELECT * FROM orders", engine)
result = df.groupby('region').revenue.sum()

# Right: the database aggregates, twelve rows cross the network
result = pd.read_sql("""
    SELECT region, SUM(revenue) AS revenue
    FROM orders
    WHERE created_at >= :since
    GROUP BY region
""", engine, params={'since': '2026-01-01'})

Databases are extremely good at filtering, joining and aggregating. That is what they are for. Move as much of the work as you can across the wire, and bring back the smallest result that answers your question.

Put it this way: you would not empty the entire warehouse onto the pavement to find one box. Ask the warehouse for the box.

flowchart LR
    subgraph BAD["Pull then filter"]
        B1[("10M rows")] -->|"network"| B2["pandas filters<br/>and aggregates"] --> B3["12 rows"]
    end
    subgraph GOOD["Filter then pull"]
        G1[("10M rows")] --> G2["database filters<br/>and aggregates"] -->|"network"| G3["12 rows"]
    end
    style BAD fill:#f7e8e8,stroke:#c02020
    style GOOD fill:#eef1fc,stroke:#2141c8

Reading a large result in chunks

When you genuinely need many rows:

for chunk in pd.read_sql(query, engine, chunksize=50_000):
    process(chunk)

Writing back

df.to_sql('results', engine, if_exists='replace', index=False, chunksize=10_000)

if_exists takes 'fail', 'replace' or 'append'. Be careful: 'replace' drops the table and recreates it, which loses indexes, constraints and permissions. In anything shared, create the table properly in SQL and use 'append'.

SQLite for local work

No server, no credentials, one file. Excellent for intermediate results.

engine = create_engine('sqlite:///analysis.db')
df.to_sql('cleaned', engine, if_exists='replace', index=False)

APIs

An API call is an HTTP request that returns JSON. The library is httpx or requests; the examples below use httpx.

import httpx

r = httpx.get('https://api.example.com/orders',
              params={'since': '2026-01-01', 'limit': 100},
              headers={'Authorization': f'Bearer {token}'},
              timeout=30.0)
r.raise_for_status()          # turn a 4xx/5xx into an exception
data = r.json()

Two habits from that snippet. Always set a timeout, because the default in some libraries is to wait forever, and a hung request in a loop is indistinguishable from a crashed script. Always call raise_for_status(), or you will happily parse an error page as if it were data.

Pagination

APIs return results in pages. There are two common styles.

# Offset style
def fetch_all(url, token, page_size=100):
    rows, offset = [], 0
    while True:
        r = httpx.get(url, params={'limit': page_size, 'offset': offset},
                      headers={'Authorization': f'Bearer {token}'}, timeout=30)
        r.raise_for_status()
        batch = r.json()['results']
        if not batch:
            break
        rows.extend(batch)
        offset += page_size
    return rows

# Cursor style — follow the link the API gives you
def fetch_all_cursor(url, token):
    rows = []
    while url:
        r = httpx.get(url, headers={'Authorization': f'Bearer {token}'}, timeout=30)
        r.raise_for_status()
        payload = r.json()
        rows.extend(payload['results'])
        url = payload.get('next')      # None when finished
    return rows

Always have a stopping condition that does not depend on the API behaving. A while True loop against a misbehaving endpoint will run until you notice.

Rate limits and retries

Servers push back. A polite client backs off rather than hammering.

import time, httpx

def get_with_retry(url, *, headers=None, params=None, attempts=5):
    for attempt in range(attempts):
        r = httpx.get(url, headers=headers, params=params, timeout=30)

        if r.status_code == 429:                       # too many requests
            wait = int(r.headers.get('Retry-After', 2 ** attempt))
            time.sleep(wait)
            continue
        if 500 <= r.status_code < 600:                 # server problem
            time.sleep(2 ** attempt)                   # 1, 2, 4, 8 seconds
            continue

        r.raise_for_status()
        return r

    raise RuntimeError(f"giving up on {url} after {attempts} attempts")

Exponential backoff, doubling the wait each time, is the standard approach. Retry on 429 and 5xx. Do not retry on 400 or 404, because those mean your request is wrong and repeating it will not help.

Honour Retry-After when the server sends it. It is the server telling you exactly how long to wait.

Cache while you develop

During development you will run the same fetch many times. Caching responses to disk makes iteration fast and keeps you off the rate limit.

import json, hashlib
from pathlib import Path

CACHE = Path('.cache'); CACHE.mkdir(exist_ok=True)

def cached_get(url, **kwargs):
    key = hashlib.sha256((url + json.dumps(kwargs, sort_keys=True)).encode()).hexdigest()
    path = CACHE / f"{key}.json"
    if path.exists():
        return json.loads(path.read_text())
    data = get_with_retry(url, **kwargs).json()
    path.write_text(json.dumps(data))
    return data

Add .cache/ to .gitignore. Delete the directory when you want fresh data.

From JSON to a DataFrame

rows = fetch_all(url, token)
df = pd.json_normalize(rows)              # a list of dicts flattens directly

A list of dictionaries with consistent keys is exactly what DataFrame and json_normalize expect, which is why collecting API results into a list is the natural pattern.

Being a Good Client

Four things that cost nothing and keep you welcome.

• Identify yourself. Set a User-Agent header with your name or project and a contact address.
• Request only what you need. Filter server-side with query parameters.
• Sleep between calls even when you are under the limit. A short pause costs you little and reduces load meaningfully.
• Read the terms. Some APIs prohibit storing results, or require attribution.

What Carries Forward

Credentials come from the environment, never from the code.

Parameterise every query. String-formatted SQL is a security hole, not a style preference.

Push filtering and aggregation into the database. Bring back answers, not tables.

Set a timeout, call raise_for_status, and back off exponentially. Those three turn a fragile script into a reliable one.

Next: cleaning and reshaping, where the data is finally in memory and turns out to be a mess.