PyPIDataupdated 05 Aug 2026

duckdb

Item: duckdb
Rating: 4.3
Author: Keyoor

DuckDB is an in-process analytical (OLAP) database: SQLite's deployment model with a columnar, parallel engine built for aggregation-heavy queries. The duckdb PyPI package embeds the whole engine in your Python process with zero external dependencies. You can run SQL directly against Parquet, CSV, and JSON files (local or on S3), query pandas and Polars dataframes by variable name, and get results back as dataframes or Arrow tables. It handles larger-than-memory workloads by spilling to disk, which makes it the standard 'bigger than pandas, smaller than a warehouse' tool.

duckdb · v1.5.5 · 14.3M/wk · ★ 174 · pushed 30 Jul 2026 · duckdb/duckdb-python · docs · reviewed by Keyoor

Verdict

The best tool available for local and embedded analytics on files and dataframes, MIT-licensed and foundation-owned, with an active repo (pushed July 30, 2026). Know its lane: one writer, one process, analytics; it is not a replacement for a server database.

API stability4/5Post-1.0 the SQL dialect and Python API have been steady, but storage-format compatibility across versions has been a historical pain point and version pinning across a team is still advisable.

Docs4/5duckdb.org docs are thorough with a dedicated Python client section and guides for pandas/Arrow interop; some advanced relational-API corners are thinly documented compared to the SQL reference.

Maintenance5/5Backed by DuckDB Labs with IP held by the DuckDB Foundation; the Python client moved to its own actively developed repo (pushed July 30, 2026) and releases track the engine closely.

Ecosystem4/514M weekly PyPI downloads, first-class pandas/Polars/Arrow interop, community extensions, and adoption in tools like dbt and MotherDuck; smaller third-party tooling than Postgres-world offers.

Use it if

You analyze Parquet/CSV files that are painful in pandas; DuckDB queries them in place, in parallel, without loading everything into memory
You want real SQL (window functions, joins, aggregates) over dataframes already sitting in your Python session
You need a single-file analytical database you can ship with an app or notebook, with no server to run
You are building data pipelines that read from S3 and write Parquet; the httpfs extension and COPY TO cover both ends

Skip it if

Your workload is transactional (many small concurrent writes from multiple processes); DuckDB allows one read-write process per database file, so use SQLite or Postgres for OLTP
You need a shared database that several services connect to over the network; DuckDB is in-process by design and has no server mode
Your data comfortably fits in pandas or Polars and you have no SQL need; adding a database engine is a dependency you do not need
You are at true warehouse scale with many concurrent analysts; ClickHouse, BigQuery, or Snowflake exist for that shape of concurrency

Setup reality

pip install duckdb ships a prebuilt wheel with the whole engine inside; no server, no config, and duckdb.sql('select 42') works ten seconds later. Real friction points: extensions like httpfs for S3 are auto-installed on first INSTALL/LOAD, which needs network access and surprises locked-down CI; a database file written by a newer DuckDB may not open in an older one, so pin versions across a team; the single-writer rule means a second process (or a forgotten notebook kernel) holding the file gets a lock error; and the default connection is in-memory, so people lose tables by forgetting to pass a file path to connect().

Patterns

Run SQL with zero setupquery-in-memory

import duckdb

duckdb.sql("SELECT 42 AS answer").show()
rows = duckdb.sql("SELECT 1 AS a, 2 AS b").fetchall()  # [(1, 2)]

Module-level duckdb.sql uses a global in-memory connection; everything vanishes when the process exits.

Create a persistent database filepersistent-database

import duckdb

con = duckdb.connect("analytics.duckdb")
con.sql("CREATE TABLE IF NOT EXISTS events (id INT, ts TIMESTAMP, kind TEXT)")
con.sql("INSERT INTO events VALUES (1, now(), 'signup')")
con.close()

Only one process can hold the file for writing at a time; a second connect() from another process raises a lock error.

Query Parquet or CSV files without loading themquery-parquet-directly

import duckdb

