14

Using the Python Connector I can query Snowflake:

import snowflake.connector

# Gets the version
ctx = snowflake.connector.connect(
    user=USER,
    password=PASSWORD,
    account=ACCOUNT,
    authenticator='https://XXXX.okta.com',
    )
ctx.cursor().execute('USE warehouse MY_WH')
ctx.cursor().execute('USE MYDB.MYSCHEMA')


query = '''
select * from MYDB.MYSCHEMA.MYTABLE
LIMIT 10;
'''

cur = ctx.cursor().execute(query)

The result is a snowflake.connector.cursor.SnowflakeCursor. How can I convert that to a pandas DataFrame?

Malik Asad
  • 413
  • 4
  • 15
RubenLaguna
  • 15,227
  • 11
  • 82
  • 96

2 Answers2

15

You can use DataFrame.from_records() or pandas.read_sql() with snowflake-sqlalchemy. The snowflake-alchemy option has a simpler API

pd.DataFrame.from_records(iter(cur), columns=[x[0] for x in cur.description])

will return a DataFrame with proper column names taken from the SQL result. The iter(cur) will convert the cursor into an iterator and cur.description gives the names and types of the columns.

So the complete code will be 

import snowflake.connector
import pandas as pd

# Gets the version
ctx = snowflake.connector.connect(
    user=USER,
    password=PASSWORD,
    account=ACCOUNT,
    authenticator='https://XXXX.okta.com',
    )
ctx.cursor().execute('USE warehouse MY_WH')
ctx.cursor().execute('USE MYDB.MYSCHEMA')


query = '''
select * from MYDB.MYSCHEMA.MYTABLE
LIMIT 10;
'''

cur = ctx.cursor().execute(query)
df = pd.DataFrame.from_records(iter(cur), columns=[x[0] for x in cur.description])

If you prefer using pandas.read_sql then you can 

import pandas as pd

from sqlalchemy import create_engine
from snowflake.sqlalchemy import URL


url = URL(
    account = 'xxxx',
    user = 'xxxx',
    password = 'xxxx',
    database = 'xxx',
    schema = 'xxxx',
    warehouse = 'xxx',
    role='xxxxx',
    authenticator='https://xxxxx.okta.com',
)
engine = create_engine(url)


connection = engine.connect()

query = '''
select * from MYDB.MYSCHEMA.MYTABLE
LIMIT 10;
'''

df = pd.read_sql(query, connection)
RubenLaguna
  • 15,227
  • 11
  • 82
  • 96
  • Thanks! Worked for me. – user45254 Nov 29 '18 at 15:25
  • @ecerulm i'm using anaconda and cannot install from "snowflake.sqlalchemy import URL" when i use your first version that doesn't work. Any other ideas? – Maths12 Jan 21 '20 at 14:58
  • @Maths12 you need to install [snowflake python connector](https://docs.snowflake.net/manuals/user-guide/python-connector-example.html#using-the-python-connector) . I don't use anaconda but I guess either `conda install -c conda-forge snowflake-connector-python` or `pip install --upgrade snowflake-connector-python` – RubenLaguna Jan 21 '20 at 16:38
  • @ecerulm i have installed that , yet i still get the same error – Maths12 Jan 21 '20 at 16:40
  • you need also `snowflake-sqlalchemy` as explained in [Using the Snowflake SQLAlchemy Toolkit with the Python Connector](https://docs.snowflake.net/manuals/user-guide/sqlalchemy.html) – RubenLaguna Jan 21 '20 at 16:48
  • @ecerulm yes i followed that but i get the error: NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:snowflake i have tried to resolve this too by looking at other forums but no solution has worked. I will post a question. – Maths12 Jan 21 '20 at 16:57
4

There is now a method .fetch_pandas.all() for this, no need for SQL Alchemy anymore.

Note that you need to install snowflake.connector for pandas by doing this

pip install snowflake-connector-python[pandas]

Full documentation here

import pandas as pd
import snowflake.connector

conn = snowflake.connector.connect(
            user="xxx",
            password="xxx",
            account="xxx",
            warehouse="xxx",
            database="MYDB",
            schema="MYSCHEMA"
            )

cur = conn.cursor()

# Execute a statement that will generate a result set.
sql = "select * from MYTABLE limit 10"
cur.execute(sql)
# Fetch the result set from the cursor and deliver it as the Pandas DataFrame.
df = cur.fetch_pandas_all()
fmarm
  • 3,919
  • 1
  • 12
  • 23
  • Unfortunately I can't get it to work; something about an error with PyArrow. Did you ever have this problem? – Keith Oct 18 '20 at 20:14