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

Denodo Dialect for SQLAlchemy - User Manual

Download original 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.

Installation and Execution

This dialect requires the following Python modules:

  • SQLAlchemy version 1.4 or higher
  • psycopg2 version 2.7 or higher.

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 sqlalchemy and psycopg2 dependencies do not need to be installed because when you install Denodo Dialect for SQLAlchemy both should be installed automatically.

Alternatively, these packages can be manually installed by means of:

pip install sqlalchemy

pip install psycopg2

Important: note also that psycopg2 has its own requirements for installation which need to be satisfied: https://www.psycopg.org/docs/install.html#prerequisites

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

pip install --upgrade denodo-sqlalchemy

Installation from Denodo Support Site

The sqlalchemy and psycopg2 dependencies must be manually installed using:

pip install sqlalchemy

pip install psycopg2

Important: note also that psycopg2 has its own requirements for installation which need to be satisfied: https://www.psycopg.org/docs/install.html#prerequisites

The Denodo dialect for SQLAlchemy can be downloaded from the Denodo Support Site.

To install dialect just run:

        pip install route/to/denodo_sqlalchemy-20220419-py3-none-any.whl

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

denodo://<username>:<password>@<host>:<odbcport>/<database>

Jupyter Notebook Installation

You can install Jupyter Notebook by following the installation manual.

The most immediate way to execute queries against Denodo is by using the create_engine() and execute() statements.

import sqlalchemy as db

engine=db.create_engine("denodo://admin:admin@localhost:9996/admin")

result_set=engine.execute("SELECT * FROM denodotest")

Alternatively, to execute queries, you can use the iPython SQL extension

%load_ext sql

If the extension is not installed just run:

pip install ipython-sql

Connect with a Denodo database using the SQLAlchemy URL pattern for Denodo dialect.

%sql denodo://admin:admin@localhost:9996/admin

Run queries to Denodo views.

%sql select * from denodotest

It is also possible to use the results obtained from Denodo to draw histograms or other types of diagrams.

Another possible way is to install pandas to read data using SQL.

pip install pandas

When you’ve installed pandas, you will need to create a connection using:

db.create_engine("denodo://<user>:<pass>@<host>:<odbcport>/<database>")

To execute SQL queries you can use:

pd.read_sql_query(query,engine)

Apache Superset Installation

You can install Apache Superset by following the installation manual. SQLAlchemy is specified as a requirement so pip will install it if it is not already in place.

To use Superset it is necessary to download version 20201204 of the dialect from the Denodo Support Site. This is because the current version of SuperSet still does not support SQLAlchemy 1.4.

To install this older version of the dialect just unzip the Denodo dialect distribution inside the sqlalchemy/dialects folder making sure the files for the Denodo dialect are in a "..../dialects/denodo" folder.

If you have followed the recommendation to install superset in a virtualenv you must unzip the Denodo dialect in the directory  /venv/lib/python3.6/site-packages/sqlalchemy/dialects

To create a new database you can use the SQLAlchemy URL pattern for Denodo dialect.

Once the database is created we can create tables and add them to a dashboard.

Limitations

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