df = duckdb.sql("""
    SELECT kind, count(*) AS n
    FROM read_parquet('logs/*.parquet')
    GROUP BY kind
    ORDER BY n DESC
""").df()

Globs work and only needed columns/row groups are read; read_csv similarly infers schema, with parameters to override when inference guesses wrong.

Run SQL over an existing pandas or Polars dataframequery-pandas-dataframe

import duckdb
import pandas as pd

orders = pd.DataFrame({"user": ["a", "b", "a"], "amt": [10, 20, 30]})

top = duckdb.sql("""
    SELECT user, sum(amt) AS total
    FROM orders
    GROUP BY user
    ORDER BY total DESC
""").df()

DuckDB finds the dataframe by scanning local Python variables for the name in the query (a replacement scan); no copy is made.

Use parameters instead of string formattingparameterized-query

import duckdb

con = duckdb.connect("analytics.duckdb")
con.execute(
    "SELECT * FROM events WHERE kind = ? AND ts > ?",
    ["signup", "2026-01-01"],
)
print(con.fetchall())

Prepared parameters work on execute(), not on duckdb.sql(); use them for anything user-supplied instead of f-strings.

Write query results to Parquetexport-to-parquet

import duckdb

con = duckdb.connect("analytics.duckdb")
con.sql("""
    COPY (SELECT * FROM events WHERE ts >= '2026-01-01')
    TO 'events_2026.parquet' (FORMAT parquet, COMPRESSION zstd)
""")

COPY TO also writes partitioned datasets with PARTITION_BY; it is the fastest path from DuckDB to files, no dataframe detour needed.

Get results as pandas, Polars, or Arrowresult-to-dataframe

import duckdb

rel = duckdb.sql("SELECT range AS x, range * 2 AS y FROM range(5)")
pdf = rel.df()      # pandas DataFrame
pldf = rel.pl()     # Polars DataFrame
tbl = rel.arrow()   # pyarrow Table

Each conversion consumes the result; call the query again (or fetch once) if you need multiple output formats.

Query Parquet directly on S3query-s3-files

import duckdb

con = duckdb.connect()
con.sql("INSTALL httpfs; LOAD httpfs;")
con.sql("""
    CREATE SECRET (TYPE s3, PROVIDER credential_chain)
""")  # picks up AWS env/config credentials
df = con.sql(
    "SELECT count(*) FROM read_parquet('s3://my-bucket/data/*.parquet')"
).df()

INSTALL downloads the extension on first use, so offline or firewalled environments must preinstall it; secrets replaced the old SET s3_* config style.

Build queries with the relational API instead of SQL stringsrelational-api

import duckdb

rel = duckdb.read_csv("sales.csv")
result = (
    rel.filter("amount > 100")
       .aggregate("region, sum(amount) AS total", "region")
       .order("total DESC")
       .df()
)

Relations are lazy; nothing executes until you call df(), fetchall(), or similar, so you can compose steps cheaply.

Load a file into a real tablecreate-table-from-file

import duckdb

con = duckdb.connect("analytics.duckdb")
con.sql("""
    CREATE OR REPLACE TABLE sales AS
    SELECT * FROM read_csv('sales.csv')
""")
con.sql("SELECT count(*) FROM sales").show()

CREATE TABLE AS copies data into DuckDB's own columnar storage; querying the file directly each time is fine too and avoids the duplicate copy.

Read and flatten JSONunnest-json

import duckdb

df = duckdb.sql("""
    SELECT j.user ->> 'id' AS user_id,
           unnest(j.items) AS item
    FROM read_json('events.json') AS j
""").df()

read_json infers nested structs and lists; ->> extracts text while -> keeps JSON, same operator split as Postgres.

Alternatives

Package	Registry	Pick it when
polars	PyPI	You prefer a dataframe API over SQL for the same fast columnar, larger-than-memory workloads
clickhouse-connect	PyPI	You need a shared OLAP server with many concurrent users rather than an embedded engine
pandas	PyPI	Data is small, the ecosystem matters more than speed, and every tutorial you follow assumes it