SQL vs Python for Python Developers A Practical Comparison

SELECT city,
       COUNT(*)    AS orders,
       SUM(amount) AS revenue
FROM orders
WHERE order_date >= DATE '2026-10-01'
GROUP BY city
ORDER BY revenue DESC
LIMIT 10;

import pandas as pd

df = pd.read_csv("orders.csv", parse_dates=["order_date"])
top = (df[df["order_date"] >= "2026-10-01"]
         .groupby("city")
         .agg(orders=("order_id", "count"), revenue=("amount", "sum"))
         .sort_values("revenue", ascending=False)
         .head(10))
print(top)

from sqlalchemy import create_engine, text

engine = create_engine("postgresql+psycopg2://app:app@localhost/shop")

query = text("""
    SELECT o.order_id, o.amount, c.name AS customer_name
    FROM orders o
    JOIN customers c ON c.customer_id = o.customer_id
    WHERE o.order_date >= :since
""")

with engine.connect() as conn:
    rows = conn.execute(query, {"since": "2026-10-01"}).mappings().all()

for r in rows[:5]:
    print(r["order_id"], r["customer_name"], r["amount"])

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("postgresql+psycopg2://app:app@localhost/shop")

total = 0
for chunk in pd.read_sql("SELECT amount FROM orders", engine, chunksize=50_000):
    total += chunk["amount"].sum()
print("Total revenue:", total)

import sqlite3

conn = sqlite3.connect("shop.db")
city = input("City: ")

# WRONG: f"SELECT * FROM orders WHERE city = '{city}'"  (SQL injection risk)
# RIGHT: let the driver bind the value
rows = conn.execute(
    "SELECT order_id, amount FROM orders WHERE city = ?", (city,)
).fetchall()
print(rows[:5])

-- Rank each customer's orders by amount and add a running total
SELECT customer_id,
       order_id,
       amount,
       RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS amount_rank,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM orders;

Step                              | Put it in
----------------------------------|-----------
Filter rows from a big table      | SQL (WHERE, indexed)
Join tables in the same database  | SQL (JOIN)
Totals, counts, averages          | SQL (GROUP BY)
Complex business rules            | Python
Call an API / send an email       | Python
Machine learning                  | Python
Data from files or APIs           | Python (pandas)

[ ] No queries inside loops (N+1)
[ ] Only needed columns selected (no SELECT * in prod)
[ ] Filters run in SQL when data lives in the DB
[ ] All user input passed as parameters
[ ] Large reads are streamed or chunked
[ ] Complex rules live in tested Python functions

1. Rewrite a pandas groupby on a DB table as SQL
2. Find an N+1 loop in an ORM view and fix it with a join
3. Convert an f-string query to a parameterised one
4. Add a window function for "rank within customer"
5. Time both versions and note the difference