DuckDB with Pandas and Arrow

DuckDB is designed to live inside a Python data stack, and its two most important partners are pandas DataFrames and Apache Arrow tables. Data can flow in both directions with almost no copying, so DuckDB acts as a fast SQL engine over objects you already have, and returns results in exactly the container your next step expects.

Querying Pandas DataFrames

DuckDB supports replacement scans: local variables that hold DataFrames can be referenced by name directly in SQL. The explicit alternatives are register() and from_df():

import duckdb, pandas as pd

df = pd.DataFrame({
    "device": ["s1", "s2", "s1"],
    "temp":   [21.3, 19.8, 22.1],
})

con = duckdb.connect()
con.register("temps", df)              # optional named view
rel = duckdb.from_df(df)               # relation over the DataFrame

print(duckdb.sql("SELECT device, avg(temp) AS avg_temp "
                 "FROM df GROUP BY 1").df())

The last query works because df is a local variable, no registration needed; the same trick applies for polars, and it is the fastest way to throw a SQL question at in-memory data.

Getting Results Back

Result objects convert to the container of your choice:

out  = duckdb.sql("SELECT * FROM df WHERE temp > 20")
df2  = out.df()                        # pandas (fetchdf is an alias)
tbl  = out.arrow()                     # pyarrow.Table
rows = out.fetchall()                  # list of tuples
rec  = out.fetchnumpy()                # dict of numpy arrays

.arrow() avoids a pandas copy when the downstream consumer is Arrow-based, which is common in streaming and data lake tools.

Zero-Copy Arrow Integration

Apache Arrow is the standard columnar in-memory format, and DuckDB shares a representation with it, so scanning or producing Arrow tables is zero-copy in the common case. duckdb.from_arrow() wraps a pyarrow.Table as a relation, and register() accepts Arrow tables for a named SQL view:

import pyarrow as pa

table = pa.table({
    "region": ["west", "east"],
    "amount": [120.0, 80.0],
})

agg = duckdb.sql("SELECT region, sum(amount) AS total "
                 "FROM at GROUP BY region").df()
select

Hmm, that example is incomplete.