Installation To use the DuckDB Python client, visit the Python installation page.

The latest stable version of the DuckDB Python client is 2.0.0.

Installation

The DuckDB Python API can be installed using pip: pip install duckdb. Please see the installation page for details. It is also possible to install DuckDB using conda: conda install python-duckdb -c conda-forge.

Python version: DuckDB requires Python 3.11 or newer. Free-threaded builds of CPython are not supported.

If you are upgrading from DuckDB 1.x, see Migrating Python Code to DuckDB 2.0.

Basic API Usage

The most straight-forward manner of running SQL queries using DuckDB is using the duckdb.sql command.

import duckdb

duckdb.sql("SELECT 42").show()

This runs the query on an in-memory database that is stored globally inside the Python module. The first call that needs this database creates it.

duckdb.sql() returns a relation: an object you can query further or convert to Python data. A query that only reads data runs when you fetch or display its result. Reading it again runs the query again. Use duckdb.execute() for statements such as CREATE TABLE and INSERT. See Lazy Evaluation for details.

Relations can be referenced in subsequent queries by storing them inside variables, and using them as tables. This way queries can be constructed incrementally.

import duckdb

r1 = duckdb.sql("SELECT 42 AS i")
duckdb.sql("SELECT i * 2 AS k FROM r1").show()

Data Input

DuckDB can ingest data from a wide variety of formats – both on-disk and in-memory. See the data ingestion page for more information.

import duckdb

duckdb.read_csv("example.csv")                # read a CSV file into a Relation
duckdb.read_parquet("example.parquet")        # read a Parquet file into a Relation
duckdb.read_json("example.json")              # read a JSON file into a Relation

duckdb.sql("SELECT * FROM 'example.csv'")     # directly query a CSV file
duckdb.sql("SELECT * FROM 'example.parquet'") # directly query a Parquet file
duckdb.sql("SELECT * FROM 'example.json'")    # directly query a JSON file

DataFrames

DuckDB can directly query Pandas DataFrames, Polars DataFrames and Arrow tables. Note that these are read-only, i.e., editing these tables via INSERT or UPDATE statements is not possible.

Pandas

To directly query a Pandas DataFrame, run:

import duckdb
import pandas as pd

pandas_df = pd.DataFrame({"a": [42]})
duckdb.sql("SELECT * FROM pandas_df")

┌───────┐
│   a   │
│ int64 │
├───────┤
│    42 │
└───────┘

Polars

To directly query a Polars DataFrame, run:

import duckdb
import polars as pl

polars_df = pl.DataFrame({"a": [42]})
duckdb.sql("SELECT * FROM polars_df")

┌───────┐
│   a   │
│ int64 │
├───────┤
│    42 │
└───────┘

PyArrow

To directly query a PyArrow table, run:

import duckdb
import pyarrow as pa

arrow_table = pa.Table.from_pydict({"a": [42]})
duckdb.sql("SELECT * FROM arrow_table")

┌───────┐
│   a   │
│ int64 │
├───────┤
│    42 │
└───────┘

Result Conversion

DuckDB supports converting query results efficiently to a variety of formats. See the result conversion page for more information.

import duckdb

duckdb.sql("SELECT 42").fetchall()        # Python objects
duckdb.sql("SELECT 42").df()              # Pandas DataFrame
duckdb.sql("SELECT 42").pl()              # Polars DataFrame
duckdb.sql("SELECT 42").to_arrow_table()  # Arrow Table
duckdb.sql("SELECT 42").to_arrow_reader() # Arrow RecordBatchReader
duckdb.sql("SELECT 42").fetchnumpy()      # NumPy Arrays

Writing Data to Disk

DuckDB supports writing Relation objects directly to disk in a variety of formats. The COPY statement can be used to write data to disk using SQL as an alternative.

import duckdb

duckdb.sql("SELECT 42").write_parquet("out.parquet")  # Write to a Parquet file
duckdb.sql("SELECT 42").write_csv("out.csv")          # Write to a CSV file
duckdb.execute("COPY (SELECT 42) TO 'out.parquet'")   # Copy to a Parquet file

Connection Options

Applications can open a new DuckDB connection via the duckdb.connect() method.

Using an In-Memory Database

When using DuckDB through duckdb.sql(), it operates on an in-memory database, i.e., no tables are persisted on disk. Invoking the duckdb.connect() method without arguments returns a connection, which also uses an in-memory database:

import duckdb

