Share the pure logic duplicated between sync and asyncio cursors

[laughingman7743]

Use case

Most asyncio cursor and result-set methods in pyathena/aio/ are near-copies of their sync counterparts.
Their bodies differ only in await, retry_api_call versus async_retry_api_call, time.sleep versus asyncio.sleep, and KeyboardInterrupt versus asyncio.CancelledError.
Each copy carries the same request building, response parsing, state checks, and log messages, so a fix in one version can miss the other.
One drift already happened: the async Spark cursor logged Failed to terminate session. while the sync one logged the session ID. #879 aligns that message.

Approximate duplicated method bodies, counted on the #879 branch (docstrings excluded; the docstrings are duplicated too):

Area	Sync / async files	Duplicated pairs	Lines
Cursor base	common.py, result_set.py (WithFetch) / aio/common.py	17, plus 10 verbatim WithFetch / WithAsyncFetch members	~380
Spark	common.py, spark/common.py, spark/cursor.py / aio/spark/cursor.py	15	~130
Result set	result_set.py / aio/result_set.py	7	~65
Arrow, pandas, Polars, S3FS cursors	{arrow,pandas,polars,s3fs}/cursor.py / aio/{...}/cursor.py	~16, plus fetchone / fetchmany / fetchall repeated in each of the 5 aio cursors	~390

The aio format cursors reuse the sync result sets through asyncio.to_thread, so the format result sets themselves are not duplicated.

Proposed change

Move the pure (non-I/O) parts into single-underscore helpers on the existing base classes, following the existing _build_* request builders.
Keep separate sync and async methods for the I/O calls themselves; do not add a sans-I/O layer or a code generator.
Candidates, largest first:

• _find_previous_query_id: the cache-window setup and the filter / sort / expiry / match loop are pure; only _list_query_executions is I/O.
• The execute prologue (ExecuteOptions.resolve, _prepare_query / _prepare_unload, _build_start_query_execution_request), repeated in _execute and in the 10 format cursors' execute.
• The result-set kwargs built in each format cursor's execute.
• The verbatim WithFetch / WithAsyncFetch members (arraysize, rownumber, rowcount, close, and so on), which could live on WithResultSet.
• The aio format cursors' fetchone / fetchmany / fetchall, which differ only in a cast.
• Response parsing in list_databases, list_table_metadata, and _batch_get_query_execution.

