💳
Real-world scenario
Thomas (Kochi, SouthBank): 18 months of transactions in PostgreSQL

Thomas works as a financial data analyst at SouthBank India in Kochi. Every Monday morning, he manually copies customer transaction data from the PostgreSQL warehouse into an Excel file. He writes SQL queries in the database GUI, pastes the results into Excel, then transforms them in pandas. It takes 40 minutes and is error-prone.

Last week, he wrote a 3-line Python script that queries PostgreSQL directly into a DataFrame. No copying. No pasting. No waiting. Every query runs in seconds, fresh data every time.

18m
Transaction records
₹2.3cr
Monthly volume
3 sec
Load time

The transformation: from SQL query to analysable DataFrame

Below, the same query appears twice: left as SQL in the database, right as a styled DataFrame after pd.read_sql() loads it. Notice how column names are preserved and amounts are properly formatted.

SELECT customer_id, SUM(amount) AS total_spend FROM transactions WHERE txn_date >= '2026-01-01' GROUP BY customer_id ORDER BY total_spend DESC LIMIT 10;
→
pd.read_sql()
customer_id total_spend
1027 ₹8,45,200
1003 ₹7,92,500
1041 ₹7,65,800
1015 ₹7,23,400
1009 ₹6,98,900
1022 ₹6,54,300
1035 ₹6,21,100
1008 ₹5,89,700
1019 ₹5,34,200
1012 ₹4,97,600
The punchline: The database query becomes a Python DataFrame with labelled columns and proper data types. Now you can filter, group, pivot, and plot without retyping the SQL.

Three ways to talk to a database

You can connect to SQL databases from Python in different ways. Each has trade-offs. Here are the most common:

Method Best for Trade-off
psycopg2 (PostgreSQL) or mysql.connector Fine-grained control, low-level database ops Verbose; you manage connections, cursors, result iteration yourself
SQLAlchemy Production code, ORM-style queries, database-agnostic applications Steep learning curve; overkill for one-off scripts
pd.read_sql() + SQLAlchemy engine Data analysis workflows (THIS COURSE) Best of both worlds: simple, Pythonic, works with all major databases

For this lesson and most analysis work, we use pd.read_sql() + SQLAlchemy. Write your SQL query, pass an engine, get a DataFrame back in one line.

The 3-line setup: connect, query, analyse

Here's the minimal code to load SouthBank transaction data from a PostgreSQL database into a DataFrame:

📁
Sample SQLite database included
Download southbank.db to run queries locally (no server needed).
connect_query.py 3-line starter: connect → query → DataFrame
customer_id total_spend
1027 8452.00
1003 7925.00
1041 7658.00
1015 7234.00
1009 6989.00
How this works: create_engine() builds a connection string for your database. pd.read_sql() executes your query and returns a DataFrame with results. The DataFrame is ready for analysis: groupby, plot, describe, whatever you need.

Four patterns: filter, aggregate, join, window function

Most SQL-to-pandas workflows follow one of these patterns. Learn these, and you can handle 80% of real-world queries.

Pattern A: Filter + Aggregate (top spenders per month)

Find the top 10 customers by total spend across all of 2026. Simple filtering and aggregation—the bread and butter of analysis.

top_spenders.py Filter dates, aggregate by customer
name segment txn_count total_spend
Priya premium 18 ₹8,45,200
Vikram standard 16 ₹7,92,500
Anjali premium 15 ₹7,65,800
Rohan basic 14 ₹7,23,400
Deepika standard 17 ₹6,98,900
The insight: You see customer segments alongside spend. Premium customers spend more on average, but standard segment has higher transaction frequency. This is actionable intelligence—you can now target retention campaigns.

Pattern B: Time-series with GROUP BY DATE (daily volume over 90 days)

Detect trends, seasonality, anomalies. Group by date, count or sum, plot. This unlocks time-based analysis that Excel pivot tables can't match.

daily_volume.py Time-series aggregation: volume per day
Daily transaction volume over 90 days (SouthBank India)
Line chart of daily transaction volume from Jan 1 to Mar 31 showing steady upward trend, ranging from around 500k to 1.3 million rupees per day
The pattern: Volume trends steadily upward across the quarter. No major dips—system is stable. You can now detect if next quarter's volume drops, signal an alert, or investigate anomalies.

Pattern C: JOIN (customer names + transaction details)

Combine customers and transactions tables to get names, cities, segments, and transaction amounts in one result. This mirrors real-world schemas.

