Skip to main content

SQLAlchemy with DuckDB and MotherDuck

SQLAlchemy is a SQL toolkit and Object-Relational Mapping (ORM) system for Python, providing full support for SQL expression language constructs and various database dialects. A lot of Business Intelligence tools supports SQLAlchemy out of the box.

Using the DuckDB SQLAlchemy driver we can connect to MotherDuck using an SQLAlchemy URI.

Install the DuckDB SQLAlchemy driver​

pip install --upgrade duckdb-engine

Configuring the database connection to a local DuckDB database​

A local DuckDB database can be accessed using the SQLAlchemy URI:

duckdb:///path/to/file.db

Configuring the database connection to MotherDuck​

The general pattern for the SQLAlchemy URI to access a MotherDuck database is:

duckdb:///md:<my_database>?motherduck_token=<my_token>
info

The database name <my_database> in the connection string is optional. This makes it possible to query multiple databases with one connection to MotherDuck.

Connecting and authentication can be done in several ways:

  1. If no token is available, the process will direct you to a web login for authentication, which will allow you to obtain a token.
from sqlalchemy import create_engine, text

eng = create_engine("duckdb:///md:my_db")

with eng.connect() as conn:
    result = conn.sql(text("show databases"))
    for row in result:
        print(row)

When running the above, you will see something like this to authenticate:

motherduck login

  1. The MOTHERDUCK_TOKEN is already set as environment variable
from sqlalchemy import create_engine, text

eng = create_engine("duckdb:///md:my_db")

with eng.connect() as conn:
    result = conn.sql(text("show databases"))
    for row in result:
        print(row)
  1. Using configuration dictionary
from sqlalchemy import create_engine, text

config = {}
token = 'asdfwerasdf' # Fill in your token
config["motherduck_token"] = token;
eng = create_engine(
    "duckdb:///md:my_db",
    connect_args={ 'config': config}
)

with eng.connect() as conn:
    result = conn.sql(text("show databases"))
    for row in result:
        print(row)
  1. Passing the token as a connection string parameter
from sqlalchemy import create_engine, text

token = 'asdfwerasdf' # Fill in your token
eng = create_engine(f"duckdb:///md:my_db?motherduck_token={token}")

with eng.connect() as conn:
    result = conn.sql(text("show databases"))
    for row in result:
        print(row)