SQL from Python (pandas + sqlite3)

Why integrate SQL + Python

A common workflow:

1. Use SQL to extract/aggregate data efficiently
2. Load into pandas
3. Visualize/model

Using SQLite locally

SQLite is file-based and great for learning.

Create a small demo database

Create SQLite DB

import sqlite3
import pandas as pd
 
conn = sqlite3.connect("demo.db")
 
# Example: create a table
conn.execute("""
CREATE TABLE IF NOT EXISTS users (
  user_id INTEGER PRIMARY KEY,
  country TEXT,
  plan TEXT
)
""")
 
conn.execute("DELETE FROM users")
conn.executemany(
    "INSERT INTO users(user_id, country, plan) VALUES (?, ?, ?)",
    [(1, "IN", "free"), (2, "IN", "pro"), (3, "US", "pro")],
)
conn.commit()

Query into pandas

Read SQL into pandas

import pandas as pd
 
q = """
SELECT country, COUNT(*) AS users
FROM users
GROUP BY country
"""
 
df = pd.read_sql_query(q, conn)
print(df)

Good practice

• Keep SQL in triple-quoted strings or .sql files.
• Parameterize user inputs (avoid SQL injection).
• Version-control your analysis scripts.

Two ways to get from SQL to a DataFrame

McKinney’s Python for Data Analysis covers exactly this workflow in §6.4, “Interacting with Databases.” There are two paths, and it’s worth seeing both:

diagram SQL -> DataFrame, two paths mermaid

  flowchart TD
  A["sqlite3.connect(...)"] --> B["con.execute(query)"]
  B --> C["cursor.fetchall() -> list of tuples"]
  C --> D["pd.DataFrame(rows, columns=[...])"]
  A --> E["pd.read_sql(query, con)"]
  E --> F["DataFrame, built in one step"]

The raw sqlite3 driver returns tuples you must pair with column names yourself; pandas.read_sql does that munging for you.

Analyst’s tip

From the book (Ch. 6, §6.4): a raw sqlite3 cursor returns “a list of tuples when selecting data from a table,” and you still have to pull column names out of cursor.description yourself before you can build a DataFrame — “quite a bit of munging that you’d rather not repeat each time you query the database.” McKinney’s fix is pandas.read_sql, which (via SQLAlchemy for most databases) gets you a labeled DataFrame in a single call. For plain SQLite, pd.read_sql_query(query, conn) gives you the same shortcut without installing SQLAlchemy at all.

The manual way (what the book shows first)

Cursor to DataFrame, by hand

import sqlite3
import pandas as pd
 
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE test (a TEXT, b TEXT, c REAL, d INTEGER)")
data = [
    ("Atlanta", "Georgia", 1.25, 6),
    ("Tallahassee", "Florida", 2.6, 3),
    ("Sacramento", "California", 1.7, 5),
]
conn.executemany("INSERT INTO test VALUES (?, ?, ?, ?)", data)
conn.commit()
 
cursor = conn.execute("SELECT * FROM test")
rows = cursor.fetchall()
columns = [col[0] for col in cursor.description]
 
df = pd.DataFrame(rows, columns=columns)
print(df)

text

             a           b     c  d
0      Atlanta     Georgia  1.25  6
1  Tallahassee     Florida  2.60  3
2   Sacramento  California  1.70  5

The shortcut (what you’ll actually use)

read_sql_query does the munging for you

df = pd.read_sql_query("SELECT * FROM test", conn)
print(df)

Same result, one line. read_sql_query works with a plain sqlite3 connection; the book’s pd.read_sql example additionally shows connecting through SQLAlchemy (sqla.create_engine), which is the more general path once you move beyond SQLite to Postgres, MySQL, or a warehouse.

🧪 Try It Yourself

Exercise 1 – Build column names from cursor.description

pch.exerciseTag pch.exerciseTitle

Exercise 2 – pd.DataFrame from rows + columns

pch.exerciseTag pch.exerciseTitle

Exercise 3 – The read_sql_query shortcut

pch.exerciseTag pch.exerciseTitle

Next

Put everything from this phase together in the SQL Mini Project (Build a KPI Dashboard Query Set) — a small, reusable pack of queries you can point at any similar dataset.