Denodo Dialect for SQLAlchemy - User Manual
Revision 20250131
NOTE
This document is confidential and proprietary of Denodo Technologies.
No part of this document may be reproduced in any form by any means without prior
written authorization of Denodo Technologies.
Copyright © 2025
Denodo Technologies Proprietary and Confidential
Denodo Inc.: 525 University Avenue, Suite 31, Palo Alto, CA 94301. USA www.denodo.com
Denodo Dialect for SQLAlchemy - User Manual
20250131
2 of 9
Denodo Inc.: 525 University Avenue, Suite 31, Palo Alto, CA 94301. USA www.denodo.com
Denodo Dialect for SQLAlchemy - User Manual
20250131
3 of 9
2 INTRODUCTION
SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives
application developers the full power and flexibility of SQL.
It provides a full suite of well known enterprise-level persistence patterns, designed for
efficient and high-performing database access, adapted into a simple and Pythonic
domain language.
The dialect is the system SQLAlchemy uses to communicate with various types of
DBAPI implementations and databases.
With the Denodo Dialect for SQLAlchemy we can connect with Denodo databases and
query data from Denodo views.
The Denodo Dialect for SQLAlchemy can use two different drivers:
● psycopg2 (which will connect to port 9996 of VDP)
● flightsql (which will connect to port 9994 of VDP 9.1+)
Denodo Inc.: 525 University Avenue, Suite 31, Palo Alto, CA 94301. USA www.denodo.com
Denodo Dialect for SQLAlchemy - User Manual
20250131
4 of 9
3 SQLALCHEMY COMPATIBILITY
The Denodo Dialect for SQLAlchemy requires SQLAlchemy 1.4.
However, in order to use Flight SQL connectivity (available since Denodo 9.1),
SQLAlchemy 2.0 is required.
Denodo Inc.: 525 University Avenue, Suite 31, Palo Alto, CA 94301. USA www.denodo.com
Denodo Dialect for SQLAlchemy - User Manual
20250131
5 of 9
4 INSTALLATION AND EXECUTION
This dialect requires the following Python libraries:
● sqlalchemy>=1.4.36
● psycopg2>=2.9.6
In order to use the Flight SQL interface, these dependencies apply:
● sqlalchemy>=2.0.0
● adbc-driver-flightsql~=1.3.0
● pyarrow~=19.0.0
The dialect will be available from two different locations:
● The Python Package Index (PyPI)
● The Denodo Support Site.
The recommended way to install the dialect is by using the PyPi repository.
4.1 INSTALLATION FROM PYPI
The dialect can be easily installed into a Python environment using pip:
pip install denodo-sqlalchemy
Or:
pip install denodo-sqlalchemy[flightsql]
Note the flightsql extras dependency package will need to be specified in order for pip
to install the dependencies needed to use the Flight SQL interface.
Important: note also that psycopg2 has its own requirements for installation which
need to be satisfied: https://www.psycopg.org/docs/install.html#prerequisites
4.2 INSTALLATION FROM DENODO SUPPORT SITE
Although less recommended as an installation method, the Denodo Dialect for
SQLAlchemy can also be downloaded from the Denodo Support Site.
If installed from the package distributed from the Denodo Support Site, dependencies
will need to be previously installed, either manually or via pip. See above (or the
dialect’s README files) for a list of the specific dependency versions needed.
Important: note also that psycopg2 has its own requirements for installation which
need to be satisfied: https://www.psycopg.org/docs/install.html#prerequisites
Denodo Inc.: 525 University Avenue, Suite 31, Palo Alto, CA 94301. USA www.denodo.com
Denodo Dialect for SQLAlchemy - User Manual
20250131
6 of 9
Once the package is unzipped, in order to install the dialect just run:
pip install route/to/denodo_sqlalchemy-<version>-py3-none-any.whl
4.3 INSTALLING IN APACHE SUPERSET
There are several scenarios to be taken into consideration regarding the use of this
Dialect in Apache Superset:
● Denodo distributes customized Docker images of Apache Superset as a part of
the “Denodo Dashboard” DenodoConnect component. These Docker images
already contain the Denodo DBEngineSpec and the Denodo Dialect for
SQLAlchemy, both pre-installed.
● Standard distributions of Apache Superset 5.0+ will include the Denodo
DBEngineSpec and documentation in order to easily install the Denodo Dialect
for SQLAlchemy.
● Standard distributions of Apache Superset 4.x can be manually added the
Denodo DBEngineSpec (denodo.py) into its superset/db_engine_specs folder,
and then installed the Denodo Dialect for SQLAlchemy.
Note that, as of Apache Superset 4.x, Superset uses SQLAlchemy 1.4 and therefore
only the psycopg2 driver will be available to connect to Virtual DataPort (no Flight SQL
connectivity will be available).
To create a new database you can use the SQLAlchemy URL pattern for Denodo dialect.
Denodo Inc.: 525 University Avenue, Suite 31, Palo Alto, CA 94301. USA www.denodo.com
Denodo Dialect for SQLAlchemy - User Manual
20250131
7 of 9
5 USAGE
5.1 PSYCOPG2
The dialect can be used with the psycopg2 (PostgreSQL) driver like:
uri =
"denodo+psycopg2://<user>:<password>@<host>:<port[9996]>/<database>"
engine = sqlalchemy.create_engine(uri)
Note psycopg2 is the default driver so the above URL would be actually equivalent to:
uri = "denodo://<user>:<password>@<host>:<port:9996>/<database>"
Then data can be easily queried using pandas Data Frames:
query = "SELECT * FROM <table>"
data = pandas.read_sql(query, engine)
Query parameters can be expressed using the pyformat parameter style:
query = "SELECT * FROM books WHERE category = %(category)s"
data = pandas.read_sql(query, engine, params={"category": "Computer"})
5.2 FLIGHTSQL
The dialect can be used with the flightsql (Flight SQL) driver like:
uri =
"denodo+flightsql://<user>:<password>@<host>:<port[9994]>/<database>"
engine = sqlalchemy.create_engine(uri)
Note that the Flight SQL interface will assume TLS/SSL is enabled at the server and, if
not, this should be explicitly signaled:
engine = sqlalchemy.create_engine(
uri,
connect_args={
denodo_sqlalchemy.flightsql.CONNECT_ARGS_USE_ENCRYPTION:
"false"
})
Then data can be easily queried using pandas Data Frames:
query = "SELECT * FROM <table>"
data = pandas.read_sql(query, engine)
Query parameters can be expressed using the qmark parameter style:
Denodo Inc.: 525 University Avenue, Suite 31, Palo Alto, CA 94301. USA www.denodo.com
Denodo Dialect for SQLAlchemy - User Manual
20250131
8 of 9
query = "SELECT * FROM books WHERE category = ?"
data = pandas.read_sql(query, engine, params=("Computer",))
Denodo Inc.: 525 University Avenue, Suite 31, Palo Alto, CA 94301. USA www.denodo.com
Denodo Dialect for SQLAlchemy - User Manual
20250131
9 of 9
6 LIMITATIONS
■ Note that Denodo Dialect for SQLAlchemy does not support CUID
operations.
Denodo Inc.: 525 University Avenue, Suite 31, Palo Alto, CA 94301. USA www.denodo.com
