Snowflake Python Connector无法正确返回查询结果求助

问题背景 #

要通过Snowflake Python Connector获取外部扫描字节数，对应的SQL语句如下：

select
    operator_statistics:io:external_bytes_scanned::number external_bytes_scanned
from table(get_query_operator_stats('01aacd75-0504-5897-000d-dd0700c50b36'))
where operator_type='ExternalScan'

编写的Python代码：

sql = f"""
    select
        operator_statistics:io:external_bytes_scanned::number external_bytes_scanned
    from table(get_query_operator_stats('01aacd75-0504-5897-000d-dd0700c50b36'))
    where operator_type='ExternalScan'
"""

con = snowflake.connector.connect(
    account="xxxxxxxxxx",
    user="yyyyyyyyyy",
    password="xxxxxxxxxxx",
    warehouse="ttttttttttt",
    database="tttt"
)

try:
    res = con.execute_string(sql)
    for x in res:
        print(x.__dict__)
    con.commit()
except Exception as e:
    raise e
finally:
    con.close()

执行后仅返回结果集描述信息，未获取到预期的external_bytes_scanned数值（315139374775）：

"_description": [
    [
      "EXTERNAL_BYTES_SCANNED",
      0,
      null,
      null,
      38,
      0,
      true
    ]
  ],

原因分析 #

execute_string()方法返回的是Cursor对象列表，但Cursor本身不会自动加载查询结果，你当前仅打印了Cursor的内部属性__dict__，其中只包含结果集的元数据（列名、数据类型等），没有实际的数值数据。必须主动调用Cursor的fetch方法才能获取结果。

另外，execute_string()更适合执行多条SQL语句的场景，单条SQL使用execute()方法会更简洁高效。

解决方案 #

方案1：使用execute()+fetch方法（推荐） #

修改代码用execute()执行单条SQL，再通过fetchone()或fetchall()获取结果：

sql = f"""
    select
        operator_statistics:io:external_bytes_scanned::number external_bytes_scanned
    from table(get_query_operator_stats('01aacd75-0504-5897-000d-dd0700c50b36'))
    where operator_type='ExternalScan'
"""

con = snowflake.connector.connect(
    account="xxxxxxxxxx",
    user="yyyyyyyyyy",
    password="xxxxxxxxxxx",
    warehouse="ttttttttttt",
    database="tttt"
)

try:
    cursor = con.cursor()
    cursor.execute(sql)
    # 获取单条结果
    result = cursor.fetchone()
    if result:
        external_bytes = result[0]
        print(f"External Bytes Scanned: {external_bytes}")
    # 若存在多条结果，改用fetchall()遍历
    # results = cursor.fetchall()
    # for row in results:
    #     print(row[0])
except Exception as e:
    raise e
finally:
    cursor.close()
    con.close()

方案2：保留execute_string()并添加fetch操作 #

如果坚持使用execute_string()，需对每个Cursor对象调用fetch方法：

try:
    res = con.execute_string(sql)
    for cursor in res:
        result = cursor.fetchone()
        if result:
            print(f"External Bytes Scanned: {result[0]}")
    con.commit()
except Exception as e:
    raise e
finally:
    con.close()

内容的提问来源于stack exchange，提问作者Santhosh