# Query with Pandas, Polars, and Ibis

Use DuckDB's Python client and the [DuckDB Altertable extension](https://github.com/altertable-ai/duckdb-altertable) to query Altertable tables with Pandas, Polars, or [Ibis](https://ibis-project.org/). DuckDB executes SQL against the attached Altertable catalog, then returns results in the dataframe format you choose.

The Altertable community extension requires DuckDB 1.5.4 or later.

## Connect DuckDB to Altertable

Load the community extension and attach an Altertable catalog:

```python
import os

import duckdb

con = duckdb.connect()
con.execute("INSTALL altertable FROM community")
con.execute("LOAD altertable")

con.execute(
    f"ATTACH 'user={os.environ['LAKEHOUSE_USERNAME']} "
    f"password={os.environ['LAKEHOUSE_PASSWORD']} "
    f"catalog={os.environ['LAKEHOUSE_CATALOG']}' "
    "AS remote (TYPE ALTERTABLE)"
)
```

The connection uses the `LAKEHOUSE_USERNAME`, `LAKEHOUSE_PASSWORD`, and `LAKEHOUSE_CATALOG` environment variables. For other connection options and DuckDB secrets, see [Query with DuckDB](/docs/query-data/duckdb.md).

## Query into Pandas

```bash
pip install 'duckdb==1.5.4' pandas
```

Call `.df()` on the result of a DuckDB query to return a Pandas DataFrame:

```python
events = con.sql("""
    SELECT event_id, customer_id, event_name
    FROM remote.main.events
    WHERE event_name = 'activated'
    ORDER BY event_id
""").df()

print(events)
```

## Query into Polars

```bash
pip install 'duckdb==1.5.4' polars pyarrow
```

`pyarrow` is required for DuckDB's Polars integration.

Call `.pl()` to return a Polars DataFrame:

```python
events = con.sql("""
    SELECT event_id, customer_id, event_name
    FROM remote.main.events
    WHERE event_name = 'activated'
    ORDER BY event_id
""").pl()

print(events)
```

## Join remote tables with local DataFrames

Register a local DataFrame with DuckDB to use it in the same SQL query as an attached Altertable table:

```python
import polars as pl

customers = pl.DataFrame(
    {
        "customer_id": ["customer-1", "customer-2"],
        "segment": ["self_serve", "enterprise"],
    }
)

con.register("customers_df", customers)
result = con.sql("""
    SELECT
        c.customer_id,
        c.segment,
        COUNT(*) AS event_count
    FROM customers_df AS c
    JOIN remote.main.events AS e
        ON e.customer_id = c.customer_id
    GROUP BY c.customer_id, c.segment
    ORDER BY event_count DESC
""").pl()
```

The same registration pattern works with Pandas DataFrames.

## Query with Ibis

```bash
pip install 'ibis-framework[duckdb]' 'duckdb==1.5.4'
```

Install `pandas` or `polars pyarrow` as well if you plan to call `.to_pandas()` or `.to_polars()`.

Create an Ibis connection with the same attach workflow:

```python
import os

import ibis

con = ibis.duckdb.connect()
con.raw_sql("INSTALL altertable FROM community")
con.raw_sql("LOAD altertable")
con.raw_sql(
    f"ATTACH 'user={os.environ['LAKEHOUSE_USERNAME']} "
    f"password={os.environ['LAKEHOUSE_PASSWORD']} "
    f"catalog={os.environ['LAKEHOUSE_CATALOG']}' "
    "AS remote (TYPE ALTERTABLE)"
)
```

Reference a remote table, filter and sort with Ibis expressions, then materialize the result:

```python
events = con.table("events", database="remote.main")
activated = events.filter(events.event_name == "activated").order_by("event_id")

activated.to_pandas()
```

Return a Polars DataFrame instead:

```python
activated.to_polars()
```

Use `sql()` when you prefer to write SQL directly:

```python
events = con.sql("""
    SELECT event_id, customer_id, event_name
    FROM remote.main.events
    WHERE event_name = 'activated'
    ORDER BY event_id
""")

events.to_pandas()
```

Join a local `memtable` to an attached Altertable table in the same expression:

```python
customers = ibis.memtable(
    {
        "customer_id": ["customer-1", "customer-2"],
        "segment": ["self_serve", "enterprise"],
    }
)

events = con.table("events", database="remote.main")
result = (
    customers.join(events, "customer_id")
    .group_by("customer_id", "segment")
    .aggregate(event_count=events.event_id.count())
    .order_by(ibis.desc("event_count"))
)

result.to_polars()
```

For the SQL-only workflow, including attachment options and direct table access, see [Query with DuckDB](/docs/query-data/duckdb.md).