DuckDB in Python: Query CSV, Parquet, and Pandas with SQL

TL;DR

DuckDB is an in-process analytical database. Think SQLite, but built for GROUP BY over millions of rows instead of transactional writes. In Python it installs with one pip command, needs no server, and lets you run SQL straight over CSV and Parquet files or over a Pandas DataFrame sitting in memory. This guide walks through installing it, querying files without loading them first, mixing SQL with DataFrames, writing Python UDFs, and reading remote Parquet over HTTP. Every snippet runs as-is against the tiny sample data we create along the way.

Why I keep a DuckDB import in every data script

I reach for DuckDB when a job is too big for a comfortable Pandas session but nowhere near “stand up a warehouse” territory. The case that sold me: a folder of daily Parquet exports, one file per day, and a question like “what were the top ten error codes across the last quarter?” With Pandas that means a loop, ninety read_parquet calls, a concat, and praying the whole thing fits in RAM. With DuckDB it’s SELECT code, count(*) FROM 'exports/*.parquet' GROUP BY code ORDER BY 2 DESC LIMIT 10. One line, no concat, and it streams the files instead of holding them all at once.

The detail that took me a while to internalize: duckdb.sql(...) doesn’t actually run anything. It hands back a lazy relation, and the query only executes when you materialize it with .df(), .show(), or .fetchall(). That laziness is why you can chain filters and aggregations on a relation and only pay for the work once, at the end. It also means a typo in your SQL sometimes stays quiet until the line where you finally pull results — worth knowing before you spend ten minutes blaming the wrong statement.

This is a tutorial, so we’ll build up from the first query. If you’re weighing DuckDB against the DataFrame libraries for a specific pipeline, I did a head-to-head in DuckDB vs Polars that covers the “which one” question this article deliberately skips.

Install and your first query

DuckDB ships as a self-contained wheel with no external dependencies. One line installs it. If you use uv instead of pip, uv add duckdb works the same way, a workflow I covered in the uv tutorial.

pip install duckdb

The Python API has two entry points. duckdb.sql() runs a query against an in-memory database and returns a relation. duckdb.connect() opens a connection you can reuse, optionally backed by a file on disk.

import duckdb

duckdb.sql("SELECT 'hello' AS greeting, 42 AS answer").show()

.show() prints DuckDB’s boxed result, which includes the column names and their inferred types:

┌──────────┬────────┐
│ greeting │ answer │
│ varchar  │ int32  │
├──────────┼────────┤
│ hello    │     42 │
└──────────┴────────┘

That type row under the header is one of DuckDB’s nicer touches. You can see at a glance that answer came back as int32 and greeting as varchar, without a separate .dtypes call.

Query CSV and Parquet files directly

This next feature is the one that changed how I work. DuckDB can put a file path where a table name normally goes. No loading step. No read_csv into a variable first. Let’s make a small CSV to prove it.

import csv

rows = [
    {"day": "2026-08-01", "service": "api", "status": 200, "ms": 42},
    {"day": "2026-08-01", "service": "api", "status": 500, "ms": 91},
    {"day": "2026-08-01", "service": "web", "status": 200, "ms": 18},
    {"day": "2026-08-02", "service": "api", "status": 200, "ms": 55},
    {"day": "2026-08-02", "service": "web", "status": 404, "ms": 12},
]

with open("requests.csv", "w", newline="") as f:
    writer = csv.DictWriter(f, fieldnames=["day", "service", "status", "ms"])
    writer.writeheader()
    writer.writerows(rows)

Now query it as if it were a table. DuckDB sniffs the header and column types for you:

import duckdb

duckdb.sql("""
    SELECT service, count(*) AS hits, round(avg(ms), 1) AS avg_ms
    FROM 'requests.csv'
    GROUP BY service
    ORDER BY hits DESC
""").show()

┌─────────┬───────┬────────┐
│ service │ hits  │ avg_ms │
│ varchar │ int64 │ double │
├─────────┼───────┼────────┤
│ api     │     3 │   62.7 │
│ web     │     2 │   15.0 │
└─────────┴───────┴────────┘

The numbers come straight from the five rows we wrote. api appears three times with a mean latency of (42 + 91 + 55) / 3 = 62.7 ms. Because you defined the data, the output is fully reproducible on your machine.

Parquet works the same way, and it’s the format DuckDB is happiest with. Write the CSV back out as Parquet using nothing but SQL:

duckdb.sql("COPY (SELECT * FROM 'requests.csv') TO 'requests.parquet' (FORMAT parquet)")
duckdb.sql("SELECT count(*) AS n FROM 'requests.parquet'").show()

Glob patterns let you sweep a whole directory of files in one shot — the exact case I described at the top. If you had a folder of daily exports, FROM 'exports/*.parquet' reads every match as a single virtual table, and DuckDB pushes the filter and aggregation down so it never materializes all of them at once. It can even query datasets larger than your available memory by spilling to disk, which is the line Pandas can’t cross.

