How To Connect To A Snowflake Database With Python?

Snowflake is a popular cloud Data Warehousing solution that has been implemented by scores of well-known firms, including Fortune 500 companies, as their Data Warehouse provider and manager.

This article will introduce you to Snowflake, Python, and the tool required for connecting Snowflake to Python. Also, it will discuss the steps required to install that connector tool. This article will act as a guide for you and will make connecting Snowflake and Python super easy.

Prerequisites

• Python version 3.5+ is installed in your system.
• Understanding of Data Warehouses.
• Working Knowledge of Snowflake Data Warehouse.

What is Snowflake?

Snowflake is a Cloud-hosted Data Warehouse platform that enables you to store, share and analyze your data. Its multi-cluster architecture supports working with different clouds and also allows you to mix and match between different cloud platforms.

Snowflake is available on AWS, Azure as well as Google Cloud Platform. Due to its in-built performance, there is no need for you to manage any infrastructure. Moreover, it is feasible due to its pay-for-what-you-use strategy. 

What is Python?

Python is essentially a high-level programming language that enables you to create both simple and complex operations. Moreover, Python comes with a wide range of modules as well as libraries that enable it to support various features of other programming languages ​​such as Java, C, C ++, and JSON.

The Snowflake Connector for Python

• Before you can learn the steps for connecting, a major requirement is to install the Snowflake Connector for Python. This Connector delivers the interface for developing Python applications that can connect to a Cloud Data Warehouse and perform standard functions.
• The Snowflake Python Connector is a Python package that readily connects your application to Snowflake and has no dependencies on JDBC or ODBC. It is essential for connecting and it supports all standard operations. For example, query execution, loading, accessing data from an external source, and many more.
• It also provides a programming alternative to developing applications in Java or C/C++ using the Snowflake JDBC or ODBC drivers. It is supported on the most popular operating systems including Linux, macOS, and Windows platforms where Python 3.5.0 (or higher) is installed.
• SnowSQL, the command line client to use SQL, provided by Snowflake, is one of the applications developed using the connector. Once this Connector is installed, you can use it for connecting Snowflake to Python.

Installing the Snowflake Connector for Python

The Snowflake Connector for Python can be installed easily by using either the pip command or the conda installer. There are many other ways to do this as well, but pip and conda are the easiest since the package is already bundled with the required dependencies. 

• Using pip command: The Snowflake Connector for Python is installed using pip version 19.0 or later. To ensure that the correct version of pip is available, use the following command:

python -m pip install --upgrade pip

Now, to install the Snowflake Connector for Python, use the following command:

pip install --upgrade snowflake-connector-python

• Using conda installer: Snowflake Connector for Python is unavailable for standard conda libraries. You will have to use the conda-forge channel to install the connector using conda installer. The following code can be used for the same:

conda install -c conda-forge snowflake-connector-python

Steps for Connecting Snowflake to Python

The Snowflake Connector for Python supports producing applications using the Python Database API v2 specification, including the following standard API objects:

• Connection object for connecting to Snowflake.
• Cursor object for executing DDL/DML queries.

So, for connecting, you need to follow these steps:

• Step 1: Import the Snowflake Connector module

import snowflake.connector

• Step 2: Use environment variables, the command line, a configuration file, or another appropriate source, to read login credentials.

PASSWORD = os.getenv('SNOWSQL_PWD')
WAREHOUSE = os.getenv('WAREHOUSE')
...

Note: The ACCOUNT parameter may need the region and cloud platform for your account:

‘<your_account_name>.<region_id>.<cloud>’ 

(e.g. ‘xy12345.east-us-2.azure’)

• Step 3: This is the final step required for connecting. For this, you need to connect to Snowflake using the login parameters:

           conn = snowflake.connector.connect(
                user=USER,
                password=PASSWORD,
                account=ACCOUNT,
                warehouse=WAREHOUSE,
                database=DATABASE,
                schema=SCHEMA
                )

Where, user, password, and account are mandatory parameters. The other parameters are optional and Snowflake uses default values if not specified.

Following is an example Python script which prints the current date to create a Snowflake connection:

import snowflake.connector
#create connection
conn=snowflake.connector.connect(
      user=’suser’,
                password=’pass@123’,
                account=’abc123.us-east-2’,
                warehouse=’demo_wh’,
                database=’demo’,
                schema=’public’
                )
#create cursor
curs=conn.cursor()
#execute SQL statement
curs.execute(“select current date;”)
#fetch result
print cur.fetchone()[0]

Conclusion

This article introduced you to Snowflake and Python. Afterward, It explained the steps required to install and use the Snowflake Connector for Python. Furthermore, the article provided a step-by-step guide for connecting Snowflake to Python. The steps are easy to follow and anyone having a basic knowledge of Python can use them for connecting successfully.

Share your understanding of connecting Snowflake to Python in the comments below1