如何使用snowflake连接器游标获取表数据和列名。嗯,我可以通过独占游标获得它,但是合并结果集变得很复杂,因为它将所有数据都作为键对提供。我想知道是否有任何直接的方法。
发布于 2021-06-14 17:04:10
要获得列标题,可以使用游标的description属性,该属性返回结果的元数据,并被描述为here。
如下所示使用它:
import snowflake.connector
import os
snowflake_username = os.environ['SNOWFLAKE_USERNAME']
snowflake_password = os.environ['SNOWFLAKE_PASSWORD']
snowflake_account = os.environ['SNOWFLAKE_ACCOUNT']
snowflake_warehouse = os.environ['SNOWFLAKE_WAREHOUSE']
snowflake_database = 'sample_data'
snowflake_schema = 'tpch_sf1'
if __name__ == '__main__':
with snowflake.connector.connect(
user=snowflake_username,
password=snowflake_password,
account=snowflake_account,
warehouse=snowflake_warehouse,
database=snowflake_database,
schema=snowflake_schema,
autocommit=False
) as con:
# Execute cursor and print metadata
cursor = con.cursor().execute("select * from sample_data.TPCH_SF1.customer limit 10")
for c in cursor.description:
print(c)
# # Fetch and print results
# results = cursor.fetchall()
# print(results)上面的打印输出:
('C_CUSTKEY', 0, None, None, 38, 0, False)
('C_NAME', 2, None, 25, None, None, False)
('C_ADDRESS', 2, None, 40, None, None, False)
('C_NATIONKEY', 0, None, None, 38, 0, False)
('C_PHONE', 2, None, 15, None, None, False)
('C_ACCTBAL', 0, None, None, 12, 2, False)
('C_MKTSEGMENT', 2, None, 10, None, None, True)
('C_COMMENT', 2, None, 117, None, None, True)发布于 2021-08-12 16:32:39
我在使用Jupyter notebooks中的python snowflake连接器时也有同样的问题。我使用的是dataframes,所以根据@SimonD上面的回答,我用cursor.description修改了这一部分,以:
hdrs = pd.DataFrame(cursor.description)
df = pd.DataFrame(sql_data)
从我的数据中,得到的hdrs数据帧有一个属性'name‘,我可以用它来设置df数据帧的列名,如下所示:
df.columns = hdrs['name']
https://stackoverflow.com/questions/67945837
复制相似问题