Mix SQL with Pandas, Polars, and Arrow

The second superpower: DuckDB can see the DataFrames already living in your Python session. Assign a Pandas DataFrame to a variable, then reference that variable name inside your SQL string. DuckDB calls this a replacement scan, and it reads the object straight from your local scope.

import duckdb
import pandas as pd

orders = pd.DataFrame({
    "user_id": [1, 1, 2, 3, 3, 3],
    "amount":  [20, 35, 50, 12, 8, 40],
})

duckdb.sql("""
    SELECT user_id, count(*) AS orders, sum(amount) AS total
    FROM orders
    GROUP BY user_id
    ORDER BY total DESC
""").show()

┌─────────┬────────┬───────┐
│ user_id │ orders │ total │
│  int64  │ int64  │ int128│
├─────────┼────────┼───────┤
│       3 │      3 │    60 │
│       1 │      2 │    55 │
│       2 │      1 │    50 │
└─────────┴────────┴───────┘

No conversion, no copy into a DuckDB table. The string literally says FROM orders and DuckDB finds the DataFrame named orders. The read is zero-copy where the Arrow memory layout allows it, so you’re not paying to duplicate the data.

Going the other direction is just as clean. A relation converts to whatever you need next: .df() for Pandas, .pl() for Polars, .arrow() for a PyArrow table, .fetchall() for a list of tuples.

result = duckdb.sql("SELECT user_id, sum(amount) AS total FROM orders GROUP BY user_id")

pandas_df = result.df()      # back to Pandas
polars_df = result.pl()      # straight to Polars
arrow_tbl = result.arrow()   # PyArrow table
rows      = result.fetchall()  # [(1, 55), (2, 50), (3, 60)]

That round-trip is why DuckDB slots into an existing Pandas or Polars pipeline instead of replacing it. Do the heavy filtering and joining in SQL, hand the trimmed result back as a DataFrame, and keep the Python-native steps you already had.

Join across a file, a DataFrame, and a table at once

Because files, DataFrames, and persisted tables are all just table sources to DuckDB, you can join across all three in a single query. Say the request log lives in Parquet and a lookup of service owners lives in a DataFrame:

import duckdb
import pandas as pd

owners = pd.DataFrame({
    "service": ["api", "web"],
    "owner":   ["platform", "frontend"],
})

duckdb.sql("""
    SELECT o.owner, count(*) AS requests, sum((r.status >= 500)::INT) AS errors
    FROM 'requests.parquet' AS r
    JOIN owners AS o USING (service)
    GROUP BY o.owner
    ORDER BY requests DESC
""").show()

┌──────────┬──────────┬────────┐
│  owner   │ requests │ errors │
│ varchar  │  int64   │ int128 │
├──────────┼──────────┼────────┤
│ platform │        3 │      1 │
│ frontend │        2 │      0 │
└──────────┴──────────┴────────┘

One query, three sources: a Parquet file on disk and a Pandas DataFrame in memory, joined on service. The sum((r.status >= 500)::INT) trick counts errors by casting each boolean to a 0 or 1 and summing, a small SQL idiom that saves a CASE WHEN.

Persist a database and reuse a connection

Everything above used the throwaway in-memory database. When you want tables to survive between runs, open a file-backed connection.

import duckdb

con = duckdb.connect("analytics.duckdb")

con.sql("CREATE TABLE IF NOT EXISTS requests AS SELECT * FROM 'requests.parquet'")
con.sql("INSERT INTO requests SELECT * FROM 'requests.parquet'")

con.sql("SELECT service, count(*) AS n FROM requests GROUP BY service").show()
con.close()

The analytics.duckdb file now holds the table, indexes, and any views you create, all in a single file you can copy or check into cold storage. The connection object also carries the relational API, so you can build queries in Python without string concatenation:

con = duckdb.connect("analytics.duckdb")

rel = (
    con.table("requests")
       .filter("status = 200")
       .aggregate("service, count(*) AS ok")
       .order("ok DESC")
)
rel.show()

Each method returns a new relation, and nothing executes until .show() runs. I lean on the string-SQL form for anything I’d want to paste into a SQL console later, and the relational form when the query is assembled from user input and I want DuckDB to handle the composition safely.

Pass parameters instead of building SQL strings

Never build SQL with f-strings around user input. DuckDB supports parameter placeholders, so pass values separately and let the driver handle escaping.

con = duckdb.connect("analytics.duckdb")

threshold = 400
con.execute(
    "SELECT day, service, status FROM requests WHERE status >= ? ORDER BY day",
    [threshold],
).fetchall()

The ? placeholder is positional; DuckDB also accepts named parameters with $name if you prefer readability over brevity. Either way, the value never touches the SQL string, which closes the injection hole an f-string would open.

Write a Python UDF and call it from SQL

Sometimes SQL can’t express the transformation and you’d rather not drop back to a Pandas apply. DuckDB lets you register a plain Python function as a scalar UDF and call it from inside a query.

