DuckDB + Python: Query CSV File with SQL

Working with large CSV or Parquet files usually means loading everything into Pandas and hoping your machine can keep up.

What if you could run SQL queries directly on those files without importing them into a database? That’s where DuckDB comes in.

In this tutorial, you’ll learn how to:

• Install DuckDB
• Query CSV files using SQL
• Filter and aggregate data
• Export results into a Pandas DataFrame

Why DuckDB?

DuckDB is an analytical database designed for OLAP (Online Analytical Processing) workloads. Unlike traditional databases, it runs inside your Python process and can query files directly.

Get Omale Happiness’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Some advantages include:

• No server setup
• Works with CSV and Parquet files
• Very fast
• Integrates with Pandas and Polars
• Uses standard SQL

Step 1: Install DuckDB

pip install duckdb pandas pyarrow

Step 2: Create Sample Data

Let’s say we have a CSV called:

sales.csv

id,product,category,price
1,Laptop,Electronics,1500
2,Mouse,Electronics,25
3,Chair,Furniture,120
4,Desk,Furniture,300
5,Keyboard,Electronics,80

Step 3: Query a CSV File with SQL

Create a Python file or a Jupyter notebook.

import duckdb
result = duckdb.sql("""
SELECT *
FROM 'sales.csv'
""")
print(result.df())

Output:

You should notice that no database import was required.

Step 4: Filter Rows

query = """
SELECT product, price
FROM 'sales.csv'
WHERE price > 100
"""
result = duckdb.sql(query)
print(result.df())

Output:

Step 5: Aggregate Data

Find average prices by category:

query = """
SELECT
category,
AVG(price) AS avg_price,
COUNT(*) AS total_products
FROM 'sales.csv'
GROUP BY category
"""
df = duckdb.sql(query).df()
print(df)

Output:

Step 6: Convert Results to Pandas

df = duckdb.sql("""
SELECT *
FROM 'sales.csv'
WHERE category='Electronics'
""").df()
print(df)

Output:

Conclusion

DuckDB makes analytics simple. With just a few lines of Python and SQL, you can query CSV and Parquet files, perform aggregations, join datasets, and export results into Pandas all without setting up a database server.

If you work with data, DuckDB deserves a place in your toolkit.