Skip to content
Python Central Hub

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()
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)
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.sql files.
  • Parameterize user inputs (avoid SQL injection).
  • Version-control your analysis scripts.

If this helped you, consider buying me a coffee ☕

Buy me a coffee

Was this page helpful?

Let us know how we did