DuckDB 2 cheatsheet

DuckDB 2: From Python

8 common DuckDB 2 tasks.

Install DuckDB

Install the Python package. DuckDB runs inside your process, with no server.

pip install duckdb

Run a query

Run SQL and fetch the rows as Python tuples.

import duckdb

duckdb.sql("SELECT 42 AS answer").fetchall()

Query a CSV file

Read a CSV directly in SQL. DuckDB detects types and headers automatically.

duckdb.sql("SELECT * FROM read_csv('sales.csv') LIMIT 5").show()

Query Parquet files

Read one file or many with a glob pattern.

duckdb.sql("SELECT count(*) FROM read_parquet('data/*.parquet')").show()

Query a pandas DataFrame

Use a DataFrame variable as a table in SQL, with no copying step.

import pandas as pd

df = pd.DataFrame({"city": ["Rome", "Oslo", "Rome"], "sales": [10, 20, 30]})
duckdb.sql("SELECT city, sum(sales) AS total FROM df GROUP BY city ORDER BY total DESC").df()

Get the result as a DataFrame

Convert a result to pandas, Polars or Arrow. The Polars and Arrow conversions need pyarrow installed.

rel = duckdb.sql("SELECT 1 AS a")
rel.df()      # pandas
rel.pl()      # Polars
rel.arrow()   # Arrow

Use a database file

Connect to a file so tables persist between runs.

con = duckdb.connect("analytics.duckdb")
con.execute("CREATE TABLE IF NOT EXISTS events (id INTEGER, name VARCHAR)")

Pass parameters safely

Use placeholders instead of building SQL from strings.

con.execute("SELECT * FROM events WHERE id = ?", [7]).fetchall()