import duckdb
from duckdb.typing import VARCHAR

con = duckdb.connect()

def redact(email: str) -> str:
    name, _, domain = email.partition("@")
    return f"{name[0]}***@{domain}"

con.create_function("redact", redact, [VARCHAR], VARCHAR)

con.sql("""
    SELECT redact(email) AS masked
    FROM (VALUES ('ada@dev.io'), ('linus@kernel.org')) AS t(email)
""").show()

┌──────────────────┐
│      masked      │
│     varchar      │
├──────────────────┤
│ a***@dev.io      │
│ l***@kernel.org  │
└──────────────────┘

The function runs in Python, row by row, so it’s slower than native SQL. Reserve UDFs for logic that genuinely can’t be written in SQL. For a masking rule like this, that tradeoff is fine; for arithmetic you could express with operators, stay in SQL and keep the vectorized engine doing the work.

Read remote Parquet without downloading it

The httpfs extension teaches DuckDB to read files over HTTP and from S3-style object storage. Install and load it once, then a URL works anywhere a path would.

import duckdb

con = duckdb.connect()
con.sql("INSTALL httpfs")
con.sql("LOAD httpfs")

con.sql("""
    SELECT column_name, column_type
    FROM (DESCRIBE SELECT * FROM read_parquet('https://duckdb.org/data/prices.parquet'))
    LIMIT 5
""").show()

DuckDB fetches only the byte ranges it needs thanks to Parquet’s columnar layout and HTTP range requests, so a DESCRIBE or a LIMIT 5 pulls kilobytes rather than the whole file. For private S3 buckets you set credentials with a CREATE SECRET statement and then query s3://bucket/key.parquet the same way. This is the httpfs trick I reach for most on real jobs: querying an export on S3 without a download-then-load dance.

When DuckDB is the wrong tool

It isn’t a transactional database. If your workload is thousands of tiny concurrent writes from a web app, that’s SQLite or Postgres territory, not DuckDB. It’s also single-node — for genuinely distributed compute across a cluster you’re back to Spark or a cloud warehouse. And for small data that fits comfortably in a DataFrame with transformations you already know in Pandas, adding SQL to the mix can be more ceremony than it’s worth. DuckDB shines in the middle band: analytical queries over data that’s awkwardly large for Pandas but doesn’t justify distributed infrastructure. If serialization speed rather than querying is your bottleneck, that’s a different problem — I looked at it in msgspec vs Pydantic.

FAQ

Is DuckDB faster than Pandas?

For analytical queries over large data, usually yes. DuckDB uses a vectorized, multi-threaded, columnar engine and can process data larger than memory, while Pandas is largely single-threaded and materializes everything in RAM. DuckDB’s own published benchmarks report large speedups on aggregation-heavy workloads. For small DataFrames and quick wrangling, the gap disappears and Pandas is often more convenient.

Can DuckDB replace Pandas?

Not entirely, and it isn’t meant to. DuckDB is a complement: run the heavy filtering, joining, and aggregation in SQL, then hand the result back to Pandas with .df() for the Python-native steps. Many pipelines keep both, using each where it’s strongest.

Does DuckDB use SQL?

Yes. DuckDB speaks a PostgreSQL-flavored SQL dialect with analytical extras like QUALIFY, list comprehensions, and friendly GROUP BY ALL. If you know Postgres SQL, you already know most of DuckDB.

Can DuckDB query a Pandas DataFrame directly?

Yes — assign the DataFrame to a variable and reference that name in your SQL. DuckDB’s replacement scans read the object from your local Python scope with no explicit conversion, and the read is zero-copy where the memory layout allows.

Do I need to run a server for DuckDB?

No. DuckDB runs entirely inside your Python process, like SQLite. There’s nothing to start, no port to open, and no daemon to manage. pip install duckdb and import duckdb is the whole setup.

Is DuckDB free?

Yes. DuckDB is open source under the MIT license, developed by DuckDB Labs and the DuckDB Foundation. There’s no paid tier for the engine itself; the managed cloud product MotherDuck is a separate, optional service.

Sources

• DuckDB Python API documentation — the official reference for duckdb.sql, connect, relations, and conversions
• DuckDB documentation home — SQL dialect, file readers, and extension guides
• DuckDB on GitHub — source, MIT license, and release notes
• DuckDB httpfs extension docs — reading Parquet over HTTP and S3
• Introducing DuckDB — Real Python — independent walkthrough for corroboration

Bottom line

DuckDB earns its import by removing steps. There’s no server to run, no load-then-query dance for files, and no conversion to move data between SQL and DataFrames. The mental model is small: everything is a table source, queries are lazy, and results come back in whatever shape the next line of Python wants. Start by pointing a SELECT at a CSV, keep the pieces of your Pandas pipeline that already work, and let SQL take over the parts that were fighting you. After a year of using it that way, I haven’t found the ceiling in day-to-day analytics work.