Snowflake Connector for Python 

Overview

This blog will help you to understand the latest cloud data warehousing platform called snowflake, connection with python.  

As a python developer I always wanted to do most of things in python. When I started working on this topic, my first challenge was to load data from CSV to Snowflake and creating tables using python. Another challenge I faced with our clients was reading tables from SQL Server and getting the columns and datatypes to compare between Snowflake and SQL Server.  

Once I figured this way to get data to Snowflake from local CSV, I was very happy to learn that Snowflake have created its own python package to connect to Snowflake and execute commands and get data from it. As these commands are executed locally with python and transmitted to Snowflake, the full Snow SQL functionality is available for use. 

Let’s dive in.

Prerequisite  

Before moving to python connector for Snowflake, you should check the version of your python installed. The Snowflake connector for python requires python’s minimum version should be python 3.6 and more recent versions are also supported.  

To verify your version of python installed in your system run following command on CMD:  python — version  

If you don’t have python installed on your system , you can install it by following the given website https://www.python.org/ . Download the relevant packages for your Operating system.  

To upgrade the python version use following command: python -m pip install –upgrade pip  

Installing the Python Connector  

This topic provides instructions for installing the Snowflake Connector for Python. The connector can currently be installed in Linux, macOS, and Windows environments. Here we will discuss the Installation in windows environment. For other Operating systems can visit following link https://docs.snowflake.com/en/user-guide/python-connector-install.html.  

Now for installing python connector for Snowflake, Open terminal and run following command: 

• pip install snowflake-connector-python  

Next, install Snowflake-alchemy package. this will help execution of SQL code for Snowflake. To install the package run the following command:

• pip install snowflake-sqlalchemy  

Now after installing packages, lets move to your Python IDE and start working on python to connect with snowflake.   

Connecting to Snowflake via python  

To begin, open your python IDE , create new project, and create a file with .py extension. You will need to import all the packages we have installed earlier on the top of the python script.  

Now, to establish a connection to Snowflake, you need to follow the below steps:

• Snowflake account with region – You will get that from the URL which is used to access snowflake. (e.g. https://myAccount.my-region.snowflakecomputing.com). This URL is formed by https://<Account>. <Account_Region>.snowflakecomputing.com.
• Select existing user for that Account and password.  
• Role, Warehouse, Database and Schema Name for that user which is assigned in Snowflake.  

Now that you have connection to Snowflake, now let’s create variables in python. To do so, run the following commands:

• SfAccount = ‘myAccount.my-region’  
• sfUser = ‘my-user’  

For Password we can use getpass package, as it is not best practice to enter password manually, when you have to deploy your code to production.  

Import getpass  

sfPass = ”  

   if sfPass == ” :  

        sfPass = getpass.getpass(‘Password: ‘)   

We now have Every variable created with us , lets create connection with snowflake. The following code will establish connection.   

    conn = snowflake.connector.connect(  

        account=”my-account”,  

        user=”my-user”,  

        password=sfpass,  

        role=”my-role”,  

        warehouse=”my-warehouse”,  

        database=”my-database”,  

        schema=”my-schema”  

    )  

    cursor = conn.cursor()  

    cursor.execute(“SELECT current_version()”)  

    sfResults = cursor.fetchall()  

    print(‘Snowflake Version: ‘ + sfResults[0][0])  

    cursor.close()  

    conn.close()  

except:  

    print(‘Connection not created, check your credentials’)   

Finally, as our connection is created, we can start working. We can execute any SQL command string passing through cursor.execute() in our Snowflake environment.

Examples

With this new functionality, I am able to run some of my favorite Python commands directly in Snowflake. Including: Below are some examples python

• cursor.execute(“SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ‘my-schema’ “)  
• cursor.execute(“CREATE DATABASE DEMO-DB “)  
• cursor.execute(“CREATE SHCEMA DEMO-SCHEMA “)   

Summary

This article explains how to connect Python to Snowflake by installing the Snowflake Python connector and Snowflake-SQLAlchemy package. It provides step-by-step instructions for setting up a connection using necessary credentials such as account, user, and schema, and demonstrates executing SQL commands from Python