DuckDB with Python lets you run SQL directly against CSV files, Parquet datasets, and DataFrames without installing or managing a database server. The short answer is: install duckdb, create a connection, and query a source through read_csv, read_parquet, or a registered DataFrame. It fits local analytics, notebooks, data pipelines, and scripts that need SQL semantics alongside Python libraries.

DuckDB is an embedded, column-oriented analytical database. Embedded means it runs inside your Python process. Analytical means its execution model favors scans, joins, and aggregations rather than high volumes of tiny concurrent transactions. For broader context, review our Python data science guide and the practical guide to data analysis with pandas.

Install and run the first query

Use an isolated environment and record dependency versions. Our Python venv guide explains the workflow. Install DuckDB plus pandas and PyArrow when your project needs those interchange formats:

python -m pip install duckdb pandas pyarrow

A connection with no file path is in memory. A context manager closes it even when execution raises an exception:

import duckdb

with duckdb.connect() as con:
totals = con.execute("""
SELECT region, sum(amount) AS revenue
FROM read_csv('data/sales.csv', header = true)
GROUP BY region
ORDER BY revenue DESC
""").fetchall()


print(totals)

fetchall() returns Python tuples. Use .df() for a pandas result or .arrow() for Arrow. Avoid materializing a large raw result when SQL can aggregate, filter, and project it first.

Query CSV with predictable types

read_csv can detect headers, delimiters, and data types, but inference is not a data contract. Mixed columns, ambiguous dates, and identifiers with leading zeros are common failure modes. Stable production input deserves explicit options and types:

query = """
SELECT
    order_id,
    CAST(order_date AS DATE) AS order_date,
    amount
FROM read_csv(
    ?,
    header = true,
    delim = ',',
    columns = {
        'order_id': 'VARCHAR',
        'order_date': 'VARCHAR',
        'amount': 'DECIMAL(12,2)'
    }
)
WHERE amount >= ?
"""

with duckdb.connect() as con:
orders = con.execute(query, ['data/orders.csv', 100]).df()

Placeholders keep values separate from SQL text and prevent unsafe concatenation. Parameters work for paths and scalar values, not arbitrary table or column names; dynamic identifiers should come from a strict allowlist. For Python-side file concepts, see the CSV and file handling guide.

Why Parquet works well with DuckDB

Parquet stores values by column and carries useful statistics. A query selecting three columns can avoid decoding unrelated columns, while predicates may skip row groups. DuckDB's official Parquet documentation describes projection and filter pushdown.

with duckdb.connect() as con:
    monthly = con.execute("""
        SELECT date_trunc('month', sold_at) AS month,
               category,
               avg(amount) AS average_order
        FROM read_parquet('lake/sales/**/*.parquet', hive_partitioning = true)
        WHERE sold_at >= DATE '2026-01-01'
        GROUP BY ALL
        ORDER BY month, category
    """).df()

The glob combines files that share a logical schema. hive_partitioning exposes directory keys such as year=2026/month=07 as columns. Inspect schemas before combining batches. union_by_name = true can align evolving columns by name, but it cannot resolve incompatible business meanings.

Run SQL over DataFrames

DuckDB can resolve a pandas variable from Python scope. Explicit registration makes dependencies clearer, particularly inside application functions:

import pandas as pd
import duckdb

customers = pd.DataFrame({
'customer_id': [1, 2, 3],
'segment': ['business', 'consumer', 'business'],
})


with duckdb.connect() as con:
con.register('customers_df', customers)
result = con.execute("""
SELECT c.segment, count(*) AS orders
FROM read_parquet('data/orders.parquet') o
JOIN customers_df c USING (customer_id)
WHERE o.status = 'paid'
GROUP BY c.segment
""").df()
con.unregister('customers_df')

This joins a small in-memory dimension to Parquet facts without exporting the DataFrame. Do not mutate the object from another thread during a query. When the remaining operation belongs in array-oriented Python, the NumPy guide covers that layer.

Persistence, views, and exports

Pass a path, such as duckdb.connect('analytics.duckdb'), to retain database objects across runs. A view over files stores a query, not a copy. CREATE TABLE AS materializes a result in the database.

with duckdb.connect('analytics.duckdb') as con:
    con.execute("""
        CREATE OR REPLACE VIEW sales AS
        SELECT * FROM read_parquet('data/sales/*.parquet')
    """)
    con.execute("""
        COPY (
            SELECT category, sum(amount) AS total
            FROM sales GROUP BY category
        ) TO 'output/summary.parquet' (FORMAT PARQUET)
    """)

Wrap related mutations in a transaction when they must succeed or fail together. Do not casually share one connection across threads or processes. The official DuckDB Python API documentation covers connections and results, while the CSV overview lists reader options.

Common errors

  • Using SELECT * everywhere: it reads unnecessary data and couples code to every schema change. Project named columns.
  • Trusting CSV inference blindly: validate nulls, encoding, delimiters, dates, and identifier types.
  • Interpolating user input into SQL: bind scalar values and allowlist any dynamic identifiers.
  • Moving data to pandas too early: let DuckDB filter, join, and aggregate before materialization.
  • Treating DuckDB as an OLTP server: it is not a replacement for a multi-user transactional application database.
  • Assuming the working directory: resolve input and output paths from a known project base.

Reliable analytics checklist

  1. Create a virtual environment and lock or record dependency versions.
  2. Define expected schemas, date formats, decimal precision, and null rules.
  3. Project columns and filter rows before fetching a Python object.
  4. Bind external values and validate every dynamic SQL identifier.
  5. Check row counts, join cardinality, nulls, and duplicate keys.
  6. Choose memory for disposable work and a database file for persistence.
  7. Prefer Parquet output when preserving types and selective reads matters.
  8. Test empty files, malformed input, and schema changes explicitly.

DuckDB is a direct choice when your analytical data already lives in files or Python objects. Start with a narrow query, make paths and types explicit, and reduce data in the engine before returning it to pandas. The resulting workflow remains compact, reproducible, and straightforward to review.