You love Polars for its speed, but sometimes you just miss writing SQL. Maybe a colleague gave you a complex query and you don’t want to rewrite it in Python.
Enter DuckDB. It’s an in-process SQL database (like SQLite on steroids) that can read Polars DataFrames directly from memory without copying the data.
Step 1: Installation
pip install polars duckdb
Step 2: Create a Polars DataFrame
import polars as pl
import duckdb
# Create some data
df = pl.DataFrame({
"product": ["Apple", "Banana", "Apple", "Orange"],
"price": [1.00, 0.50, 1.20, 0.80],
"sales": [100, 200, 150, 50]
})Step 3: Query with SQL
You don’t need to “insert” the data into DuckDB. DuckDB can “see” your Python variables!
# Run standard SQL directly on the variable 'df'
result = duckdb.sql("""
SELECT
product,
AVG(price) as avg_price,
SUM(sales) as total_sales
FROM df
GROUP BY product
HAVING total_sales > 100
ORDER BY total_sales DESC
""")
# Show the result (it returns a DuckDB relation)
print(result)Step 4: Convert Back to Polars
DuckDB results can be instantly converted back to Polars.
# Execute and get a Polars DataFrame df_result = result.pl() print(df_result)
Output:
shape: (2, 3) ┌─────────┬───────────┬─────────────┐ │ product ┆ avg_price ┆ total_sales │ │ --- ┆ --- ┆ --- │ │ str ┆ f64 ┆ i128 │ ╞═════════╪═══════════╪═════════════╡ │ Apple ┆ 1.1 ┆ 250 │ │ Banana ┆ 0.5 ┆ 200 │ └─────────┴───────────┴─────────────┘
This “Modern Data Stack in a Box” (Polars + DuckDB) allows you to mix and match Python and SQL logic in the same script with zero performance penalty.
Key Takeaways
- Combine the speed of Polars with the power of SQL using DuckDB, a high-performance in-process SQL database.
- Install DuckDB and create a Polars DataFrame without needing to insert data.
- DuckDB can directly access your Python variables for SQL queries, enabling seamless integration.
- Results from DuckDB can be instantly converted back to Polars, ensuring a smooth workflow.
- This setup, called the ‘Modern Data Stack in a Box’, allows mixing Python and SQL logic without a performance penalty.
