Roel is a data generalist with a special interest in making business cases using structured data and simulation techniques.
SQLAlchemy is one of the most popular libraries to interface with relational databases in Python. Its distinguishing feature is the object-relational mapper (ORM), which allows software engineers to develop classes and automatically map them to a database.
Sure, SQLAlchemy is known to support operational databases, but did you know it supports also data warehouses like Snowflake? While ORM use cases are less frequent with data warehouses like Snowflake, SQLAlchemy provides a solid interface for running analytical queries.
In this tutorial, you’ll learn several different approaches to how to set up SQLAlchemy to interface with a Snowflake data warehouse. We’ll also explore the various pitfalls of these approaches so you can be prepared for anything. 💪
Snowflake + SQLAlchemy
Since Snowflake is a data warehouse, its main users are analytics professionals. Although many of them will interact with it through a SQL IDE, others will prefer to embed queries in their Python code.
But while Python is widely used in the data world, most applications require some kind of database technology for storing and processing relational data. That’s where SQLAlchemy comes in. 🦸
SQLAlchemy is a well-known library for interfacing with all kinds of database technologies like Oracle, Postgres, and of course, Snowflake, offering a unified way to run queries on both operational and analytical databases. Plus, it’s widely supported and vendor-agnostic, which helps organizations avoid software lock-in.
Here are a few use cases to show just how powerful it is. 👇
To translate the logical representations of objects into an atomized form that can be stored in a database, you use an object-relational mapper.
SQLAlchemy is a high-performing and accurate toolkit that’s used in tens of thousands of applications, so it excels in the area of object-relational mapping. Since it was designed with the database administrator (DBA) in mind, generated SQL can be swapped out for hand-optimized statements, making it a flexible solution for most ORM use cases in Python.
Nowadays, most data professionals working in Python use pandas, a data analysis and manipulation tool centered around the DataFrame. pandas has the built-in capability to translate the results of database queries to a DataFrame, but it requires a connector to a database. While most people are familiar with Open Database Connectivity (ODBC) and Java Database Connectivity (JDBC), you might not know that Snowflake also has its own proprietary connector.
The Snowflake connector uses a cursor to retrieve data and put it in a pandas DataFrame object, but there's a more convenient way to do this – one that allows analysts to query Snowflake within their familiar pandas experience.
Using SQLAlchemy with Snowflake
To generate a pandas DataFrame from Snowflake query results in Python, you could use the Python connector. However, a more straightforward solution is to use a SQLAlchemy engine with pandas’ read_sql function. Below, both solutions are presented.
To interface with Snowflake from Python, you’re going to need some packages. They can be installed with pip install in your terminal, or in a notebook cell by appending the commands with an exclamation mark, as shown in the example below. Note that these versions have been set to ensure that they don’t run into any compatibility issues:
query = """ SELECT O_CUSTKEY, O_ORDERSTATUS, SUM(O_TOTALPRICE) AS sum_totalprice, COUNT(O_ORDERKEY) AS count_orderkeys FROM TPCH_SF1.ORDERS GROUP BY 1, 2 LIMIT 10; """
In the sections below, we’ll execute this query in two different ways.
Generate a pandas DataFrame from a Snowflake Query with the Snowflake Connector
In this section, we’ll generate a DataFrame by iterating over the rows of a query result.
To start, import and create a Snowflake connector object. To make it work properly, you’ll need to provide it with the credentials you specified in the credentials dictionary you set earlier:
import pandas as pd import snowflake.connector
con = snowflake.connector.connect( account = credentials['account'], user = credentials['user'], password = credentials['password'], database = credentials['database'], schema = credentials['schema'], warehouse = credentials['warehouse'], role = credentials['role'] )
With the next piece of code, we’ll create a generate_df function. We’ll then indicate a cursor, a SQL query, and the maximum number of rows that should be loaded in a DataFrame. The function uses the cursor’s fetchmany method, which returns a fixed number of rows:
def generate_df(cur, query, n): cur.execute(query) col_names = [col.name for col in cur.description] rows = 0 while True: dat = cur.fetchmany(n) if not dat: break df = pd.DataFrame(dat, columns = col_names) rows += df.shape return df
Next, execute the generate_df function with a cursor based on the connection you opened earlier, a query, and optionally, the number of rows you’d like the function to return:
df = generate_df(con.cursor(), query, 10)
If you inspect the contents of your DataFrame, you should see the following results:
Note that the number of connections you can open is finite, so it's good practice to close the connection you opened. Otherwise, you’ll run into issues if you run a large number of queries in a short time span:
try: con.close() except Exception as e: print(e)
Generate a pandas DataFrame from a Snowflake Query with SQLAlchemy
Alternatively, you could use SQLAlchemy to wrap the Snowflake connector in an easy-to-use interface.
To do so, we’ll start by loading the required modules. You’ll notice that it not only loads SQLAlchemy, but also a module from the Snowflake SQLAlchemy toolkit, which acts as a bridge between SQLAlchemy and Snowflake.
Next, we’ll create a SQLAlchemy engine containing all the information that SQLAlchemy needs to interface with a database technology (which in this case is Snowflake). The engine is created with a Snowflake connection string, constructed by the URL function:
from snowflake.sqlalchemy import URL from sqlalchemy import create_engine import pandas as pd
If that doesn’t work, try registering the Snowflake dialect explicitly in the SQLAlchemy register by referring it to the Snowflake SQLAlchemy bridge:
from sqlalchemy.dialects import registry registry.register('snowflake', 'snowflake.sqlalchemy', 'dialect')
ModuleNotFoundError: No module named 'snowflake.sqlalchemy'
Some Anaconda users are unaware of the required Snowflake-SQLAlchemy bridge. While it can be installed through pip, Anaconda users can install it through the conda command. However, the package is not in the default channel, but in conda-forge. This requires that you set the channel parameters, as follows:
conda install -c conda-forge snowflake-sqlalchemy
snowflake.connector.errors. OperationalError: 250003: Failed to get the response
Many people report getting this error when setting up their connection for the first time. The most common root cause is that they ignored a warning in the documentation and included `snowflakecomputing.com` in their account identifier. Dropping it will likely solve the issue.
pandas only writes NULLs to Snowflake
pandas can do more than just read data from Snowflake, it can also write to it. However, some users reported getting columns with only NULL values, and no error or warning to indicate why. The cause? Snowflake expects uppercase column names. Fixing it is easy. Just change the case of your column names as follows:
df.columns = df.columns.str.upper()
AttributeError: 'SnowflakeCursor' object has no attribute cursor
This tutorial walked you through using Snowflake with SQLAlchemy to run SQL queries and store the results as pandas DataFrames. You probably noticed that using SQLAlchemy to interface with Snowflake from Python is superior to using the default connector in terms of simplicity. By creating an SQL engine and providing it to pandas methods, data professionals can work from within their familiar pandas setup.
After you integrate Snowflake and SQLAlchemy and you need a solution to sync your Snowflake data with your sales and marketing tools, Census has your back. Book a demo with a product specialist to see how Census can make your data actionable. 🚀