Data Science · मराठी आवृत्ती

24.4 SQLAlchemy आणि DB-API — beginner patterns

By Ravindra Bagale

या course चे सर्व chapters (py + ch)

हे का शिकायचं?

काही databases ना SQLAlchemy engine सोयीस्कर. DB-API connection सुद्धा चालतो.

हे काय आहे?

create_engine → connect → read_sql. Beginner स्तर: फक्त select patterns.

कसं करायचं?

Step 1. Engine/connection तयार करा.

Step 2. छोटी LIMIT query आधी.

Step 3. मग पूर्ण filtered query.

Step 4. Connection context manager ने बंद.

उदाहरणं: copy करा आणि चालवा

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

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

उदाहरण 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()

उदाहरण 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)

उदाहरण 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.