Snowflake and Python

Introduction

Python has become one of the go to languages for data analytics. I’m constantly using jupyter notebooks to quickly clean, analyze, and visualize data. That’s why I was ecstatic to learn that my favorite data warehouse, Snowflake, has a simple python connector. In fact, it took me just 10 minutes to set up my environment and start running analytics on some COVID-19 data!

Allow me to walk you through it.

Overview

The Snowflake Connector for Python provides an interface to develop Python applications which connect to Snowflake. The connector supports all standard operations.

Prerequisites

If you don’t have python yet, install it here.

This tutorial requires a minimum of Python 2.7.9 or Python 3.5.0, any version above that is supported. To check what version of Python you have installed on your machine, open terminal and run the following command:

python –version

If your python version is out of date, run this command to update:

python -m pip install –upgrade pip

Install Python Connector for Snowflake

Snowflake’s Python Connector is part of the Python Package Index (PyPI) so we can install it with pip or conda.

pip install –upgrade snowflake-connector-python
#Or (if your using python 3)
pip3 install –upgrade snowflake-connector-python
#Or (if you prefer conda)
conda install -c conda-forge snowflake-connector-python

Connecting to Snowflake with Python

Now that the connector is installed, let’s connect to Snowflake. I’m using jupyter notebooks but you can use any Python IDE for this. To begin, let’s import the Snowflake package we just downloaded.

import snowflake.connector

Now that we’ve imported the library, we’ll need 2 key pieces of information to connect to snowflake.

Snowflake account and region
User login and password

The snowflake account and region can be found in the URL when you log into Snowflake website. For example:

http://demo-account.demo-region.snowflakecomputing.com

The format is https://ACCOUNT.ACCOUNT_REGION.snowflakecomputing.com. So our account would be demo-account.demo-region. Your user information is the same you use to login to snowflake. We can create some variables to store this information.

sfAccount = ‘demo-account.demo-region’

sfUser = ‘demo-user’

sfPassword = ‘demo-pass’

Now we have all the information needed to use the Python connector in our application. The following example attempts to establish a connection and print out the version of snowflake we have running. If the connection fails, an error message is printed out.

import snowflake.connector

sfAccount = ‘demo-account.demo-region’
sfUser = ‘demo-user’
sfPass = ‘demo-pass’
// Connection object holds the connection and session information with the database alive
conn = snowflake.connector.connect(
user = sfUser,
password = sfPass,
account = sfAccount
)
// creates a cursor object to for execute and fetch operations
cs = conn.cursor()

try:
cs.execute(“SELECT current_version()”)
one_row = cs.fetchone()
print(one_row[0])
finally:
cs.close()
conn.close()

There you go! We’ve just connected to our snowflake database with python and retrieved some information. This should serve as a starting point for you to build your application. To dive deeper into what snowflake operations you can do with python check out the official documentation.

My Account