DuckDB Cheat Sheet

1Install, connect, query1.x

pip install duckdb import duckdb★
Single wheel, no server, no dependencies. Same engine as the duckdb CLI.
duckdb.sql("SELECT 42")★
Run SQL against the default in-memory database and get a lazy relation back. .show() to print.
con = duckdb.connect("my.duckdb")★
Persistent, single-file database. duckdb.connect() (no arg) or ":memory:" for ephemeral. con.close() when done.
con.execute("INSERT INTO t VALUES (?, ?)", [1, "a"])
Parameterized DB-API style for writes/DDL. con.sql(...) for the relational form.

2Get results outto Python

duckdb.sql("...").df() # pandas DataFrame★
The everyday exit. Also .pl() (Polars), .arrow() (Arrow table), .fetchnumpy().
.fetchall() · .fetchone() · .fetchmany(n)
Row tuples, DB-API style. .fetchdf() == .df().
duckdb.sql("...").to_parquet("out.parquet")★
Stream a result straight to Parquet/CSV without materializing in Python (.to_csv(...)).
rel.columns · rel.types · rel.shape · rel.limit(5).show()
Inspect a relation's schema and peek at rows before pulling everything.

3Parquet / CSV / JSONno load step

duckdb.sql("SELECT * FROM 'data.parquet'")★
Reference a file path directly in FROM — no CREATE TABLE first. Projection + predicate pushdown skip unread data.
duckdb.read_parquet("part-*.parquet") # glob = one virtual table★
Globs and lists of paths union automatically. read_csv(...), read_json(...) too.
read_csv("f.csv", header=True, sep=",", types={"id":"BIGINT"})
Types are auto-sniffed; override when needed. FROM 'f.csv' also works with auto-detection.
SELECT * FROM read_parquet("y=*/*.parquet", hive_partitioning=True)★
Hive-style partition columns (y=2026/...) become real columns you can filter on.

4Remote (S3 / HTTP)httpfs

INSTALL httpfs; LOAD httpfs;★
Enables reading over HTTP(S) and S3. Bundled — installs on first use.
SELECT * FROM 'https://.../file.parquet'
Range-reads only the needed byte ranges — you can query a remote Parquet without downloading it whole.
CREATE SECRET (TYPE s3, KEY_ID '...', SECRET '...', REGION '...'); SELECT * FROM 's3://bucket/key/*.parquet'★
The Secrets manager holds credentials (S3/GCS/Azure/R2). Then query s3:// paths like local files.

5Pandas / Polars / Arrowquery DataFrames

df = pd.DataFrame(...) duckdb.sql("SELECT * FROM df WHERE x > 0")★
Replacement scan: a DataFrame in your Python scope is queryable by its variable name — zero copy, no registration.
con.register("t", df) · con.unregister("t")
Give a DataFrame/Arrow table an explicit SQL name (needed with a persistent con or dynamic names).
duckdb.sql("SELECT ...").pl() # Polars in, Polars out★
Works the same for Polars and Arrow — DuckDB reads their columnar memory directly (no serialization).
con.execute("CREATE TABLE t AS SELECT * FROM df")
Materialize a DataFrame into a real DuckDB table when you'll query it many times.

6Relational APIPythonic, lazy

rel = duckdb.sql("FROM 'sales.parquet'") rel.filter("amount > 100").aggregate("region, sum(amount)")★
Chainable, lazy operators: .filter, .project, .aggregate, .order, .limit, .join. Nothing runs until you fetch.
duckdb.values([...]) · duckdb.from_df(df) · con.table("t")
Build relations from Python data or existing tables, then compose.
rel.join(other, "a.id = b.id") · rel.union(other) · rel.except_(other)
Set ops & joins as methods. Mix freely with SQL — a relation is usable inside a later duckdb.sql(...).
rel.df() · rel.fetchall() · rel.create("my_table")
Terminal ops trigger execution: to a DataFrame, rows, or a persisted table.

7Friendly SQLDuckDB extras

FROM t SELECT a, b # FROM-first; SELECT is optional★
FROM t alone == SELECT * FROM t. Reads top-down like a pipeline.
SELECT * EXCLUDE (id) REPLACE (price*1.1 AS price) FROM t★
EXCLUDE/REPLACE edit a * without listing every column. COLUMNS('regex') selects by pattern.
SELECT ... GROUP BY ALL · ORDER BY ALL · QUALIFY rn = 1★
GROUP/ORDER BY ALL infer the keys; QUALIFY filters window results without a subquery.
SUMMARIZE t · FROM t USING SAMPLE 10%★
SUMMARIZE = instant per-column stats (min/max/nulls/approx-unique). USING SAMPLE for quick exploration.

8Lists, structs, JSONnested data

[1,2,3] · list[i] · list_transform(l, x -> x*2) · unnest(l)
First-class LIST type with functional ops and comprehensions: [x*2 for x in l].
{'a': 1, 'b': 2} · s.a · struct_pack(...)
STRUCT and MAP types; dot-access fields directly.
SELECT j->>'$.name' FROM read_json_auto("data.json")★
JSON extension (bundled): path extraction with ->/->>, read_json_auto infers the schema.

9Write & persistexport

COPY (SELECT ...) TO 'out.parquet' (FORMAT parquet)★
The export workhorse. FORMAT csv/json/parquet; add COMPRESSION zstd.
COPY t TO 'dir/' (FORMAT parquet, PARTITION_BY (year))★
Write a Hive-partitioned dataset in one statement.
CREATE TABLE t AS FROM 'big.parquet'
Persist into the .duckdb file for repeated queries & indexes. CREATE VIEW to keep it lazy.
con.execute("ATTACH 'other.duckdb' AS o"); con.sql("FROM o.t")
ATTACH multiple databases (incl. remote/read-only) and query across them.

10Extensions & performancesuperpowers

INSTALL postgres; LOAD postgres; ATTACH '...' (TYPE postgres)★
Query live Postgres/MySQL/SQLite tables. Other favorites: spatial, iceberg, delta, fts, vss (vector search).
SET threads = 8; SET memory_limit = '8GB';
DuckDB parallelizes across cores by default; cap memory to avoid OOM (it spills to disk when needed).
EXPLAIN ANALYZE SELECT ...★
See the physical plan + timings. PRAGMA database_size, PRAGMA show_tables for introspection.
CREATE FUNCTION / con.create_function("f", py_fn)
Register a Python UDF to call from SQL (scalar or vectorized/Arrow for speed).

!Common gotchasread before shipping

relations are lazy
Building a relation runs nothing — it executes on .df()/.fetchall()/.show(). Re-fetching re-runs the query.
one writer per database file
A .duckdb file allows a single read-write process at a time (many readers if read-only). It's embedded, not a shared server.
default connection is in-memory
duckdb.sql(...) uses a throwaway in-memory DB — tables vanish at exit. Use connect("file.duckdb") to persist.
replacement scan needs the name in scope
Querying FROM df works only if df is a live Python variable; for dynamic names use con.register().
1-based, SQL-typed
It's SQL: NULLs, types, and ORDER BY semantics follow SQL, not Pandas — e.g. string vs number comparisons are typed.

DuckDB cheat sheet