24. How to Load SQL Query Results into a pandas DataFrame

24.4 SQLAlchemy and DB-API: Beginner Patterns

By Ravindra Bagale Last updated: 23 October 2026

Why. SQLite’s sqlite3 connection is enough for class. Real teams often pass a SQLAlchemy engine URL so MySQL and Postgres look similar. You do not need deep ORM knowledge — only “make engine → read_sql_query”.

What.

Stack	Typical connectable	Notes
SQLite	`sqlite3.connect(path)` or `sqlite:///file.db`	Best for learning
MySQL	SQLAlchemy URL + driver (e.g. `pymysql`)	Needs server + user
PostgreSQL	SQLAlchemy URL + driver (e.g. `psycopg2` / `psycopg`)	Needs server + user
DB-API cursor	Connection with `.cursor()`	pandas usually wants the connection / engine, not a raw cursor alone

How.

Pick a stack and stay consistent

Learning alone today → SQLite file (no install fight).
Office MySQL/Postgres → ask IT for a read-only user and a staging database name.
pip install sqlalchemy once in your venv; add the driver your DB needs.
Build an engine from a URL stored in an environment variable.
Pass engine (or engine.connect()) to pd.read_sql_query.
Keep SQL text in a triple-quoted string or a .sql file — not glued with random Python +.

Worked example A — SQLAlchemy + SQLite (same lab DB).

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("sqlite:///data/blinkit_lab.db")
df = pd.read_sql_query(
    "SELECT city, status, amount_inr FROM orders LIMIT 5",
    engine,
)
print(df)
engine.dispose()

Worked example B — MySQL and Postgres URL shapes (placeholders).

# Classroom shapes only — replace host/user/db; put passwords in env vars.
# pip install sqlalchemy pymysql        # MySQL example
# pip install sqlalchemy psycopg2-binary  # Postgres example

MYSQL_URL = "mysql+pymysql://readonly_user:SECRET@127.0.0.1:3306/blinkit_lab"
PG_URL = "postgresql+psycopg2://readonly_user:SECRET@127.0.0.1:5432/blinkit_lab"

# from sqlalchemy import create_engine
# engine = create_engine(os.environ["DS_DB_URL"])
# df = pd.read_sql_query("SELECT city, amount_inr FROM orders LIMIT 100", engine)

Worked example C — Compact comparison.

Task	SQLite	MySQL / Postgres
Install for class	Built into Python	Server + driver package
Connection object	`sqlite3.connect` or SQLAlchemy	Almost always SQLAlchemy URL
Limit rows while exploring	`LIMIT 100`	`LIMIT 100` (MySQL/Postgres)
Same pandas after load	Yes	Yes

Rani in Nagpur uses SQLite on the training laptop, then swaps only the URL when IT gives her a read-only Postgres user — the read_sql_query cell stays almost identical.

Ravindra Bagale's Tip

URL badla, pandas code jasta same. Driver install once; secret env madhe.