Snowflake SQLAlchemy

Snowflake SQLAlchemy runs on top of the Snowflake Connector for Python as a dialect to bridge a Snowflake database and SQLAlchemy applications.

Prerequisites

Python and SQLAlchemy versions

Snowflake SQLAlchemy 2.x requires:

  • Python 3.11 or later.
  • SQLAlchemy 2.0.44 or later, but earlier than 2.1 (>=2.0.44,<2.1).

Snowflake Connector for Python

The only other requirement for Snowflake SQLAlchemy is the Snowflake Connector for Python; however, the connector does not need to be installed separately because installing Snowflake SQLAlchemy automatically installs the connector.

Data analytics and web application frameworks (optional)

Snowflake SQLAlchemy can be used with Pandas, Jupyter and Pyramid, which provide higher levels of application frameworks for data analytics and web applications. However, building a working environment from scratch is not a trivial task, particularly for novice users. Installing the frameworks requires C compilers and tools, and choosing the right tools and versions is a hurdle that might deter users from using Python applications.

An easier way to build an environment is through Anaconda, which provides a complete, precompiled technology stack for all users, including non-Python experts such as data analysts and students. For Anaconda installation instructions, see the Anaconda install documentation. The Snowflake SQLAlchemy package can then be installed on top of Anaconda using pip.

Installing Snowflake SQLAlchemy

The Snowflake SQLAlchemy package can be installed from the public PyPI repository using pip:

pip install --upgrade snowflake-sqlalchemy

pip automatically installs all required modules, including the Snowflake Connector for Python.

Snowflake SQLAlchemy 2.x requires Python 3.11 or later, SQLAlchemy >=2.0.44,<2.1, and snowflake-connector-python>=5.0.0. If you’re upgrading from 1.x, see Migrating Snowflake SQLAlchemy from 1.x to 2.x.

To use pandas with Snowflake SQLAlchemy, install the pandas extra:

pip install "snowflake-sqlalchemy[pandas]"

Verifying your installation

  1. Create a file (for example, validate.py) that contains the following Python sample code, which connects to Snowflake and displays the Snowflake version:
from sqlalchemy import create_engine, text

engine = create_engine(
    'snowflake://{user}:{password}@{account}/'.format(
        user='<your_user_login_name>',
        password='<your_password>',
        account='<account_identifier>',
    )
)
try:
    with engine.connect() as connection:
        results = connection.execute(text('select current_version()')).fetchone()
        print(results[0])
finally:
    engine.dispose()

  1. Replace <your_user_login_name>, <your_password>, and <account_identifier> with the appropriate values for your Snowflake account and user.

    For more details, see Connection Parameters.

  2. Execute the sample code. For example, if you created a file named validate.py:

python validate.py

The Snowflake version (for example, 10.38.10) should be displayed.

Note

SQLAlchemy 2.x doesn’t accept plain SQL strings in connection.execute(). Wrap raw SQL in sqlalchemy.text(), or use connection.exec_driver_sql(). Passing a bare string raises sqlalchemy.exc.ObjectNotExecutableError.

Verifying package signatures

Python packages on PyPI can be digitally signed by their publishers using GPG. Verify the package signature of snowflake-sqlalchemy to ensure that the package you downloaded was published by Snowflake and has not been tampered with.

The package signatures and the public key can be found on the Snowflake Python Packages repository.

Next Topics:

Support

For support, please visit Snowflake Community Forum or contact Snowflake Support.