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. 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:

1. Write data to new or existing tables
2. Read data from views or tables
3. Read data from custom SQL queries
4. 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 username
• password : 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 session
• schema_name : The initial schema for the session
• warehouse_name : The warehouse for the session
• role_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:

1. Write data to an existing table
2. Create new tables
3. Read data from a view, table, or SQL query
4. 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 username
• password : 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 session
• schema : The initial schema for the session
• warehouse : The warehouse for the session
• role : 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:

1. Write data to existing tables
2. Read data from a view, table, or SQL query
3. 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