Connect Snowflake using Python Pyodbc – ODBC Driver Example

Snowflake provides many connectors, you can use those to interact with Snowflake cloud data warehouse. There are many different options to connect to Snowflake. Many applications use jdbc or odbc drivers. In my other article, we have discussed how to connect Snowflake using Python and jdbc driver. In this article, we will check method on connect Snowflake using Python pyodbc and odbc driver with a working example.

Snowflake ODBC Driver

Snowflake data warehouse server comes with ODBC support. Before attempting to connect Snowflake from either Windows or Linux, you must download and install platform specific ODBC driver. Python program will expect you to install and configure odbc details.

Snowflake provides ODBC drivers for Windows, Linux and Mac OS. Download appropriate package as per your requirements.

Configure Snowflake ODBC on Windows

Go to official repository and download windows (MSI file) odbc driver based on your operating system.

Following link is the official repository.

• ODBC driver Official Repository for windows
• Configuration instructions

Note that, you will not be able to use pyodbc driver without installing and configuring OBDC drivers on required system. This step is one of the pre-requisites to use pyodbc module.

Configure Snowflake ODBC on Linux

Download and configure the Linux ODBC driver from following official repository.

• ODBC driver Official Repository for Linux
• Configuration instructions

Snowflake ODBC Connection String

The Snowflake odbc connection string is similar to other relational database connection string.

Following is the sample Snowflake ODBC connection string:

Driver={SnowflakeDSIIDriver}; Server=xyz11223.us-east-2.snowflakecomputing.com; Database=demo_db;schema=public; UID=snuser; PWD=snPassword123;

Connect Snowflake using Python pyodbc Example

If you have configured Snowflake ODBC drivers properly, then you are good go ahead and test it using the Python pyodbc module.

Note that, you may install pyodbc module if you haven’t done it already.

For example, use pip install to install pyodbc.

 pip install pyodbc

Following is the small Python program to test ODBC connection Python pyodbc module. This program return current date.

import pyodbc

# pyodbc connection string
conn = pyodbc.connect("Driver={SnowflakeDSIIDriver}; Server=xyz112233.us-east-2.snowflakecomputing.com; Database=DEMO_DB; schema=public; UID=snuser; PWD=snPassword123")

# Define Cursor
cus=conn.cursor()

# Execute SQL statement to get current datetime and store result in cursor
cus.execute("select current_date;")

# Display the content of cursor
row = cus.fetchone()

print(row)

Following is the sample output of the program:

(datetime.date(2019, 12, 8), )

Related Articles,

• Connect Snowflake using Python and Jdbc Driver- Example
• How to use Snowflake Python Connector and Example

Hope this helps 🙂