DuckDB, Python & Pandas

DuckDB’s Python client is more than a driver — it can query in-memory Pandas and Polars DataFrames directly, with no copy, by scanning their underlying Arrow-compatible buffers.

Querying a DataFrame with SQL

import duckdb
import pandas as pd

df = pd.DataFrame({
    "customer": ["Alice", "Bob", "Alice", "Carol"],
    "amount":   [120.0, 45.5, 80.0, 200.0],
})

# duckdb.sql() sees local variables in scope automatically
result = duckdb.sql("""
    SELECT customer, SUM(amount) AS total
    FROM df
    GROUP BY customer
    ORDER BY total DESC
""")

print(result)                 # DuckDB relation, prints like a table
result.df()                   # -> back to a Pandas DataFrame
result.pl()                   # -> to a Polars DataFrame
result.arrow()                # -> to a PyArrow Table

This is often dramatically faster than the equivalent groupby() on large DataFrames, because DuckDB parallelizes across cores and spills to disk automatically if the data doesn’t fit in memory — something Pandas alone won’t do.

The Relational (Fluent) API

For code that reads more like a data pipeline than a SQL string, DuckDB also offers a chained, Pandas-like API:

con = duckdb.connect()
(
    con.sql("SELECT * FROM 'orders.parquet'")
       .filter("amount > 100")
       .aggregate("customer, SUM(amount) AS total", "customer")
       .order("total DESC")
       .limit(10)
       .show()
)

Polars Interop

import polars as pl

pl_df = pl.read_parquet("orders.parquet")

duckdb.sql("SELECT customer, SUM(amount) FROM pl_df GROUP BY customer").pl()

Because both DuckDB and Polars are built on Apache Arrow’s columnar memory format, conversions between them are effectively zero-copy — there’s no serialization tax for moving data back and forth mid-pipeline.

In Jupyter / Notebooks

%load_ext sql
%sql duckdb:///:memory:

%%sql
SELECT * FROM read_parquet('events.parquet') LIMIT 20

Combined with duckdb.sql(...).df(), this makes DuckDB a natural drop-in accelerator for existing Pandas-based notebooks — swap the slow, memory-heavy groupby/merge calls for a SQL string against the same DataFrame, keep everything else unchanged.