PyAthena

PyAthena is a Python client for Amazon Athena that implements the standard DB API 2.0 (PEP 249) interface, so you can run Athena queries with the same connect/cursor/execute pattern used by every other Python database driver. It handles query submission, result polling, and result-set retrieval over boto3.

Beyond the raw DB-API cursor, PyAthena ships a SQLAlchemy dialect, pandas and Apache Arrow cursors for efficient dataframe loading, and async cursors for concurrent query execution, making it the de-facto way to query Athena from Python data pipelines and notebooks.

What You Get

• A PEP 249 compliant connection and cursor for Amazon Athena.
• A SQLAlchemy dialect for using Athena with the SQLAlchemy Core and ORM.
• pandas and Apache Arrow cursors that return query results directly as dataframes.
• Async and retry-aware cursors for concurrent, resilient query execution.

Common Use Cases

• Querying an S3 data lake from Python scripts, Airflow tasks, or Jupyter notebooks.
• Loading Athena query results straight into pandas dataframes for analysis.
• Using Athena as a SQLAlchemy-backed database in existing Python applications.

Under The Hood

Architecture

PyAthena is organized around a Connection factory (pyathena/connection.py) that produces cursor variants under pyathena/cursor.py, pandas/, arrow/, and sqlalchemy/. Each cursor submits a query via boto3’s Athena StartQueryExecution, polls execution state, and reads results either row-by-row from the API or by loading the query’s S3 output file (CSV/Parquet) directly for the pandas and Arrow paths.

Tech Stack

Pure Python (3.10-3.14) depending on boto3/botocore for AWS access, tenacity for retries, fsspec for S3 result access, and python-dateutil; SQLAlchemy support is an optional extra. Tooling uses uv, Ruff, and mypy.

Code Quality

The repository has an extensive tests tree covering the core cursors, pandas/Arrow paths, and the SQLAlchemy dialect, plus a benchmarks directory. Linting with Ruff and type checking with mypy are enforced in CI, and the codebase is a mature project maintained since 2017.

API Design

The public API deliberately mirrors PEP 249, so any developer familiar with sqlite3, psycopg, or similar drivers is immediately productive: connect(...) then cursor().execute(sql). Swapping in a PandasCursor or ArrowCursor is a single keyword argument, and the SQLAlchemy dialect follows standard URL conventions, keeping the surface predictable and well documented at pyathena.dev.