duckdb_engine
Basic SQLAlchemy driver for DuckDB
Installation
$ pip install duckdb-engine
DuckDB Engine also has a conda feedstock available, the instructions for the use of which are available in it's repository.
Usage
Once you've installed this package, you should be able to just use it, as SQLAlchemy does a python path search
from sqlalchemy import Column, Integer, Sequence, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.session import Session
Base = declarative_base()
class FakeModel(Base): # type: ignore
__tablename__ = "fake"
id = Column(Integer, Sequence("fakemodel_id_sequence"), primary_key=True)
name = Column(String)
eng = create_engine("duckdb:///:memory:")
Base.metadata.create_all(eng)
session = Session(bind=eng)
session.add(FakeModel(name="Frank"))
session.commit()
frank = session.query(FakeModel).one()
assert frank.name == "Frank"
Usage in IPython/Jupyter
With IPython-SQL and DuckDB-Engine you can query DuckDB natively in your notebook! Check out DuckDB's documentation or Alex Monahan's great demo of this on his blog.
Configuration
You can configure DuckDB by passing connect_args to the create_engine function
create_engine(
'duckdb:///:memory:',
connect_args={
'read_only': False,
'config': {
'memory_limit': '500mb'
}
}
)
The supported configuration parameters are listed in the DuckDB docs
How to register a pandas DataFrame
conn = create_engine("duckdb:///:memory:").connect()
# with SQLAlchemy 1.3
conn.execute("register", ("dataframe_name", pd.DataFrame(...)))
# with SQLAlchemy 1.4+
conn.execute(text("register(:name, :df)"), {"name": "test_df", "df": df})
conn.execute("select * from dataframe_name")
Things to keep in mind
Duckdb's SQL parser is based on the PostgreSQL parser, but not all features in PostgreSQL are supported in duckdb. Because the duckdb_engine dialect is derived from the postgresql dialect, SQLAlchemy may try to use PostgreSQL-only features. Below are some caveats to look out for.
Auto-incrementing ID columns
When defining an Integer column as a primary key, SQLAlchemy uses the SERIAL datatype for PostgreSQL. Duckdb does not yet support this datatype because it's a non-standard PostgreSQL legacy type, so a workaround is to use the SQLAlchemy.Sequence() object to auto-increment the key. For more information on sequences, you can find the SQLAlchemy Sequence documentation here.
The following example demonstrates how to create an auto-incrementing ID column for a simple table:
>>> import sqlalchemy
>>> engine = sqlalchemy.create_engine('duckdb:////path/to/duck.db')
>>> metadata = sqlalchemy.MetaData(engine)
>>> user_id_seq = sqlalchemy.Sequence('user_id_seq')
>>> users_table = sqlalchemy.Table(
... 'users',
... metadata,
... sqlalchemy.Column(
... 'id',
... sqlalchemy.Integer,
... user_id_seq,
... server_default=user_id_seq.next_value(),
... primary_key=True,
... ),
... )
>>> metadata.create_all(bind=engine)
Pandas read_sql() chunksize
NOTE: this is no longer an issue in versions >=0.5.0 of duckdb
The pandas.read_sql() method can read tables from duckdb_engine into DataFrames, but the sqlalchemy.engine.result.ResultProxy trips up when fetchmany() is called. Therefore, for now chunksize=None (default) is necessary when reading duckdb tables into DataFrames. For example:
>>> import pandas as pd
>>> import sqlalchemy
>>> engine = sqlalchemy.create_engine('duckdb:////path/to/duck.db')
>>> df = pd.read_sql('users', engine) ### Works as expected
>>> df = pd.read_sql('users', engine, chunksize=25) ### Throws an exception
Unsigned integer support
Unsigned integers are supported by DuckDB, and are available in duckdb_engine.datatypes.
Alembic Integration
SQLAlchemy's companion library alembic can optionally be used to manage database migrations.
This support can be enabling by adding an Alembic implementation class for the duckdb dialect.
from alembic.ddl.impl import DefaultImpl
class AlembicDuckDBImpl(DefaultImpl):
"""Alembic implementation for DuckDB."""
__dialect__ = "duckdb"
After loading this class with your program, Alembic will no longer raise an error when generating or applying migrations.
Preloading extensions (experimental)
DuckDB 0.9.0+ includes builtin support for autoinstalling and autoloading of extensions, see the extension documentation for more information.
Until the DuckDB python client allows you to natively preload extensions, I've added experimental support via a connect_args parameter
from sqlalchemy import create_engine
create_engine(
'duckdb:///:memory:',
connect_args={
'preload_extensions': ['https'],
'config': {
's3_region': 'ap-southeast-1'
}
}
)
Registering Filesystems
DuckDB allows registering filesystems from fsspec, see documentation for more information.
Support is provided under connect_args parameter
from sqlalchemy import create_engine
from fsspec import filesystem
create_engine(
'duckdb:///:memory:',
connect_args={
'register_filesystems': [filesystem('gcs')],
}
)
The name
Yes, I'm aware this package should be named duckdb-driver or something, I wasn't thinking when I named it and it's too hard to change the name now
1 maintainer
Release files for duckdb-engine 0.17.0
For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.
Source distribution (sdist)
| File | Size | Uploaded | |
|---|---|---|---|
| duckdb_engine-0.17.0.tar.gz | 48.1 kB | Details |
Built distribution (wheel)
| File | Interpreter | ABI | Platform | Reset |
|---|---|---|---|---|
| duckdb_engine-0.17.0-py3-none-any.whl | Python 3 | none | any | Details |
Total release size: 97.7 kB
Release files / duckdb_engine-0.17.0.tar.gz
| Download URL | duckdb_engine-0.17.0.tar.gz |
|---|---|
| Size | 48.1 kB |
| Tags | Source |
|
SHA-256 checksum How to use checksums |
396b23869754e536aa80881a92622b8b488015cf711c5a40032d05d2cf08f3cf
|
|
BLAKE2b-256 checksum How to use checksums |
89d5c0d8d0a4ca3ffea92266f33d92a375e2794820ad89f9be97cf0c9a9697d0
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
twine/6.1.0 CPython/3.12.9
|
Provenance
Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.
PyPI Publish Attestation
PyPI verified that this artifact, at this checksum, originated from the publisher listed below.
Signed by GitHub Actions, verified by PyPI on Mar 29, 2025.
Transparency logRelease files / duckdb_engine-0.17.0-py3-none-any.whl
| Download URL | duckdb_engine-0.17.0-py3-none-any.whl |
|---|---|
| Size | 49.7 kB |
| Tags | Python 3 |
|
SHA-256 checksum How to use checksums |
3aa72085e536b43faab635f487baf77ddc5750069c16a2f8d9c6c3cb6083e979
|
|
BLAKE2b-256 checksum How to use checksums |
2aa2e90242f53f7ae41554419b1695b4820b364df87c8350aa420b60b20cab92
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
Yes |
| Uploaded via |
twine/6.1.0 CPython/3.12.9
|
Provenance
Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.
PyPI Publish Attestation
PyPI verified that this artifact, at this checksum, originated from the publisher listed below.
Signed by GitHub Actions, verified by PyPI on Mar 29, 2025.
Transparency log
