TopolearnMixing SQL and DataFrames lets you use whichever is clearer for each step, and it is often much faster than pandas.
DuckDB can query a pandas DataFrame, Polars DataFrame or Arrow table that is a Python variable in scope, simply by naming it: duckdb.sql("SELECT * FROM df WHERE x > 1"). This is called a replacement scan: if a table name is not found in the database, DuckDB looks for a Python object with that name.
Results convert back just as easily:
.df()returns a pandas DataFrame.pl()returns a Polars DataFrame.arrow()returns a PyArrow table (or reader),.fetchnumpy()a dict of NumPy arrays
Arrow and Polars data is read with zero or near-zero copying, and pandas numeric columns are scanned directly. That makes DuckDB a great accelerator for heavy pandas steps: complex joins, group-bys with many keys, or window functions are often several times faster and use less memory as SQL.
To persist a DataFrame, con.execute("CREATE TABLE t AS SELECT * FROM df") or con.register("name", df) to give it an explicit name (needed when the variable is not in the calling scope, e.g. inside another function).
Watch types at the boundary: pandas object columns of strings become VARCHAR, NaN in float columns is not the same as SQL NULL, and timezone-aware timestamps map to TIMESTAMP WITH TIME ZONE.
Example
import duckdb
import pandas as pd
orders = pd.DataFrame({"user": ["a", "b", "a", "c"], "amount": [10, 5, 7, 3]})
users = pd.DataFrame({"user": ["a", "b", "c"], "country": ["AM", "FR", "AM"]})
result = duckdb.sql("""
SELECT u.country, sum(o.amount) AS total
FROM orders o JOIN users u USING (user)
GROUP BY ALL ORDER BY total DESC
""").df() # back to pandas
print(result.to_dict("list"))
# {'country': ['AM', 'FR'], 'total': [20.0, 5.0]}Remember
- Name a DataFrame or Arrow table in FROM and DuckDB scans it in place
.df(),.pl(),.arrow()convert results back- Use
con.registerwhen the object is not a variable in scope
Watch out for
- Expecting NaN in a pandas float column to behave like SQL NULL in filters and aggregates.