pyathena
PyAthena is a Python DB API 2.0 client and SQLAlchemy dialect for Amazon Athena. It submits SQL through Athena's API, waits for the remote query, then converts the result into Python rows or optional Pandas, Arrow, Polars, and S3FS forms. It also has a native asyncio connection, background-future cursors, query-result reuse, workgroup support, and AWS credential options. It is a client for a billed cloud query service, not a local SQL engine.
PyAthena is the practical full-featured Athena client when Python cursor, dataframe, or SQLAlchemy ergonomics matter. It does not make Athena behave like a local database, so permissions, S3 output, memory limits, query cost, and freshness still need explicit engineering.
Use it if
- You want familiar DB API cursor code for Athena instead of manually polling boto3 query executions
- You need Athena as a SQLAlchemy dialect for an existing Python data application
- You regularly move Athena results into Pandas, Arrow, or Polars and will install the matching extra
- You need native asyncio, result reuse, query callbacks, or chunked result handling beyond boto3's low-level API
- You need low-latency transactional queries: every execute call is a remote Athena job with queueing, scanning, result storage, and polling
- You assume DB API parameter substitution is always server-side: the default pyformat mode formats named values in the client; use qmark explicitly for Athena execution parameters
- You cannot grant both Athena and S3 permissions; ordinary workgroups need an S3 staging directory for results, while managed-result workgroups require a deliberate empty staging setting
- You fetch unbounded results into memory: fetchall and the default Pandas cursor can materialize the entire result, and the docs specifically warn about Pandas memory capacity
- You want a small base dependency: PyAthena requires boto3, botocore, fsspec, requests-related support, retry tooling, compression packages, and adds substantial extras for dataframe or ORM paths
Setup reality
PyAthena 3.35.4 requires Python 3.10 or newer and brings boto3 and botocore, so it should normally use the AWS credential provider chain rather than keys embedded in code. Your identity needs permission to start, inspect, and stop Athena queries, read catalog metadata, and read or write the S3 result location. For a normal workgroup, pass s3_staging_dir or set AWS_ATHENA_S3_STAGING_DIR; the bucket must exist in a compatible region and policy failures often appear only after the first query. A workgroup with Athena managed query results can omit S3 output, but if the staging environment variable is present the docs say to pass s3_staging_dir="" explicitly. Choose and spell the region, workgroup, catalog, and schema carefully because a successful connection object does not prove the first query can run. The plain cursor is synchronous and polls a paid remote job. fetchall can be expensive in time and memory, and PandasCursor loads CSV results into memory unless you set chunksize or use iter_chunks. Install extras such as PyAthena[pandas], [arrow], [polars], [sqlalchemy], or [aiosqlalchemy] before importing their cursor paths. AsyncCursor uses concurrent futures and is not DB API 2.0; aio_connect is the native awaitable API. SQLAlchemy URLs need percent-encoded S3 paths and secrets should stay out of the URL. Athena result reuse can return semantically equivalent prior results, so do not enable it where freshness is mandatory. Always cap scanned data with partitions, projections, limits for exploration, workgroup controls, and cancellation monitoring.
Patterns
Run a DB API queryrun-basic-query
from pyathena import connect
with connect(
s3_staging_dir="s3://analytics-results/pyathena/",
region_name="us-east-1",
work_group="analysts",
) as conn:
with conn.cursor() as cur:
cur.execute("SELECT current_date")
print(cur.fetchone())Creating the connection does not contact Athena; permissions and staging errors commonly appear on execute.
Use workgroup-managed query resultsuse-managed-results
conn = connect(
work_group="managed-results-group",
s3_staging_dir="",
region_name="us-east-1",
)The empty string prevents AWS_ATHENA_S3_STAGING_DIR from overriding managed result storage.
Use Athena execution parametersparameterize-query
cur = connect(
s3_staging_dir=staging,
region_name="us-east-1",
paramstyle="qmark",
).cursor()
cur.execute(
"SELECT * FROM events WHERE event_date = ? AND account_id = ?",
["2026-08-01", "acct-42"],
)qmark selects Athena's parameterized-query path; default pyformat accepts named placeholders and uses client formatting.
Iterate rows without fetchallstream-result-rows
cur.execute("SELECT id, total FROM orders")
for order_id, total in cur:
process(order_id, total)Iteration avoids building a second full Python list, but Athena has already materialized query results remotely.
Return rows keyed by column namefetch-dictionaries
from pyathena.cursor import DictCursor
cur = connect(
s3_staging_dir=staging,
region_name="us-east-1",
).cursor(DictCursor)
cur.execute("SELECT order_id, status FROM orders LIMIT 100")
for row in cur:
print(row["order_id"], row["status"])Duplicate column names are a poor fit for dictionary rows; alias columns explicitly.
Load a bounded result into Pandasload-pandas-frame
from pyathena.pandas.cursor import PandasCursor
cur = connect(
s3_staging_dir=staging,
region_name="us-east-1",
).cursor(PandasCursor)
df = cur.execute("SELECT * FROM daily_summary WHERE day >= DATE '2026-08-01'").as_pandas()Install PyAthena[pandas]. Without chunksize, the CSV result is handled in memory.
Process a large result in dataframe chunksprocess-pandas-chunks
cur = connect(
s3_staging_dir=staging,
region_name="us-east-1",
).cursor(PandasCursor)
cur.execute("SELECT * FROM large_events", chunksize=50_000)
for frame in cur.iter_chunks():
write_partition(frame)Chunking limits local dataframe memory, but it does not reduce bytes scanned by the Athena query.
Run a query with native asynciorun-native-async
import asyncio
from pyathena import aio_connect
async def main():
async with await aio_connect(s3_staging_dir=staging, region_name="us-east-1") as conn:
cur = conn.cursor()
await cur.execute("SELECT 1")
print(await cur.fetchone())
asyncio.run(main())Use aio_connect for native async code; AsyncCursor is a different futures-based API.
Allow Athena result reusereuse-query-result
cur.execute(
"SELECT count(*) FROM events WHERE day = DATE '2026-08-01'",
result_reuse_enable=True,
result_reuse_minutes=15,
)Athena may reuse semantically equivalent query results, so leave this off when data freshness is mandatory.
Create a SQLAlchemy engine without URL secretscreate-sqlalchemy-engine
from sqlalchemy import create_engine, text
engine = create_engine(
"awsathena+rest://:@athena.us-east-1.amazonaws.com:443/default",
connect_args={"s3_staging_dir": staging, "work_group": "analysts"},
)
with engine.connect() as conn:
rows = conn.execute(text("SELECT 1")).all()Install PyAthena[sqlalchemy]. Let boto3 discover credentials instead of placing keys in the engine URL.
Alternatives
| Package | Registry | Pick it when |
|---|---|---|
| boto3 | PyPI | You need only a few Athena operations and prefer AWS's low-level client without a DB API abstraction |
| awswrangler | PyPI | Your workflow is dataframe-first across Athena, Glue, S3, and other AWS analytics services |
| pyhive | PyPI | You need a DB API and SQLAlchemy client spanning Hive or Presto rather than Athena-specific features |