Last modified on: 03 Dec 2020
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.
This dialect requires SQLAlchemy. To install, just run:
pip install sqlalchemy
The Denodo dialect for SQLAlchemy can be downloaded from the Denodo Support Site.
To install 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.
To connect to Denodo VDP Server with SQLAlchemy, the following URL pattern can be used:
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.
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.
You can install Jupyter Notebook by following the installation manual.
If SQLAlchemy is not installed you can use pip from a Jupyter notebook to install it.
pip install sqlalchemy psycopg2
To execute Denodo queries you need to load the iPython SQL extension.
Connect with a Denodo database using the SQLAlchemy URL pattern for Denodo dialect.
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:
To execute SQL queries you can use: