I'm really looking for best practices here, or something I've not thought of.
I have scripts which loop through multiple tables (potentially hundreds) in Snowflake within Snowflake, and do some form of processing on them. I have other scripts which just work with one table in Snowflake.
I'm really looking for a function I can use between all scripts that efficiently manages connecting to snowflake.
My question is, is it better to create multiple connection, or one connection with multiple cursors? Or is there a recommended approach to what I'm trying to achieve.
Using Context Manager:
from contextlib import contextmanager
@contextmanager
def snowflake_cursor():
conn = connect_to_snowflake()
cur = conn.cursor()
try:
yield cur
finally:
cur.close()
conn.close()
# One Approach
with snowflake_cursor() as cur:
for table in list_of_tables:
cur.execute(f"SELECT * FROM {table}")
# Another Approach
for table in list_of_tables:
with snowflake_cursor() as cur:
cur.execute(f"SELECT * FROM {table}")
I've also noticed this is possible:
import snowflake.connector
with snowflake.connector.connect(**connection_details) as connection:
with connection.cursor() as cursor:
pass
My current approach doesn't actually use any of the above. Instead I'm just returning a connection object from my function and creating cursors throughout my scripts when needed, then closing the cursor and connection at the end of the scripts.
发布者：admin，转转请注明出处：http://www.yc00.com/questions/1744930214a4601689.html
评论列表（0条）