Connect to Snowflake using Snowflake Connector for Python

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

To connect to Snowflake using the Snowflake Connector for Python, the process is as follows:

1. Pip install the snowflake-connector-python Python package
2. Use the function connect to create a connection using your user credentials
3. Create a cursor object using the cursor method
4. Execute SQL queries using the execute method

Connecting to Snowflake using Snowflake Connector for Python, what can you do?

The benefit of being able to connect through the Snowflake Connector is that you can do the following from within your scripts:

1. Write data to 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

Pip install snowflake-connector-python to your environment

In order to replicate the code in these examples locally on your machine you will need to have the snowflake-connector-python 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
conda activate env_name
pip install snowflake-connector-python

Connect using Snowflake Connector for Python

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

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. 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()

Snowflake Connector for Python examples

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

Create table from Pandas using Snowflake Connector for Python

Credentials when using Snowflake Connector

When using the Snowflake Connector, 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

Snowflake Connector for Python documentation