Using Pandas with the Python Connector¶

pandas is a library for data analysis. With pandas, you use a data structure called a DataFrame to analyze and manipulate two-dimensional data (such as data from a database table).

For more information see the pandas documentation.

If you need to get data from a Snowflake database to a pandas DataFrame, you can use the API methods provided with the Snowflake Connector for Python. The connector also provides API methods for writing data from a pandas DataFrame to a Snowflake database.

Note

Some of these API methods require a specific version of the PyArrow library. See Requirements for details.

For more information, see the PyArrow library documentation.

Requirements¶

The pandas-oriented API methods work with:

Snowflake Connector for Python.
PyArrow 14.0.1 or later, which the pandas extra installs.
pandas earlier than 3.0.0. The minimum is 1.0.0 on Python before 3.13, and 2.1.2 on Python 3.13 and later.

Note

Before version 5.0, these methods worked with connector 2.1.2 or later, PyArrow 3.0.x or later, and pandas 0.25.2 or later, depending on the connector version.

The pandas extra installs PyArrow. If a different PyArrow version is already installed, uninstall it before you install the connector extra, and don’t install another PyArrow version afterward.

Installation¶

To install the pandas-compatible version of the Snowflake Connector for Python, execute the command:

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

You must enter the square brackets ([ and ]) as shown in the command. The square brackets specify the extra part of the package to install.

For more information, see the extras Python dependency.

Use quotes around the name of the package (as shown) to prevent the square brackets from being interpreted as a wildcard.

The connector does not publish a secure-local-storage extra. Token caching is part of the connector.

Note

Before version 5.0, Linux token caching used that extra:

pip install "snowflake-connector-python[secure-local-storage,pandas]"

See Using MFA token caching to minimize the number of prompts during authentication — *optional* and Renamed and deprecated parameters.

Reading data from a Snowflake database to a pandas DataFrame¶

To read data into a pandas DataFrame, you use a Cursor to retrieve the data and then call one of these Cursor methods to put the data into a pandas DataFrame:

fetch_pandas_all().
fetch_pandas_batches().

These methods return a DataFrame for every result set, including JSON results from SHOW, DESCRIBE, DDL, PUT, and GET.

Note

Before version 5.0, they raised NotSupportedError unless the server returned Arrow.

Keyword arguments are forwarded to pyarrow.Table.to_pandas. Arguments that only pandas.concat accepts, such as sort, keys, and copy, raise TypeError.

Note

Before version 5.0, those arguments were applied to pandas.concat while the connector combined per-batch frames.

Writing data from a pandas DataFrame to a Snowflake database¶

To write data from a pandas DataFrame to a Snowflake database, do one of the following:

Call the write_pandas() function.
Call the pandas.DataFrame.to_sql() method.

For more information, see the pandas.DataFrame.to_sql documentation, and specify pd_writer() as the method to use to insert the data into the database.

Snowflake to pandas data mapping¶

The table below shows the mapping from Snowflake data types to pandas data types:

Snowflake Data Type	pandas Data Type
FIXED NUMERIC type (scale = 0) except DECIMAL	`(u)int{8,16,32,64}` or `float64` (for NULL)
FIXED NUMERIC type (scale > 0) except DECIMAL	`float64`
FIXED NUMERIC type DECIMAL	`decimal`
FLOAT/DOUBLE	`float64`
VARCHAR	`str`
BINARY	`str`
VARIANT	`str`
DATE	`object` (with `datetime.date` objects)
TIME	`pandas.Timestamp(np.datetime64[ns])`
TIMESTAMP_NTZ, TIMESTAMP_LTZ, TIMESTAMP_TZ	`pandas.Timestamp(np.datetime64[ns])`

Notes:

If the Snowflake data type is FIXED NUMERIC and the scale is zero, and if the value is NULL, then the value is converted to float64, not an integer type.
If any conversion causes overflow, the Python connector throws an exception.

Importing pandas¶

Customarily, pandas is imported with the following statement:

import pandas as pd

You might see references to pandas objects as either pandas.object or pd.object.

Migrating to Pandas¶

This section is primarily for users who have used pandas (and possibly SQLAlchemy) previously.

Previous pandas users might have code similar to either of the following:

This example shows the original way to generate a pandas DataFrame from the Python connector:

import pandas as pd

def fetch_pandas_old(cur, sql):
 cur.execute(sql)
 rows = 0
 while True:
     dat = cur.fetchmany(50000)
     if not dat:
         break
     df = pd.DataFrame(dat, columns=cur.description)
     rows += df.shape[0]
 print(rows)

This example shows how to use SQLAlchemy to generate a pandas DataFrame:

import pandas as pd

def fetch_pandas_sqlalchemy(sql):
 rows = 0
 for chunk in pd.read_sql_query(sql, engine, chunksize=50000):
     rows += chunk.shape[0]
 print(rows)

Code that is similar to either of the preceding examples can be converted to use the Python connector pandas API calls listed in Reading Data from a Snowflake Database to a pandas DataFrame (in this topic).

Note

With support for pandas in the Python connector, SQLAlchemy is no longer needed to convert data in a cursor into a DataFrame.

However, you can continue to use SQLAlchemy if you wish; the Python connector maintains compatibility with SQLAlchemy.