How to Get Data from Snowflake using Python

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“

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

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

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

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)

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)

Close the Connections

Once we are done, we can close the connections.

conn.close()
connection.close()
cur.close()