joins.py JOIN customers to transactions
customer_id name city segment txn_date amount category
1027 Priya Mumbai premium 2026-03-28 ₹75,000 utilities
1041 Anjali Bangalore premium 2026-03-27 ₹72,500 fuel
1027 Priya Mumbai premium 2026-03-26 ₹68,900 groceries
1015 Aditya Hyderabad premium 2026-03-25 ₹65,200 entertainment
1022 Meera Chennai premium 2026-03-24 ₹61,800 healthcare
The join unlocks: You now see which cities premium customers are in, what they buy, and how much they spend. This supports geo-based marketing, category strategies, and risk assessment.

Pattern D: Window functions (rank top 3 per branch)

Find top spenders per branch using RANK() OVER PARTITION BY. Window functions are SQL's superpower for comparative rankings.

window_func.py Rank top 3 customers per branch
name branch branch_spend rank_in_branch
Vikram Bangalore ₹2,34,500 1
Pooja Bangalore ₹2,12,300 2
Sanjay Bangalore ₹1,98,700 3
Priya Chennai ₹2,45,800 1
Rashmi Chennai ₹2,18,900 2
Deepika Chennai ₹2,05,400 3
Window function power: Each branch gets its own ranking (1, 2, 3). You can now identify VIP customers per location, tailor retention campaigns by branch, and benchmark branch performance. Window functions avoid messy subqueries and groupby chains.

Security: parameters, not string concatenation

Never build SQL queries by pasting user input into strings. SQL injection is a real attack vector. Always use parameterized queries.

