---
sidebar_position: 3
title: SQLAlchemy with DuckDB and MotherDuck
sidebar_label: SQLAlchemy
description: Connect to MotherDuck using SQLAlchemy and the DuckDB SQLAlchemy driver for Python applications.
---

[SQLAlchemy](https://www.sqlalchemy.org/) 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](https://github.com/Mause/duckdb_engine) we can connect to MotherDuck using an SQLAlchemy URI.


## Install the DuckDB SQLAlchemy driver

```bash
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:

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

## Configuring the database connection to MotherDuck

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

```bash
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.

```python
from sqlalchemy import create_engine, text

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

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

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

![motherduck login](../img/sqlalchemy_auth.png)

2. The `MOTHERDUCK_TOKEN` is already set as environment variable

```python
from sqlalchemy import create_engine, text

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

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

3. Using configuration dictionary

```python
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.execute(text("show databases"))
    for row in result:
        print(row)
```


4. Passing the token as a connection string parameter

```python
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.execute(text("show databases"))
    for row in result:
        print(row)
```

:::info
While the DuckDB Python API has a `.sql()` method on the connection API, SQLAlchemy does not. However, they both share the `.execute()` function and concept. More info in the [SQLAlchemy connection documentation](https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Connection)
:::