SNOW-650716: SnowflakeCursor doesn't call `_prefetch_hook()` before yielding results of an `execute_async()`

[Kache]

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using?

Python 3.7.6

2. What operating system and processor architecture are you using?

Darwin-20.6.0-x86_64-i386-64bit

3. What are the component versions in the environment (pip freeze)?

   Replace with the output of python -m pip freeze Not particularly relevant, should be able to repro in a blank project.

4. What did you do?

Following from docs: https://docs.snowflake.com/en/user-guide/python-connector-example.html

def exec_and_log(conn, sql_str):
    cur = conn.cursor()
    if not cur.execute_async(sql_str):
        raise Exception('Some unknown error in Snowflake SDK')

    sfqid = str(cur.sfqid or '')
    logging.info(sfqid)
    cur.get_results_from_sfqid(sfqid) 
    yield from cur

_result_iterator() will fail because cur._result is not set, with TypeError: 'NoneType' object is not iterable at:

https://github.com/snowflakedb/snowflake-connector-python/blob/8bcd1fbfc4f01ff4d48226a7a15691c9e406b67c/src/snowflake/connector/cursor.py#L1089

5. What did you expect to see?

(I may be mistaken, but it seems clear that...) Cursor should first wait for results, then yield them in the same manner as fetchone() does by calling _prefetch_hook(): https://github.com/snowflakedb/snowflake-connector-python/blob/8bcd1fbfc4f01ff4d48226a7a15691c9e406b67c/src/snowflake/connector/cursor.py#L1104-L1114

For example, my current workaround:

def exec_and_log(conn, sql_str):
    cur = conn.cursor()
    if not cur.execute_async(sql_str):
        raise Exception('Some unknown error in Snowflake SDK')

    sfqid = str(cur.sfqid or '')
    logging.info(sfqid)

    # these private accesses to prepare internal state shouldn't be necessary
    cur._connection = conn  # have to do this if cursor was closed, despite connection still being open
    cur.get_results_from_sfqid(sfqid) 
    if cur._prefetch_hook:
        cur._prefetch_hook()  # calls wait_until_ready(), which'll set self._result_set

    yield from cur

But it should instead be called internally, perhaps at the beginning of __iter__() (note section with self._result_set is identical to fetchone()): https://github.com/snowflakedb/snowflake-connector-python/blob/8bcd1fbfc4f01ff4d48226a7a15691c9e406b67c/src/snowflake/connector/cursor.py#L1193-L1199

Afterwards, the body of fetchone() can be just: return next(iter(self))

6. Can you set logging to DEBUG and collect the logs?

   import logging
   import os
   
   for logger_name in ('snowflake.connector',):
       logger = logging.getLogger(logger_name)
       logger.setLevel(logging.DEBUG)
       ch = logging.StreamHandler()
       ch.setLevel(logging.DEBUG)
       ch.setFormatter(logging.Formatter('%(asctime)s - %(threadName)s %(filename)s:%(lineno)d - %(funcName)s() - %(levelname)s - %(message)s'))
       logger.addHandler(ch)