Pandas Integration¶
This section covers pandas-specific cursors, result sets, and data converters.
Pandas Cursors¶
- class pyathena.pandas.cursor.PandasCursor(*, s3_staging_dir: str | None = None, schema_name: str | None = None, catalog_name: str | None = None, work_group: str | None = None, poll_interval: float = 1, encryption_option: str | None = None, kms_key: str | None = None, kill_on_interrupt: bool = True, unload: bool = False, engine: str = 'auto', chunksize: int | None = None, block_size: int | None = None, cache_type: str | None = None, s3_max_workers: int | None = None, result_reuse_enable: bool = False, result_reuse_minutes: int = 60, auto_optimize_chunksize: bool = False, **kwargs)[source]¶
Cursor for handling pandas DataFrame results from Athena queries.
This cursor returns query results as pandas DataFrames with memory-efficient processing through chunking support and optional automatic chunksize optimization for large result sets. It’s ideal for data analysis and data science workflows.
The cursor supports both regular CSV-based results and high-performance UNLOAD operations that return results in Parquet format, which is significantly faster for large datasets and preserves data types more accurately.
- description¶
Sequence of column descriptions for the last query.
- rowcount¶
Number of rows affected by the last query (-1 for SELECT queries).
- arraysize¶
Default number of rows to fetch with fetchmany().
Example
>>> from pyathena.pandas.cursor import PandasCursor >>> cursor = connection.cursor(PandasCursor) >>> cursor.execute("SELECT * FROM sales_data WHERE year = 2023") >>> df = cursor.as_pandas() # Returns pandas DataFrame >>> print(df.describe())
# Memory-efficient iteration for large datasets >>> cursor = connection.cursor(PandasCursor, chunksize=50_000) >>> cursor.execute(“SELECT * FROM huge_table”) >>> for chunk_df in cursor.iter_chunks(): … process_chunk(chunk_df) # Process data in chunks
# High-performance UNLOAD for large datasets >>> cursor = connection.cursor(PandasCursor, unload=True) >>> cursor.execute(“SELECT * FROM big_table”) >>> df = cursor.as_pandas() # Faster Parquet-based result
- __init__(*, s3_staging_dir: str | None = None, schema_name: str | None = None, catalog_name: str | None = None, work_group: str | None = None, poll_interval: float = 1, encryption_option: str | None = None, kms_key: str | None = None, kill_on_interrupt: bool = True, unload: bool = False, engine: str = 'auto', chunksize: int | None = None, block_size: int | None = None, cache_type: str | None = None, s3_max_workers: int | None = None, result_reuse_enable: bool = False, result_reuse_minutes: int = 60, auto_optimize_chunksize: bool = False, **kwargs) None[source]¶
Initialize PandasCursor with configuration options.
- Parameters:
s3_staging_dir – S3 directory for query result staging.
schema_name – Default schema name for queries.
catalog_name – Default catalog name for queries.
work_group – Athena workgroup name.
poll_interval – Query polling interval in seconds.
encryption_option – S3 encryption option.
kms_key – KMS key for encryption.
kill_on_interrupt – Cancel query on interrupt signal.
unload – Use UNLOAD statement for faster result retrieval.
engine – CSV parsing engine (‘auto’, ‘c’, ‘python’, ‘pyarrow’).
chunksize – Number of rows per chunk for memory-efficient processing. If specified, takes precedence over auto_optimize_chunksize.
block_size – S3 read block size.
cache_type – The default fsspec cache type of the S3 filesystem that reads the results, such as
"none","bytes","readahead","blockcache", or"background"; seeS3FileSystem. Withoutblock_sizeorcache_type, a CSV result file is read in 32 MiB blocks with fsspec’s background cache, which reads the next block while pandas parses the current one. Withoutcache_type, PyAthena’s filesystem reads a Parquet file of a chunked UNLOAD result that is larger than 32 MiB without a cache.s3_max_workers – The size of the request pool of the S3 filesystem that reads the result files. If None, the
max_pool_connectionsof the connection’s S3 client (10 unless set withs3_config).result_reuse_enable – Enable query result reuse.
result_reuse_minutes – Result reuse duration in minutes.
auto_optimize_chunksize – Enable automatic chunksize determination for large files. Only effective when chunksize is None. Default: False (no automatic chunking).
**kwargs – Arguments forwarded to
WithResultSet.__init__andBaseCursor.__init__, such asarraysize,connection,converter,formatter, andretry_config. Pass pandasread_csv/read_parquetoptions toexecute()instead.
- static get_default_converter(unload: bool = False) DefaultPandasTypeConverter | Any[source]¶
Get the default type converter for this cursor class.
- Parameters:
unload – Whether the converter is for UNLOAD operations. Some cursor types may return different converters for UNLOAD operations.
- Returns:
The default type converter instance for this cursor type.
- execute(operation: str, parameters: dict[str, Any] | list[str] | None = None, *, work_group: str | None = None, s3_staging_dir: str | None = None, cache_size: int | None = None, cache_expiration_time: int | None = None, result_reuse_enable: bool | None = None, result_reuse_minutes: int | None = None, paramstyle: str | None = None, keep_default_na: bool = False, na_values: Iterable[str] | None = ('',), quoting: int = 1, on_start_query_execution: Callable[[str], None] | None = None, result_set_type_hints: dict[str | int, str] | None = None, options: ExecuteOptions | None = None, **kwargs) PandasCursor[source]¶
Execute a SQL query and return results as pandas DataFrames.
Executes the SQL query on Amazon Athena and configures the result set for pandas DataFrame output. Supports both regular CSV-based results and high-performance UNLOAD operations with Parquet format.
- Parameters:
operation – SQL query string to execute.
parameters – Query parameters for parameterized queries.
work_group – Athena workgroup to use for this query.
s3_staging_dir – S3 location for query results.
cache_size – Number of queries to check for result caching.
cache_expiration_time – Cache expiration time in seconds.
result_reuse_enable – Enable Athena result reuse for this query.
result_reuse_minutes – Minutes to reuse cached results.
paramstyle – Parameter style (‘qmark’ or ‘pyformat’).
keep_default_na – Whether to keep default pandas NA values.
na_values – Additional values to treat as NA.
quoting – CSV quoting behavior (pandas csv.QUOTE_* constants).
on_start_query_execution – Callback invoked with the query ID before
execute()waits for the query: after theStartQueryExecutioncall, or after a reusable query ID is found throughcache_size.result_set_type_hints – Athena type signatures for complex-type columns, keyed by column name (case-insensitive) or zero-based column index.
options – Shared execution options as an
ExecuteOptionsinstance. Individual keyword arguments take precedence overoptionsfields.**kwargs – Additional pandas read_csv/read_parquet parameters.
engine,chunksize,block_size,cache_type,s3_max_workers, andauto_optimize_chunksizeoverride the cursor’s values for this query.storage_optionsand, for UNLOAD results,filesystemreplace PyAthena’s S3 filesystem (seeAthenaPandasResultSet).
- Returns:
Self reference for method chaining.
- Raises:
ProgrammingError – If the cursor is closed.
Example
>>> cursor.execute("SELECT * FROM sales WHERE year = %(year)s", ... {"year": 2023}) >>> df = cursor.as_pandas() # Returns pandas DataFrame
- as_pandas() DataFrame | PandasDataFrameIterator[source]¶
Return DataFrame or PandasDataFrameIterator based on chunksize setting.
- Returns:
DataFrame when chunksize is None, PandasDataFrameIterator when chunksize is set.
- Raises:
ProgrammingError – If the cursor is closed or has no result set.
- iter_chunks() Generator[DataFrame, None, None][source]¶
Iterate over DataFrame chunks for memory-efficient processing.
This method provides an iterator interface for processing large result sets in chunks, preventing memory exhaustion when working with datasets that are too large to fit in memory as a single DataFrame.
Chunking behavior: - If chunksize is explicitly set, uses that value - If auto_optimize_chunksize=True and chunksize=None, automatically determines
optimal chunksize based on file size
If auto_optimize_chunksize=False and chunksize=None, yields entire DataFrame
- Yields:
DataFrame –
- Individual chunks of the result set when chunking is enabled,
or the entire DataFrame as a single chunk when chunking is disabled.
- Raises:
ProgrammingError – If the cursor is closed or has no result set when the iteration starts, or its result set is closed during the iteration, such as by another query of the cursor.
Examples
# Explicit chunksize cursor = connection.cursor(PandasCursor, chunksize=50000) cursor.execute(“SELECT * FROM large_table”) for chunk in cursor.iter_chunks():
process_chunk(chunk)
# Auto-optimization enabled cursor = connection.cursor(PandasCursor, auto_optimize_chunksize=True) cursor.execute(“SELECT * FROM large_table”) for chunk in cursor.iter_chunks():
process_chunk(chunk) # Chunks determined automatically for large files
# No chunking (default behavior) cursor = connection.cursor(PandasCursor) cursor.execute(“SELECT * FROM large_table”) for chunk in cursor.iter_chunks():
process_chunk(chunk) # Single DataFrame regardless of size
- DEFAULT_RESULT_REUSE_MINUTES = 60¶
- LIST_DATABASES_MAX_RESULTS = 50¶
- LIST_QUERY_EXECUTIONS_MAX_RESULTS = 50¶
- LIST_TABLE_METADATA_MAX_RESULTS = 50¶
- property arraysize: int¶
The default number of rows per
fetchmany()call.execute()passes it to the new result set, so a change applies to the result sets of later executions. Setting it to zero or a negative value raisesProgrammingError.- Returns:
The default number of rows per
fetchmany()call.
- cancel() None¶
Cancel the currently executing query.
- Raises:
ProgrammingError – If the cursor is closed or no query is currently executing.
- close() None¶
Close the cursor and its result set.
Later operations on the cursor raise
ProgrammingError, andquery_idkeeps the ID of the last query. Closing a closed cursor does nothing.
- property connection: Connection[Any]¶
The connection that created this cursor.
- property data_manifest_location: str | None¶
The S3 location of the data manifest that lists the files the query wrote.
- property description: list[tuple[str, str, None, None, int, int, str]] | None¶
The DB API 2.0 column descriptions of the result set, or None without one.
- property encryption_option: str | None¶
The
EncryptionOptionof the query results, such asSSE_S3orSSE_KMS.
- property engine_execution_time_in_millis: int | None¶
The time in milliseconds that the query engine took to run the query.
- property error_category: int | None¶
1 for system, 2 for user, 3 for other.
- Type:
The
ErrorCategoryof the failure
- executemany(operation: str, seq_of_parameters: list[dict[str, Any] | list[str] | None], **kwargs) None¶
Execute a SQL query multiple times with different parameters.
On success,
rowcountis the sum of the affected row counts, or -1 if any execution has an unknown count. An empty parameter list sets it to 0. On failure, it is -1; earlier executions are not rolled back. Result sets are discarded.On failure,
query_idretains the current query ID when available. If parameter iteration fails, this can identify the last successful execution.- Parameters:
operation – SQL query string to execute.
seq_of_parameters – Sequence of parameter sets, one per execution.
**kwargs – Additional keyword arguments passed to each
execute(). An empty parameter list makes noexecute()call, so it does not report unsupported keyword names.
- Raises:
ProgrammingError – If the cursor is closed.
- property expected_bucket_owner: str | None¶
The AWS account ID expected to own the S3 bucket of the query results.
- fetchall() list[tuple[Any | None, ...] | dict[Any, Any | None]]¶
Fetch all remaining rows from the result set.
- Returns:
The remaining rows.
- Raises:
ProgrammingError – If the cursor is closed or has no result set.
- fetchmany(size: int | None = None) list[tuple[Any | None, ...] | dict[Any, Any | None]]¶
Fetch multiple rows from the result set.
- Parameters:
size – Maximum number of rows to fetch. If None or not positive,
arraysizeis used.- Returns:
The fetched rows.
- Raises:
ProgrammingError – If the cursor is closed or has no result set.
- fetchone() tuple[Any | None, ...] | dict[Any, Any | None] | None¶
Fetch the next row of the result set.
- Returns:
The next row (a tuple, or a dict for dict cursors), or None if no more rows.
- Raises:
ProgrammingError – If the cursor is closed or has no result set.
- get_table_metadata(table_name: str, catalog_name: str | None = None, schema_name: str | None = None, logging_: bool = True) AthenaTableMetadata¶
Get one table’s metadata.
In
AwsDataCatalogand S3 Tables catalogs, a throttled request is answered from the AWS Glue Data Catalog; seeglue_metadata_fallback.- Parameters:
table_name – The table name.
catalog_name – The catalog, or None for the cursor’s catalog.
schema_name – The database, or None for the cursor’s schema.
logging – Whether to log a failed request.
- Returns:
The table’s metadata.
- Raises:
OperationalError – If the request fails, including when the table does not exist.
ProgrammingError – If the cursor is closed.
- list_databases(catalog_name: str | None, max_results: int | None = None) list[AthenaDatabase]¶
List the catalog’s databases.
In
AwsDataCatalogand S3 Tables catalogs, a throttled request is answered from the AWS Glue Data Catalog; seeglue_metadata_fallback.- Parameters:
catalog_name – The catalog, or None for the cursor’s catalog.
max_results – The page size of each request.
- Returns:
The catalog’s databases.
- Raises:
OperationalError – If the request fails.
ProgrammingError – If the cursor is closed.
- list_table_metadata(catalog_name: str | None = None, schema_name: str | None = None, expression: str | None = None, max_results: int | None = None, logging_: bool = True) list[AthenaTableMetadata]¶
List a database’s table metadata.
In
AwsDataCatalogand S3 Tables catalogs, a throttled request is answered from the AWS Glue Data Catalog; seeglue_metadata_fallback.- Parameters:
catalog_name – The catalog, or None for the cursor’s catalog.
schema_name – The database, or None for the cursor’s schema.
expression – A table name pattern.
max_results – The page size of each request.
logging – Whether to log a failed request.
- Returns:
The metadata of the database’s tables.
- Raises:
OperationalError – If the request fails.
ProgrammingError – If the cursor is closed.
- property query_id: str | None¶
The query execution ID of the last execution.
With
cache_sizeorcache_expiration_time, this can be the ID of a previous execution whose result is reused.- Returns:
The query execution ID, or None if there is none since the last reset.
- property query_planning_time_in_millis: int | None¶
The time in milliseconds that Athena took to plan the query.
- property query_queue_time_in_millis: int | None¶
The time in milliseconds that the query waited in the queue.
- property result_reuse_enabled: bool | None¶
Whether reuse of previous query results by age is enabled for the query.
- property result_reuse_minutes: int | None¶
The maximum age in minutes of a previous query result that Athena can reuse.
- property result_set: AthenaResultSet | None¶
The result set of the last executed query.
- Returns:
The result set, or None before a query succeeds or after a reset.
- property reused_previous_result: bool | None¶
Whether Athena reused a previous query result instead of running the query.
- property rowcount: int¶
Get the number of rows affected by the last operation.
For SELECT statements, this returns -1 as per DB API 2.0 specification. For DML operations (INSERT, UPDATE, DELETE) and CTAS, this returns the number of affected rows. After a successful
executemany(), this is the sum across executions, or -1 if any count is unknown.- Returns:
The number of rows, or -1 if not applicable or unknown.
- property rownumber: int | None¶
The zero-based index of the next row in the result set.
- Returns:
The row index, or None if there is no result set or the index is unknown.
- property s3_acl_option: str | None¶
The
S3AclOptionof the query results, such asBUCKET_OWNER_FULL_CONTROL.
- property service_processing_time_in_millis: int | None¶
The time in milliseconds that Athena took to publish the query results.
- setinputsizes(sizes)¶
Accept input sizes as DB API 2.0 requires, and ignore them.
- Parameters:
sizes – Sequence of parameter types or sizes.
- Raises:
ProgrammingError – If the cursor is closed.
- setoutputsize(size, column=None)¶
Accept a column buffer size as DB API 2.0 requires, and ignore it.
- Parameters:
size – Buffer size for large columns.
column – Index of the column the size applies to, or None for all large columns.
- Raises:
ProgrammingError – If the cursor is closed.
- property state_change_reason: str | None¶
The
StateChangeReasonthat gives further detail about the state.
- class pyathena.pandas.async_cursor.AsyncPandasCursor(*, s3_staging_dir: str | None = None, schema_name: str | None = None, catalog_name: str | None = None, work_group: str | None = None, poll_interval: float = 1, encryption_option: str | None = None, kms_key: str | None = None, kill_on_interrupt: bool = True, max_workers: int = 20, arraysize: int = 1000, unload: bool = False, engine: str = 'auto', chunksize: int | None = None, result_reuse_enable: bool = False, result_reuse_minutes: int = 60, block_size: int | None = None, cache_type: str | None = None, auto_optimize_chunksize: bool = False, s3_max_workers: int | None = None, **kwargs)[source]¶
Asynchronous cursor that returns results as pandas DataFrames.
This cursor extends AsyncCursor to provide asynchronous query execution with results returned as pandas DataFrames. It’s designed for data analysis workflows where pandas integration is required and non-blocking query execution is beneficial.
- Features:
Asynchronous query execution with concurrent futures
Direct pandas DataFrame results for data analysis
Configurable CSV and Parquet engines for optimal performance
Support for chunked processing of large datasets
UNLOAD operations for improved performance with large results
Memory optimization through configurable chunking
- arraysize¶
Default number of rows that fetchmany() returns on the result sets this cursor creates.
Example
>>> from pyathena.pandas.async_cursor import AsyncPandasCursor >>> >>> cursor = connection.cursor(AsyncPandasCursor, chunksize=10000) >>> query_id, future = cursor.execute("SELECT * FROM large_table") >>> >>> # Get result when ready >>> result_set = future.result() >>> df = result_set.as_pandas() >>> >>> # Or iterate through chunks for large datasets >>> for chunk_df in result_set.iter_chunks(): ... process_chunk(chunk_df)
Note
Requires pandas to be installed. For large datasets, consider using chunksize or UNLOAD operations for better memory efficiency.
- __init__(*, s3_staging_dir: str | None = None, schema_name: str | None = None, catalog_name: str | None = None, work_group: str | None = None, poll_interval: float = 1, encryption_option: str | None = None, kms_key: str | None = None, kill_on_interrupt: bool = True, max_workers: int = 20, arraysize: int = 1000, unload: bool = False, engine: str = 'auto', chunksize: int | None = None, result_reuse_enable: bool = False, result_reuse_minutes: int = 60, block_size: int | None = None, cache_type: str | None = None, auto_optimize_chunksize: bool = False, s3_max_workers: int | None = None, **kwargs) None[source]¶
Initialize an AsyncPandasCursor.
- Parameters:
s3_staging_dir – S3 location for query results.
schema_name – Default schema name.
catalog_name – Default catalog name.
work_group – Athena workgroup name.
poll_interval – Query status polling interval in seconds.
encryption_option – S3 encryption option for query results.
kms_key – KMS key for encrypting query results.
kill_on_interrupt – Cancel a query whose start in
execute()is interrupted byKeyboardInterrupt. Waiting runs on worker threads, which do not receive the interrupt.max_workers – Size of the cursor thread pool for waiting and collecting results.
arraysize – Number of rows to fetch per batch. Must be a positive integer.
unload – Whether to wrap queries in
UNLOADand read the Parquet output.engine – Parsing engine (
auto,c,python, orpyarrow).chunksize – Number of rows per DataFrame chunk when reading CSV results. If set, it takes precedence over
auto_optimize_chunksize.result_reuse_enable – Whether to enable Athena query result reuse.
result_reuse_minutes – Maximum age of a reused query result in minutes.
block_size – Default block size of the S3 filesystem that reads the results.
cache_type – The default fsspec cache type of the S3 filesystem that reads the results, such as
"none","bytes","readahead","blockcache", or"background"; seeS3FileSystem. Withoutblock_sizeorcache_type, a CSV result file is read in 32 MiB blocks with fsspec’s background cache, which reads the next block while pandas parses the current one. Withoutcache_type, PyAthena’s filesystem reads a Parquet file of a chunked UNLOAD result that is larger than 32 MiB without a cache.auto_optimize_chunksize – Whether to choose a chunk size from the size of the CSV result file when
chunksizeis None.s3_max_workers – The size of the request pool of the S3 filesystem that reads the result files. If None, the
max_pool_connectionsof the connection’s S3 client (10 unless set withs3_config).**kwargs – Other cursor arguments, such as
connectionandconverter, passed toAsyncCursor.__init__.
- static get_default_converter(unload: bool = False) DefaultPandasTypeConverter | Any[source]¶
Get the default type converter for this cursor class.
- Parameters:
unload – Whether the converter is for UNLOAD operations. Some cursor types may return different converters for UNLOAD operations.
- Returns:
The default type converter instance for this cursor type.
- LIST_DATABASES_MAX_RESULTS = 50¶
- LIST_QUERY_EXECUTIONS_MAX_RESULTS = 50¶
- LIST_TABLE_METADATA_MAX_RESULTS = 50¶
- cancel(query_id: str) Future[None]¶
Cancel a running query asynchronously.
Submits a cancellation request for the specified query. The cancellation itself runs asynchronously in the background.
- Parameters:
query_id – The Athena query execution ID to cancel.
- Returns:
Future object that completes when the cancellation request finishes.
Example
>>> query_id, future = cursor.execute("SELECT * FROM huge_table") >>> # Later, cancel the query >>> cancel_future = cursor.cancel(query_id) >>> cancel_future.result() # Wait for cancellation to complete
- Raises:
ProgrammingError – If the cursor is closed.
- close(wait: bool = False) None¶
Close the cursor and shut down its executor.
Later operations on the cursor raise
ProgrammingError. The futures ofexecute(),description(), andpoll()that still wait for their query raiseProgrammingErrorat their next status check, also when that check finds the query finished; the query itself keeps running. A future ofexecute()that is building its result set resolves with a closed result set, and one that is just finishing can resolve with either an open or a closed one. A result set whose future resolved before the close is left to the caller. The futures ofcancel()andquery_execution()still run.- Parameters:
wait – Whether to wait for submitted futures to finish before returning.
- property connection: Connection[Any]¶
The connection that created this cursor.
- description(query_id: str) Future[list[tuple[str, str, None, None, int, int, str]] | None]¶
Get the column descriptions of a query’s result set asynchronously.
The future waits for the query to finish, then reads only the result set metadata with the metadata reader of the cursor: a
GetQueryResultspage of one row, or for an UNLOAD result of the pandas, Arrow, and Polars cursors, the schema of its Parquet files. The descriptions are those of the result set ofexecute()with the options of the cursor, not those given toexecute(). If the cursor closes first, the future raisesProgrammingError.- Parameters:
query_id – The Athena query execution ID.
- Returns:
Future object containing the DB API 2.0 column descriptions, or None.
- Raises:
ProgrammingError – If the cursor is closed.
- execute(operation: str, parameters: dict[str, Any] | list[str] | None = None, *, work_group: str | None = None, s3_staging_dir: str | None = None, cache_size: int | None = None, cache_expiration_time: int | None = None, result_reuse_enable: bool | None = None, result_reuse_minutes: int | None = None, paramstyle: str | None = None, result_set_type_hints: dict[str | int, str] | None = None, keep_default_na: bool = False, na_values: Iterable[str] | None = ('',), quoting: int = 1, options: ExecuteOptions | None = None, **kwargs) tuple[str, Future[AthenaPandasResultSet | Any]][source]¶
Execute a SQL query asynchronously and return results as pandas DataFrames.
- Parameters:
operation – SQL query string to execute.
parameters – Query parameters for parameterized queries.
work_group – Athena workgroup to use for this query.
s3_staging_dir – S3 location for query results.
cache_size – Number of queries to check for result caching.
cache_expiration_time – Cache expiration time in seconds.
result_reuse_enable – Enable Athena result reuse for this query.
result_reuse_minutes – Minutes to reuse cached results.
paramstyle – Parameter style (‘qmark’ or ‘pyformat’).
result_set_type_hints – Athena type signatures for complex-type columns, keyed by column name (case-insensitive) or zero-based column index.
keep_default_na – Whether to keep default pandas NA values.
na_values – Additional values to treat as NA.
quoting – CSV quoting behavior (pandas csv.QUOTE_* constants).
options – Shared execution options as an
ExecuteOptionsinstance. Individual keyword arguments take precedence overoptionsfields.**kwargs – Additional pandas read_csv/read_parquet parameters.
engine,chunksize,block_size,cache_type, andauto_optimize_chunksizeoverride the cursor’s values for this query.storage_optionsand, for UNLOAD results,filesystemreplace PyAthena’s S3 filesystem (seeAthenaPandasResultSet).s3_max_workerssets the number of S3 read workers for this query.
- Returns:
Tuple of (query_id, future) where future resolves to AthenaPandasResultSet.
- Raises:
ProgrammingError – If the cursor is closed.
- executemany(operation: str, seq_of_parameters: list[dict[str, Any] | list[str] | None], **kwargs) None¶
Execute multiple queries asynchronously (not supported).
This method is not supported for asynchronous cursors because managing multiple concurrent queries would be complex and resource-intensive.
- Parameters:
operation – SQL query string.
seq_of_parameters – Sequence of parameter sets.
**kwargs – Additional arguments.
- Raises:
NotSupportedError – Always raised as this operation is not supported.
ProgrammingError – If the cursor is closed.
Note
For bulk operations, consider using execute() with parameterized queries or batch processing patterns instead.
- get_table_metadata(table_name: str, catalog_name: str | None = None, schema_name: str | None = None, logging_: bool = True) AthenaTableMetadata¶
Get one table’s metadata.
In
AwsDataCatalogand S3 Tables catalogs, a throttled request is answered from the AWS Glue Data Catalog; seeglue_metadata_fallback.- Parameters:
table_name – The table name.
catalog_name – The catalog, or None for the cursor’s catalog.
schema_name – The database, or None for the cursor’s schema.
logging – Whether to log a failed request.
- Returns:
The table’s metadata.
- Raises:
OperationalError – If the request fails, including when the table does not exist.
ProgrammingError – If the cursor is closed.
- list_databases(catalog_name: str | None, max_results: int | None = None) list[AthenaDatabase]¶
List the catalog’s databases.
In
AwsDataCatalogand S3 Tables catalogs, a throttled request is answered from the AWS Glue Data Catalog; seeglue_metadata_fallback.- Parameters:
catalog_name – The catalog, or None for the cursor’s catalog.
max_results – The page size of each request.
- Returns:
The catalog’s databases.
- Raises:
OperationalError – If the request fails.
ProgrammingError – If the cursor is closed.
- list_table_metadata(catalog_name: str | None = None, schema_name: str | None = None, expression: str | None = None, max_results: int | None = None, logging_: bool = True) list[AthenaTableMetadata]¶
List a database’s table metadata.
In
AwsDataCatalogand S3 Tables catalogs, a throttled request is answered from the AWS Glue Data Catalog; seeglue_metadata_fallback.- Parameters:
catalog_name – The catalog, or None for the cursor’s catalog.
schema_name – The database, or None for the cursor’s schema.
expression – A table name pattern.
max_results – The page size of each request.
logging – Whether to log a failed request.
- Returns:
The metadata of the database’s tables.
- Raises:
OperationalError – If the request fails.
ProgrammingError – If the cursor is closed.
- poll(query_id: str) Future[AthenaQueryExecution]¶
Poll for query completion asynchronously.
Waits for the query to complete (succeed, fail, or be cancelled) and returns the final execution status. This method blocks until completion but runs the polling in a background thread.
- Parameters:
query_id – The Athena query execution ID to poll.
- Returns:
Future object containing the final AthenaQueryExecution status.
Note
This method performs polling internally, so it will take time proportional to your query execution duration. If the cursor closes first, the future raises
ProgrammingError.- Raises:
ProgrammingError – If the cursor is closed.
- query_execution(query_id: str) Future[AthenaQueryExecution]¶
Get query execution details asynchronously.
Retrieves the current execution status and metadata for a query. This is useful for monitoring query progress without blocking.
- Parameters:
query_id – The Athena query execution ID.
- Returns:
Future object containing AthenaQueryExecution with query details.
- Raises:
ProgrammingError – If the cursor is closed.
- setinputsizes(sizes)¶
Accept input sizes as DB API 2.0 requires, and ignore them.
- Parameters:
sizes – Sequence of parameter types or sizes.
- Raises:
ProgrammingError – If the cursor is closed.
- setoutputsize(size, column=None)¶
Accept a column buffer size as DB API 2.0 requires, and ignore it.
- Parameters:
size – Buffer size for large columns.
column – Index of the column the size applies to, or None for all large columns.
- Raises:
ProgrammingError – If the cursor is closed.
Pandas Result Set¶
- class pyathena.pandas.result_set.AthenaPandasResultSet(connection: Connection[Any], converter: Converter, query_execution: AthenaQueryExecution, arraysize: int, retry_config: RetryConfig, keep_default_na: bool = False, na_values: Iterable[str] | None = ('',), quoting: int = 1, unload: bool = False, unload_location: str | None = None, engine: str = 'auto', chunksize: int | None = None, block_size: int | None = None, cache_type: str | None = None, max_workers: int | None = None, auto_optimize_chunksize: bool = False, result_set_type_hints: dict[str | int, str] | None = None, **kwargs)[source]¶
Result set that provides pandas DataFrame results with memory optimization.
This result set handles CSV and Parquet result files from S3, converting them to pandas DataFrames with configurable chunking for memory-efficient processing. With
auto_optimize_chunksize=True, it chooses a chunk size based on file size, and it provides iterative processing capabilities for large datasets.- Features:
Optional chunk size optimization based on file size
Support for both CSV and Parquet result formats
Memory-efficient iterative processing
Automatic date/time parsing for pandas compatibility
PyArrow integration for Parquet files
Example
>>> # Used automatically by PandasCursor >>> cursor = connection.cursor(PandasCursor) >>> cursor.execute("SELECT * FROM large_table") >>> >>> # Get full DataFrame >>> df = cursor.as_pandas() >>> >>> # Or iterate through chunks for memory efficiency >>> cursor = connection.cursor(PandasCursor, chunksize=50_000) >>> cursor.execute("SELECT * FROM large_table") >>> for chunk_df in cursor.iter_chunks(): ... process_chunk(chunk_df)
Note
This class is used internally by PandasCursor and typically not instantiated directly by users.
- __init__(connection: Connection[Any], converter: Converter, query_execution: AthenaQueryExecution, arraysize: int, retry_config: RetryConfig, keep_default_na: bool = False, na_values: Iterable[str] | None = ('',), quoting: int = 1, unload: bool = False, unload_location: str | None = None, engine: str = 'auto', chunksize: int | None = None, block_size: int | None = None, cache_type: str | None = None, max_workers: int | None = None, auto_optimize_chunksize: bool = False, result_set_type_hints: dict[str | int, str] | None = None, **kwargs) None[source]¶
Initialize AthenaPandasResultSet with pandas-specific configurations.
- Parameters:
connection – Database connection instance.
converter – Data type converter for Athena types to pandas types.
query_execution – Query execution metadata from Athena.
arraysize – Default number of rows that
fetchmany()returns.retry_config – Retry configuration for the
GetQueryResultscalls and for the HeadObject and GetObject calls this result set makes. Result files are read through the connection’s S3 filesystem, which uses the connection’s retry configuration.keep_default_na – pandas option for handling NA values.
na_values – Additional values to recognize as NA.
quoting – CSV quoting behavior.
unload – Whether result uses UNLOAD statement (Parquet format).
unload_location – S3 location for UNLOAD results.
engine – Parsing engine (‘auto’, ‘c’, ‘python’, ‘pyarrow’).
chunksize – Number of rows per chunk. If specified, takes precedence over auto_optimize_chunksize.
block_size – S3 read block size.
cache_type – The default fsspec cache type of the S3 filesystem that reads the results, such as
"none","bytes","readahead","blockcache", or"background"; seeS3FileSystem. Withoutblock_sizeorcache_type, a CSV result file is read in 32 MiB blocks with fsspec’s background cache, which reads the next block while pandas parses the current one. Withoutcache_type, PyAthena’s filesystem reads a Parquet file of a chunked UNLOAD result that is larger than 32 MiB without a cache.max_workers – The size of the request pool of the S3 filesystem that reads the result files. If None, the
max_pool_connectionsof the connection’s S3 client.auto_optimize_chunksize – Enable automatic chunksize determination for large files when chunksize is None.
result_set_type_hints – Athena type signatures for complex-type columns, keyed by column name (case-insensitive) or zero-based column index.
**kwargs – Additional arguments passed to pandas.read_csv/read_parquet.
coerce_int96_timestamp_unit=Noneis the same as nocoerce_int96_timestamp_unitfor an UNLOAD result. A givenstorage_options, even None, replaces PyAthena’s S3 filesystem for reading the result files, and so doesfilesystemfor UNLOAD results. The UNLOAD manifest is still read with the connection’s S3 client, and the schema with PyAthena’s filesystem.
- property dtypes: dict[str, type[Any]]¶
Get pandas-compatible data types for result columns.
- Returns:
Dictionary mapping column names to their corresponding Python types based on the converter’s type mapping.
- property converters: dict[Any | None, Callable[[str | None], Any | None]]¶
The conversion functions for the result columns the converter maps, keyed by name.
- property parse_dates: list[Any | None]¶
The names of the result columns with date, time, or timestamp types.
- fetchone() tuple[Any | None, ...] | dict[Any, Any | None] | None[source]¶
Fetch the next row of the result.
- DEFAULT_RESULT_REUSE_MINUTES = 60¶
- property connection: Connection[Any]¶
The connection of the result set; raises
ProgrammingErrorif closed.
- property data_manifest_location: str | None¶
The S3 location of the data manifest that lists the files the query wrote.
- property description: list[tuple[str, str, None, None, int, int, str]] | None¶
The DB API 2.0 column descriptions.
None without result metadata, or for
INSERT,UPDATE,DELETE, andMERGE. The metadata reader of the result set formats them (seeAthenaResultSetMetadataReader.description()). A subclass that changes the descriptions does so in its reader class (_metadata_reader_class), whichAsyncCursor.description()also reads with.
- property encryption_option: str | None¶
The
EncryptionOptionof the query results, such asSSE_S3orSSE_KMS.
- property engine_execution_time_in_millis: int | None¶
The time in milliseconds that the query engine took to run the query.
- property error_category: int | None¶
1 for system, 2 for user, 3 for other.
- Type:
The
ErrorCategoryof the failure
- property expected_bucket_owner: str | None¶
The AWS account ID expected to own the S3 bucket of the query results.
- fetchall() list[tuple[Any | None, ...] | dict[Any, Any | None]]¶
Fetch all remaining rows of the query result.
- Returns:
The remaining rows.
- Raises:
ProgrammingError – If the result set is closed or an earlier fetch failed.
- fetchmany(size: int | None = None) list[tuple[Any | None, ...] | dict[Any, Any | None]]¶
Fetch the next set of rows of the query result.
- Parameters:
size – Maximum number of rows to fetch. If None or not positive,
arraysizeis used.- Returns:
The rows, fewer than
sizewhen the result is exhausted.- Raises:
TypeError – If
sizeis not an integer and not replaced witharraysize.ProgrammingError – If the result set is closed or an earlier fetch failed.
- property is_unload: bool¶
Whether the query is an UNLOAD statement that the cursor runs for its result.
Its result is read from the Parquet files of the UNLOAD output when the query has a result location; without one, as with managed query result storage, it is read from
GetQueryResults.- Returns:
True if the metadata reader of the result set finds an UNLOAD result.
- property query_planning_time_in_millis: int | None¶
The time in milliseconds that Athena took to plan the query.
- property query_queue_time_in_millis: int | None¶
The time in milliseconds that the query waited in the queue.
- property result_reuse_enabled: bool | None¶
Whether reuse of previous query results by age is enabled for the query.
- property result_reuse_minutes: int | None¶
The maximum age in minutes of a previous query result that Athena can reuse.
- property reused_previous_result: bool | None¶
Whether Athena reused a previous query result instead of running the query.
- property s3_acl_option: str | None¶
The
S3AclOptionof the query results, such asBUCKET_OWNER_FULL_CONTROL.
- property service_processing_time_in_millis: int | None¶
The time in milliseconds that Athena took to publish the query results.
- property state_change_reason: str | None¶
The
StateChangeReasonthat gives further detail about the state.
- property total_execution_time_in_millis: int | None¶
The total time in milliseconds that Athena took to run the query.
- as_pandas() PandasDataFrameIterator | DataFrame[source]¶
Return the query results as a DataFrame or an iterator of DataFrame chunks.
- Returns:
If
chunksizeis None, the DataFrame of the whole result, the same one on every call. Whenauto_optimize_chunksizechose a chunk size, one DataFrame that joins the chunks the result iterator has not yet yielded, which is the whole result only if neither the fetch methods noriter_chunks()read from it before, and a later call returns an empty DataFrame. Ifchunksizeis set, the iterator thatiter_chunks()returns.- Raises:
ProgrammingError – If the result set is closed.
- iter_chunks() PandasDataFrameIterator[source]¶
Iterate over result chunks as pandas DataFrames.
This method provides an iterator interface for processing large result sets. When the result is read in chunks, because chunksize is specified for a CSV result or for an UNLOAD result read with at most the
columns(distinct top-level column names without dots) anddtype_backendoptions, orauto_optimize_chunksizechose a chunk size for a CSV result, it yields DataFrames in chunks for memory-efficient processing. These chunks come from the same iterator as the fetch methods, so a chunk that one of them reads is not available to the other. Otherwise, each call returns a new iterator that yields the entire result as a single DataFrame, and the fetch methods keep their position.- Returns:
PandasDataFrameIterator that yields pandas DataFrames for each chunk of rows, or the entire DataFrame if the result was not read in chunks. Its reads raise
ProgrammingErroronce the result set is closed.- Raises:
ProgrammingError – If the result set is closed.
Example
>>> # With chunking for large datasets >>> cursor = connection.cursor(PandasCursor, chunksize=50000) >>> cursor.execute("SELECT * FROM large_table") >>> for chunk in cursor.iter_chunks(): ... process_chunk(chunk) # Each chunk is a pandas DataFrame >>> >>> # Without chunking - yields entire result as single chunk >>> cursor = connection.cursor(PandasCursor) >>> cursor.execute("SELECT * FROM small_table") >>> for df in cursor.iter_chunks(): ... process(df) # Single DataFrame with all data
- class pyathena.pandas.result_set.AthenaPandasResultSetMetadataReader(connection: Connection[Any], query_execution: AthenaQueryExecution, retry_config: RetryConfig, unload: bool = False, unload_location: str | None = None, engine: str = 'auto', chunksize: int | None = None, block_size: int | None = None, cache_type: str | None = None, max_workers: int | None = None, **kwargs: Any)[source]¶
Reads the metadata of a pandas result set, and the footers of its Parquet files.
It owns the read configuration that the metadata depends on: PyAthena’s S3 filesystem for the result files, the read options normalized for the result, the Parquet engine, and whether an UNLOAD result is read in chunks. A result read in chunks is described by the footer of its first file, read without listing the unload location; a result read whole by the schema of its dataset, which also has the partition columns of a partitioned UNLOAD.
AthenaPandasResultSettakes the first footer (take_parquet_first_footer()) and reads the footers of the other files (read_parquet_footers()) for their row counts and the dtypes of the columns that hold NULL.- __init__(connection: Connection[Any], query_execution: AthenaQueryExecution, retry_config: RetryConfig, unload: bool = False, unload_location: str | None = None, engine: str = 'auto', chunksize: int | None = None, block_size: int | None = None, cache_type: str | None = None, max_workers: int | None = None, **kwargs: Any) None[source]¶
Initialize the reader and the filesystem of the result files; it sends no request.
- Parameters:
connection – The connection that ran the query.
query_execution – The query execution whose result set to read.
retry_config – The retry configuration for API calls.
unload – Whether the cursor runs its queries as UNLOAD to Parquet.
unload_location – The S3 location of the UNLOAD output.
engine – The parsing engine:
"auto","c","python", or"pyarrow".chunksize – The number of rows per chunk, if the result is read in chunks.
block_size – The S3 read block size of the filesystem.
cache_type – The default fsspec cache type of the filesystem.
max_workers – The size of the request pool of the S3 filesystem that reads the result files. If None, the
max_pool_connectionsof the connection’s S3 client.**kwargs – The options of
pandas.read_csv()orpandas.read_parquet().coerce_int96_timestamp_unit=Noneis dropped for an UNLOAD result.
- property filesystem: S3FileSystem¶
PyAthena’s S3 filesystem that reads the result files, created on first use.
- property read_kwargs: dict[str, Any]¶
The read options of the result set, normalized for the result.
- property parquet_engine: str¶
The Parquet engine, with
"auto"resolved to an available one.- Raises:
ImportError – If pyarrow is not available for
"auto".
- static available_engine(engine_candidates: list[str]) str[source]¶
Get the first available engine from a list of candidates.
- Parameters:
engine_candidates – List of engine names to try in order.
- Returns:
First available engine name.
- Raises:
ImportError – If no engines are available.
- reads_parquet_in_chunks() bool[source]¶
Whether the options allow reading the UNLOAD result in chunks of
chunksizerows.Chunks are read with
ParquetChunkReaderthrough PyAthena’s filesystem whenexecute()was given at most thesepandas.read_parquet()options:dtype_backendas"numpy_nullable"or"pyarrow", andcolumnsas distinct names without dots, whichread()also requires to be top-level columns of the result. A name with a dot can select a nested field inpandas.read_parquet(). With other options, the result is read whole, as withoutchunksize, and pandas validates them.- Returns:
True if the options allow reading the UNLOAD result in chunks.
Take the footer of the first file of an UNLOAD result read in chunks.
- Returns:
The footer that
read()read, if the result is read in chunks; None for a result read whole, and on later calls.
Read the footers of Parquet files of the UNLOAD result.
The footers are read concurrently, with no more threads than the connection pool of the S3 client.
- Parameters:
paths – The paths of the files, from the data manifest.
- Returns:
The footers of the files, in the order of
paths.- Raises:
OperationalError – If reading the footers fails.
- build_get_query_results_request(max_results: int, next_token: str | None = None) dict[str, Any]¶
Build a
GetQueryResultsrequest for the query.- Parameters:
max_results – The maximum number of rows in the page.
next_token – The token of the page to get; the first page if None.
- Returns:
The request parameters.
- Raises:
ProgrammingError – If the query ID is missing or the query has not succeeded.
- property data_manifest: list[str]¶
The S3 paths of the Parquet files that
read()read from the data manifest.
- describe() list[tuple[str, str, None, None, int, int, str]] | None¶
Read the column descriptions of the result set without its rows.
- Returns:
The descriptions, as
description()formats them; None if the query has not succeeded, without a request.- Raises:
OperationalError – If reading the metadata fails.
ProgrammingError – If the query execution of an UNLOAD result has no data manifest location.
- description(metadata: tuple[dict[str, Any], ...] | None) list[tuple[str, str, None, None, int, int, str]] | None¶
Format the DB API 2.0 column descriptions of column metadata.
- Parameters:
metadata – The column metadata.
- Returns:
The descriptions; None without metadata, or for
INSERT,UPDATE,DELETE, andMERGE.
- get_query_results(max_results: int, next_token: str | None = None) dict[str, Any]¶
Get a page of query results with
GetQueryResults.- Parameters:
max_results – The maximum number of rows in the page.
next_token – The token of the page to get; the first page if None.
- Returns:
The
GetQueryResultsresponse.- Raises:
ProgrammingError – If the query ID is missing or the query has not succeeded.
OperationalError – If the request fails.
- property is_unload: bool¶
Whether the query is an UNLOAD statement that the cursor runs for its result.
- property metadata: tuple[dict[str, Any], ...] | None¶
The column metadata that
read()read; None before it.
- parse_metadata(response: dict[str, Any]) tuple[dict[str, Any], ...]¶
Parse the column metadata of a
GetQueryResultsresponse.- Parameters:
response – The
GetQueryResultsresponse.- Returns:
The
ColumnInfoof the response.- Raises:
DataError – If the response has no result metadata.
- property query_execution: AthenaQueryExecution¶
The query execution whose result set the reader reads.
- read() None¶
Read the metadata, from the Parquet files of the UNLOAD output.
A result read from the files has no update count, and no
GetQueryResultsrequest is sent for it.- Raises:
OperationalError – If reading the data manifest or the metadata fails.
ProgrammingError – If the query execution has no data manifest location.
- property reads_unload_files: bool¶
Whether the result set is read from the Parquet files of the UNLOAD output.
A query without a result location, as with managed query result storage, is read from
GetQueryResults.
- class pyathena.pandas.result_set.PandasDataFrameIterator(reader: TextFileReader | Iterator[DataFrame] | DataFrame, trunc_date: Callable[[DataFrame], DataFrame], csv_stream: IOBase | None = None, fetch_types: Mapping[Any, str] | None = None, closed: threading.Event | None = None)[source]¶
Iterator for chunked DataFrame results from Athena queries.
This class wraps either an iterator of DataFrame chunks, such as a pandas TextFileReader for CSV results or the chunks of an UNLOAD result, or a single DataFrame, providing a unified iterator interface. It applies optional date truncation to each DataFrame chunk as it’s yielded.
The iterator is used by AthenaPandasResultSet to provide chunked access to large query results, enabling memory-efficient processing of datasets that would be too large to load entirely into memory.
Example
>>> # Iterate over DataFrame chunks >>> for df_chunk in iterator: ... process(df_chunk) >>> >>> # Iterate over individual rows >>> for idx, row in iterator.iterrows(): ... print(row)
Note
This class is primarily for internal use by AthenaPandasResultSet. Most users should access results through PandasCursor methods.
- __init__(reader: TextFileReader | Iterator[DataFrame] | DataFrame, trunc_date: Callable[[DataFrame], DataFrame], csv_stream: IOBase | None = None, fetch_types: Mapping[Any, str] | None = None, closed: threading.Event | None = None) None[source]¶
Initialize the iterator.
- Parameters:
reader – A TextFileReader or another iterator of DataFrame chunks, or a single DataFrame.
trunc_date – Function to apply to each chunk, such as date truncation.
csv_stream – Optional CSV stream owned and closed by this iterator.
fetch_types – The labels of the datetime columns whose values the row iterators return as
"datetime"or"date"objects, with None for NaT, instead of pandasTimestampvalues.closed – Set when the result set that created the iterator is closed, after which reads raise
ProgrammingError.
- __next__() DataFrame[source]¶
Get the next DataFrame chunk.
- Returns:
The next pandas DataFrame chunk with date truncation applied.
- Raises:
StopIteration – When no more chunks are available.
ProgrammingError – If an earlier read failed, or the result set that created the iterator is closed.
- __iter__() PandasDataFrameIterator[source]¶
Return self as iterator.
- __enter__() PandasDataFrameIterator[source]¶
Context manager entry.
- iterrows() Iterator[tuple[int, dict[str, Any]]][source]¶
Iterate over rows as (index, row_dict) tuples.
Row indices are continuous across all chunks, starting from 0.
Once the result set that created the iterator is closed, the rows of the chunk already read are still returned, and reading the next chunk raises
ProgrammingError.- Yields:
Tuple of (row_index, row_dict) for each row across all chunks.
- itertuples() Iterator[tuple[Any, ...]][source]¶
Iterate over rows as tuples of their values across all chunks.
The tuples hold the values of the columns by position, as
DataFrame.itertuples(index=False, name=None)gives them, so columns with the same name keep their own values. The next chunk is read when the rows of the current one run out. After an exception, such as a value that fails to convert, the iteration ends.Once the result set that created the iterator is closed, the rows of the chunk already read are still returned, and reading the next chunk raises
ProgrammingError.- Yields:
The rows of all remaining chunks.
- get_chunk(size: int | None = None) DataFrame[source]¶
Get a chunk of specified size.
- Parameters:
size – Number of rows to retrieve. If None, returns entire chunk.
- Returns:
DataFrame chunk, with date truncation applied as in iteration.
- Raises:
StopIteration – When no more chunks are available.
ProgrammingError – If an earlier read failed, or the result set that created the iterator is closed.
- as_pandas() DataFrame[source]¶
Collect all remaining chunks into a single DataFrame.
The chunks keep their index, so the result has the row numbers or the
index_colvalues of the CSV file. Categorical columns and a categorical index stay categorical. Categories given in the dtype keep their order; when the chunks inferred different categories, they are inferred again from all chunks in sorted order. A whole-file read of a large file can order inferred categories differently, because pandas joins its internal parser blocks in the order they were read.- Returns:
Single pandas DataFrame containing all data.
Pandas Data Converters¶
- class pyathena.pandas.converter.DefaultPandasTypeConverter[source]¶
Optimized type converter for pandas DataFrame results.
This converter is specifically designed for the PandasCursor and provides optimized type conversion that works well with pandas data types. It minimizes conversions for types that pandas handles efficiently and only converts complex types that need special handling.
- The converter focuses on:
Preserving numeric types for pandas optimization
Converting only complex types (json, binary, etc.)
Maintaining compatibility with pandas data type inference
Example
>>> from pyathena.pandas.converter import DefaultPandasTypeConverter >>> converter = DefaultPandasTypeConverter() >>> >>> # Used automatically by PandasCursor >>> cursor = connection.cursor(PandasCursor) >>> # converter is applied automatically to results
Note
This converter is used by default in PandasCursor. Most users don’t need to instantiate it directly.
- __init__() None[source]¶
Initialize the converter with the default pandas conversion functions and dtypes.
- convert(type_: str, value: str | None, type_hint: str | None = None) Any | None[source]¶
Convert a value returned by Athena to a Python object.
- Parameters:
type – The Athena data type name.
value – The string value to convert, or None.
type_hint – Optional Athena DDL type signature of the value.
- Returns:
The converted value.
- class pyathena.pandas.converter.DefaultPandasUnloadTypeConverter[source]¶
Type converter for pandas UNLOAD operations.
This converter is designed for use with UNLOAD queries that write results directly to Parquet files in S3. Since UNLOAD operations bypass the normal conversion process and write data in native Parquet format, this converter has minimal functionality.
Note
Used automatically when PandasCursor is configured with unload=True. UNLOAD results are read directly as DataFrames from Parquet files.
- convert(type_: str, value: str | None, type_hint: str | None = None) Any | None[source]¶
Convert a value returned by Athena to a Python object.
- Parameters:
type – The Athena data type name.
value – The string value to convert, or None.
type_hint – Optional Athena DDL type signature of the value.
- Returns:
The converted value.
Pandas Utilities¶
- pyathena.pandas.util.get_chunks(df: DataFrame, chunksize: int | None = None) Iterator[DataFrame][source]¶
Split a DataFrame into chunks of specified size.
- Parameters:
df – The DataFrame to split into chunks.
chunksize – Number of rows per chunk. If None, yields the entire DataFrame.
- Yields:
DataFrame chunks of the specified size.
- Raises:
ValueError – If chunksize is less than or equal to zero.
- pyathena.pandas.util.reset_index(df: DataFrame, index_label: str | None = None) None[source]¶
Reset the DataFrame index and add it as a column.
- Parameters:
df – The DataFrame to reset the index on (modified in-place).
index_label – Name for the index column. Defaults to “index”.
- Raises:
ValueError – If the index name conflicts with existing column names.
- pyathena.pandas.util.as_pandas(cursor: Cursor, coerce_float: bool = False) DataFrame[source]¶
Convert cursor results to a pandas DataFrame.
Fetches all remaining rows from the cursor and converts them to a DataFrame with column names from the cursor description.
- Parameters:
cursor – A PyAthena cursor with executed query results.
coerce_float – If True, attempt to convert non-string columns to float.
- Returns:
A DataFrame containing the query results, or an empty DataFrame if no results are available.
- pyathena.pandas.util.to_sql_type_mappings(col: Series) str[source]¶
Map a pandas Series data type to an Athena SQL type.
Infers the appropriate Athena SQL type based on the pandas Series dtype. Used when creating tables from DataFrames.
- Parameters:
col – A pandas Series to determine the SQL type for.
- Returns:
The Athena SQL type name (e.g., “STRING”, “BIGINT”, “DOUBLE”).
- Raises:
ValueError – If the data type is not supported (complex, time).
- pyathena.pandas.util.to_parquet(df: DataFrame, bucket_name: str, prefix: str, retry_config: RetryConfig, session_kwargs: dict[str, Any], client_kwargs: dict[str, Any], compression: str | None = None, flavor: str = 'spark') str[source]¶
Write a DataFrame to S3 as a Parquet file.
Converts the DataFrame to Apache Arrow format and writes it to S3 as a Parquet file with a UUID-based filename.
- Parameters:
df – The DataFrame to write.
bucket_name – S3 bucket name.
prefix – S3 key prefix (path within the bucket).
retry_config – The retry policy of the upload. It is resolved with
resolve_retry_config(), so a policy with the default codes also retries the S3 error codes ofRETRY_ERROR_CODES.session_kwargs – Arguments for creating a boto3 Session.
client_kwargs – Arguments for creating the S3 client.
compression – Parquet compression codec (e.g., “snappy”, “gzip”).
flavor – The pyarrow Parquet flavor.
"spark"writes timestamps as INT96 and replaces the characters in column names that Spark does not accept.
- Returns:
The S3 URI of the written Parquet file.
- pyathena.pandas.util.to_sql(df: DataFrame, name: str, conn: Connection[Any], location: str, schema: str = 'default', index: bool = False, index_label: str | None = None, partitions: list[str] | None = None, chunksize: int | None = None, if_exists: str = 'fail', compression: str | None = None, flavor: str = 'spark', type_mappings: Callable[[Series], str] = <function to_sql_type_mappings>, executor_class: type[ThreadPoolExecutor | ProcessPoolExecutor] = <class 'concurrent.futures.thread.ThreadPoolExecutor'>, max_workers: int = 20, repair_table=True) None[source]¶
Write a DataFrame to an Athena table backed by Parquet files in S3.
Creates an external Athena table from a DataFrame by writing the data as Parquet files to S3 and executing the appropriate DDL statements. Supports partitioning, compression, and parallel uploads.
The S3 requests use the connection’s
s3_configand credentials. The upload workers resolve the credentials themselves, except for a connection given asessionand no explicit keys, or created withrole_arnorserial_number, whose session’s credentials they receive once, when the uploads start. They are not refreshed during the uploads, so an upload that runs past their expiry fails.The S3 requests, the uploads and, with
if_exists="replace", the listing and the deletion of the existing objects, are retried with the connection’s retry policy, resolved as for the S3 filesystem (resolve_retry_config()): a policy with the default codes also retries the S3 error codes ofRETRY_ERROR_CODES. The deletion is retried as a whole: a retry lists the objects that remain and deletes them.The SQL statements run on a
pyathena.cursor.Cursor, whatever the connection’s cursor class is, with the connection’s query settings, such aswork_group, except that the existence check never reuses a query result; the connection’sconverterand the options of its cursor class, such asunloadandarraysize, do not apply.- Parameters:
df – The DataFrame to write to Athena.
name – Name of the table to create.
conn – PyAthena connection object.
location – S3 location for the table data (e.g., “s3://bucket/path/”).
schema – Database schema name. Defaults to “default”.
index – If True, include the DataFrame index as a column.
index_label – Name for the index column if index=True.
partitions – List of column names to use as partition keys.
chunksize – Number of rows per Parquet file. None for single file.
if_exists – Action if table exists: “fail”, “replace”, or “append”.
compression – Parquet compression codec (e.g., “snappy”, “gzip”).
flavor – The pyarrow Parquet flavor.
"spark"writes timestamps as INT96 and replaces the characters in column names that Spark does not accept.type_mappings – Function to map pandas types to SQL types.
executor_class – Executor class for parallel uploads.
max_workers – Maximum number of parallel upload workers.
repair_table – If True, run ALTER TABLE ADD PARTITION for partitioned tables.
- Raises:
ValueError – If if_exists is invalid, compression is unsupported, or partition keys contain None values.
OperationalError – If if_exists=”fail” and table already exists, or if if_exists=”replace” and S3 answers that it could not delete some of the existing objects; this answer is not retried. The table has been dropped then, and no data is written. A later call deletes the objects under
locationonly when the table exists, so delete the remaining objects before calling it again.
- pyathena.pandas.util.get_column_names_and_types(df: DataFrame, type_mappings) OrderedDict[str, str][source]¶
Extract column names and their SQL types from a DataFrame.
- Parameters:
df – The DataFrame to extract column information from.
type_mappings – Function to map pandas types to SQL types.
- Returns:
An OrderedDict mapping column names to their SQL type strings.
- pyathena.pandas.util.generate_ddl(df: DataFrame, name: str, location: str, schema: str = 'default', partitions: list[str] | None = None, compression: str | None = None, type_mappings: Callable[[Series], str] = <function to_sql_type_mappings>, flavor: str | None = None) str[source]¶
Generate CREATE EXTERNAL TABLE DDL for a DataFrame.
Creates DDL for an external Athena table with Parquet storage format based on the DataFrame’s schema.
- Parameters:
df – The DataFrame to generate DDL for.
name – Name of the table to create.
location – S3 location for the table data.
schema – Database schema name. Defaults to “default”.
partitions – List of column names to use as partition keys.
compression – Parquet compression codec, set as the
parquet.compressiontable property.type_mappings – Function to map pandas types to SQL types.
flavor – The pyarrow Parquet flavor of the files. With
"spark", the columns other than the partition keys are declared with the names that the flavor writes, in which spaces, tabs, newlines, and,;{}()=are replaced with_. Pass the flavor thatto_parquet()wrote the files with; without one, the DataFrame’s names are declared.
- Returns:
The CREATE EXTERNAL TABLE DDL statement as a string.