使用Snowflake Python连接器的Pandas数据帧

Question

尝试使用Snowflake的连接器获取数据查询。

import snowflake.connector as sf
import pandas as pd
import sys
ctx = sf.connect(
user='<user>',
password='<password>',
account='<account>',
warehouse='<warehouse?',
database='<db>',
schema='<schema>',
)
cs = ctx.cursor()
try:
cs.execute('select TOP 5 fish, price from fishtable order by fish;')
except Exception as error:
error = sys.exc_info()[0]
message = sys.exc_info()[1]
print(f"Error: {error}nMessage: {message}")
finally:
ctx.close()

print(cs.rowcount)
print(cs.sfqid)
df = cs.fetch_pandas_all()   #could not get this to work
df

print(cs.rrowcount(显示正确的总数5。

在snowflake控制台中使用sfqid可以按预期返回查询结果。

但DF中没有数据。。。

Answer 1

应在关闭/处理连接之前完成分配：

try:
cs.execute('select TOP 5 fish, price from fishtable order by fish;')
df = cs.fetch_pandas_all() 
except Exception as error:
error = sys.exc_info()[0]
message = sys.exc_info()[1]
print(f"Error: {error}nMessage: {message}")
finally:
ctx.close()
df