---
sidebar_position: 2
title: "Connect from Python via Postgres endpoint"
sidebar_label: Python
description: Connect to MotherDuck from Python using psycopg2 or psycopg3 via the Postgres wire protocol
feature_stage: preview
---

You can query MotherDuck from Python using standard PostgreSQL client libraries. No DuckDB installation is required. This guide covers [psycopg2](https://www.psycopg.org/docs/) and [psycopg (v3)](https://www.psycopg.org/psycopg3/docs/).

For connection parameters, SSL options, and limitations, see the [Postgres Endpoint reference](/sql-reference/postgres-endpoint).

## Prerequisites

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

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

import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';

## Connect

<Tabs>
<TabItem value="psycopg3" label="psycopg (v3)">

```python
# /// script
# dependencies = ["psycopg"]
# ///

import os
import psycopg

with psycopg.connect(
    host="pg.us-east-1-aws.motherduck.com",  # or eu-central-1-aws
    port=5432,
    dbname="md:",
    user="postgres",
    password=os.environ["MOTHERDUCK_TOKEN"],
    sslmode="verify-full",
    sslrootcert="system",  # available in libpq 16+
) as conn:
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT title, score
            FROM sample_data.hn.hacker_news
            WHERE type = 'story'
            ORDER BY score DESC
            LIMIT 5
            """
        )
        for row in cur:
            print(row)
```

You can also use a connection URI:

```python
import os
import psycopg

token = os.environ["MOTHERDUCK_TOKEN"]
with psycopg.connect(
    f"postgresql://postgres:{token}@pg.us-east-1-aws.motherduck.com:5432/md:?sslmode=verify-full&sslrootcert=system"
) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT current_database()")
        print(cur.fetchone())
```

</TabItem>
<TabItem value="psycopg2" label="psycopg2">

```python
# /// script
# dependencies = ["psycopg2-binary", "certifi"]
# ///

import os
import certifi
import psycopg2

conn = psycopg2.connect(
    host="pg.us-east-1-aws.motherduck.com",  # or eu-central-1-aws
    port=5432,
    dbname="md:",
    user="postgres",
    password=os.environ["MOTHERDUCK_TOKEN"],
    sslmode="verify-full",
    sslrootcert=certifi.where(),
)

with conn:
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT title, score
            FROM sample_data.hn.hacker_news
            WHERE type = 'story'
            ORDER BY score DESC
            LIMIT 5
            """
        )
        for row in cur.fetchall():
            print(row)
```

</TabItem>
</Tabs>

Use `md:` as the database name to connect to your default database, or replace it with a specific database name such as `sample_data`.

## Loading data from Python

For loading through the Postgres endpoint, the recommended pattern is server-side reads from remote storage:

- Use `psycopg` or SQLAlchemy to execute `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 those file reads.

Example with SQLAlchemy:

```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": "require",
    },
)

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
        )
        """
        )
    )
```

The following patterns are not currently supported from Python over the Postgres endpoint:

- `COPY ... FROM '/local/file.csv'`
- `cursor.copy(...)` / `COPY FROM STDIN`
- `psql \copy`
- `MD_RUN = LOCAL`
- SQLAlchemy's default `executemany` path for bulk ingest

If the rows exist only in application memory and the volume is modest, prefer explicit multi-values `INSERT` statements. For large local bulk loads, switch to a DuckDB client path instead.

See [Loading data via the Postgres endpoint](/key-tasks/loading-data-into-motherduck/loading-data-via-postgres-endpoint) for the full decision guide.

## SSL notes

- **psycopg (v3)** wraps libpq and supports `sslrootcert=system` directly.
- **psycopg2** bundles its own statically linked OpenSSL, so `sslrootcert=system` is not supported. Use the `certifi` package to point to CA certificates, or download the [ISRG Root X1](https://letsencrypt.org/certs/isrgrootx1.pem) certificate and set `sslrootcert` to its path.

For more details on SSL options, see [SSL and certificate verification](/sql-reference/postgres-endpoint#ssl-and-certificate-verification).