DuckDB-Engine

0.17.0 · active · verified Fri Apr 10

duckdb-engine is an SQLAlchemy driver for DuckDB, a high-performance analytical in-process SQL database system. It enables Python applications to interact with DuckDB databases using SQLAlchemy's ORM and SQL Expression Language. Currently at `v0.17.0`, it receives frequent updates addressing bug fixes and introducing features like filesystem registration.

Warnings

breaking Python 3.8 support was dropped in `v0.16.0`. Attempting to use `duckdb-engine` with Python 3.8 will fail.
Fix: Upgrade your Python environment to version 3.9 or newer.
gotcha Prior to `v0.15.1`, there were potential panics in multi-threaded environments. DuckDB generally follows a single-writer, multiple-reader concurrency model.
Fix: Ensure `duckdb-engine` is `v0.15.1` or newer. For concurrent operations, particularly writes, ensure each thread uses its own distinct connection (or cursor from a connection pool) to the database.
gotcha SQLAlchemy's `SERIAL` datatype, typically used for auto-incrementing primary keys in PostgreSQL, is not directly supported by DuckDB, which can lead to issues with ORM-generated schemas.
Fix: For auto-incrementing ID columns, explicitly use `sqlalchemy.Sequence()` in your model definitions.
gotcha The `duckdb-engine` dialect is derived from PostgreSQL, and as such, SQLAlchemy may attempt to use PostgreSQL-only features not supported by DuckDB's SQL parser.
Fix: Consult the official DuckDB documentation for supported SQL features and adjust your SQLAlchemy ORM or SQL Expression Language constructs accordingly to avoid unsupported syntax.
gotcha Division operations might produce unexpected results due to default casting behavior (`div_is_floordiv=True` by default) prior to `v0.14.1`.
Fix: Upgrade to `duckdb-engine` `v0.14.1` or newer, which sets `div_is_floordiv` to `False` by default, ensuring more predictable division casts.
gotcha Connecting to MotherDuck databases requires specific connection string parameters, including an optional `motherduck_token` for authentication.
Fix: Use the URI format `duckdb:///md:<my_database>?motherduck_token=<my_token>`. It is recommended to manage `MOTHERDUCK_TOKEN` as an environment variable rather than hardcoding.

Install

pip install duckdb-engine Pip
conda install duckdb-engine -c conda-forge Conda

Imports

create_engine
```
from sqlalchemy import create_engine
```

Quickstart

This quickstart demonstrates how to create an in-memory DuckDB database using `duckdb-engine` with SQLAlchemy, create a table, insert data, and query it.

from sqlalchemy import create_engine, text

# Connect to an in-memory DuckDB database
engine = create_engine("duckdb:///:memory:")

with engine.connect() as connection:
    # Execute a simple DDL statement
    connection.execute(text("CREATE TABLE users (id INTEGER, name VARCHAR)"))
    connection.commit()

    # Execute an INSERT statement
    connection.execute(text("INSERT INTO users (id, name) VALUES (:id, :name)"), {"id": 1, "name": "Alice"})
    connection.execute(text("INSERT INTO users (id, name) VALUES (:id, :name)"), {"id": 2, "name": "Bob"})
    connection.commit()

    # Execute a SELECT statement and fetch results
    result = connection.execute(text("SELECT id, name FROM users"))
    for row in result:
        print(f"ID: {row.id}, Name: {row.name}")

view raw JSON →