Native Asyncio Cursors

PyAthena provides native asyncio cursor implementations under pyathena.aio. These cursors use asyncio.sleep for polling and asyncio.to_thread for boto3 calls, keeping the event loop free. Concurrency comes from asyncio tasks rather than a ThreadPoolExecutor owned by the cursor; the boto3 calls run on the event loop’s default executor.

Why native asyncio?

PyAthena has two families of async cursors:

AsyncCursor

AioCursor

Concurrency model

concurrent.futures.ThreadPoolExecutor

Native asyncio (await / async for)

Event loop

Blocks a thread per query

Non-blocking

Connection

connect() (sync)

aio_connect() (async)

execute() returns

(query_id, Future)

Awaitable cursor (self)

Fetch methods

Sync (via Future.result())

await cursor.fetchone() for streaming cursors

Iteration

for row in result_set

async for row in cursor

Context manager

with conn.cursor() as cursor

async with conn.cursor() as cursor

Best for

Adding concurrency to sync code

Async frameworks (FastAPI, aiohttp, etc.)

Choose AioCursor when your application already uses asyncio (e.g., web frameworks, async pipelines). Choose AsyncCursor when you want simple parallel query execution from synchronous code.

Connection

Use the aio_connect() function to create an async connection. It returns an AioConnection that produces AioCursor instances by default.

from pyathena import aio_connect

conn = await aio_connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                      region_name="us-west-2")

The connection supports the async context manager protocol:

from pyathena import aio_connect

async with await aio_connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                          region_name="us-west-2") as conn:
    cursor = conn.cursor()
    await cursor.execute("SELECT 1")
    print(await cursor.fetchone())

AioCursor

AioCursor is a native asyncio cursor that uses await for query execution and result fetching. It follows the DB API 2.0 interface adapted for async usage.

from pyathena import aio_connect
from pyathena.aio.cursor import AioCursor

async with await aio_connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                          region_name="us-west-2") as conn:
    cursor = conn.cursor()
    await cursor.execute("SELECT * FROM many_rows")
    print(await cursor.fetchone())
    print(await cursor.fetchmany(10))
    print(await cursor.fetchall())

The cursor supports the async with context manager:

from pyathena import aio_connect

async with await aio_connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                          region_name="us-west-2") as conn:
    async with conn.cursor() as cursor:
        await cursor.execute("SELECT * FROM many_rows")
        rows = await cursor.fetchall()

Leaving the block closes the cursor in a thread without blocking the event loop, and execute() and executemany() close the previous result the same way. Calling cursor.close() directly closes it on the calling thread.

You can iterate over results with async for:

from pyathena import aio_connect

async with await aio_connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                          region_name="us-west-2") as conn:
    async with conn.cursor() as cursor:
        await cursor.execute("SELECT * FROM many_rows")
        async for row in cursor:
            print(row)

Execution information of the query can also be retrieved:

from pyathena import aio_connect

async with await aio_connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                          region_name="us-west-2") as conn:
    async with conn.cursor() as cursor:
        await cursor.execute("SELECT * FROM many_rows")
        print(cursor.state)
        print(cursor.state_change_reason)
        print(cursor.completion_date_time)
        print(cursor.submission_date_time)
        print(cursor.data_scanned_in_bytes)
        print(cursor.engine_execution_time_in_millis)
        print(cursor.query_queue_time_in_millis)
        print(cursor.total_execution_time_in_millis)
        print(cursor.query_planning_time_in_millis)
        print(cursor.service_processing_time_in_millis)
        print(cursor.output_location)

To cancel a running query:

from pyathena import aio_connect

async with await aio_connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                          region_name="us-west-2") as conn:
    async with conn.cursor() as cursor:
        await cursor.execute("SELECT * FROM many_rows")
        await cursor.cancel()

Task cancellation

With kill_on_interrupt enabled, which is the default, cancelling the task while execute() waits for the query requests cancellation of the query, waits until it reaches a terminal state, and then raises asyncio.CancelledError. Cancellation is a best-effort request, so the query can still end as SUCCEEDED or FAILED. The query_id property keeps the ID of the cancelled query. If the cancellation request or that wait fails, asyncio.CancelledError is raised with the error as its cause. Cancelling the task while execute() is still starting the query first waits for the start request to finish, and then cancels the query it started in the same way. If the task is cancelled before execute() begins the request, the request is never sent. Cancelling the task again during the cancellation request or these waits raises asyncio.CancelledError immediately, and the query can keep running. With kill_on_interrupt=False, asyncio.CancelledError is raised immediately, and a query that has already started keeps running.

With kill_on_interrupt enabled, a timeout from asyncio.wait_for() while execute() starts or waits for the query therefore cancels it and raises asyncio.TimeoutError. If the timeout expires while execute() is still looking up a cached result, no query is started and query_id is None.

