Metadata-Version: 2.3
Name: thunderduck-sqlalchemy
Version: 0.1.0
Summary: SQLAlchemy dialect and DBAPI for thunderduck.io — query your lakehouse from Superset, Airflow, pandas and friends.
Keywords: sqlalchemy,duckdb,iceberg,lakehouse,thunderduck,superset
Author: YILDIRIM ADIGUZEL
Author-email: YILDIRIM ADIGUZEL <yadiguzel@gmail.com>
License: Apache-2.0
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Topic :: Database
Classifier: Topic :: Database :: Front-Ends
Requires-Dist: httpx>=0.27.0
Requires-Dist: sqlalchemy>=1.4
Requires-Python: >=3.10
Project-URL: Homepage, https://thunderduck.io
Project-URL: Source, https://github.com/212data/thunderduck
Description-Content-Type: text/markdown

# thunderduck-sqlalchemy

A SQLAlchemy dialect and PEP 249 DBAPI for [thunderduck](https://thunderduck.io).
Query your lakehouse from anything that speaks SQLAlchemy — Superset, Airflow,
Dagster, Redash, pandas, Jupyter, Streamlit.

```bash
pip install thunderduck-sqlalchemy
```

Works with SQLAlchemy 1.4 and 2.x, on Python 3.10+ — so it installs cleanly
into Apache Superset (which pins SQLAlchemy 1.4) as well as modern Airflow,
Dagster and pandas environments.

## Connect

Create an API token in the thunderduck console (**Settings → API Tokens**; it
starts with `tdk_` and is shown once), then:

```python
from sqlalchemy import create_engine, text
from sqlalchemy.pool import NullPool

engine = create_engine(
    "thunderduck://:tdk_your_token@api.thunderduck.io/",
    poolclass=NullPool,
)

with engine.connect() as conn:
    for row in conn.execute(text('SELECT * FROM "lego"."public"."lego_sets" LIMIT 10')):
        print(row)
```

pandas works directly:

```python
import pandas as pd

df = pd.read_sql('SELECT * FROM "thunder_duck_demo"."iris"', engine)
```

### URL options

| Option | Default | Meaning |
| --- | --- | --- |
| password / `?token=` | — | Required. Your `tdk_…` API token. |
| `?ssl=false` | `true` | Use plain HTTP. For in-cluster use against the console-api Service. |
| `?poll_ms=` | `1000` | How often to poll for completion. |
| `?timeout_ms=` | `300000` | How long to wait for a query. |
| `?arraysize=` | `1000` | Rows fetched per page. |

## Table names

thunderduck names are `catalog.schema.table` (flat-file catalogs) or
`catalog.table` (Iceberg/Nessie catalogs). SQLAlchemy models two levels, so
**the schema is the whole dotted prefix**:

```python
from sqlalchemy import MetaData, Table

md = MetaData()
sets = Table("lego_sets", md, schema="lego.public", autoload_with=engine)  # 3-level
iris = Table("iris", md, schema="thunder_duck_demo", autoload_with=engine)  # 2-level
```

Reflection (`inspect(engine).get_schema_names()` etc.) reads thunderduck's
catalog API rather than issuing SQL, so browsing schemas is fast and free.

## Apache Superset

Installing the package is all that is needed — it registers a Superset DB
engine spec automatically. Add a database of type **Other** with:

```
thunderduck://:tdk_your_token@api.thunderduck.io/
```

Superset's stop button genuinely cancels the running query.

## How it works, and what follows from that

Every statement is submitted to thunderduck's REST API, which runs it as an
isolated Kubernetes Job; results are then paged back over HTTP. That model has
consequences worth knowing up front:

- **Latency.** Expect seconds, not milliseconds, before the first row. Built
  for analytics and BI, not for tight interactive loops.
- **Read-only.** No DML, no DDL, no transactions. `commit()` and `rollback()`
  are no-ops.
- **Use `NullPool`.** A connection holds no server-side state, so pooling buys
  nothing. The driver also never issues a `SELECT 1` health check, because
  that would start a whole Job.
- **Parameters are rendered client-side.** thunderduck's API accepts SQL only,
  so this driver renders bound parameters into the statement, escaping them
  strictly and refusing types it does not recognise. Always pass untrusted
  values as parameters — never build SQL by string concatenation. The
  paramstyle is `pyformat`, so — as with psycopg2 — a literal percent sign in
  hand-written SQL must be escaped as `%%` when you pass parameters.
- **Row ceiling.** The server caps how many rows one page returns and how many
  a result set stores. Very large result sets are truncated; aggregate in SQL
  rather than pulling raw rows.

## Also available

For JVM tools (DBeaver, DataGrip) there is a JDBC driver — see the
`jdbc-driver/` directory of the thunderduck repository.

## Development

```bash
uv sync --dev
uv run pytest
uv run ruff check . && uv run ruff format --check .
```

One gotcha: the committed `uv.lock` resolves SQLAlchemy 2.x, so a plain
`uv run pytest` only exercises **one** of the two supported majors. Apache
Superset pins `sqlalchemy<2`, so check that combination too before releasing:

```bash
uv venv --python 3.10 .sa14
uv pip install --python .sa14 -e . "sqlalchemy<2" pytest
.sa14/bin/python -c "import sqlalchemy; print('SQLALCHEMY', sqlalchemy.__version__)"
.sa14/bin/pytest -q
```

CI runs both legs on every push.

## License

Apache-2.0
