Connect Snowflake using Python and Jdbc Driver- Example

The Snowflake provides unique, one of its kind cloud data warehouse. It is popular because of its unique architecture designed for the cloud, flexible features and benefits. Snowflake support a wide range of connectors. The JDBC driver is one of the popular connectors. You can use jdbc driver from any programming language to connect to the Snowflake data warehouse. In this article, we will check how to connect Snowflake using Python and Jdbc driver with a working example.

Snowflake Jdbc Driver

Snowflake provides a JDBC type 4 driver that supports core JDBC functionality. The JDBC driver must be installed in a 64-bit environment and requires Java 1.8 (or higher).

The driver can be used with most client tools, applications or programming language that support JDBC for connecting to a database server. For example, you can use it in Java program or in a Python program to connect Snowflake server.

You can download latest version of Snowflake JDBC connector from maven repository. Make sue you download latest version. As of now, the latest version is 3.9.2.

You can use any python module that supports JDBC connection. We will be using the Jaydebeapi module for simplicity. Jaydebeapi is one of the easiest module that I have been using.

Install Jaydebeapi

The JayDeBeApi module allows you to connect from Python code to various databases using Java JDBC drivers and a connection string. It provides a Python DB-API v2.0 to that database.

You can install it using pip:

# Python
pip install Jaydebeapi

Set CLASSPATH to Driver Location

If there are dependent jar files for any JDBC jar file, you have to provide path of those jar files in CLASSPATH shell or environment variable. However, Snowflake provided JDBC driver does not have any dependent jars, you can directly refer this driver in your jaydebeapi module.

Alternatively, if you are on Linux operating system, you can export jar location to CLASSPATH shell variable.

You can read on how to set CLASSPATH variable in my other post – Set and Use Environment Variable inside Python Script.

Snowflake JDBC Connection String

Connection string differs from database to database. The Snowflake jdbc connection string is slightly different from databases like Netezza, Vertica, Redshift, etc.

Following is the JDBC connection string that you can use to connect Snowflake database:

jdbc:snowflake://<account_name>.snowflakecomputing.com/?<connection_params>

For example,

jdbc:snowflake://xyz12345.snowflakecomputing.com/?user=vithal&warehouse=mywh&db=demo_db&schema=public

How to Connect Snowflake using JDBC Driver from Python?

Now we have Snowflake jdbc driver downloaded and a classpath variable or jar location is set. In this section, we will check Python program that you can use to connect to Snowflake using JDBC driver.

Note that, Snowflake jdbc driver class name is “net.snowflake.client.jdbc.SnowflakeDriver “.

Following Python program query the snowflake database:

import jaydebeapi as jay
import os

# Snowflake Server Details
dsn_database = "DEMO_DB"
dsn_schema = "public"
dsn_hostname = "xyz112244.us-east-2.snowflakecomputing.com"
dsn_uid = "snuser"
dsn_pwd = "snpassword123"

# Snowflake JDBC class name
jdbc_driver_name = "net.snowflake.client.jdbc.SnowflakeDriver"

# Snowflake JDBC driver path
jdbc_driver_loc = os.path.join(r'D:\Snowflake\snowflake-jdbc-3.9.2.jar')

# Sample SQL Query
sql_str = "select current_date()"

# JDBC connection string
connection_string='jdbc:snowflake://'+ dsn_hostname+'/?'+'db=' +dsn_database + '&' + 'schema=' + dsn_schema

url = '{0}:user={1}; password={2}'.format(connection_string, dsn_uid, dsn_pwd)
print("Connection String: " + connection_string)

# Establish JDBC connection
conn = jay.connect(jdbc_driver_name, connection_string, {'user': dsn_uid, 'password': dsn_pwd},
jars=jdbc_driver_loc)

curs = conn.cursor()
curs.execute(sql_str)
result = curs.fetchall()

print(result[0])

Following is the sample output:

Connection String: jdbc:snowflake://xyz112244.us-east-2.snowflakecomputing.com/?db=DEMO_DB&schema=public
('2019-12-07',)

Hope this helps 🙂