Predictive Hacks

How to Get Data from Snowflake using Python

snowflake

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()

Share This Post

Share on facebook
Share on linkedin
Share on twitter
Share on email

Leave a Comment

Subscribe To Our Newsletter

Get updates and learn from the best

More To Explore

pip
Python

Python pip Tips for Data Scientists

Data Scientists use to work with Anaconda Environments and for installing packages they use to run the “conda” commands. However,