❌ VULNERABLE (DON'T DO THIS)
user_id = input("Enter customer ID: ")
query = f"SELECT * FROM customers WHERE customer_id = {user_id}"
# If user enters: 1 OR 1=1
# Query becomes: SELECT * FROM customers WHERE customer_id = 1 OR 1=1
# Result: attacker sees ALL customer data!
✓ SAFE (USE THIS)
user_id = input("Enter customer ID: ")
query = "SELECT * FROM customers WHERE customer_id = %(id)s"
df = pd.read_sql(query, engine, params={'id': user_id})
# The database driver escapes the input
# Query is safe, even if user enters malicious input
The rule: Never use f-strings or .format() in SQL queries. Use %(name)s placeholders and pass params as a dictionary. The database driver handles escaping. Even if a user enters SQL commands, they're treated as literal values, not code.

When to filter in SQL vs pandas

You can filter and aggregate in SQL, or load raw data and work in pandas. Which is faster? Use this guide:

Operation Do in SQL Do in pandas Why
Filter rows WHERE date > 2026-01-01 df[df['date'] > '2026-01-01'] SQL filters at the source; pandas loads everything then filters (slower on huge data)
Aggregate (sum, count, avg) GROUP BY, SUM() df.groupby().sum() SQL aggregates in the database engine (optimized); pandas does it in memory
JOIN tables JOIN ... ON ... df.merge() SQL joins are database-optimized; pandas merge is slower on large tables
Reshape / pivot CASE WHEN (verbose) df.pivot(), df.melt() Pandas pivot syntax is cleaner; SQL PIVOT is database-specific
Plot / visualize Not applicable matplotlib, seaborn Python plotting is the entire point of pandas
Machine learning features Not applicable df['new_col'] = ... Python is where ML libraries live (sklearn, xgboost)

Rule of thumb: Filter and aggregate in SQL. Shape, plot, and model in pandas. This splits work where each tool excels.

SQL + pandas cheatsheet

Task Code
Connect to SQLite engine = create_engine('sqlite:///southbank.db')
Connect to PostgreSQL engine = create_engine('postgresql://user:pwd@localhost/dbname')
Connect to MySQL engine = create_engine('mysql+pymysql://user:pwd@localhost/dbname')
Load entire table df = pd.read_sql_table('transactions', engine)
Run SQL query df = pd.read_sql('SELECT * FROM transactions', engine)
Query with parameters pd.read_sql(query, engine, params={'id': user_id})
Filter by date range WHERE txn_date BETWEEN '2026-01-01' AND '2026-03-31'
Aggregate by group GROUP BY customer_id, branch
LIMIT results LIMIT 100
Inner JOIN JOIN customers c ON t.customer_id = c.customer_id
Rank within group RANK() OVER (PARTITION BY branch ORDER BY amount DESC)
Convert result to CSV df.to_csv('output.csv', index=False)
🎯 Mini challenge: find your power users

Task: Using the southbank.db sample database, write a query that finds the 5 customers with the highest total spend in the last 90 days (Jan 1 – Mar 31, 2026). Your result should show: customer_id, customer name, total spend, and number of transactions.

Bonus: Which customer has the highest average transaction amount?

You'll need:

  • JOIN transactions to customers
  • WHERE to filter the date range
  • GROUP BY customer
  • SUM(amount) and COUNT(*) for aggregates
  • AVG(amount) for the bonus
  • ORDER BY total_spend DESC
  • LIMIT 5

5 common pitfalls (and how to avoid them)

1. Forgetting to close the connection
If you create a connection directly (not via SQLAlchemy engine), close it: conn.close(). With create_engine(), pandas handles cleanup. But if you open raw connections, they stay open and drain resources.
2. Loading the entire table without LIMIT
On a 1B-row table, SELECT * will crash your laptop. Always start with LIMIT 1000 to preview. Then add WHERE filters to narrow the result set before loading all data.
3. Mixing transactions from different time zones
If the database stores timestamps in UTC and your dashboard is in IST (UTC+5:30), you'll see off-by-hours results. Store all timestamps in UTC; convert on read in pandas with df['time'].dt.tz_convert('Asia/Kolkata').
4. Assuming column names are case-sensitive (they're not—usually)
Different databases handle case differently. PostgreSQL lowercases unquoted names; MySQL is case-insensitive by default. Use lowercase in your query, or quote names: "CustomerID" (PostgreSQL) vs `CustomerID` (MySQL).
5. Not LIMIT-ing exploratory queries on production databases
You're experimenting. You write a GROUP BY query without WHERE. It runs for 30 seconds and slows down the entire production system. Always add LIMIT 100 during development, remove it only after testing on sample data.

Interview questions

CSVs store all data in files; databases store data in structured tables with indexes. Databases let you filter at the source (WHERE), so you only transfer rows you need. CSVs require loading the entire file into memory first. For 1M+ rows, databases are faster.
F-strings paste user input directly into the query, creating SQL injection vulnerabilities. Parameters (like %(id)s) are escaped by the database driver, treating user input as literal values, not code. This prevents attacks.
Filter in SQL when you have huge datasets and specific WHERE conditions (it's database-optimized). Filter in pandas when you're already working with data or doing exploratory, ad-hoc analysis. Rule of thumb: if you know what you want, write SQL; if you're exploring, load to pandas first.
GROUP BY collapses rows (you get one output row per group). PARTITION BY keeps all rows but adds a window function value to each (e.g., rank). With GROUP BY, you lose the original data. With PARTITION BY, you keep it and augment each row with a rank/running total/lag.
The column is stored as TEXT in the database, or the database driver inferred type incorrectly. To fix: (1) Check the database schema with `DESC table_name` or `\d table` (PostgreSQL). (2) In pandas, use `pd.to_numeric(df['amount'], errors='coerce')` to convert. (3) Or, in SQL, CAST: `CAST(amount AS DECIMAL(10,2))` before querying. Always verify dtypes after loading with `df.info()`.

Quick quiz

Question 1 of 5
What does pd.read_sql() return?
Question 2 of 5
Which is the safest way to query a database with user input?
Question 3 of 5
You're loading 500M rows from PostgreSQL. What should you do first?
Question 4 of 5
What's the main difference between GROUP BY and PARTITION BY (window functions)?
Question 5 of 5
Why is filtering in SQL better than filtering in pandas for huge datasets?
0
/5
Excellent! You're ready to query production databases.

Where else you'll use this

Domain applications

🏦
Fintech / Banking
Query transaction ledgers, customer balances, compliance reports. Thomas's exact use case: SouthBank analysts run dashboards every morning sourced from SQL queries.
🛍️
E-commerce
Join orders, customers, and inventory tables. Group by category, city, date to track sales trends. Filter for high-value customers, detect churning segments.
📊
Business Intelligence
Load data warehouse tables (fact + dimension tables) into pandas, transform, and feed into reporting dashboards. This is the ETL bridge between data warehouse and BI tool.

How AI uses SQL + pandas

Large language models like ChatGPT are trained to generate SQL queries from plain-English prompts. You say "find customers who spent more than ₹100k in March", and the AI generates the WHERE clause. But the AI can't validate whether the query is correct until a human reviews it and runs it against real data.

This is where pandas comes in: load the result into a DataFrame, inspect with .head(), .describe(), and .groupby() to verify the answer makes sense. Then feed that verification back to the AI: "Your query returned 47 customers; I expected ~50 based on my dataset size. Let's refine it."

AI assistant tags you'll see:

text-to-SQL query-generation data-validation SQL-optimization
Africa & India's Best AI Academy
🤖 AI-first 🎓 Placement Assistance

Ready to build with data?

Join the Best AI Program: The AI-Era Analyst — 5 months, fully online, with placement assistance. Week 1 is free.

Explore The AI-Era Analyst →