import asyncio

from pyathena import aio_connect

async with await aio_connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                          region_name="us-west-2") as conn:
    async with conn.cursor() as cursor:
        try:
            await asyncio.wait_for(cursor.execute("SELECT * FROM many_rows"), timeout=60)
        except asyncio.TimeoutError:
            print(f"Query timed out: {cursor.query_id}")

Cancelling a task also stops the retries of the Athena API requests that the asyncio cursors make in a thread for it, such as the status polling of execute() and the page requests of AioCursor. No further attempt is made, and the wait before the next attempt ends. A request that has already been sent still runs to its end in its thread. The requests that stop a query or a calculation and the request that terminates a Spark session keep their retries. The start request of execute() with kill_on_interrupt enabled keeps its retries after a cancellation, and stops them when the task is cancelled again. After a fetch is cancelled, later fetches of its result raise ProgrammingError. The fetch methods of AioPandasCursor, AioArrowCursor, AioPolarsCursor, and AioS3FSCursor run in a thread. A cancellation skips a fetch that has not started, and a started fetch keeps running in its thread without the retries of its requests. The cursor drops the result, which is closed once its fetch is no longer running. Cancelling AioConnection.create() or aio_connect() closes a connection that is created after the cancellation, and cancelling AioSparkCursor.create() terminates the session of a cursor created after it, as close() does.

AioDictCursor

AioDictCursor is an AioCursor that returns rows as dictionaries with column names as keys.

from pyathena import aio_connect
from pyathena.aio.cursor import AioDictCursor

async with await aio_connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                          region_name="us-west-2") as conn:
    cursor = conn.cursor(AioDictCursor)
    await cursor.execute("SELECT * FROM many_rows LIMIT 10")
    async for row in cursor:
        print(row["a"])

If you want to change the dictionary type (e.g., use OrderedDict):

from collections import OrderedDict
from pyathena import aio_connect
from pyathena.aio.cursor import AioDictCursor

async with await aio_connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                          region_name="us-west-2") as conn:
    cursor = conn.cursor(AioDictCursor, dict_type=OrderedDict)
    await cursor.execute("SELECT * FROM many_rows LIMIT 10")
    async for row in cursor:
        print(row)

Specialized Aio Cursors

Native asyncio versions are available for all cursor types:

Cursor

Module

Result format

AioPandasCursor

pyathena.aio.pandas.cursor

pandas DataFrame

AioArrowCursor

pyathena.aio.arrow.cursor

PyArrow Table

AioPolarsCursor

pyathena.aio.polars.cursor

Polars DataFrame

AioS3FSCursor

pyathena.aio.s3fs.cursor

Row tuples (lightweight)

AioSparkCursor

pyathena.aio.spark.cursor

PySpark execution

Fetch behavior

All aio cursors use await for fetch operations, so fetching does not block the event loop.

  • AioCursor and AioDictCursor page through GetQueryResults as rows are fetched.

  • AioPandasCursor, AioArrowCursor, and AioPolarsCursor download the result file (CSV or Parquet) inside execute(), wrapped in asyncio.to_thread(). When CSV results are read in chunks (chunksize for pandas and Polars, or a chunk size chosen by auto_optimize_chunksize for pandas), or with chunksize on Polars UNLOAD results, fetch calls read S3 lazily instead. The fetch methods also run in a thread.

  • AioS3FSCursor streams rows from the result file in S3 as they are fetched.

With managed query result storage, where the query has no S3 output location, the pandas, Arrow, Polars, and S3FS cursors instead read every row through GetQueryResults inside execute().

await cursor.execute("SELECT * FROM many_rows")
row = await cursor.fetchone()
rows = await cursor.fetchall()

await cursor.execute("SELECT * FROM many_rows")
df = await cursor.as_pandas()

The as_pandas(), as_arrow(), and as_polars() methods of the aio cursors are coroutines. The pandas and Polars cursors read the result in a thread, and the Arrow cursor converts it to Polars in a thread. When the result is read in chunks, AioPandasCursor.as_pandas() and AioPolarsCursor.as_polars() join the chunks that have not yet been read, and a later call returns an empty DataFrame. Unlike PandasCursor.as_pandas(), AioPandasCursor.as_pandas() always returns a DataFrame. AioPandasCursor.iter_chunks() and AioPolarsCursor.iter_chunks() return async iterators that read each chunk of the synchronous iter_chunks() in a thread:

await cursor.execute("SELECT * FROM many_rows", chunksize=50_000)
async for df in cursor.iter_chunks():
    process(df)

The iterator reads the result of the query that the cursor has when iter_chunks() is called. Leaving the loop early keeps the chunks that have not been read for the fetch methods and a later iter_chunks(); closing the cursor or running another query releases them. After the cursor runs another query or closes, the iterator raises ProgrammingError. Cancelling a chunk read, or an as_*() call that reads or converts in a thread, drops the result, as cancelling a fetch does.

