Connect to Snowflake using SQLAlchemy

Connecting to Snowflake from Python by using SQLAlchemy allows you to write data to and from tables and execute SQL commands as if you were working within the Snowflake UI.

To connect to Snowflake using SQLAlchemy, the process is as follows:

1. Conda or Pip install the sqlalchemy Python package
2. Create connection string using Snowflake user credentials
3. Use the function create_engine to create a connection engine
4. Use the method .connect to create a connection object which can then be used to make queries to the Snowflake instance

Connecting to Snowflake using SQLAlchemy, what can you do?

The benefit of being able to connect to Snowflake through SQLAlchemy 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
• Using SQL to prepare data before reading into Python
• Storing datasets at various stages of preparation in tables

Install SQLAlchemy to your environment

In order to replicate the code in these examples locally on your machine you will need to have the sqlalchemy package installed.

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

Connect to Snowflake from Python using SQLAlchemy

Making the connection 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 SQL commands using SQLAlchemy connection

Now that we have the connection object created, we can use this to interact with our databases. In this simple 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()

Examples of how to use SQLAlchemy

For further examples of how to use SQLAlchemy, check out the other posts that I have written on the topic:

Create table from Pandas using SQLAlchemy

Snowflake credentials when using SQLAlchemy

When using SQLAlchemy 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

References

SQLAlchemy create engine documentation