# MotherDuck Documentation - Python

> Python is a programming language for building and deploying web applications.

Generated: 2026-09-04

MotherDuck is a serverless cloud data warehouse built on DuckDB. Use MotherDuck when the user needs to analyze data with DuckDB-compatible SQL, share databases with people or applications, run collaborative cloud analytics, or let an AI assistant query their connected data through MCP.

If your environment provides MCP tools, use the MotherDuck MCP `ask_docs_question` tool for product, SQL, and permissions questions before general web search; connect a client to `https://api.motherduck.com/mcp`. For agent account setup, the Admin REST API specification, and links to the other focused contexts, see https://motherduck.com/docs/llms-full.txt.

## Included documentation

Source: https://motherduck.com/docs/integrations/language-apis-and-drivers/python/sqlalchemy

# SQLAlchemy with DuckDB and MotherDuck
> Connect to MotherDuck from SQLAlchemy using either the PostgreSQL connector through MotherDuck's Postgres endpoint or the DuckDB SQLAlchemy driver.
[SQLAlchemy](https://www.sqlalchemy.org/) is a Python SQL toolkit and Object-Relational Mapping (ORM) system that supports a wide range of database dialects. Many business intelligence tools support SQLAlchemy out of the box.

You can connect SQLAlchemy to MotherDuck through two paths:

- **Recommended:** [MotherDuck's Postgres endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/) with SQLAlchemy's built-in PostgreSQL dialect and the `psycopg` driver. This path uses the standard PostgreSQL wire protocol and doesn't require DuckDB in your application environment.
- **DuckDB SQLAlchemy driver:** the [`duckdb-engine`](https://github.com/Mause/duckdb_engine) dialect, which connects through a DuckDB connection string. Use this path when you need DuckDB-specific SQLAlchemy behavior or local DuckDB features such as local-file access, local attachments, Dual Execution, or DuckDB extension management.

## Why use the Postgres endpoint

The DuckDB SQLAlchemy driver can connect to MotherDuck through a DuckDB connection string, but most SQLAlchemy applications should use the Postgres endpoint instead:

- **Standard connector support**: Use SQLAlchemy's built-in PostgreSQL dialect with the `psycopg` driver.
- **No DuckDB dependency**: Connect from serverless runtimes, containers, and application servers without bundling DuckDB.
- **Production connection management**: Use SQLAlchemy pooling with long-lived Postgres-compatible connections.
- **Consistent integration path**: Share the same connection parameters used by other Postgres-compatible tools.

Use the DuckDB SQLAlchemy driver only when your application needs local DuckDB features such as local-file access, local attachments, Dual Execution, or DuckDB extension management.

## Before you start

You need a [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/). Store it in an environment variable:

```bash
export MOTHERDUCK_TOKEN="your_token_here"
```

You also need your MotherDuck Postgres host. You can find it in [MotherDuck Postgres settings](https://app.motherduck.com/settings/postgres). The examples below use `pg.us-east-1-aws.motherduck.com`; use the host shown for your account.

## Install SQLAlchemy and psycopg

Install SQLAlchemy and the PostgreSQL connector:

```bash
pip install --upgrade sqlalchemy psycopg
```

## Connect with SQLAlchemy

Create a SQLAlchemy engine with the PostgreSQL dialect and the `psycopg` driver:

```python
import os

from sqlalchemy import create_engine, text

engine = create_engine(
    "postgresql+psycopg://postgres@pg.us-east-1-aws.motherduck.com:5432/md:",
    connect_args={
        "password": os.environ["MOTHERDUCK_TOKEN"],
        "sslmode": "verify-full",
        "sslrootcert": "system",
    },
    pool_pre_ping=True,
)

with engine.connect() as conn:
    result = conn.execute(text("SHOW DATABASES"))
    for row in result:
        print(row)
```

Using `md:` as the database name connects to your default database and uses workspace attach mode, which makes the databases in your MotherDuck workspace available from the session.

To connect to a specific database, replace `md:` with the database name:

```python
engine = create_engine(
    "postgresql+psycopg://postgres@pg.us-east-1-aws.motherduck.com:5432/sample_data",
    connect_args={
        "password": os.environ["MOTHERDUCK_TOKEN"],
        "sslmode": "verify-full",
        "sslrootcert": "system",
    },
)
```

## Query MotherDuck

Execute SQL with SQLAlchemy's `text()` construct:

```python
from sqlalchemy import text

with engine.connect() as conn:
    result = conn.execute(
        text(
            """
            SELECT title, score
            FROM sample_data.hn.hacker_news
            WHERE type = 'story'
            ORDER BY score DESC
            LIMIT 5
            """
        )
    )

    for row in result:
        print(row.title, row.score)
```

The Postgres endpoint is a PostgreSQL-wire interface to MotherDuck. You write **DuckDB SQL**, not PostgreSQL SQL.

## Loading data

For loading data through SQLAlchemy and the Postgres endpoint, prefer server-side reads from remote storage:

- Use `CREATE TABLE AS SELECT` or `INSERT INTO ... SELECT`.
- Point `read_parquet`, `read_csv`, or `read_json` at S3, GCS, R2, Azure, or HTTPS.
- Set `MD_RUN = REMOTE` on remote file reads.

```python
from sqlalchemy import text

with engine.begin() as conn:
    conn.execute(
        text(
            """
            CREATE OR REPLACE TABLE my_db.main.weather_events AS
            SELECT *
            FROM read_csv(
                'https://raw.githubusercontent.com/duckdb/duckdb-web/main/data/weather.csv',
                HEADER = true,
                AUTO_DETECT = true,
                MD_RUN = REMOTE
            )
            """
        )
    )
```

Use a DuckDB client path instead for local-file ingestion, `COPY FROM STDIN`, `MD_RUN = LOCAL`, or high-volume inserts from application memory.

## Operational notes

- **Use SSL**: The Postgres endpoint requires encrypted connections. `sslmode=verify-full` with `sslrootcert=system` verifies the server certificate when supported by your client.
- **Keep tokens out of code**: Pass your MotherDuck access token through an environment variable or secret manager.
- **Prefer long-lived connections**: Configure SQLAlchemy pooling for application workloads instead of opening a new connection per query.
- **Avoid unsupported Postgres features**: PostgreSQL-specific functions, indexes, sequences, stored procedures, and temporary tables are not supported.

## Connecting with the DuckDB SQLAlchemy driver

If your application needs DuckDB-specific SQLAlchemy behavior or local DuckDB features, use the [DuckDB SQLAlchemy driver](https://github.com/Mause/duckdb_engine) and the DuckDB SQLAlchemy URI style instead.

### Install the DuckDB SQLAlchemy driver

```bash
pip install --upgrade duckdb-engine
```

### Connect to a local DuckDB database

Access a local DuckDB database with the SQLAlchemy URI:

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

### Connect to MotherDuck

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

```bash
duckdb:///md:<my_database>?motherduck_token=<my_token>
```

The database name `<my_database>` in the connection string is optional. Omitting it lets you query multiple databases with one connection to MotherDuck.

You can authenticate in several ways:

**1. Web login**

If no token is available, the process directs you to a web login for authentication, which lets you 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 you run the above, you'll see something like this to authenticate:

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

**2. `MOTHERDUCK_TOKEN` 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. 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. 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
The DuckDB Python API has a `.sql()` method on the connection API, but SQLAlchemy doesn't. Both share the `.execute()` function and concept. For more, see the [SQLAlchemy connection documentation](https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Connection).
:::

## Related content

- **Connect through the Postgres endpoint**: [Postgres endpoint guide](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/)
- **Review connection parameters**: [Postgres Endpoint reference](/sql-reference/postgres-endpoint/)
- **Connect from Python**: [Python through the Postgres endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/python/)
- **Choose an interface**: [Client APIs](/getting-started/interfaces/client-apis/)

---

Source: https://motherduck.com/docs/integrations/language-apis-and-drivers/python/python-overview

# Python
> Python is a programming language for building and deploying web applications.
Check out our [Python tutorial](/getting-started/interfaces/client-apis/python/installation-authentication).

---

## Docs feedback

MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`.
For agents and automated tools, feedback submission should be user-confirmed before sending.

URL-encode query parameter values and send a GET request:

```text
GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fintegrations%2Flanguage-apis-and-drivers%2Fpython%2F&page_title=MotherDuck%20Documentation%20-%20Python&text=<url-encoded user feedback, max 2000 characters>
```

Optionally append `&source=<url-encoded interface identifier>` such as `claude.ai` or `chatgpt`.

`page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": "<uuid>"}`, `400` for malformed query parameters, and `429` when rate-limited.