See each cursor’s documentation page for detailed usage examples.

AioS3FileSystem

AioS3FileSystem is a native asyncio filesystem interface for Amazon S3, built on fsspec’s AsyncFileSystem. It provides the same functionality as S3FileSystem, and its coroutines run the blocking boto3 calls in threads that PyAthena owns, sized by max_workers, instead of the default executor of the event loop.

S3FileSystem

AioS3FileSystem

Blocking calls

Caller’s thread

Thread pool of the filesystem

Parallelism

Request pool of the filesystem

asyncio.gather on a thread pool; requests in the request pool

File handles

S3File with thread pool

AioS3File with thread pool; AioS3StreamedFile from open_async()

Bulk delete

Request pool, batch_size requests at once

Request pool, batch_size requests at once

Multipart copy

Request pool, max_workers requests per copy

Request pool, max_workers requests per copy

Best for

Synchronous applications

Async frameworks (FastAPI, aiohttp, etc.)

Threads

The threads of AioS3FileSystem belong to S3ThreadPool instances, not to the default executor of the event loop, so loop.set_default_executor() neither limits nor blocks them:

  • The filesystem has a pool of max_workers threads for the blocking calls of its coroutines, such as _info(), _cat_file(), _put_file(), and the completion and cleanup of a multipart copy, and for the commit and abort of the files of open_async().

  • Its internal S3FileSystem has a request pool of max_workers threads for the requests that these calls and the files run in parallel: the part copies of multipart copies and their annotation copies, the DeleteObjects batches of _rm(), and the range reads and parts of the files of open() and open_async(). A file or a copy runs at most its own max_workers requests at once, and all of them together run at most the max_workers of the filesystem.

max_workers defaults to the max_pool_connections of the S3 client, 10 unless configured, as for S3FileSystem.

The pools create threads as the calls need them, and each call runs with the caller’s context variables, as with asyncio.to_thread(). In a forked child process, a pool creates new threads.

close() releases the threads of both pools once no call runs or waits in them, and so does the end of a with or async with block. Until then, calls, including those made after close(), run in those threads within max_workers, and the calls in progress, including the cleanup of a multipart copy, run to their end. The filesystem stays usable, also when other callers share it through fsspec’s instance cache: a call after the release creates new threads. The requests of open files run to their end. Without close(), the threads are released when the filesystem is garbage collected, which a filesystem in fsspec’s instance cache is not until clear_instance_cache().

from pyathena.filesystem.s3_async import AioS3FileSystem

async with AioS3FileSystem(asynchronous=True) as fs:
    files = await fs._ls("s3://my-bucket/data/")

Cancelling a coroutine of the filesystem also stops the retries of the S3 requests that its call makes in these threads, including the part uploads and range reads that the call runs in parallel: no further attempt is made, and the wait before the next attempt ends. A write, such as one of _put_file() or _pipe_file(), sends no further part and aborts its multipart upload instead of completing it, and _get_file() reads and writes no further block. In a transaction, a cancelled _put_file() or _pipe_file() is discarded and left out of the commit. A request that has already been sent still runs to its end in its thread. The requests that run to their end after a cancellation keep their retries: the commit of a file, the CompleteMultipartUpload request of a multipart copy, and the aborts of multipart uploads. The call, the range reads of a file from open_async(), the DeleteObjects requests of _rm(), and the annotation copies of a multipart copy are waited for before the cancellation is raised, also when the shutdown of the event loop at the end of asyncio.run() cancels the coroutine; a second cancellation stops that wait.

