API Masterclass in Python: Authentication, Pagination, and JSON to DataFrame

APIs feed every analyst pipeline you will build after SQL. This notebook turns you from "I copied a fetch" into someone who handles auth, pagination, errors, and nested JSON without Stack Overflow at 2 a.m.

What will you be able to do after this notebook?

Issue GET/POST, pass query params, authenticate with API key and Basic Auth, handle 401/429/500 correctly, paginate to exhaustion, and normalise nested JSON into a clean DataFrame. Link the output to database connectivity when you persist results, and to Pandas fundamentals for DataFrame mechanics.

Ingredients: https://httpbin.org as a test bed (echo service) plus a real paginated API pattern you can point at any provider.

How do you make a basic request and add query params?

Setup:

import requests
import pandas as pd
import json
from requests.auth import HTTPBasicAuth
from datetime import datetime
print("Libraries loaded successfully!")

url = 'https://httpbin.org/get'
response = requests.get(url)
if response.status_code == 200:
    print("Success!")
    print(response.json())
else:
    print(f"Failed with status: {response.status_code}")
 
# Query params as a dict — requests encodes them
params = {'q': 'data analyst', 'page': 2, 'limit': 10}
r = requests.get('https://httpbin.org/get', params=params)
print(r.url)  # ...?q=data+analyst&page=2&limit=10
print(r.json()['args'])

Rendered output: JSON with args.q == 'data analyst' confirming encoding; the url field echoes the full query string for debugging.

How do you authenticate without leaking secrets?

Never paste keys into source. Use environment or .env.

A. API Key (header)

import os
headers = {'X-API-Key': os.getenv('MY_API_KEY', 'demo-key-for-notebook')}
resp = requests.get('https://httpbin.org/headers', headers=headers)
print(resp.json()['headers']['X-Api-Key'] if resp.status_code==200 else resp.text)

B. Basic Auth

from requests.auth import HTTPBasicAuth
# httpbin has a test endpoint: /basic-auth/user/passwd
resp = requests.get('https://httpbin.org/basic-auth/user/passwd', auth=HTTPBasicAuth('user','passwd'))
print(resp.status_code, resp.json() if resp.status_code==200 else resp.text[:120])

Rendered output: 200 with {"authenticated": true} when credentials match; 401 otherwise — the branch your pipeline must handle, not crash on.

How do you handle errors and paginate?

Status-first then parse:

def fetch_with_retry(url, params=None, headers=None, max_retries=2):
    for attempt in range(max_retries+1):
        resp = requests.get(url, params=params, headers=headers, timeout=10)
        if resp.status_code == 200:
            return resp.json()
        elif resp.status_code == 429:
            wait = int(resp.headers.get('Retry-After', 2))
            print(f"Rate limited — wait {wait}s (attempt {attempt+1})")
            import time; time.sleep(wait)
        elif resp.status_code in (401, 403):
            raise PermissionError(f"Auth failed: {resp.status_code} {resp.text[:200]}")
        elif resp.status_code >= 500:
            print(f"Server error {resp.status_code}, retrying...")
        else:
            resp.raise_for_status()
    raise RuntimeError("Max retries exceeded")
 
print(fetch_with_retry('https://httpbin.org/get', params={'page':1}))

Pagination loop that exhausts a list endpoint:

results=[]
page=1
while True:
    payload = fetch_with_retry('https://httpbin.org/json', params={'page': page})
    results.append(payload)
    page+=1
    if page>2:
        break
print(f"Fetched {len(results)} pages")

How do you turn nested JSON into a DataFrame?

Most analyst time is spent here.

sample = {
    'results': [
        {'user': {'id': 1, 'name': 'Alice'}, 'orders': [{'id': 10, 'amount': 200}]},
        {'user': {'id': 2, 'name': 'Bob'}, 'orders': [{'id': 11, 'amount': 90},{'id':12,'amount':30}]},
    ]
}
# Flatten top level
df_users = pd.json_normalize(sample['results'])
print(df_users.head())
 
# Flatten nested list with record_path
df_orders = pd.json_normalize(sample['results'], record_path='orders', meta=[['user','id'],['user','name']])
print(df_orders.head())

Rendered output: df_users shows columns user.id, user.name, orders; df_orders expands to 3 rows (one per order) with parent user columns joined — the pattern you copy for any provider.

What do you persist and practise?

Persist the cleaned DataFrame to SQLite/Postgres via database connectivity, then schedule the fetch as a daily job. Practise the full pull->clean->store loop on Topfolio Practice Python tasks.

Download the Notebook and Practise

This article is a walkthrough of a runnable Jupyter notebook. Download the original .ipynb and run it locally or on Colab — every code block above appears in order.

Continue your track: Data Analyst Roadmap · Python and Pandas Guide · SQL NULL Handbook · SQL JOIN Fan-Out · Topfolio Practice · Data Analyst vs Engineer

Dataset generators where applicable are in courses/workbooks/generators/ — see citations atop for the exact *.py source for this notebook.

Frequently Asked Questions

What is the difference between API key and Basic Auth?

API key is a token in a header (X-API-Key or Authorization: Bearer <key>); Basic Auth is username:password base64-encoded in Authorization: Basic. Both belong in headers, never in URLs.

How do you handle pagination in REST APIs?

Loop while 'next' URL or page token is returned: requests.get(url, params={'page': p}), extend results, increment p. Stop when the payload's next is null or items < page_size.

How do you flatten nested JSON into a DataFrame?

Use pd.json_normalize(payload) for nested dicts, or pd.DataFrame(payload['results']) for lists. Inspect payload keys with list(payload.keys()) first.

Why check status_code before parsing JSON?

A 401/429/500 still has a body, but response.json() will mislead. Guard with if response.status_code==200 then parse, else log response.text.