lesson 223 Data Formats & Analytical Workflows · advanced

SQL with SQLite and DuckDB

pandas can send SQL to SQLite and get a DataFrame back, with ? parameters keeping values out of the query text. DuckDB runs analytical SQL directly on DataFrames and files.

You'll be able to

Store a DataFrame in SQLite with to_sql and query it with pd.read_sql_query(sql, conn, params=...)
Pass values as ? parameters, never by pasting them into the SQL text
Query a DataFrame or a CSV file with duckdb.sql(...) and get a DataFrame back with .df()

The idea

In the Packaging & Databases module you ran SQL against SQLite with sqlite3. pandas connects to the same connection: df.to_sql("trips", conn, index=False) creates a table and inserts every row, and pd.read_sql_query(sql, conn, params=(...)) runs a query and returns a DataFrame. Values go in as ? parameters, exactly as with execute, so a value can never change the query.

DuckDB is a database built for analysis that runs inside your program. duckdb.sql("SELECT … FROM trips") can name a DataFrame variable as if it were a table, or a CSV or Parquet file by its path. .df() turns the result into a DataFrame and .fetchall() into a list of tuples.

Run each example to see what it prints. Running examples doesn't count toward progress.

example-1.py · Trips in SQLite, queried with a parameter

import sqlite3
import pandas as pd

trips = pd.DataFrame({
    "trip_id": [16126, 16127, 16128, 16129, 16130],
    "payment_type_id": [1, 1, 0, 0, 1],
    "fare_amount": [40.8, 7.2, 17.2, 22.06, 5.1],
})
conn = sqlite3.connect(":memory:")
trips.to_sql("trips", conn, index=False)
query = "SELECT trip_id, fare_amount FROM trips WHERE fare_amount >= ? ORDER BY fare_amount DESC"
print(pd.read_sql_query(query, conn, params=(17.2,)))
counts = pd.read_sql_query("SELECT payment_type_id, COUNT(*) AS n FROM trips GROUP BY payment_type_id", conn)
print(counts.to_dict("records"))
conn.close()

example-2.py · A table name that doesn't exist

import sqlite3
import pandas as pd

conn = sqlite3.connect(":memory:")
pd.DataFrame({"trip_id": [16126]}).to_sql("trips", conn, index=False)
print(pd.read_sql_query("SELECT * FROM trip", conn))

example-3.py · DuckDB over a DataFrame and a CSV file

import duckdb
import pandas as pd

zones = pd.DataFrame({"zone_id": [186, 264, 39, 100, 263], "borough": ["Manhattan", "Unknown", "Brooklyn", "Manhattan", "Manhattan"]})
pd.DataFrame({
    "trip_id": [16126, 16127, 16128, 16129, 16130],
    "pickup_zone_id": [186, 264, 39, 100, 263],
    "fare_amount": [40.8, 7.2, 17.2, 22.06, 5.1],
}).to_csv("trips.csv", index=False)
report = duckdb.sql("""
    SELECT z.borough, COUNT(*) AS trips, ROUND(AVG(t.fare_amount), 2) AS avg_fare
    FROM 'trips.csv' AS t JOIN zones AS z ON t.pickup_zone_id = z.zone_id
    GROUP BY z.borough
    ORDER BY trips DESC, z.borough
""").df()
print(report)

When to use which: SQLite when the data lives in a database file that other programs also use; DuckDB for analytical queries (joins, group by, window functions) over files and DataFrames, often faster than pandas on big inputs; pandas for step-by-step work in Python. Mistakes to avoid: building SQL with f-strings from values (use ?); forgetting index=False in to_sql; and assuming a query's row order without ORDER BY.

Review

df.to_sql(name, conn, index=False) stores a table; pd.read_sql_query(sql, conn, params=...) returns a DataFrame.
Values always travel as ? parameters.
duckdb.sql(...) queries DataFrame variables and files; .df() and .fetchall() return the result.
Next: make an analysis give the same answer every time it runs.

Practise this