_cat(), _cat_ranges(), _pipe(), _put(), _get(), _copy(), and _mv() run the coroutines of their objects as tasks, batch_size of them at a time as in fsspec, except _mv(), which runs all its copies at once. Cancelling one of them cancels those tasks and waits for them before the cancellation is raised, also when the shutdown of the event loop cancels them together with the coroutine. Each download of _get() gets max(1, max_workers // n) range reads, as with S3FileSystem.get, n being the number of files, or batch_size when it is positive and smaller. Pairs of _get(), _put(), _copy(), and _pipe() whose result would depend on the order of the transfers raise ValueError before anything is transferred, as with S3FileSystem (see S3 filesystem), and _get() with outfile writes the objects into it one after another, in order, with n = 1. A recursive _put() sends no request for the local directories. _rm() sends batch_size DeleteObjects requests at a time, max_workers by default and with -1; its max_workers argument is a deprecated alias of batch_size.

Executor strategy

S3FileSystem and S3File use a pluggable executor abstraction (S3Executor) for parallel operations. S3ThreadPool queues the calls in the order they were submitted and runs them in a pool of max_workers threads; in a forked child process, the pool creates new threads. S3ThreadPoolExecutor and S3AioThreadPool are aliases of S3ThreadPool. S3AioExecutor is kept for compatibility: it is an S3ThreadPool that ignores its loop argument, with a DeprecationWarning if one is given. S3Executor.map() and S3Executor.map_async() run a callable for each item in the executor and return the outcomes in the order of the items. With stop_on_failure, the default, the first failure cancels the calls that have not started, stops the retries of the running ones, and is raised once they have ended. A call that runs in an S3ThreadPool must not map on that pool, which would wait for the threads that the call holds; a scope of the filesystem runs such calls in the calling thread.

The file handles of AioS3FileSystem.open() run their multipart uploads and parallel range reads in the request pool of the filesystem, at most the max_workers of the file at once, as S3File does.

In a forked child process, create a new filesystem. A filesystem created in the parent keeps the parent’s boto3 client, which is not meant to be shared across processes, and the event loop of the parent does not run in the child, so fsspec’s synchronous methods of an AioS3FileSystem created in the parent, such as ls(), raise RuntimeError there.

Async file handles

open_async() returns an AioS3StreamedFile, whose read(), write(), and close() are coroutines. It supports the rb, wb, ab, and xb modes:

from pyathena.filesystem.s3_async import AioS3FileSystem

fs = AioS3FileSystem(asynchronous=True)

async with await fs.open_async("s3://my-bucket/data/file.bin", "wb") as f:
    await f.write(data)

async with await fs.open_async("s3://my-bucket/data/file.bin", "rb") as f:
    data = await f.read()
  • A read splits the requested range into ranges of the block size, and reads them in parallel. When a range fails, the reads that have not finished are cancelled, and the running ones make no further attempt. The file has no read cache.

  • A write uploads each full block as a part of a multipart upload, with at most max_workers parts, including the parts copied from the existing object in an append, in flight. A write that would upload another part awaits one of them, so the event loop keeps running. A part that has failed by the next write, or before the write uploads another part, stops the write with its error, and the multipart upload is aborted. A part that fails after the last write raises its error when the file is closed. The commit cancels the parts that have not started as soon as one fails, and waits for the running ones, which make no further attempt, before it aborts the upload and raises the error.

  • Closing the file commits the written data, as S3File does.

  • Leaving an async with block with an exception, or cancelling a write, closes the file without committing it, and aborts the multipart upload after the running parts, which make no further attempt, finish. The abort completes even if the task is cancelled again.

  • Once closing or the final flush has begun the commit, the commit completes even if the task that closes or flushes the file is cancelled, and leaving an async with block with an exception afterward does not abort it. A commit that fails aborts the upload; leaving the block with an exception after a cancelled final flush, while that abort runs, waits for it. An interrupt while the commit is being queued, such as a repeated Ctrl+C under asyncio.run(), closes the file without committing it and aborts its multipart upload, if any. A further interrupt during that cleanup can leave the multipart upload incomplete; the close still does not wait forever.

  • Closing a file, or leaving its async with block, waits for its range reads, commit, and abort, also if the task is cancelled, and raises the cancellation after they end.

  • A file opened for writing must be closed with await f.close() or an async with block. A file that is garbage collected before it is closed or its commit has begun is not committed: it emits a ResourceWarning, and its multipart upload is aborted. A synchronous with block raises TypeError.

  • Cancelling open_async() closes a file that is opened after the cancellation without committing it.

  • open_async() raises NotImplementedError for the write modes inside a transaction, and for autocommit=False, because the file commits when it is closed.

Usage with AioS3FSCursor

AioS3FSCursor automatically uses AioS3FileSystem internally. No additional configuration is needed:

from pyathena import aio_connect
from pyathena.aio.s3fs.cursor import AioS3FSCursor

async with await aio_connect(s3_staging_dir="s3://YOUR_S3_BUCKET/path/to/",
                          region_name="us-west-2") as conn:
    cursor = conn.cursor(AioS3FSCursor)
    await cursor.execute("SELECT * FROM many_rows")
    async for row in cursor:
        print(row)

Standalone usage

AioS3FileSystem can also be used directly for S3 operations:

from pyathena.filesystem.s3_async import AioS3FileSystem

# Async context
fs = AioS3FileSystem(asynchronous=True)

files = await fs._ls("s3://my-bucket/data/")
data = await fs._cat_file("s3://my-bucket/data/file.csv")
await fs._rm("s3://my-bucket/data/old/", recursive=True)

fsspec also generates synchronous wrappers such as ls(). Call them on an instance created without asynchronous=True; each call blocks the caller until it completes:

from pyathena.filesystem.s3_async import AioS3FileSystem

fs = AioS3FileSystem()
files = fs.ls("s3://my-bucket/data/")