SQL + Python (pandas)

Chapter 12 12 code blocks 2764 words

This chapter is the hinge of the whole guide. Everything before it produced a result inside the database; everything after it needs that result inside Python, as a DataFrame that Seaborn can plot.

Where SQL ends and pandas begins

Students always ask "if pandas can do groupby, why did I learn GROUP BY?" The answer is about where the work happens.

# ❌ pulls everything, then throws most of it away
df = pd.read_sql("SELECT * FROM enrollments", conn)
result = df[df.status == 'completed'].groupby('course_id')['grade'].mean()

# ✅ the database does the work; only 5 rows cross the wire
result = pd.read_sql("""
    SELECT course_id, AVG(grade) AS avg_grade
    FROM enrollments WHERE status = 'completed'
    GROUP BY course_id
""", conn)

sqlite3 ships with Python — nothing to install.

import sqlite3

conn = sqlite3.connect("school.db")     # a file; ":memory:" for a temporary DB
cur  = conn.cursor()

# run a query and fetch rows
cur.execute("SELECT first_name, city FROM students WHERE city = 'Tashkent'")
for row in cur.fetchall():
    print(row)                          # ('Aziza', 'Tashkent') — a plain tuple

# fetchone() for a single row, fetchmany(n) for a batch

# writes must be committed
cur.execute("UPDATE students SET city = 'Tashkent' WHERE student_id = 7")
conn.commit()

conn.close()

:::tip 💡 Two quality-of-life settings ``

python
# rows behave like dicts instead of tuples
conn.row_factory = sqlite3.Row
row = conn.execute("SELECT * FROM students LIMIT 1").fetchone()
print(row["first_name"])        # 'Aziza'

# foreign keys are OFF by default (Chapter 10)
conn.execute("PRAGMA foreign_keys = ON")

# a context manager commits on success and rolls back on exception
with conn:
    conn.execute("DELETE FROM enrollments WHERE enrollment_id = 20")

`` :::

pandas read_sql

One line turns any query into a DataFrame.

import pandas as pd
import sqlite3

conn = sqlite3.connect("school.db")

df = pd.read_sql("""
    SELECT
        s.first_name || ' ' || s.last_name AS student,
        c.title                            AS course,
        e.grade,
        e.status,
        e.enrolled_at
    FROM enrollments e
    JOIN students s ON e.student_id = s.student_id
    JOIN courses  c ON e.course_id  = c.course_id
""", conn)

print(df.shape)      # (20, 5)
print(df.dtypes)
df.head()

:::gotcha ⚠️ Three things pandas changes on the way in - SQL NULL becomes NaN — and because NaN is a float, an integer column containing NULLs arrives as float64. That is why grade shows as 92.0, not 92. - Dates arrive as strings. SQLite has no date type, so enrolled_at is an object column. Convert it or nothing time-based will work: df["enrolled_at"] = pd.to_datetime(df["enrolled_at"]) Or let pandas do it: pd.read_sql(..., parse_dates=["enrolled_at"]). - Column names come from your aliases. A computed column without an alias gets an ugly expression as its name — another reason to always write AS. :::

Useful options:

pd.read_sql(query, conn, index_col="student_id")          # set the index
pd.read_sql(query, conn, parse_dates=["enrolled_at"])      # parse dates on the way in
pd.read_sql(query, conn, params=(60,))                     # parameters — see below
pd.read_sql_table("students", engine)                      # whole table (SQLAlchemy only)

Parameters and SQL injection The most important safety topic in this chapter, and one every analyst should understand even if they never build a web app.

# ❌ NEVER build SQL with f-strings or +
city = "Tashkent"
df = pd.read_sql(f"SELECT * FROM students WHERE city = '{city}'", conn)

# ✅ pass values as parameters
df = pd.read_sql("SELECT * FROM students WHERE city = ?", conn, params=(city,))

# sqlite3 directly
cur.execute("SELECT * FROM students WHERE city = ? AND gender = ?", ("Tashkent", "F"))

# named parameters read better with several values
cur.execute("SELECT * FROM enrollments WHERE grade > :min AND status = :st",
            {"min": 80, "st": "completed"})

:::gotcha ⚠️ Why the f-string version is dangerous Suppose the value comes from a form and someone types:

