duckdb

duckdb is the official Python package for DuckDB, a fast, in-process, columnar analytical database engine. It wraps DuckDB’s C++ core (vendored in this repo alongside the Python bindings) in a DB-API 2.0-compatible connection interface, while adding Python-specific ergonomics: zero-copy result fetching into Pandas, NumPy, Arrow, and Polars, a fluent Relation API for building queries programmatically, scalar/table Python UDFs, and a duckdb.experimental.spark compatibility layer for porting PySpark code.

As of mid-2025 this bindings/build repo was split out from the main duckdb/duckdb monorepo into its own dedicated repository so the Python client could iterate and release independently of the core engine, while still vendoring the C++ source needed to build the extension module. It remains the canonical source for the duckdb PyPI package and is actively maintained by the DuckDB Foundation.

What You Get

• An embedded, in-process analytical SQL engine with no server to install or manage — import duckdb is enough to run SQL
• Zero-copy or near-zero-copy interchange with Pandas, NumPy, PyArrow, and Polars dataframes for both querying and result fetching
• A fluent Relation API (duckdb.sql(...), .filter(), .aggregate(), .df()) for building queries programmatically instead of string concatenation
• Native readers/writers for Parquet, CSV, and JSON, including remote reads from S3 and other object stores via the httpfs/fsspec filesystem layer
• Python scalar and table user-defined functions (UDFs) callable directly from SQL
• A duckdb.experimental.spark compatibility layer that mimics PySpark’s DataFrame API on top of DuckDB’s engine
• An adbc_driver_duckdb package for Arrow Database Connectivity (ADBC) integration

Common Use Cases

• Running fast, local analytical SQL queries directly over Pandas/Polars dataframes or Parquet/CSV files without spinning up a database server
• Replacing ad-hoc Pandas transformation code with SQL for large or complex aggregations that benefit from a columnar, vectorized engine
• Querying data lakes (Parquet/CSV/JSON on S3 or local disk) as an embedded analytics layer inside a Python data pipeline or notebook
• Prototyping or migrating PySpark-style code locally via the duckdb.experimental.spark compatibility layer before deploying to a full Spark cluster

Under The Hood

Architecture - the repo pairs a Python package (duckdb/) with a C++ extension (src/pyconnection, src/pyrelation, src/pyresult, src/pyexpression) that bridges DuckDB’s core engine to Python objects; pyconnection.cpp implements the DB-API-style Connection, pyrelation.cpp implements the lazy, chainable Relation query-builder, and dedicated arrow/, pandas/, and numpy/ subdirectories under src/ implement the zero-copy conversion paths for each dataframe library. The pure-Python layer (duckdb/experimental/spark, duckdb/func, duckdb/udf.py) builds higher-level compatibility and UDF support on top of the C++ core rather than reimplementing engine logic. Tech Stack - built with scikit-build-core and CMake to compile the vendored C++ DuckDB engine into a Python extension module, targeting Python 3.11+ with optional dependencies (pandas, pyarrow, numpy, fsspec, ipython, adbc-driver-manager) gated behind the duckdb[all] extra so the core install stays dependency-free. Code Quality - the tests/ directory is split into fast/ and slow/ suites plus dedicated extensions/, coverage/, and spark_namespace/ subdirectories, indicating a deliberately tiered CI strategy; a .pre-commit-config.yaml and CLAUDE.md (repo-specific AI-agent guidance) point to an actively enforced contribution workflow with linting and formatting gates. API Design - the package layers three access patterns of increasing abstraction: raw DB-API execute()/fetchall() for SQL-first users, duckdb.sql() returning a chainable Relation for a query-builder style, and direct .df()/.arrow()/.pl() result methods for immediate dataframe interop — letting a new user run a query in one line while still exposing the lower-level connection object for advanced use.