
Connect to Snowflake from Python
Connecting to Snowflake from Python allows you to write data to and from tables and execute SQL commands as if you were working within the Snowflake UI.
Connecting to Snowflake from Python allows you to write data to and from tables and execute SQL commands as if you were working within the Snowflake UI. Pretty helpful, right?
To connect to Snowflake from Python there are currently two suggested methods, these are:
- SQLAlchemy
- Snowflake Connector for Python
The industry best practice is to use SQLAlchemy as it offers the most functionality, but I will also explain how to do this with Snowflake’s Python package.
Connecting to Snowflake from Python, what can we do?
The beauty of being able to connect to Snowflake from your Python programme is that you can do the following from within your scripts:
- Write data to new or existing tables
- Read data from views or tables
- Read data from custom SQL queries
- Run SQL queries to edit tables and views
This functionality is especially helpful for those working with machine learning applications, who will benefit from:
- Writing model training statistics to a table to track over time
- Storing historical predictions
- Using SQL to prepare data before reading into Python
- Storing datasets at various stages of preparation in tables
Installing SQLAlchemy and Snowflake Connector for Python to your environment
In order to replicate the code in these examples locally on your machine you will need to have the following packages installed:
sqlalchemy
snowflake-connector-python
It is always recommended to create an isolated Python environment for your projects, so we will do this by creating a Conda environment:
conda create -n env_name python=3.9 sqlalchemy
conda activate env_name
pip install snowflake-connector-python
Connect to Snowflake from Python using SQLAlchemy
In this section we will use SQLAlchemy to connect to our instance. It’s a fairly simple process, but still requires us to enter in some information. The fields we need to enter are:
account_indentifier
: The identifier for your instance, which is normally found in the URL use when loading up the Snowflake UI:“account_identifier".snowflakecomputing.com
user
: Your usernamepassword
: Your password
Once we have this information we can then use this to create a connection object, which is what we will use to make queries to our Snowflake instance.
from sqlalchemy import create_engine
#Create connection to Snowflake using your account and user
account_identifier = '<account_identifier>'
user = '<user_login_name>'
password = '<password>'
conn_string = f"snowflake://{user}:{password}@{account_identifier}"
engine = create_engine(conn_string)
connection = engine.connect()
Additional SQLAlchemy connection parameters
There are also a number of optional fields that we could provide in our connection string. These are:
database_name
: The initial database for the sessionschema_name
: The initial schema for the sessionwarehouse_name
: The warehouse for the sessionrole_name
: The user role for the session
These additional parameters would be added to the end of the connection string like so:
from sqlalchemy import create_engine
#Create connection to Snowflake using your account and user
account_identifier = '<account_identifier>'
user = '<user_login_name>'
password = '<password>'
database_name = '<database_name>'
schema_name = '<schema_name>'
warehouse_name = '<warehouse_name>'
role_name = '<role_name>'
conn_string = f"snowflake://{user}:{password}@{account_identifier}/{database_name}/{schema_name}?warehouse={warehouse_name}&role={role_name}"
engine = create_engine(conn_string)
connection = engine.connect()
Executing Snowflake commands using SQLAlchemy connection
Now that we have the connection object created, we can use this to interact with our databases. There are several things that we can do with this SQLAlchemy object, for example:
- Write data to an existing table
- Create new tables
- Read data from a view, table, or SQL query
- Run SQL commands in your instance
In this example we are going to run a SQL query which creates a new view:
from sqlalchemy import create_engine
#Create connection to Snowflake using your account and user
account_identifier = '<account_identifier>'
user = '<user_login_name>'
password = '<password>'
conn_string = f"snowflake://{user}:{password}@{account_identifier}"
engine = create_engine(conn_string)
connection = engine.connect()
sql = "create view my-view as (select * from my-table)"
try:
connection.execute(sql)
finally:
connection.close()
engine.dispose()
Connect using Snowflake Connector for Python
Now we will look at how to use the Snowflake Connector for Python to connect to our instance. This works in a similar way to SQLAlchemy but instead of creating a connection string, we just use one of the functions from the package. The fields that are required are also the same as with the previous example:
account_indentifier
: The identifier for your instance, which is normally found in the URL use when loading up the Snowflake UI:“account_identifier".snowflakecomputing.com
user
: Your usernamepassword
: Your password
from snowflake.connector import connect
#Use your Snowfake user credentials to connect
conn = connect(
user='<user_login_name>',
password='<password>',
account='<account_identifier>'
)
Additional parameters for Snowflake Connector for Python
There are also a number of optional fields that we could provide in this connection function, some of the most useful are:
database
: The initial database for the sessionschema
: The initial schema for the sessionwarehouse
: The warehouse for the sessionrole
: The user role for the session
These additional parameters would be added to the same connection function:
from snowflake.connector import connect
#Use your Snowfake user credentials to connect
conn = connect(
user='<user_login_name>',
password='<password>',
account='<account_identifier>',
database='<database>',
schema='<schema>',
warehouse='<warehouse>',
role='<role>'
)
Executing SQL commands using Snowflake Connector for Python connection
Now that we have our connection open with Snowflake using their connector, we can start to undertake some actions. The Snowflake Connector is a little more restricting with it’s standard functionality, but there are still a number of critical tasks that we can undertake using this connection object:
- Write data to existing tables
- Read data from a view, table, or SQL query
- Run custom SQL queries
In this example we will execute a simple SQL query to create a view in our database:
from snowflake.connector import connect
#Use your Snowfake user credentials to connect
conn = connect(
user='<user_login_name>',
password='<password>',
account='<account_identifier>'
)
sql = 'create view my-view as (select * from my-table)'
cursor = conn.cursor()
cursor.execute(sql)
cursor.close()
Entering Snowflake credentials in Python
For both SQLAlchemy and Snowflake Connector for Python you are required to enter your credentials to create the connection. You can of course decide what the best insertion method is for your project, whether that be environment variables or something else, but given that this is sensitive information you should remember the golden rule:
Do not enter your credentials in plain text, especially when working within a Git repository
Related articles
Write Pandas data to Snowflake table
Create Snowflake table from Pandas
Read Snowflake data to Pandas DataFrame
Grant role to a user in Snowflake
References
Snowflake Connector for Python documentation
SQLAlchemy create engine documentation