From a SQL Database into pandas
The bookshop's till does not really write CSV files: it stores every sale in a database, and the CSV is an export someone remembers to run. Going straight to the database means fresher data and no middle step. pandas can send a SQL query and receive the answer as a DataFrame.
The rule
Connect, send a query, get a DataFrame; let the database filter first.
Python ships with sqlite3, a small database stored in a single file. No server needed:
import sqlite3
import pandas as pd
conn = sqlite3.connect("shop.db")
delhi = pd.read_sql(
"SELECT branch, genre, copies FROM sales WHERE branch = 'Delhi'",
conn,
)
conn.close()read_sql runs the query and builds a DataFrame from the result, using the SQL column names as column names. Writing works too: sales.to_sql("sales", conn, if_exists="replace", index=False) creates a table from a DataFrame.
When a value comes from a variable, do not glue it into the string. Pass it as a parameter so the database treats it purely as data:
pd.read_sql("SELECT * FROM sales WHERE branch = ?", conn, params=("Delhi",))For other databases, such as PostgreSQL or MySQL, you connect through SQLAlchemy, which gives every database the same connection style:
from sqlalchemy import create_engine
engine = create_engine("postgresql://user:password@host/shop")
pd.read_sql("SELECT * FROM sales", engine)DuckDB is a third option built for analysis. It can run SQL directly on DataFrames and files already on your machine:
import duckdb
duckdb.sql("SELECT branch, SUM(copies) FROM sales GROUP BY branch").df()The shortcut
Ask "Can the database shrink it first?" Filtering and grouping in SQL means only the answer crosses to Python. Pulling a million rows to keep ten wastes time and memory.
The slow way
pd.read_sql("SELECT * FROM sales", conn)pulls every row, then pandas filters and sums.
The trap is building queries with f-strings, like f"... WHERE branch = '{city}'". A value containing a quote breaks the query, and hostile input can change what it does. That is SQL injection, and params prevents it.
Drills
Answer fast — trust the rule.
What does pd.read_sql(query, conn) return?
Which is the safe way to use a branch name stored in city?
You need one total per branch from a 50-million-row table. What is the better plan?
Where does a sqlite3 database live?
Remember
- Connect, send a query, get a DataFrame; let the database filter first.
- sqlite3 is built in; SQLAlchemy connects to server databases; DuckDB runs SQL on local data.
- Pass values with
params, never by pasting them into the string.