DuckDB Python API

Python is the most common way to embed DuckDB. The duckdb module mirrors the DB-API style used by sqlite3, adds a fluent relation API for iterative work, and integrates tightly with pandas, Arrow and numpy. Install the package from PyPI before using it:

pip install duckdb

Connecting to a Database

duckdb.connect() opens either an in-memory database or a persistent file. Connections are independent, so a script can use several databases at once:

import duckdb

con = duckdb.connect()                     # in-memory database
con = duckdb.connect("analytics.db")       # persistent .db file
con = duckdb.connect("analytics.db",
                     read_only=True)       # shared read access

A read_only connection is the standard way to let multiple processes query one database file concurrently.

Executing Queries and Fetching Results

con.execute() runs any SQL, and result objects expose fetchers for several formats. Use ? or named $param placeholders to pass values safely:

con.execute(
    "CREATE TABLE sales(id INTEGER, region VARCHAR, amount DOUBLE)"
)
con.executemany(
    "INSERT INTO sales VALUES (?, ?, ?)",
    [(1, "west", 120.0), (2, "east", 80.0)]
)

rows = con.execute("SELECT region, sum(amount) FROM sales GROUP BY 1").fetchall()
first = con.execute("SELECT * FROM sales LIMIT 1").fetchone()

df = con.execute("SELECT * FROM sales").df()      # pandas DataFrame
arr = con.execute("SELECT * FROM sales").arrow()  # pyarrow.Table

df = con.execute("SELECT * FROM sales WHERE amount > ?", [90]).df()
df = con.execute("SELECT * FROM sales WHERE region = $r",
                 {"r": "west"}).df()

The Relation API

Class-level helpers build lazy relations that can be transformed and re-read later without duplicating data:

rel = duckdb.sql("SELECT * FROM 'sales.csv'")      # lazy, nothing ran yet
big = rel.filter("amount > 100").project("region, amount")

agg = big.aggregate("region, sum(amount) AS total").sort("total DESC")
result = agg.execute()        # runs now
rel.to_parquet("out.parquet") # materialize to a file

duckdb.sql(), duckdb.table(), duckdb.read_csv() and duckdb.from_df() all return DuckDBPyRelation objects, whose methods return further relations. Nothing is computed until execute(), .df(), .arrow(), or fetchall() is called, and calling methods repeatedly does not re-read the source.

Registering and Managing Data

con.register() gives an external Python object, such as a DataFrame, a named handle queryable from SQL, and it can be unregistered when finished:

import pandas as pd
df = pd.DataFrame({"x": [1, 2, 3]})
con.register("my_view", df)
print(con.execute("SELECT avg(x) FROM my_view").fetchone())
con.unregister("my_view")

Management statements go through the usual SQL entry points: con.sql("SHOW TABLES") lists tables, con.install_extension("httpfs") plus con.load_extension("httpfs") manage extensions, and con.close() releases the connection.

Summary

The DuckDB Python package offers a complete embedding story: duckdb.connect() for in-memory, file-based or read-only databases, execute() with parameter binding and fetchers for tuples, pandas and Arrow, a lazy relation API for building queries step by step, and registration of Python objects as SQL views. The same API covers DDL, extension loading and bulk loading, so most Python data work needs nothing beyond the duckdb module.