我正在使用Snowflake连接到python (以下代码)。正如您所看到的,我正在尝试获取行计数,数据表是从SNOWFLAKE_SAMPLE_DATA数据库中存在的用于模式'TPCDS_SF100TCL‘的信息模式中创建和更改的。我的问题不是为每个表编写"OR“条件(在下面的示例中: CUSTOMER、CALL_CENTER、CUSTOMER_ADDRESS),而是想知道我是否可以在某个地方提供一个列表,并迭代该列表以获得"OR”条件。我不知道这是否可能?如果是,请建议代码和解释。
提前感谢您的时间和建议!
Python代码
import pandas as pd
import snowflake.connector
conn = snowflake.connector.connect(
user="MY_USER",
password="MY_PSWD",
account="MY_ACCOUNT",
warehouse="COMPUTE_WH",
database="SNOWFLAKE_SAMPLE_DATA",
schema="INFORMATION_SCHEMA",
role="SYSADMIN"
)
cur = conn.cursor()
try:
cur.execute("SELECT TABLE_NAME,ROW_COUNT,CREATED,LAST_ALTERED FROM TABLES WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_SCHEMA='TPCDS_SF100TCL' AND TABLE_NAME='CUSTOMER' OR TABLE_NAME='CALL_CENTER'
OR TABLE_NAME='CUSTOMER_ADDRESS'")
df = cur.fetch_pandas_all()
finally:
cur.close()
conn.close()代码根据BeRT2me更新建议:这两种方法都是由SO用户提供的。见下面他建议的代码
cur = conn.cursor()
tables = ['CUSTOMER', 'CALL_CENTER', 'CUSTOMER_ADDRESS']
try:
cur.execute(f"""SELECT TABLE_NAME, ROW_COUNT, CREATED, LAST_ALTERED FROM TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA='TPCDS_SF100TCL' AND TABLE_NAME IN ({','.join(tables)})""")
df = cur.fetch_pandas_all()
finally:
cur.close()
conn.close()发布于 2022-05-14 02:17:28
如果仅仅简化查询就足够好了,那么您可以从以下几个方面开始:
cur.execute(
"""
SELECT TABLE_NAME, ROW_COUNT, CREATED, LAST_ALTERED
FROM TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_SCHEMA='TPCDS_SF100TCL'
AND TABLE_NAME IN ('CUSTOMER', 'CALL_CENTER', 'CUSTOMER_ADDRESS')
"""
)如果你想用节奏曲的方式注射清单,这应该是可行的:
tables = ['CUSTOMER', 'CALL_CENTER', 'CUSTOMER_ADDRESS']
cur.execute(
f"""
SELECT TABLE_NAME, ROW_COUNT, CREATED, LAST_ALTERED
FROM TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_SCHEMA='TPCDS_SF100TCL'
AND TABLE_NAME IN ({','.join("'" + x + "'" for x in tables)})
"""
)https://stackoverflow.com/questions/72236777
复制相似问题