Data Access Libraries (SQLAlchemy & Requests)

Introduction

Before you can clean, analyze, or model anything, the data has to get into Python in the first place. So far in this course that has mostly meant reading a CSV file. Real projects usually pull data from somewhere live: a production database, or a REST API run by a third party. This lesson covers the two most common libraries for exactly that.

sqlalchemy: Talking to SQL Databases

SQLAlchemy is the standard way Python code talks to relational databases — PostgreSQL, MySQL, SQLite, and others — through one consistent interface. It has two layers you will see referenced constantly: Core, which lets you build and run SQL queries directly, and the ORM (Object-Relational Mapper), which lets you represent database tables as Python classes and rows as Python objects.

pip install sqlalchemy

The Core style is the fastest way to run a query and get data into pandas — it is what you will use most often for pure data science work, since pandas.read_sql() accepts a SQLAlchemy engine directly.

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("sqlite:///sales.db")

df = pd.read_sql("SELECT * FROM orders WHERE amount > 100", engine)
print(df.head())

The ORM style is more common in application code that reads and writes individual records — for example, a FastAPI backend around a model you trained. It maps a table to a Python class.

from sqlalchemy import create_engine, Column, Integer, String, Float
from sqlalchemy.orm import declarative_base, Session

engine = create_engine("sqlite:///sales.db")
Base = declarative_base()

class Order(Base):
    __tablename__ = "orders"
    id = Column(Integer, primary_key=True)
    customer = Column(String)
    amount = Column(Float)

with Session(engine) as session:
    big_orders = session.query(Order).filter(Order.amount > 100).all()
    for order in big_orders:
        print(order.customer, order.amount)

requests: Talking to REST APIs

A huge amount of real-world data lives behind REST APIs rather than in a database you can query directly — weather data, stock prices, public government datasets, internal company services. requests is the standard Python library for making HTTP calls: GET to fetch data, POST to send it, along with headers, authentication, and query parameters.

pip install requests

import requests

response = requests.get(
    "https://api.example.com/v1/exchange-rates",
    params={"base": "USD"},
    headers={"Authorization": "Bearer YOUR_API_KEY"},
)

response.raise_for_status()  # raises an exception on 4xx/5xx errors
data = response.json()

print(data["rates"]["EUR"])

It is common to combine requests with pandas to turn an API response straight into a DataFrame for analysis.

import pandas as pd
import requests

response = requests.get("https://api.example.com/v1/orders")
response.raise_for_status()

orders = response.json()["results"]
df = pd.DataFrame(orders)
print(df.head())

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

SQLAlchemy and requests cover the two most common ways real data enters a Python project: pulled from a database, or fetched from an API. Next, we look at a third source — scraping data directly out of web pages, using BeautifulSoup and Scrapy.