city = "Tashkent'; DROP TABLE students; --" The f-string builds this, and the database obediently runs both statements:

SELECT * FROM students WHERE city = 'Tashkent'; DROP TABLE students; --' That is SQL injection. With ?, the value is sent separately from the query text and is treated as data, never as SQL — the attack becomes a harmless search for a city with a strange name.

It is not only about attackers: a perfectly innocent name like O'Brien breaks an f-string query outright, because the apostrophe closes the string. Parameters fix that too. :::

Writing DataFrames back (to_sql)

summary = pd.read_sql("""
    SELECT course_id, COUNT(*) AS n, AVG(grade) AS avg_grade
    FROM enrollments GROUP BY course_id
""", conn)

summary.to_sql("course_summary", conn, if_exists="replace", index=False)

if_existsBehaviour

'fail' (default)Raise an error if the table exists 'replace'Drop the table and recreate it 'append'Add rows to the existing table

:::gotcha ⚠️ if_exists='replace' drops the table It does not empty it — it DROPs it and builds a new one from the DataFrame's dtypes. Every constraint, index and foreign key is destroyed. Never point it at a real table; use it only for scratch/summary tables you own.

Also pass index=False unless you want the DataFrame index written as a column — a stray "index" column in the database is a classic annoyance. :::

SQLAlchemy and other databases

sqlite3 only speaks SQLite. SQLAlchemy gives pandas one interface to every database, so the same code works against Postgres in production and SQLite in class.

from sqlalchemy import create_engine
import pandas as pd

# the connection string is the only thing that changes
engine = create_engine("sqlite:///school.db")
# engine = create_engine("postgresql+psycopg2://user:pass@localhost:5432/school")
# engine = create_engine("mysql+pymysql://user:pass@localhost:3306/school")

df = pd.read_sql("SELECT * FROM students", engine)
df.to_sql("students_copy", engine, if_exists="replace", index=False)

:::gotcha ⚠️ Never hard-code a password A connection string with a real password committed to git is a genuine security incident — and it happens constantly. Read it from the environment instead:

import os
engine = create_engine(
    f"postgresql+psycopg2://{os.environ['DB_USER']}:{os.environ['DB_PASS']}"
    f"@{os.environ['DB_HOST']}/school"
)

Keep the values in a .env file that is listed in .gitignore. :::

Large results and chunking

If a result genuinely will not fit in memory, read it in pieces:

total = 0
for chunk in pd.read_sql("SELECT * FROM huge_table", conn, chunksize=100_000):
    total += chunk["amount"].sum()      # process 100k rows at a time
print(total)

:::tip 💡 Before you reach for chunking, ask one question "Do I actually need every row?" Ninety-nine times out of a hundred the answer is no, and a GROUP BY in SQL returns 500 rows instead of 50 million. Chunking is for the genuine exceptions — exporting, row-by-row transformation, feeding a model in batches. :::

SQL ↔ pandas equivalents

A translation table is the fastest way to make students fluent in both.

From query to plot

The complete handover, end to end. This exact pattern is what Part II of the guide builds on:

import sqlite3
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

conn = sqlite3.connect("school.db")

# 1. SQL does the joining and reduction — only 14 rows come back
df = pd.read_sql("""
    SELECT
        c.title    AS course,
        c.category AS category,
        e.grade    AS grade,
        e.status   AS status
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
    WHERE e.grade IS NOT NULL
""", conn)
conn.close()

# 2. Seaborn does the drawing
sns.set_theme(style="whitegrid")
plt.figure(figsize=(9, 5))
sns.boxplot(data=df, x="course", y="grade", hue="category")
plt.xticks(rotation=20)
plt.title("Grade distribution by course")
plt.tight_layout()
plt.savefig("grades_by_course.png", dpi=150)

:::info 📌 The shape Seaborn wants Seaborn expects long (tidy) format: one row per observation, one column per variable. That is exactly what a normalized database plus a JOIN naturally produces — which is why Chapter 9's UNION ALL unpivot trick exists, for the rare case where your data is already wide.

Every x=, y=, hue=, col= argument in Part II is simply the name of a column in this DataFrame. Getting the SQL right is most of the work; the plotting is one line. :::

