Duckdb Python

DuckDB Python

Produce explicit, injection-safe DuckDB integrations whose connection, transaction, relation, input registration, result representation, and execution boundary match the host application.

Boundary

Use this skill when Python embeds DuckDB or invokes its Python client. SQL semantics are in scope only for a DuckDB execution target. Do not introduce DuckDB into a library-neutral dataframe task, and do not apply this skill to a remote database just because its SQL resembles DuckDB.

Know the runtime objects

The module-level duckdb.sql() family uses a shared global connection. A relation is lazy in the sense that it represents a query until a fetch, display, write, or catalog-producing action, but not every relation construction is free of metadata work. A cursor created from a connection is another handle on the same connection: it can be thread-local, but queries through sibling handles serialize instead of running concurrently. Read the connection and relation model before changing lifetimes, transactions, registration, or concurrency.

Ordered workflow

1. Recover the contract: database location, read/write mode, caller-owned or callee-owned connection, transaction boundary, input sources, result object, expected cardinality/order, and memory limit.
2. In reusable code, accept or create an explicit connection. Avoid module-level query state. For parallel query execution, give each worker an independent connection; sharing one explicit connection or its cursor handles serializes queries and must be an intentional policy.
3. Parameterize values. For identifiers or SQL structure, use a fixed mapping or validated AST/building path; placeholders do not quote identifiers.
4. Choose SQL or the relational API for clarity, then keep one connection and one deferred query until the required output boundary.
5. Push filters and projections into file/table scans; avoid materializing an input dataframe or full query result merely to continue processing.
6. Choose fetchone/fetchall, Arrow batches/table, pandas, Polars, NumPy, file output, table, or view from the consumer contract.
7. Test values, types, nulls, cardinality, deterministic ordering, transaction behavior, and cleanup. Inspect EXPLAIN only for a plan claim.

Choose by intent

Read the operation map for connection, query, ingestion, export, extension, and plan choices.

Canonical package boundary

from collections.abc import Sequence
from pathlib import Path

import duckdb


SUMMARY_SQL = """
    SELECT customer_id, sum(amount) AS total_amount
    FROM read_parquet(?)
    WHERE amount >= ?
    GROUP BY customer_id
    ORDER BY total_amount DESC, customer_id ASC
"""


def customer_totals(
    parquet_path: Path,
    minimum_amount: float,
) -> Sequence[tuple[object, ...]]:
    with duckdb.connect(database=":memory:") as connection:
        return connection.execute(
            SUMMARY_SQL,
            [str(parquet_path), minimum_amount],
        ).fetchall()

The function owns and closes its connection, treats path and threshold as values, and defines result ordering including a tie-breaker. If the result is not proven small, change the public return contract to a batched Arrow reader or write boundary; do not retain fetchall() and hope memory is sufficient.

High-risk rules

Connections, transactions, and threads

• Use explicit connection objects in libraries. Module-level calls share hidden state and can collide across packages or threads.
• One explicit connection is thread-safe in the current DB-API, but it locks for each query. Its .cursor() handles share that connection and therefore serialize. Give each worker an independent connection when actual parallel execution is required; use thread-local cursors only when shared-database, serialized access is the intended contract.
• State who owns each connection. A helper must not close an injected connection and must not return a relation tied to a connection it just closed.
• Wrap dependent writes in an explicit transaction and test rollback. Do not mix irreversible external file effects into a database rollback claim.
• Configure access mode, filesystem/network policy, memory, threads, and extension behavior at the intended scope rather than mutating a global default invisibly.

SQL, names, and Python inputs

• Bind data values through parameters. Validate dynamic identifiers against a closed mapping; never use f-strings for untrusted SQL.
• Replacement scans can resolve Python variable names from scope. Prefer explicit registration in reusable code so dependencies and lifetime are visible, then unregister in a finally block when the connection continues.
• A registered dataframe/Arrow object is not automatically copied into a durable table. Keep its owner alive until the query is consumed.
• Never load unsigned or remote extensions merely to satisfy a prompt. Require explicit trust and environment policy; pin or inspect extension availability.

Results and performance

• A relation or executed query becomes useful only at a consumer boundary. Choose the narrowest representation the consumer accepts and avoid dataframe round trips between DuckDB operations.
• SQL result order is contractual only with ORDER BY. Add stable tie-breakers when exact order matters.
• DuckDB can push projections and filters into Parquet scans, but prove the specific plan with EXPLAIN or profiling when performance is a requirement.
• CREATE TABLE AS copies data into DuckDB; a view over an external scan does not. Choose persistence intentionally and test reopening when required.
• Schema unification across files can hide drift or insert nulls. Enable it only under an explicit union contract and test missing, extra, reordered, and incompatible columns.

Run the installed-API inspector and read version and API grounding. Test with the DuckDB verification matrix.

Completion gate

Do not declare completion until connection ownership and cleanup are tested; values are parameterized and dynamic identifiers allowlisted; required writes are atomic at the declared boundary; returned relations/readers do not outlive their connection or source; result columns, DuckDB types, null behavior, cardinality, and explicit order match the contract; large-result handling is bounded; persisted output is reopened; and plan/performance claims have direct evidence. Report unavailable extensions, skipped integration checks, and their consequences.

References

• Query and result recipes
• Resource and persistence recipes
• Connection and relation model
• Operation map
• Version and API grounding
• Testing DuckDB integrations