24.3 pd.read_sql आणि read_sql_query

उदाहरण A — Seed + read (copy-paste lab).

import sqlite3
import pandas as pd
from pathlib import Path

Path("data").mkdir(exist_ok=True)
con = sqlite3.connect("data/blinkit_lab.db")

con.execute("DROP TABLE IF EXISTS orders")
con.execute(
    """
    CREATE TABLE orders (
        order_id TEXT,
        city TEXT,
        status TEXT,
        amount_inr REAL,
        customer TEXT
    )
    """
)
rows = [
    ("BLK-261024-001", "Pune", "Delivered", 189.0, "Ruhi Bagale"),
    ("BLK-261024-002", "Pune", "Cancelled", 90.0, "Amir"),
    ("BLK-261024-003", "Nashik", "Delivered", 95.0, "Zoya"),
    ("AMN-261024-004", "Kolhapur", "Delivered", 210.0, "Ravina"),
    ("BLK-261024-005", "Nagpur", "Returned", 150.0, "Raja"),
    ("BLK-261024-006", "Solapur", "Delivered", 120.0, "Salman"),
    ("BLK-261024-007", "Sambhaji Nagar", "Delivered", 340.0, "Rani"),
    ("AMN-261024-008", "Pune", "Delivered", 72.0, "Shraddha Bagale"),
]
con.executemany("INSERT INTO orders VALUES (?,?,?,?,?)", rows)
con.commit()

df = pd.read_sql_query(
    "SELECT order_id, city, status, amount_inr, customer FROM orders",
    con,
)
print(df.shape)
print(df.head())
con.close()

What you should see. Eight rows, five columns. amount_inr is numeric. You can now df.loc[df["city"] == "Pune"] like any other chapter.

उदाहरण B — read_sql vs read_sql_query.

import sqlite3
import pandas as pd

con = sqlite3.connect("data/blinkit_lab.db")
sql = "SELECT city, COUNT(*) AS n FROM orders GROUP BY city ORDER BY n DESC"
a = pd.read_sql(sql, con)
b = pd.read_sql_query(sql, con)
print(a.equals(b))
con.close()

For everyday SELECT work, either name is fine. Prefer read_sql_query when you want the code to shout “I am not loading a whole table blindly”.

उदाहरण C — After the pull, same pandas you know.

import sqlite3
import pandas as pd

con = sqlite3.connect("data/blinkit_lab.db")
df = pd.read_sql_query(
    "SELECT city, status, amount_inr FROM orders WHERE status = 'Delivered'",
    con,
)
con.close()

g = (
    df.groupby("city", as_index=False)["amount_inr"]
    .agg(orders="count", gmv="sum")
    .sort_values("gmv", ascending=False)
)
print(g)