Practice set

# 1. Connect and build the database from the setup script
import sqlite3, pandas as pd
conn = sqlite3.connect("school.db")
with open("school_setup.sql", encoding="utf-8") as f:
    conn.executescript(f.read())

# 2. Read a whole table
students = pd.read_sql("SELECT * FROM students", conn)

# 3. Parameterised query — never an f-string
min_grade = 80
top = pd.read_sql("SELECT * FROM enrollments WHERE grade >= ?", conn, params=(min_grade,))

# 4. Aggregate in SQL, not in pandas
by_course = pd.read_sql("""
    SELECT c.title, COUNT(*) AS n, ROUND(AVG(e.grade),1) AS avg_grade
    FROM enrollments e JOIN courses c USING (course_id)
    WHERE e.grade IS NOT NULL
    GROUP BY c.course_id, c.title ORDER BY avg_grade DESC
""", conn)

# 5. Parse dates on the way in
enr = pd.read_sql("SELECT * FROM enrollments", conn, parse_dates=["enrolled_at"])
print(enr.enrolled_at.dt.month.value_counts())

# 6. Write a summary table back
by_course.to_sql("course_summary", conn, if_exists="replace", index=False)

# 7. Insert several rows safely
conn.executemany(
    "INSERT OR IGNORE INTO courses (course_id, title, category, credits, price) VALUES (?,?,?,?,?)",
    [(10, 'Power BI', 'Data', 3, 1600000), (11, 'Excel', 'Data', 2, 800000)])
conn.commit()

# 8. Same query through SQLAlchemy
from sqlalchemy import create_engine
engine = create_engine("sqlite:///school.db")
pd.read_sql("SELECT COUNT(*) AS n FROM students", engine)

conn.close()

Interview questions

Q: Should you filter in SQL or in pandas?

In SQL. The database has indexes and does not have to move the data first; pandas must load everything into RAM before it can filter. Reduce in SQL, analyse in pandas.

Q: What is SQL injection and how do you prevent it?

Building a query by string concatenation lets user input become executable SQL. Prevent it with parameterised queries (? or named parameters), which send the value separately from the query text.

Q: Why is an integer column sometimes a float in a DataFrame?

Because SQL NULL becomes NaN, and NaN is a float — so the whole column is promoted to float64. Use a nullable integer dtype (Int64) or fill the NULLs if you need integers.

Q: What does to_sql(..., if_exists='replace') do?

Drops the existing table and recreates it from the DataFrame — losing all constraints and indexes. Not DELETE, not TRUNCATE. Use it only on scratch tables.

Q: sqlite3 vs SQLAlchemy?

sqlite3 is built in but speaks only SQLite. SQLAlchemy provides one interface across PostgreSQL, MySQL, SQL Server and others, plus connection pooling — so the same analysis code runs anywhere.

Q: Your INSERT ran without error but the data is not there. Why?

Missing conn.commit(). The transaction was rolled back when the connection closed. Use with conn: to commit automatically.

Q: How do you handle a result too large for memory?

First check whether you need every row — usually an aggregate in SQL is the real answer. If you genuinely do, use chunksize and process iteratively.

Summary

• Reduce in SQL, analyse in pandas. Filtering, joining and aggregating belong in the database; plotting and modelling belong in Python.
• sqlite3 ships with Python. Remember conn.commit() — or use with conn:, which commits and rolls back for you.
• pd.read_sql(query, conn) turns any query into a DataFrame. Use parse_dates=, and always alias your computed columns.
• SQL NULL → NaN, which makes integer columns float. SQLite dates arrive as strings.
• Never build SQL with f-strings. Use ? parameters — it prevents SQL injection and handles apostrophes in names. executemany for bulk inserts.
• to_sql(..., if_exists='replace') drops the table; pass index=False.
• SQLAlchemy gives one interface to every database — only the connection string changes. Read credentials from the environment, never hard-code them.
• Use chunksize only when you genuinely need every row.
• Seaborn wants long/tidy format, which a JOIN produces naturally. Every x=/y=/hue= in Part II is a column name from your query.

Next: Chapter 13 closes the SQL half — every gotcha in one catalogue, the classic interview problems worked through, and a style guide.