Python & SQLAlchemy

thunderduck-sqlalchemy is a SQLAlchemy dialect, published on PyPI as thunderduck-sqlalchemy (opens in a new tab), that talks to the Thunderduck API. It is what makes Python tooling — pandas, Superset, anything built on SQLAlchemy — work without a custom client.

pip install thunderduck-sqlalchemy

Versions and release notes are on the PyPI page (opens in a new tab).

Connecting

from sqlalchemy import create_engine, text
 
engine = create_engine("thunderduck://:[email protected]")
 
with engine.connect() as conn:
    for row in conn.execute(text("SELECT * FROM lake.default.events LIMIT 5")):
        print(row)

The token goes in the password position. Nothing else is required.

With pandas

import pandas as pd
 
df = pd.read_sql("SELECT region, sum(total) AS revenue "
                 "FROM sales_pg.public.orders GROUP BY region", engine)

Bulk results with pandas & polars

pd.read_sql above pages results over the API — fine for a quick look, but each row pays JSON overhead. For pulling a large result set (a notebook doing real data-science work, not a preview), fetch_result_url/fetch_dataframe fetch the query's full stored result directly as Parquet instead — the same file the query already wrote, with no re-serialization step in between.

pip install "thunderduck-sqlalchemy[pandas]"   # pandas + pyarrow
pip install "thunderduck-sqlalchemy[polars]"   # polars

from thunderduck_sqlalchemy import fetch_dataframe
 
df = fetch_dataframe(
    engine,
    "SELECT region, sum(total) AS revenue FROM sales_pg.public.orders GROUP BY region",
    backend="pandas",  # or "polars"
)

Each call submits the query fresh and waits for it to finish, so results aren't paged and there's no row cap. If you'd rather handle the file yourself — a different Parquet reader, streaming it somewhere, whatever — fetch_result_url(engine, sql) returns just {"url": ..., "expires_in": ...}, no pandas/polars required:

from thunderduck_sqlalchemy import fetch_result_url
 
result = fetch_result_url(engine, "SELECT * FROM lake.default.events")
# result["url"] is a presigned link to the full result, valid for
# result["expires_in"] seconds.

Behaviour worth knowing

• Reflection is served from collected metadata, so inspect(engine) is fast and does not open connections to your sources.
• Results are paged under the hood; iterate the result rather than expecting a server-side cursor.
• A cost refusal raises, with the estimate in the message. Catch it and narrow the query rather than retrying.
• Presigned URLs expire. fetch_result_url/fetch_dataframe hand back a short-lived link — read it promptly rather than caching the URL for later.