Introduction

Data scientists rely on Python, and Python-workbooks for its seamless transition between data manipulation, machine learning, and visualization. Denodo enhances this workflow by acting as the semantic interoperability layer, handling the complexities of enterprise data access and governance across internal and external silos, so Python users can consume clean data directly.

This article explores the methods for connecting Python to Denodo, specifically focusing on how to efficiently ingest data using SQLAlchemy. We will cover the technical implementation of these connections to provide a foundation for exploration, integration, and querying.

Connection Methods

Denodo maintains two SQL Alchemy dialects to seamlessly establish the connection between Python and Denodo:

The easiest way to connect to the Denodo Platform from Python is to use a Denodo Dialect for SQLAlchemy. Although you can use SQLAlchemy with either Arrow Flight SQL and psycopg2 interfaces, we recommend the use of the Denodo’s Flight SQL Dialect for greater throughput, as the connection leverages Apache Arrow’s columnar data format and RPC framework to reduce serialization overhead and maximize throughput.

What is SQL Alchemy

SQLAlchemy provides a Pythonic abstraction of SQL, mapping database operations to generative Python expressions. This is intuitive by representing database concepts like engines, connections, and schemas, as native Python objects allowing for a direct translation of Python logic into Denodo VQL. Take the following example of a standard VQL Query:

This can also be expressed in SQL Alchemy with the following using the select() and where() operator:

For information on how SQLAlchemy maps specific operations visit the SQLAlchemy documentation.

Although SQLAlchemy allows VQL strings to be passed directly, utilizing SQLAlchemy Core functions offers several advantages for production-grade workflows:

• Automatically handling parameterization, protecting your Denodo environment from SQL injection.
• Ensures that Pythonic commands are correctly translated into VQL syntax.
• Allows to construct conditional logic using Python variables.

Installation

denodo-sqlalchemy is typically installed from Python’s package repository PyPI using pip or uv.

Requirements

• Python 3.9 or newer is needed.
• Denodo

• Denodo 9.1 or newer for FlightSQL
• Older versions of Denodo have legacy support via Psycopg2

Flight SQL

pip

uv

Psycopg2

pip

uv

Connecting to Denodo

SQLAlchemy utilizes Connection URIs to define how the engine (connection manager) communicates with your Denodo instance. The format changes depending on the driver and the authentication required.

Flight SQL Connection URL:

Note that the Flight SQL interface will assume TLS/SSL is enabled at the server and, if not, this should be explicitly signaled:

Psycopg2 Connection URL:

Note:  The ports used by the two interfaces are different.

Authentication

Basic

To connect with a Username and Password using Basic Authentication, you can pass credentials directly within the connection string.

OAuth

OAuth is not supported by the psycopg2 and is only available via the flightsql driver.

To use OAuth with FlightSQL, we pass the Oauth token, without any Bearer prefix, in the password parameter of the engine object. We must also specify to Denodo that this is an OAUTH connection with the username “_$#AUTHTYPE_OAUTH2#$_”.

A standard pattern for dynamically retrieving OAuth tokens is using the @event.listens_for decorator. This decorator works by executing code when an object, like the engine, emits a call to a function. In our case it can inject a token into the driver configuration when the engine wants to make a call to Denodo.

The generation of the token is outside the scope of this documentation. In a typical Oauth deployment, a user would use a library like requests-oauthlib or msal to dynamically fetch a fresh token first, then inject it using @event.listens_for.

The following is a simplified template of what using @event.listens_for to inject a token into the driver configuration ‘password’ can look like.

For more information on driver parameters, you can reference the driver documentation.

Querying Denodo with SQLAlchemy and Pandas

Denodo SQL Alchemy works with many python native in-Memory Analytical Engines, the most common being Pandas. Pandas is one of the largest communities within python with many scientific libraries using it in the background.

Querying with Denodo SQLAlchemy is straightforward with the read_sql() function.

This will send a query, designated by the query variable in this example, using the connection managed by the SQLAlchemy engine. The result will be a dataframe with the name df_filtered.

Summary

Integrating Denodo with Python via Denodo-SQLAlchemy transforms the way data scientists interact with their enterprise data ecosystem. By offloading the heavy lifting of data governance and silo integration to Denodo’s virtual semantic layer, Data Scientists can focus on what matters: driving analytics, extracting insights, and building models.

With Denodo’s Flight SQL interface, connections are faster and more efficient than ever. Leveraging the Apache Arrow framework, Denodo eliminates the traditional overhead of data serialization, providing the high-speed throughput required for modern, massive datasets. This high-performance connection ensures data-driven applications are built on a standardized,  scalable and future-proof foundation.

Examples

Querying Denodo via Arrow Flight SQL

Example with Arrow Flight SQL and Pandas

Querying Denodo via Psycopg2 (prior Denodo 9.1)

Example with Psycopg2 and Pandas

Tested Software Versions