How To Connect To A Snowflake Database With Python?

Shruti Garg • Last Modified: August 23rd, 2023

Snowflake to Python - Featured Image | Hevo Data

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.

Are you just getting started with ETL functions? Are you looking for a way to connect your Snowflake Data Warehouse to Python? You are in the right place.

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 to Python super easy.

Table of Contents

Prerequisites

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

What is Snowflake?

Snowflake to Python - Snowflake Logo | Hevo Data
Image Source

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. 

Snowflake’s columnar database engine uses advanced optimizations, including automatic clustering, which removes the tediousness of having to manually re-cluster data when loading new data into a table. It allows easy integration with custom and packaged tools and applications. It provides connectors for not only Python but also ODBC, JDBC, Javascript, Spark, R, and Node.js.

Snowflake Python connector simplifies the process of connecting Snowflake to Python or to any other framework.

What is Python?

Snowflake to Python - Python Logo | Hevo Data
Image Source

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.

Python is one of the most popular and flexible programming languages ​​in the industry today. Since its birth in the 1990s, Python has become immensely popular, and even today thousands of people are learning this object-oriented programming language. In programming, you’ve heard the buzz about Python lately, and you must be wondering what makes this programming language so special.

Simplify ETL with Hevo’s No-code Pipeline

Hevo Data is a No-code Data Pipeline. Hevo can easily load data from 150+ data sources to Snowflake in a few simple steps. It offers real-time data migration so that your data is always ready for analysis. You can also perform data transformations without writing custom code.

Get Started with Hevo for Free

Let’s look at some unbeatable features of Hevo:

  • Fully Automated: Hevo automates your data flow without writing any code. It flags errors and corrects them.
  • Fault-Tolerant: Hevo is built on a fault-tolerant architecture. Hevo detects anomalies and informs you instantly. All the affected rows are kept aside for correction.
  • Scalability: Hevo can handle millions of records per minute without latency. It can scale according to the need of your organization.
  • Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
  • Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to export. 
  • Live Support: Team Hevo is ready to help its customers 24/7 through calls, messages, and emails.
Sign up here for a 14-Day Free Trial!

The Snowflake Connector for Python

Connecting Snowflake to Python: Snowflake Python Connector | Hevo Data
Image Source

Before you can learn the steps for connecting Snowflake to Python, 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 Snowflake to Python 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 Snowflake to Python, 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 Snowflake to Python. 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 Snowflake to Python successfully.

Visit our Website to Explore Hevo

If you want to integrate data from multiple sources into Snowflake before analysis, then try Hevo. Hevo Data is a No-Code Data Pipeline. It supports pre-built integrations from 100+ data sources and is completely automatic!

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

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