Denodo Dialect for SQLAlchemy

This package includes the Denodo Dialect for SQLAlchemy, which offers an easy way to connect to Denodo databases from SQLAlchemy https://www.sqlalchemy.org/

Denodo documentation and software resources are available at https://community.denodo.com

This dialect supports SQLAlchemy v2.0 and Denodo 8.0 and higher.

Pre-requisites

Python 3.9 or newer is needed.

This dialect requires the following Python modules:

• SQLAlchemy 2.0.x
• psycopg2 version 2.9 or higher.

This Python modules are also optional dependencies in order to use Denodo Virtual DataPort's (optional) Flight SQL interface:

• flightsql
  • adbc-driver-flightsql version 1.3.0 or higher
  • pyarrow version 18.1.0 or higher

Required dependencies will be installed automatically, in order to install dependencies for Flight SQL the flightsql extras package needs to be installed:

.. code-block::
    
   pip install denodo-sqlalchemy[flightsql]

Alternatively, dependency packages can be installed manually:

.. code-block::

    pip install sqlalchemy~=2.0.0
    pip install psycopg2-binary>=2.9
    pip install adbc-driver-flightsql~=1.4.0
    pip install pyarrow~=19.0.0

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

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

.. code-block::

    pip install --upgrade denodo-sqlalchemy
    or
    pip install --upgrade denodo-sqlalchemy[flightsql]

:code:pip automatically installs all required modules.

Usage

To connect to Denodo VDP Server with SQLAlchemy, the following URL pattern can be used:

.. code-block::

    denodo://<username>:<password>@<host>:<port[9996]>/<database>

This will create a connection using the default driver psycopg2, so it is equivalent to:

.. code-block::

    denodo+psycopg2://<username>:<password>@<host>:<port[9996]>/<database>

In order to create a connection using Flight SQL, you would need:

.. code-block::

    denodo+flightsql://<username>:<password>@<host>:<port[9994]>/<database>

Please note that Flight SQL connections assume SSL is enabled at the server. If not, you would need to add the use_encryption parameter set to False, like:

.. code-block::

    engine = sqlalchemy.create_engine("denodo+flightsql://someuser:somepwd@someserver:9994/somedb", connect_args={"use_encryption":"false"})