DuckDB Python Tutorial: SQL on DataFrames and Parquet
Chat2DB TeamEvery Python data project eventually produces the same file: a script with fourteen chained pandas operations that nobody can read six months later, running out of memory on a dataset that would be trivial for a database. DuckDB fixes that without adding infrastructure. It installs with pip, runs inside your Python process, queries pandas and Polars DataFrames directly with SQL, and handles datasets larger than RAM.
This guide covers the parts you will actually use.
Installation and first query
pip install duckdbThat is the whole setup. No server, no daemon, no configuration file.
import duckdb
duckdb.sql("SELECT 42 AS answer").show()┌────────┐
│ answer │
│ int32 │
├────────┤
│ 42 │
└────────┘Calling duckdb.sql() at module level uses a default in-memory database. For anything persistent, create a connection:
con = duckdb.connect("analytics.duckdb") # file-backed, survives restarts
mem = duckdb.connect() # in-memory, discarded on exitQuerying DataFrames directly
This is the feature that changes how you write analysis code. DuckDB finds DataFrames in your Python scope and queries them by variable name — no registration, no copy.
import duckdb
import pandas as pd
orders = pd.DataFrame({
"order_id": [1, 2, 3, 4, 5, 6],
"customer_id": [10, 11, 10, 12, 11, 10],
"country": ["GB", "DE", "GB", "FR", "DE", "GB"],
"total": [120.50, 89.00, 45.25, 210.00, 15.75, 300.00],
"status": ["paid", "paid", "refunded", "paid", "pending", "paid"],
})
result = duckdb.sql("""
SELECT country,
count(*) AS orders,
round(sum(total),2) AS revenue,
round(avg(total),2) AS avg_order
FROM orders
WHERE status = 'paid'
GROUP BY country
ORDER BY revenue DESC
""")
result.show()┌─────────┬────────┬─────────┬───────────┐
│ country │ orders │ revenue │ avg_order │
│ varchar │ int64 │ double │ double │
├─────────┼────────┼─────────┼───────────┤
│ GB │ 2 │ 420.50 │ 210.25 │
│ FR │ 1 │ 210.00 │ 210.00 │
│ DE │ 1 │ 89.00 │ 89.00 │
└─────────┴────────┴─────────┴───────────┘The equivalent pandas is orders[orders.status == 'paid'].groupby('country').agg(...) with a rename step and a sort — readable enough here, considerably less so once you add three joins and a window function.
Crucially, this is zero-copy. DuckDB reads the DataFrame's memory in place rather than duplicating it, so querying a 4 GB DataFrame does not need another 4 GB.
Getting results back out:
df = result.df() # pandas DataFrame
arrow = result.arrow() # PyArrow Table
pl_df = result.pl() # Polars DataFrame
rows = result.fetchall() # list of tuples
one = result.fetchone() # single tuplePolars works the same way as pandas:
import polars as pl
sales = pl.DataFrame({"region": ["EU", "US", "EU"], "amount": [100, 250, 175]})
duckdb.sql("SELECT region, sum(amount) AS total FROM sales GROUP BY region").pl()Reading files without loading them
DuckDB queries files as if they were tables:
# CSV — schema and types are inferred automatically
duckdb.sql("SELECT * FROM 'data/customers.csv' LIMIT 5").show()
# Parquet
duckdb.sql("SELECT count(*) FROM 'data/events.parquet'").show()
# JSON, including newline-delimited
duckdb.sql("SELECT * FROM 'data/logs.jsonl' WHERE level = 'ERROR'").show()
# Glob patterns across many files
duckdb.sql("SELECT count(*) FROM 'data/2026/*/events_*.parquet'").show()When inference gets something wrong, override it:
duckdb.sql("""
SELECT * FROM read_csv('data/messy.csv',
delim = ';',
header = true,
columns = {'id': 'INTEGER', 'name': 'VARCHAR', 'signed_up': 'DATE'},
dateformat = '%d/%m/%Y',
ignore_errors = true
)
""").show()For partitioned datasets, hive_partitioning turns directory names into columns:
# Files at data/year=2026/month=08/part-0.parquet
duckdb.sql("""
SELECT year, month, count(*) AS events
FROM read_parquet('data/**/*.parquet', hive_partitioning = true)
WHERE year = '2026'
GROUP BY year, month
ORDER BY month
""").show()The WHERE year = '2026' is evaluated against directory names, so files from other years are never opened. That is a huge saving on a large lake.
Remote files
The httpfs extension reads over HTTP and from S3:
con = duckdb.connect()
con.sql("INSTALL httpfs; LOAD httpfs;")
con.sql("""
CREATE SECRET s3_creds (
TYPE s3,
KEY_ID 'AKIA...',
SECRET '...',
REGION 'eu-west-1'
)
""")
con.sql("""
SELECT device_id, avg(temperature) AS avg_temp
FROM 's3://my-bucket/telemetry/2026/08/*.parquet'
WHERE temperature IS NOT NULL
GROUP BY device_id
ORDER BY avg_temp DESC
LIMIT 10
""").show()DuckDB issues HTTP range requests, so it fetches only the column chunks and row groups the query needs rather than downloading whole files.
Parameterised queries
Never build SQL with f-strings. Use parameters:
# Positional
con.execute(
"SELECT * FROM orders WHERE country = ? AND total > ?",
["GB", 100.0]
).fetchall()
# Named
con.execute(
"SELECT * FROM orders WHERE country = $country AND total > $min_total",
{"country": "GB", "min_total": 100.0}
).fetchall()Beyond avoiding injection, this lets DuckDB reuse the prepared plan.
For bulk inserts, executemany batches efficiently:
con.execute("CREATE TABLE metrics (name VARCHAR, value DOUBLE, ts TIMESTAMP)")
con.executemany(
"INSERT INTO metrics VALUES (?, ?, ?)",
[("cpu", 0.82, "2026-08-16 10:00:00"),
("cpu", 0.79, "2026-08-16 10:01:00"),
("mem", 0.55, "2026-08-16 10:00:00")]
)Though for anything large, inserting from a DataFrame or file is far faster than row-by-row:
con.execute("CREATE TABLE metrics AS SELECT * FROM my_dataframe")
con.execute("INSERT INTO metrics SELECT * FROM 'more_metrics.parquet'")The relational API
If you prefer method chaining to SQL strings, DuckDB offers a lazy relational API that builds the same query plan:
rel = duckdb.sql("SELECT * FROM orders")
(rel
.filter("status = 'paid'")
.aggregate("country, sum(total) AS revenue", "country")
.order("revenue DESC")
.limit(3)
.show())Nothing executes until you call .show(), .df() or .fetchall(), so intermediate steps cost nothing. This is useful for building queries conditionally:
rel = duckdb.sql("SELECT * FROM orders")
if country_filter:
rel = rel.filter(f"country = '{country_filter}'") # prefer parameters in real code
if min_total:
rel = rel.filter(f"total >= {min_total}")
final = rel.aggregate("country, count(*) AS n", "country").df()Processing more data than fits in memory
This is where DuckDB decisively beats an in-memory DataFrame library. Give it a memory limit and a temp directory, and it spills to disk:
con = duckdb.connect("big.duckdb")
con.sql("SET memory_limit = '4GB'")
con.sql("SET temp_directory = '/tmp/duckdb_spill'")
con.sql("SET threads = 8")
# Aggregating 200 GB of Parquet on a laptop with 16 GB of RAM
con.sql("""
COPY (
SELECT user_id,
count(*) AS events,
min(ts) AS first_seen,
max(ts) AS last_seen
FROM 's3://bucket/events/**/*.parquet'
GROUP BY user_id
) TO 'user_summary.parquet' (FORMAT parquet, COMPRESSION zstd)
""")Hash aggregation, sorting and joins all spill when they exceed the limit. The same operation in pandas raises MemoryError.
To process results incrementally rather than materialising them:
result = con.sql("SELECT * FROM 'huge.parquet'")
while batch := result.fetchmany(100_000):
process(batch)
# Or stream Arrow record batches
for record_batch in result.fetch_arrow_reader(batch_size=100_000):
process(record_batch)Calling Python functions from SQL
Register a Python function and use it as a scalar function:
from duckdb.typing import VARCHAR, BIGINT
def normalise_sku(sku: str) -> str:
return sku.strip().upper().replace("-", "")
con.create_function("normalise_sku", normalise_sku, [VARCHAR], VARCHAR)
con.sql("SELECT normalise_sku(sku) AS sku, count(*) FROM items GROUP BY 1").show()Be aware that a Python UDF breaks vectorised execution — each row crosses into the interpreter. It is a convenience for logic you cannot express in SQL, not a performance tool. Where a built-in exists, use it.
Querying live databases
DuckDB can attach to PostgreSQL, MySQL and SQLite and query them in place, which makes it a useful joining layer:
con.sql("INSTALL postgres; LOAD postgres;")
con.sql("ATTACH 'dbname=appdb host=localhost user=postgres' AS pg (TYPE postgres, READ_ONLY)")
# Join production Postgres data against a local Parquet file
con.sql("""
SELECT c.name, c.email, s.lifetime_value
FROM pg.public.customers c
JOIN 'scores.parquet' s ON s.customer_id = c.id
WHERE s.lifetime_value > 1000
ORDER BY s.lifetime_value DESC
""").df()READ_ONLY is worth including by default when the target is a production database.
For the exploratory half of this work — checking what the Postgres schema actually contains before writing the join — a GUI client is quicker than round-tripping through Python. Chat2DB (opens in a new tab) connects to PostgreSQL, MySQL, SQLite and 20+ other databases, and will draft the SQL from a plain-English description when you are working against an unfamiliar schema.
Practical notes
Set threads deliberately. DuckDB defaults to using all cores, which is right for a batch job and wrong inside a web request handler where several requests run concurrently.
Persist intermediate results. Repeatedly scanning the same CSV is wasteful. Convert once:
con.sql("COPY (SELECT * FROM 'raw.csv') TO 'raw.parquet' (FORMAT parquet)")Parquet with compression is typically 5–10× smaller than CSV and much faster to scan because of column pruning.
EXPLAIN ANALYZE works and is worth reading:
con.sql("EXPLAIN ANALYZE SELECT country, sum(total) FROM orders GROUP BY country").show()Connections are not thread-safe for concurrent writes. Use con.cursor() to get an independent cursor per thread against the same database.
Summary
DuckDB gives Python SQL over DataFrames, CSV, Parquet and JSON with no server and no import step, reading DataFrames zero-copy and spilling to disk when data exceeds memory. Use duckdb.sql() for quick work and an explicit connection when you need persistence or settings; use parameters rather than f-strings; convert repeatedly-scanned CSVs to Parquet; and reach for the relational API when you are composing queries programmatically. The clearest signal it is worth adopting is a pandas pipeline that has become either unreadable or too large for RAM — DuckDB usually replaces both problems with one query.