Execute Snowflake SQL query in Python (with examples)

Running Snowflake queries from Python opens up many possibilities for developers. In this post, I show you how to query Snowflake using simple examples.

Run Snowflake query from Python

Running a SQL query from Python to be executed in a Snowflake instance is simple with the Snowflake Python connector package. In order to run a SQL query in Snowflake, you need to follow these steps:

1. Install the Snowflake Python connector package in your environment
2. Create a Snowflake connection object using the Python connector
3. Write the SQL command you want to run as a string
4. Execute the SQL command using the connection object that was created

The query could be anything you would typically do in the Snowflake UI, such as reading data, creating tables, replacing views, or copying data to external stages.

Query Snowflake from Python using connector

The Python connector from Snowflake has native functionality for reading and writing data to and from Python, in addition to running ad-hoc commands in the instance. Let’s look at running an ad-hoc query to make changes in the database.

Snowflake cursor execute example

This simple example shows how you can run a SQL query to create a view in the database, exactly like you would in the Snowflake UI.

from snowflake.connector import connect

sql = 'create view my-view as (select * from my-table)'

conn = connect(
        user='<user_login_name>',
        password='<password>',
        account='<account_identifier>'
    )

cursor = conn.cursor()
cursor.execute(sql)
cursor.close()

Read data from Snowflake using Python connector

We can also use the same connector to read data from Snowflake into Python. To do this we use the function fetch_pandas_all with the cursor object. The data is helpfully returned as a Pandas DataFrame.

from snowflake.connector import connect
import pandas as pd

sql = 'select * from my-view'

conn = connect(
        user='<user_login_name>',
        password='<password>',
        account='<account_identifier>'
    )

cursor = conn.cursor()
cursor.execute(sql)

df = cursor.fetch_pandas_all()
cursor.close()

Related articles

Write Pandas data to Snowflake table
Read data to Pandas from Snowflake
Connect to Snowflake from Python
Create Snowflake table from Pandas

References

Snowflake documentation