Iterating a cursor or calling fetchone() in a loop is 100x+ slower than fetchall() (affects SQLAlchemy) #981

Iterating a cursor (for row in cursor) or calling fetchone() in a loop costs about 0.6 to 0.8 ms per row, more than 100x slower than fetchall(). SQLAlchemy's default Result iteration calls cursor.fetchone() once per row, so a 1M-row read through SQLAlchemy takes many minutes instead of seconds

Example (20,000 rows of bigint + string from an Arrow result, connector main at 61b9a7f):

Cause: ResultSet.__iter__ calls fetchone(), and every backend's fetchone() (Thrift, SEA and kernel) fetches a single row and converts it on its own. On the Arrow path that goes through _convert_arrow_table, which builds a pandas DataFrame for each row

The change is larger than 100 lines because the three backends each had their own fetchone, so I'm opening this issue alongside the fix. I have a fix with tests ready and am opening the PR now. It converts rows arraysize at a time and serves fetchone() from a small buffer that every other fetch method drains first, so results and ordering stay the same. With it, the same 20,000 rows take about 0.1s for both iteration and the fetchone() loop