Snowflake Connector For Python

Introduction

In this article, we are going to learn the Snowflake connector for Python.

Snowflake provides lots of connectors & drivers to connect Snowflake and perform query operations. In my other articles on Snowflake, I have illustrated the Snowflake Web Interface Client and SnowSQL command line Client. Apart from this, I have also explained the use of connecting Snowflake using .Net driver. Here, we use the new connector to connect Snowflake i.e. Python Connector. Usually, we use python programming to connect Snowflake for automating the DBA operation. All the queries like loading\unloading the data from one database to another database, which the DBA team performs, can be automated through Python.

Snowflake Connector for Python

This provides an interface for creating Python application and with a connector, we can connect to Snowflake and perform all of our operations. It can be installed using PIP (Python package installer) on Linux, macOS, and Windows where Python is installed. If you are working on Python, then I believe you have already installed the PIP. To install the snowflake connector for Python, please execute the below command

pip install snowflake-connector-python

If it is giving error related to PIP, then upgrade the PIP by running below command:

python -m pip install --upgrade pip

Refer to the below screen:

Now you have Snowflake connector for Python installed in your system. I am using Windows OS and Visual Studio code to work with Python.

Problem Statement

Using a Python program:

Create a table in the Snowflake database.
Insert a record into the newly created table.
Retrieve the table record.

Solution

To create a table, insert a record, and fetch the record from the table. We need to write the SQL query, and these SQL queries can be processed in a Python program with Snowflake connector for Python. Not only this, but you can also write a lot of custom logic to automate the DBA work using Python.

I am creating a table store table in the TEST_DB database under management schema. Once the table created, there will be two records inserting to the table. Once the record inserted, we can fetch those records. So, all operations will be performed in python programming. Let's create a Python program to achieve this problem statement. To work on this problem, perform the following steps.

Step 1

Step 2

Set the context by selecting the role, warehouse, database, and schema.

You can run the below query to set the context:

Use role sysadmin;
Use warehouse compute_wh;
Use database TEST_DB;
Use schema management;

Step 3

Run the select query to see whether the table is available or not.

Select * from store;

When you will run this query, you will see the error message like ‘’SQL compilation error: store object does not exist or not authorized”. There will be a case when the table is already there, but the role does not have sufficient privilege to see the data. Here in our case, the table is not created which is why we are getting an error.

Step 4

Write a Python program to execute the required SQL query.

Refer to the code snippet below. Change the value of user, password, account, warehouse, database, schema, region as per your snowflake account. In this example, I have used a hard-coded value for creating a connection, but you can pass as a parameter and get those parameters assigned to the snowflake connector method. We have to import the snowflake.connector package which we have installed by PIP. After importing the connector, you can use the connection and cursor object of it.

Connection - snowflake.connector.connect method is used to establish the connection.
Cursor - it is used to perform/execute DML/DDL query.

import snowflake.connector
USER='nitesh'
PASSWORD='XXXXXX'
ACCOUNT='bea78282'
WAREHOUSE='compute_wh'
DATABASE='TEST_DB'
SCHEMA='management'
REGION='us-east-1'
print("Connecting...")
con = snowflake.connector.connect(
user=USER,
password=PASSWORD,
account=ACCOUNT,
region=REGION,
warehouse=WAREHOUSE,
database=DATABASE,
schema=SCHEMA
)
print("Creating table store in TEST_DB under management schema...")
con.cursor().execute("""CREATE OR REPLACE TABLE
store(store_id integer, store_name varchar(30))""")
con.cursor().execute("""INSERT INTO store(store_id, store_name) VALUES
(1, 'Universal')""")
con.cursor().execute("""INSERT INTO store(store_id, store_name) VALUES
(2, 'HappyStore')""")
print("\nSelecting from store...")
cur = con.cursor()
try:
cur.execute("SELECT store_id, store_name FROM store")
for (store_id, store_name) in cur:
print('{0}, {1}'.format(store_id, store_name))
finally:
cur.close()

Step 5

I have captured the below screen to show the result here.

Step 6

The SQL queries which we have executed through the Python program can be seen in the Snowflake web interface. Go to the History tab and select the appropriate user where you executed the query. Refer to the below screen.

Step 7

Now let's execute the select query again to see the table and data.

Select * from store;

Refer to the below screen:

Conclusion

Snowflake Connector for Python can be used with DBA to customize the logic. DBA can use Python programming and automate all the SQL queries as per their requirement.

Snowflake web interface does not support the lopping mechanism, so we use programming language. Not only this, but you can create a bash script and inside the script and you can call the Python program to execute SQL query. If you have a bash script you can schedule it by making jobs (crone job).

Happy Learning!