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. Once I was figuring out way to get data from snowflake to local csv, and 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.
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
Also install Snowflake-alchemy package, this will help execution of SQL code for Snowflake:
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 , then create file with .py extension.
Import all the packages we have installed earlier on the top of the python script.
Import snowflake.connector as sf
Now, required listed information to establish a connection to Snowflake:
As we have all the credentials of snowflake now let’s create variables in python:
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.
try:
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. Let’s see some examples.
cursor.execute(“SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ‘my-schema’ “)
cursor.execute(“CREATE DATABASE DEMO-DB “)
cursor.execute(“CREATE SHCEMA DEMO-SCHEMA “)
we have completed connecting python with snowflake.
What else we can do on Snowflake with Python
When I started working on this topic, my first challenge was to load data from CSV to snowflake and creating tables using python . Then loading CSV file to internal stage and then loading data to tables. This was the basic thing to do with python, but it saved a lot of time to load data to snowflake.
Another challenge was to read tables and get columns and datatype to compare those with sql server. This was the most challenging thing but python helped a lot in this task and saved a lot time.
Written By: Ashmit Singh