How to Get Data from Snowflake using Python

[This article was first published on Python – Predictive Hacks, and kindly contributed to python-bloggers]. (You can report issue about the content on this page here)
Want to share your content on python-bloggers? click here.

In a previous post, we provided an example of how to load data from S3 to Snowflake. Data Scientists and Data Engineers are very familiar with Python and Pandas Data Frames, so it is essential to be able to connect Snowflake with Python. In this tutorial, we will show you how to get data from Snowflake in your local environment in Python.

Snowflake Database and Tables

For this tutorial, we have created a database called “GPIPIS_DB” where there is a table called “MYIRISTABLE

How to Get Data from Snowflake using Python 1

Install the Python Libraries

We will need to install the following Python libraries.

pip install snowflake-connector-python
pip install --upgrade snowflake-sqlalchemy
pip install "snowflake-connector-python[pandas]"

There are different ways to get data from Snowflake to Python. Below, we provide some examples, but first, let’s load the libraries.

import snowflake.connector
import pandas as pd
from snowflake.sqlalchemy import URL
from sqlalchemy import create_engine

Get Data as Pandas Data Frame using the sqlalchemy

We will need to create a connection engine and then run the SQL query. Notice that the account id (account), is what you see in the URL up to .snowflakecomputing.com

url = URL(
    user='XXX',
    password='XXX',
    account='<ACCOUNT_ID>',
    warehouse='TEST_WH',
    database='GPIPIS_DB',
    schema='PUBLIC',
    role = 'ACCOUNTADMIN'
)
engine = create_engine(url)


connection = engine.connect()

query = '''
select * from myiristable
'''

df = pd.read_sql(query, connection)

df
How to Get Data from Snowflake using Python 2

As we can see, we managed to load the snowflake table to our local environment.

Get Data as Pandas Data Frame using the snowflake.connector and fetch_pandas_all

Provided that you have installed the snowflake-connector-python[pandas] you can load the data as follows.

conn = snowflake.connector.connect(
    user='XXX',
    password='XXX',
    account='<ACCOUNT_ID>',
    warehouse='TEST_WH',
    database='GPIPIS_DB',
    schema='PUBLIC',
    role = 'ACCOUNTADMIN'
            )

cur = conn.cursor()


sql = "select * from myiristable"
cur.execute(sql)

df = cur.fetch_pandas_all()

df
How to Get Data from Snowflake using Python 3

Voilà, we loaded the snowflake table as a pandas data frame.

Get Data as Pandas Data Frame using the snowflake.connector and from_records

Alternatively, we can iterate over the cur object as follows.

conn = snowflake.connector.connect(
    user='XXX',
    password='XXX',
    account='<ACCOUNT_ID>',
    warehouse='TEST_WH',
    database='GPIPIS_DB',
    schema='PUBLIC',
    role = 'ACCOUNTADMIN'
            )

cur = conn.cursor()


sql = "select * from myiristable"
cur.execute(sql)

df = pd.DataFrame.from_records(iter(cur), columns=[x[0] for x in cur.description])
df
How to Get Data from Snowflake using Python 4

Get the Data by Iterating the Cursor

Finally, we can get the data by iterating the cursor as follows.

conn = snowflake.connector.connect(
    user='XXX',
    password='XXX',
    account='<ACCOUNT_ID>',
    warehouse='TEST_WH',
    database='GPIPIS_DB',
    schema='PUBLIC',
    role = 'ACCOUNTADMIN'
            )

cur = conn.cursor()


sql = "select * from myiristable"
cur.execute(sql)

for record in cur:
    print(record)
How to Get Data from Snowflake using Python 5

We can get the data for each column as follows:

cur.execute(sql)

for i,j,k,l,m in cur:
    print(i,j,k,l,m)
How to Get Data from Snowflake using Python 6

Close the Connections

Once we are done, we can close the connections.

conn.close()
connection.close()
cur.close()
To leave a comment for the author, please follow the link and comment on their blog: Python – Predictive Hacks.

Want to share your content on python-bloggers? click here.