Connect Python to Snowflake - 2 methods
Sun Aug 22 2021 20:07:43 GMT+0000 (UTC)
Saved by
@mcdave
#python
#snowflake
#sqlalchemy
## Using snowflake connector
import snowflake.connector
from configparser import ConfigParser
import pandas as pd
config = ConfigParser()
config.read("config.ini")
user=config["SNOWFLAKE"]["USER"]
password=config["SNOWFLAKE"]["PASSWORD"]
database=config["SNOWFLAKE"]["DATABASE"]
schema=config["SNOWFLAKE"]["SCHEMA"]
warehouse=config["SNOWFLAKE"]["WAREHOUSE"]
role=config["SNOWFLAKE"]["ROLE"]
## snowflake connector method
connection = snowflake.connector.connect(
user=user,
password=password,
account='hostelworld.eu-west-1',
database = database,
warehouse = warehouse,
role = role)
print("snowflake connector connected...")
query = '''select * from production.ppc.campaign_performance_daily limit 1000 ;'''
df = pd.read_sql_query(sql=query, con=connection)
print('DF shape = ', df.shape)
connection.close()
print("connection closed")
### Using sql alchemy
from sqlalchemy import create_engine
from configparser import ConfigParser
import pandas as pd
config = ConfigParser()
config.read("config.ini")
user=config["SNOWFLAKE"]["USER"]
password=config["SNOWFLAKE"]["PASSWORD"]
database=config["SNOWFLAKE"]["DATABASE"]
schema=config["SNOWFLAKE"]["SCHEMA"]
warehouse=config["SNOWFLAKE"]["WAREHOUSE"]
role=config["SNOWFLAKE"]["ROLE"]
# sql alchemy method
engine = create_engine(
f"""snowflake://{user}:{password}@hostelworld.eu-west-1/{database}/{schema}?warehouse={warehouse}&role={role}"""
)
connection = engine.connect()
print('sql alchemy connected...')
query = '''select * from production.ppc.campaign_performance_daily limit 1000 ;'''
df = pd.read_sql_query(sql=query, con=connection)
print('DF shape = ', df.shape)
connection.close()
print("connection closed")
content_copyCOPY
Here we assume you have your details in a config file (see example of config file in another snippet)
Comments