SQL with Python: sqlite3, pandas and SQLAlchemy Basics

Part 5 of the Python for Data Science track. Last updated: September 2026.

Most data lives in databases, not CSVs. Python talks to SQL databases natively via sqlite3 (built in, zero setup — perfect for learning) and scales up with pandas and SQLAlchemy.

Connecting and creating a table

import sqlite3

conn = sqlite3.connect("shop.db")   # creates the file if it doesn't exist
cur = conn.cursor()

cur.execute("""CREATE TABLE IF NOT EXISTS customers (
    id INTEGER PRIMARY KEY, name TEXT, city TEXT)""")
conn.commit()

Inserting safely: parameterized queries

# ? placeholders — values travel SEPARATELY from the SQL text
cur.execute("INSERT INTO customers (name, city) VALUES (?, ?)", ("Ada", "London"))
cur.execute("INSERT INTO customers (name, city) VALUES (?, ?)", ("Grace", "New York"))
conn.commit()

# NEVER build SQL with f-strings: f"SELECT ... WHERE city = '{city}'"
# invites SQL injection. Placeholders are non-negotiable.

Querying

cur.execute("SELECT * FROM customers WHERE city = ?", ("London",))
print(cur.fetchall())          # [(1, 'Ada', 'London')]

for row in cur.execute("SELECT name FROM customers ORDER BY name"):
    print(row[0])              # rows are tuples — index into them

pandas and SQL: read_sql_query and to_sql

import pandas as pd

# SQL result straight into a DataFrame
df = pd.read_sql_query(
    "SELECT city, COUNT(*) AS n FROM customers GROUP BY city", conn)
print(df)

# DataFrame straight into a table
orders = pd.DataFrame({"customer_id": [1, 1, 2], "amount": [50, 75, 120]})
orders.to_sql("orders", conn, if_exists="replace", index=False)

joined = pd.read_sql_query(
    """SELECT c.name, o.amount FROM customers c
       JOIN orders o ON c.id = o.customer_id""", conn)
print(joined)
conn.close()

This two-way bridge — SQL for filtering at the source, pandas for analysis — is how production data work actually looks.

A taste of SQLAlchemy

For larger apps, SQLAlchemy is the standard toolkit (install with pip install sqlalchemy). Same database, richer API:

from sqlalchemy import create_engine, text

engine = create_engine("sqlite:///shop.db")   # works for Postgres/MySQL too — just change the URL
with engine.connect() as c:
    rows = c.execute(text("SELECT name FROM customers WHERE city = :city"),
                     {"city": "London"}).fetchall()
print(rows)

Key takeaways

• sqlite3 is built in — zero-setup SQL for learning and prototypes.
• Always use parameterized queries (? or :name); never f-strings for values.
• read_sql_query / to_sql bridge SQL and pandas in both directions.
• SQLAlchemy is the production-grade layer when you outgrow raw sqlite3.

Next in this series: Exploratory Data Analysis: A Complete Walkthrough.