• User Manuals /
  • Denodo Dialect for SQLAlchemy - User Manual

Denodo Dialect for SQLAlchemy - User Manual

Download original document


You can translate the document:

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

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.

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 recommended way to install the dialect is by using the PyPi repository.

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

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

Once the package is unzipped, in order to install the dialect just run:

        pip install route/to/denodo_sqlalchemy-<version>-py3-none-any.whl

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.


Usage

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"})

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:

query = "SELECT * FROM books WHERE category = ?"

data = pandas.read_sql(query, engine, params=("Computer",))

Limitations

  • Note that Denodo Dialect for SQLAlchemy does not support CUID operations.