con = duckdb.connect()
con.sql("SELECT 42 AS x").show()

Persistent Storage

The duckdb.connect(dbname) creates a connection to a persistent database. Any data written to that connection will be persisted, and can be reloaded by reconnecting to the same file, both from Python and from other DuckDB clients.

import duckdb

# create a connection to a file called 'file.db'
con = duckdb.connect("file.db")
# create a table and load data into it
con.execute("CREATE TABLE test (i INTEGER)")
con.execute("INSERT INTO test VALUES (42)")
# query the table
con.table("test").show()
# explicitly close the connection
con.close()
# Note: connections also closed implicitly when they go out of scope

When you reconnect with a context manager, the connection is closed at the end of the with block:

import duckdb

with duckdb.connect("file.db") as con:
    con.execute("INSERT INTO test VALUES (43)")
    con.table("test").show()
    # the context manager closes the connection automatically

Configuration

The duckdb.connect() method takes three arguments: database, read_only and config. Settings go into the config dictionary, where configuration options can be specified. For example:

import duckdb

con = duckdb.connect(config={"threads": 1})

Besides its worker threads, each database starts a pool of asynchronous I/O threads: by default four times the number of CPU cores, at most 256. To start fewer, set async_threads:

con = duckdb.connect(config={"threads": 4, "async_threads": 4})

To specify the storage version, pass the storage_compatibility_version option. DuckDB 1.5 cannot open a database file that DuckDB 2.0 wrote in its default format. To keep a file readable by DuckDB 1.5, write it with v1.5.0:

con = duckdb.connect("compatible.db", config={"storage_compatibility_version": "v1.5.0"})

Connection Object and Module

Nearly every method of a connection object is also a function of the duckdb module, which runs it on a global in-memory database. A few differ: duckdb.df(frame) builds a relation from a DataFrame, as con.from_df(frame) does, while con.df() fetches the result of execute(); duckdb.arrow(obj) builds a relation from an Arrow object; and description and rowcount are functions of the module but properties of a connection.

If you are developing a package designed for others to use, and use DuckDB in the package, it is recommended that you create connection objects instead of using the methods on the duckdb module. That is because the duckdb module uses a shared global database, which can cause hard to debug issues if used from within multiple different packages.

Using Connections in Parallel Python Programs

A connection runs one call at a time. A call from another thread while the connection is busy does not wait: it raises a ResourceInUseException saying connection is busy with another call; it refuses instead of waiting, so use a separate connection from cursor().

duckdb.sql(), the other functions of the duckdb module and duckdb.connect(":default:") all use one shared default connection, so the same holds for them. To run queries in parallel, give each thread its own cursor:

import threading

import duckdb

con = duckdb.connect()
con.execute("CREATE TABLE numbers AS SELECT range AS n FROM range(1000)")


def count_even(cursor, results):
    results.append(cursor.sql("SELECT count(*) FROM numbers WHERE n % 2 = 0").fetchall())


results = []
threads = [threading.Thread(target=count_even, args=(con.cursor(), results)) for _ in range(4)]
for thread in threads:
    thread.start()
for thread in threads:
    thread.join()
print(results)

[[(500,)], [(500,)], [(500,)], [(500,)]]

About cursor()

The DuckDBPyConnection.cursor() method opens a new connection to the same database. A cursor has its own transaction and its own session settings, such as search_path, so cursors can run queries in parallel. A global setting, such as threads or memory_limit, applies to the whole database, also when a cursor sets it. Closing a connection closes its cursors.

Code that runs inside a query, such as a Python function called from SQL, cannot use the connection that runs the query: the call raises an error saying code running inside a query cannot use that query's connection; use a separate connection from cursor(). Let the function use a cursor instead.

Loading and Installing Extensions

The install_extension() and load_extension() methods install and load an extension, as the INSTALL and LOAD statements do. Many core extensions are installed and loaded automatically when a query uses them.

Community Extensions

To load community extensions, use the repository="community" argument with the install_extension method.

For example, install and load the h3 community extension as follows:

import duckdb

con = duckdb.connect()
con.install_extension("h3", repository="community")
con.load_extension("h3")

Unsigned Extensions

To load unsigned extensions, use:

con = duckdb.connect(config={"allow_unsigned_extensions": "true"})

Warning Only load unsigned extensions from sources you trust. Avoid loading unsigned extensions over HTTP. Consult the Securing DuckDB page for guidelines on how to set up DuckDB in a secure manner.