Keep the signatures of the single-underscore methods that downstream code calls (_execute, _poll, _cancel, _get_query_execution, _find_previous_query_id; see #734).
No behavior change. Split the work into one PR per area in the table.

Validation plan (if implementing)

• just lint.
• For each area, the matching sync and aio PyAthena tests on AWS (cursor, aio cursor, Spark, and the format cursors), plus just test sqla and just test sqla-async when the format cursors change.
• Re-count the duplicated bodies per area to show the reduction.

[laughingman7743]

Handoff after the cursor-base area

PR #883 (cursor base, the first of the four areas) is merged at f15e640439c33fc77df3dcd683a3b484146d9b5c.
This issue remains open for the Result set, Spark, and format-cursor areas, one PR each.

What landed in #883

• BaseCursor._cache_search_limits and _match_previous_query hold the I/O-free parts of _find_previous_query_id; BaseCursor._build_execute_request holds the _execute prologue after ExecuteOptions.resolve.
• WithFetch is removed. WithResultSet is now class WithResultSet(BaseCursor, CursorIterator) and holds the result set properties, the fetch methods, close, and the sync executemany / cancel. The sync SQL cursors subclass it directly; WithAsyncFetch(AioBaseCursor, WithResultSet) overrides executemany / cancel with async versions.
• Duplicated sync/aio pairs in the cursor-base area: 30 pairs / 344 aio body lines → 17 / 281. The remaining pairs are I/O methods that this issue keeps separate.

Counting method (not the one behind the estimates in the issue table): method pairs with the same name in the sync class and its aio counterpart whose bodies are identical, or mostly shared, after removing await, retry_api_call / async_retry_api_call, time.sleep / asyncio.sleep, and KeyboardInterrupt / asyncio.CancelledError; docstrings, comments, and blank lines excluded. Re-count each area before and after with the same method.

Decisions to carry forward

• Keep the signatures of _execute, _poll, _cancel, _get_query_execution, and _find_previous_query_id (Backwards incompatible changes in 3.35 breaks dbt projects #734).
• Plain single-underscore methods on the owning base class; no __name helpers, no sans-I/O layer, no code generator.
• No _parse_*_response helpers for one-expression response parsing: with docstrings they add more lines than they remove.
• WithAsyncFetch and WithResultSet keep their names for now, although WithAsyncFetch no longer holds fetch methods and WithResultSet is now a base class. Revisit after the format-cursor area; if the aio fetch methods are shared there, WithAsyncFetch holds fetch methods again.

Pitfalls found in #883

• Base-class order decides which copy wins. A member moved to a class that comes after CursorIterator in the MRO is shadowed by CursorIterator's arraysize (capped at 1000), rownumber, rowcount, and abstract fetch methods. A member that comes after BaseCursor is shadowed by its abstract close, which makes every cursor abstract.
• For each structural change, compare, for every SQL cursor class, the member each name resolves to, against master (instructions compared by value, since a new docstring shifts constant indexes), and check that __abstractmethods__ is empty.
• Add no-AWS tests that pin the current behavior before moving logic, and check that they fail rather than hang or pass vacuously on a regression (the cache search swallows exceptions and returns None).

Remaining areas

1. Result set: result_set.py / aio/result_set.py (7 pairs, ~65 lines in the issue table). Plain for loops over asyncio cursors never end and yield coroutines #898 changes AthenaAioResultSet in the same file; decide the order first.
2. Spark: common.py, spark/common.py, spark/cursor.py / aio/spark/cursor.py (15 pairs, ~130 lines).
3. Format cursors: {arrow,pandas,polars,s3fs}/cursor.py / aio/{...}/cursor.py, plus fetchone / fetchmany / fetchall repeated in each aio cursor (differ only in a cast), the execute prologue (_reset_state, ExecuteOptions.resolve, _prepare_unload), and the result-set kwargs built in each execute. Run just test sqla and just test sqla-async for this area.

Related

• SQL cursors ignore the arraysize keyword argument #897: the SQL cursors ignore the arraysize keyword argument (BaseCursor.__init__ does not forward it). The natural place for the fix is WithResultSet.__init__.
• Plain for loops over asyncio cursors never end and yield coroutines #898: a plain for loop over an asyncio cursor (or AthenaAioResultSet) never ends and yields coroutines.
• Release notes for 4.0.0 (breaking): pyathena.result_set.WithFetch is removed; WithResultSet now subclasses BaseCursor and CursorIterator, so a class that lists either of them before WithResultSet fails with an MRO TypeError.

Copyable kickoff prompt

Continue pyathena-dev/PyAthena Issue #880 after merged PR #883
(f15e640439c33fc77df3dcd683a3b484146d9b5c). Read the issue body and its
handoff comment before taking action. Do not assume the local main checkout is
up to date.

Next area: Result set (pyathena/result_set.py and pyathena/aio/result_set.py),
one PR. First decide the order with #898, which changes AthenaAioResultSet in
the same file. Move only I/O-free logic to shared single-underscore methods;
keep separate sync and async methods for I/O, keep the private signatures listed
in the issue, and change no behavior.

Pin the current behavior with no-AWS tests before refactoring. Compare member
resolution of the affected classes against master, re-count the duplicated
pairs with the method in the handoff, and report the numbers in the PR. Follow
the development-workflow skill: Draft PR, two self-reviews, independent review,
then Ready and AWS CI.

[laughingman7743]

Result set area: no PR

The Result set area (pyathena/result_set.py / pyathena/aio/result_set.py) ends without a refactor PR.

The table's 7 pairs / ~65 lines were counted on the #879 branch, where f4cc848 had already moved the request building and response parsing into _build_get_query_results_request and _parse_result_rows.
The rest of the 7 pairs is I/O:

• _get_query_results / _async_get_query_results: the GetQueryResults call with its error handling. The closed-state messages also differ on purpose (AthenaResultSet is closed. / AthenaAioResultSet is closed.).
• fetchmany, fetchall, and __next__ / __anext__: loops around fetchone.

The I/O-free code that is still duplicated is small: about 5 lines after the first page is fetched in _pre_fetch / _async_pre_fetch, about 5 lines of row pop in fetchone, and the NextToken guard plus 2 lines in _fetch / _async_fetch.
A single-underscore method with a Google-style docstring would add about as many lines as it removes, the same reason the _parse_*_response helpers were rejected.

For the format-cursors area: the same fetchone row pop also appears in pyathena/arrow/result_set.py and pyathena/s3fs/result_set.py, 4 copies in total. Revisit it there if sharing becomes worthwhile.

#898, which touches AthenaAioResultSet in the same file, was fixed first as its own PR (#899).

Remaining areas: Spark next, then the format cursors.

[laughingman7743]

Correction to the cursor-base area: mixin design restored in #909

#883 (commit 7d4562a, "Fold WithFetch into WithResultSet") changed the class design without agreement. It removed WithFetch and made the WithResultSet mixin a subclass of BaseCursor and CursorIterator, with WithAsyncFetch overriding its sync members. With* classes are mixins by design, so this was a regression in the design, not a naming issue. The "revisit the names after the format-cursor area" note in the handoff above is withdrawn.

#909 restores the design and keeps the deduplication:

• WithResultSet is a mixin with no base class again. It holds the members WithFetch and WithAsyncFetch duplicated before Share the pure cursor-base logic between the sync and asyncio cursors #883 (__init__ with arraysize, result_set / query_id storage, arraysize, rownumber, rowcount, close).
• WithFetch(WithResultSet, BaseCursor, CursorIterator) is restored for the sync fetch, executemany, and cancel; WithAsyncFetch(WithResultSet, AioBaseCursor, CursorIterator) is its asyncio sibling.
• No behavior change (member resolution and instance state compared with master for the 12 SQL cursors). Duplicated pairs: 17 / 281, unchanged.

Release notes: with #909, the 4.0.0 items from #883 ("WithFetch is removed", MRO TypeError) no longer apply; a v3.37.0-style class MyCursor(BaseCursor, CursorIterator, WithResultSet) works again. The remaining difference from 3.x is that WithResultSet.result_set / query_id are concrete instead of abstract.

Next area: Spark, a small PR after #909 (removing the pass-through SparkCursor.__init__ / AioSparkCursor.__init__ and the unused BaseCursor._calculate). Measured 18 pairs / 145 aio body lines in the Spark area; the rest are I/O wrappers that this issue keeps separate.