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.
pip install duckdb-engineVerified import paths — ran on the pinned version, not inferred.
This quickstart demonstrates how to create an in-memory DuckDB database using `duckdb-engine` with SQLAlchemy, create a table, insert data, and query it.
Upgrade your Python environment to version 3.9 or newer.
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.
For auto-incrementing ID columns, explicitly use `sqlalchemy.Sequence()` in your model definitions.
Consult the official DuckDB documentation for supported SQL features and adjust your SQLAlchemy ORM or SQL Expression Language constructs accordingly to avoid unsupported syntax.
Upgrade to `duckdb-engine` `v0.14.1` or newer, which sets `div_is_floordiv` to `False` by default, ensuring more predictable division casts.
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 both `duckdb` and `duckdb-engine` using pip: `pip install duckdb duckdb-engine`
This is a known incompatibility. Ensure you are using recent versions of `duckdb-engine` and `duckdb`. If the issue persists with `MetaData.reflect()`, consider selectively reflecting specific tables or columns, or defining your models directly rather than relying solely on reflection for problematic schemas. If it's related to auto-incrementing IDs, explicitly use `sqlalchemy.Sequence` (see the next problem).
When defining auto-incrementing primary keys in your SQLAlchemy models or table definitions, explicitly use `sqlalchemy.Sequence` instead of relying on the implicit `SERIAL` type mapping. For example:
```python
from sqlalchemy import Column, Integer, Sequence, create_engine, MetaData, Table
engine = create_engine('duckdb:///:memory:')
metadata = MetaData()
user_id_seq = Sequence('user_id_seq')
users_table = Table(
'users',
metadata,
Column('id', Integer, user_id_seq, server_default=user_id_seq.next_value(), primary_key=True),
Column('name', String)
)
metadata.create_all(bind=engine)
```Ensure the required DuckDB extensions (e.g., `httpfs`, `s3`, `azure`) are installed and loaded. For authenticated access to cloud storage, configure secrets using DuckDB's `CREATE SECRET` command or by passing appropriate connection arguments to `create_engine` that allow DuckDB to handle the authentication. For example, to preload an extension and configure a secret for S3:
```python
from sqlalchemy import create_engine
engine = create_engine(
'duckdb:///:memory:',
connect_args={
'preload_extensions': ['httpfs', 's3'],
'config': {
's3_region': 'your-s3-region',
's3_access_key_id': 'YOUR_ACCESS_KEY',
's3_secret_access_key': 'YOUR_SECRET_KEY'
}
}
)
# Alternatively, register fsspec filesystem and then use create secret in SQL for newer DuckDB versions
# import duckdb
# conn = duckdb.connect()
# conn.execute("INSTALL httpfs;")
# conn.execute("LOAD httpfs;")
# conn.execute("CREATE SECRET s3_secret (TYPE S3, KEY_ID 'YOUR_ACCESS_KEY', SECRET 'YOUR_SECRET_KEY', REGION 'your-s3-region');")
```