DuckDB in-process database

1.5.1 · active · verified Sun Mar 29

DuckDB is an in-process SQL OLAP (Online Analytical Processing) database management system designed for fast analytical queries directly within your Python application. It operates without a separate server, integrating seamlessly with the Python data ecosystem like Pandas and Polars. It is actively maintained with frequent releases, currently at version 1.5.1, and focuses on efficient data handling for large datasets.

Warnings

breaking Python 3.9 support has been dropped with DuckDB Python v1.5.0. Users on Python 3.9 will encounter errors.
Fix: Upgrade your Python environment to version 3.10 or newer. DuckDB v1.5.0 requires Python >=3.10.0.
breaking The `duckdb.typing` and `duckdb.functional` modules were removed in v1.5.0, having been deprecated in v1.4.0.
Fix: Replace imports and usage of `duckdb.typing` with `duckdb.sqltypes`, and `duckdb.functional` with `duckdb.func`.
deprecated The methods `fetch_arrow_table()` and `fetch_record_batch()` on connections and relations have been deprecated.
Fix: Use the new `to_arrow_table()` and `to_arrow_reader()` methods instead for Arrow export APIs.
gotcha DuckDB's persistent storage format is not stable across major/minor versions prior to v1.0. Upgrading DuckDB can lead to `IOException` when trying to read older database files.
Fix: If you encounter this, load the old database file with the DuckDB version that created it, `EXPORT DATABASE` to a new location, then `IMPORT DATABASE` with the newer DuckDB version. After DuckDB v0.10, the storage format is backwards-compatible.
gotcha The `column` parameter in relational API functions (e.g., `min`, `max`, `sum`) was renamed to `expression` to better reflect that it accepts expressions, not just column names.
Fix: Update calls to these relational API functions to use `expression` instead of `column`.
deprecated The lambda arrow syntax `x -> x + 1` in SQL queries is deprecated in v1.5.0 and will emit a warning.
Fix: Transition to the new Python-style lambda syntax: `lambda x: x + 1`. You can configure `lambda_syntax` to change behavior.

Install

pip install duckdb Install DuckDB Python Package

Imports

duckdb
```
import duckdb
```
While 'from duckdb import connect' works, 'import duckdb' is more common as many functions (like duckdb.sql) operate on a default in-memory connection if no explicit connection object is created.
duckdb.sqltypes
```
from duckdb import sqltypes
```
The `duckdb.typing` module was deprecated in 1.4.0 and removed in 1.5.0; use `duckdb.sqltypes` instead for type definitions.
duckdb.func
```
from duckdb import func
```
The `duckdb.functional` module was deprecated in 1.4.0 and removed in 1.5.0; use `duckdb.func` instead for functional APIs.

Quickstart

This quickstart demonstrates how to connect to an in-memory DuckDB database, execute SQL queries, insert data, and retrieve results as a Pandas DataFrame. It also shows the convenience of using the default global in-memory database directly via `duckdb.sql()` for quick operations. For persistent storage, specify a file path in `duckdb.connect()`.

import duckdb

# Connect to an in-memory database (data is lost after session)
con = duckdb.connect(database=':memory:')

# Execute a SQL query and show results
result = con.sql("SELECT 42 AS answer").show()

# Create a table and insert data
con.execute("CREATE TABLE my_table (id INTEGER, name VARCHAR)")
con.execute("INSERT INTO my_table VALUES (1, 'Alice'), (2, 'Bob')")

# Query the table and fetch results as a Pandas DataFrame
df_result = con.sql("SELECT * FROM my_table WHERE id = 1").df()
print(df_result)

# Example of using the default global in-memory database
df_global = duckdb.sql("SELECT 'Hello, DuckDB!' AS message").df()
print(df_global)

view raw JSON →