Integrating DuckDB with pandas and Polars in Python

When you have data already loaded in a pandas or Polars DataFrame, you do not need to copy it into DuckDB before running SQL against it. The duckdb Python package can query both DataFrame types directly, and it can return results back in either format. This makes python duckdb pandas and polars integration mostly a matter of knowing which APIs move data without unnecessary copies and which force a conversion.

Why DuckDB Can Query Both pandas and Polars DataFrames

The duckdb Python package can run SQL directly against a pandas or Polars DataFrame that already exists in memory. You do not need to load the data into DuckDB first, and you do not need to convert it to a different format just to query it. The package resolves the DataFrame by its variable name inside the SQL string and scans it through the Arrow interface that both libraries expose.

This shared columnar interchange format is what makes the integration practical: one SQL engine, two DataFrame libraries, and a path for data to move between them without going through Python objects.

Querying a pandas DataFrame with DuckDB

import duckdb
import pandas as pd

orders = pd.DataFrame({
    "order_id": [1, 2, 3, 4],
    "customer_id": [10, 20, 10, 30],
    "amount": [10.5, 20.0, 15.75, 42.0],
})

result = duckdb.sql(
    "SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id"
).df()

duckdb.sql() looks up orders in the calling scope and treats it as a table. The .df() call at the end materializes the query result as a new pandas DataFrame. Because the input is a pandas DataFrame and the output is also a pandas DataFrame, this pattern fits directly into existing pandas pipelines.

For queries that need to run more than once, register the DataFrame under an explicit name:

duckdb.register("orders_table", orders)
result = duckdb.sql("SELECT COUNT(*) FROM orders_table").fetchone()

duckdb.register() creates a view that stays available for the lifetime of the connection, so later queries can reference orders_table without re-scanning the local variable each time. The equivalent one-off form is duckdb.from_df(orders), which returns a relation you can chain further SQL onto.

Querying a Polars DataFrame with DuckDB

The same pattern works with Polars:

import duckdb
import polars as pl

events = pl.DataFrame({
    "event_id": [1, 2, 3],
    "user_id": [100, 200, 100],
    "value": [5.5, 7.25, 3.0],
})

arrow_result = duckdb.sql(
    "SELECT user_id, AVG(value) AS avg_value FROM events GROUP BY user_id"
).arrow()

result = pl.from_arrow(arrow_result)

DuckDB reads the Polars DataFrame directly from its Arrow-compatible buffers. The query result is returned as a PyArrow table, and pl.from_arrow() converts that into a Polars DataFrame. This round trip avoids constructing Python objects for every row, which matters when the result set is large.

Joining pandas and Polars DataFrames in One Query

Because both DataFrame types are visible to the same SQL session, you can join them without converting either one first:

orders = pd.DataFrame({"order_id": [1, 2, 3], "customer_id": [10, 20, 10]})
customers = pl.DataFrame({"customer_id": [10, 20], "name": ["Alice", "Bob"]})

joined = duckdb.sql("""
    SELECT o.order_id, c.name
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
""").arrow()

DuckDB resolves orders and customers from the local scope, scans each through its respective interchange path, and performs the join internally. The result is a single Arrow table that you can convert to either format. This is useful when part of your pipeline produces pandas DataFrames and another part produces Polars DataFrames, and you need a combined view without normalizing everything to one library first.

Returning Results Back to pandas or Polars

The relation returned by duckdb.sql() offers several output methods:

For a Polars result, combine .arrow() with pl.from_arrow(). Avoid .fetchall() for large result sets: converting every column to Python objects is slow and memory-heavy compared to returning Arrow buffers.

Moving Data Between pandas and Polars

DuckDB is not the only path between the two libraries. Direct conversion methods exist on both sides:

pandas_df = pl_df.to_pandas()
pl_df = pl.from_pandas(pandas_df)

arrow_table = pandas_df.to_arrow()
pl_df = pl.from_arrow(arrow_table)

pl.from_pandas() copies the pandas data into Polars' own columnar storage. pl_df.to_pandas() materializes a pandas DataFrame and copies the data. When the data already uses Arrow-compatible types, converting through Arrow (to_arrow() then pl.from_arrow()) avoids the per-value conversion that pandas object columns require.

Memory Behavior and Zero-Copy Interchange

The main performance difference between querying a Polars DataFrame and a pandas DataFrame is how the data reaches DuckDB.

Polars stores data in Arrow-compatible buffers, so DuckDB can scan those buffers directly without copying the column data. The result is effectively zero-copy on the read path.

pandas stores data in NumPy arrays. DuckDB converts the DataFrame to Arrow before scanning. For numeric columns this conversion is cheap and often avoids copying. For object columns, such as strings stored as Python objects, the conversion has to build Arrow string buffers, which allocates memory and takes time. Keeping pandas data in numeric or nullable dtypes keeps this conversion cheap.

On the output side, .arrow() returns Arrow buffers without building Python objects. .df() materializes a pandas DataFrame, which is the right choice when you are handing the result to pandas code, but it costs more than reading the same result as Arrow.

None of this changes the correctness of the query. It only changes how much memory and CPU the data movement costs, which becomes visible when you query large DataFrames repeatedly.

Choosing the Right Combination

The decision is driven by where the data already lives and what you need to do with it.

Use DuckDB directly over a pandas DataFrame when SQL is the most natural way to express the query and you want to stay inside pandas for the rest of the pipeline. The integration costs nothing extra because DuckDB scans the DataFrame in place.

Use DuckDB over a Polars DataFrame when you want DuckDB's SQL engine for joins and aggregations but prefer Polars' expression API and lazy evaluation for the surrounding pipeline. The Arrow interchange keeps the data movement cheap.

Use DuckDB as the primary engine when you are combining many sources — CSV files, Parquet, remote tables, and in-memory DataFrames — in a single analytical query. Load the sources into DuckDB, run the query, and export the result to pandas or Polars only at the boundary where your application needs it.

The one combination to avoid is converting data back and forth repeatedly. Each to_pandas() or from_pandas() call copies the data. If a pipeline alternates between pandas and Polars several times, the copies add up. Pick one DataFrame library for the main flow and use DuckDB as the query layer, converting only at the edges.