SQLAlchemy and Snowflake Query ID

Question

当使用Snowflake -connector-python时，可以获得雪花查询Id，即来自游标对象的sfqid属性。

使用Snowflake的SQLAlchemy Toolkit时是否可以获得该属性?文档页没有提到- https://docs.snowflake.com/en/user-guide/sqlalchemy.html.

谢谢,Eric

Answer 1

您可以使用sqlalchemy的execute方法并获得对SnowflakeCursor的引用，如下所示:

import os
from sqlalchemy import create_engine
from dotenv import load_dotenv
load_dotenv()
snowflake_username = os.getenv('SNOWFLAKE_USERNAME')
snowflake_password = os.getenv('SNOWFLAKE_PASSWORD')
snowflake_account = os.getenv('SNOWFLAKE_ACCOUNT')
snowflake_warehouse = os.getenv('SNOWFLAKE_WAREHOUSE')
snowflake_database = 'simon_db'
snowflake_schema = 'public'

if __name__ == '__main__':
engine = create_engine(
'snowflake://{user}:{password}@{account}/{db}/{schema}?warehouse={warehouse}'.format(
user=snowflake_username,
password=snowflake_password,
account=snowflake_account,
db=snowflake_database,
schema=snowflake_schema,
warehouse=snowflake_warehouse,
)
)
connection = engine.connect()
results = connection.execute("SELECT * FROM TEST_TABLE")
queryId = results.cursor.sfqid
print(f"queryId = {queryId}")
print(f"results: {results.fetchone()}")
connection.close()
engine.dispose()

输出:

queryId = 019edbef-0000-114f-0000-0f9500612j23
results: ('n/a', '2021-01-01')

Answer 2

我发现的一种方法是使用函数LAST_QUERY_ID，就像这样:

results = connection.execute("SELECT * FROM CITIBIKE_TRIPS LIMIT 1").fetchone()
query_id = connection.execute("SELECT LAST_QUERY_ID()").fetchone()
print(query_id)

我得到的结果是:

$ python test_sqlalchemy.py 
('019edb4b-0502-8a31-0000-16490cd95072',)

可能不是理想的方式。