Using pandas DataFrames with the Python Connector¶

Requirements¶

Currently, the pandas-oriented API methods in the Python connector API work with:

• Snowflake Connector 2.1.2 (or higher) for Python.

• PyArrow library version 3.0.x.

  If you do not have PyArrow installed, you do not need to install PyArrow yourself; installing the Python Connector as documented below automatically installs the appropriate version of PyArrow.

  Caution

  If you already have any version of the PyArrow library other than the recommended version listed above, please uninstall PyArrow before installing the Snowflake Connector for Python. Do not re-install a different version of PyArrow after installing the Snowflake Connector for Python.

• pandas 0.25.2 (or higher). Earlier versions might work, but have not been tested.

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.

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

If you need to install other extras (for example, secure-local-storage for caching connections with browser-based SSO or caching MFA tokens), use a comma between the extras:

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

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

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 (https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html) 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:

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

Copy

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

Migrating to pandas DataFrames¶

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)
  

  Copy

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

  Copy

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