SQLAlchemy Dialect for EXASOL DB

How to get started

Currently, sqlalchemy-exasol supports multiple dialects. The core difference being if the dialect is odbc or websocket based.

Generally, we advise to use the websocket based Dialect, because odbc based dialects require a good understanding of (unix)ODBC and the setup is significant more complicated.

Turbodbc support

Warning

Maintenance of this feature is on hold. Also it is very likely that turbodbc support will be dropped in future versions.

• You can use Turbodbc with sqlalchemy_exasol if you use a python version >= 3.8.
• Multi row update is not supported, see test/test_update.py for an example

Meet the system requirements

• Python
• An Exasol DB (e.g. docker-db or a cloud instance)

ODBC-based dialects additionally require the following to be available and set up:

• The packages unixODBC and unixODBC-dev >= 2.2.14
• The Exasol ODBC driver
• The ODBC.ini and ODBCINST.ini configurations files setup

Setup your python project and install sqlalchemy-exasol

$ pip install sqlalchemy-exasol

for turbodbc support:

$ pip install sqlalchemy-exasol[turbodbc]

Talk to the EXASOL DB using SQLAlchemy

Websocket based Dialect:

For more details regarding the websocket support checkout the section: "What is Websocket support?"

from sqlalchemy import create_engine
url = "exa+websocket://A_USER:A_PASSWORD@192.168.1.2..8:1234/my_schema?CONNECTIONLCALL=en_US.UTF-8"
e = create_engine(url)
r = e.execute("select 42 from dual").fetchall()

Pyodbc (ODBC based Dialect):

from sqlalchemy import create_engine
url = "exa+pyodbc://A_USER:A_PASSWORD@192.168.1.2..8:1234/my_schema?CONNECTIONLCALL=en_US.UTF-8&driver=EXAODBC"
e = create_engine(url)
r = e.execute("select 42 from dual").fetchall()

Turbodbc (ODBC based Dialect):

from sqlalchemy import create_engine
url = "exa+turbodbc://A_USER:A_PASSWORD@192.168.1.2..8:1234/my_schema?CONNECTIONLCALL=en_US.UTF-8&driver=EXAODBC"
e = create_engine(url)
r = e.execute("select 42 from dual").fetchall()

The dialect supports two types of connection urls creating an engine. A DSN (Data Source Name) mode and a host mode:

Type	Example
DSN URL	'exa+pyodbc://USER:PWD@exa_test'
HOST URL	'exa+pyodbc://USER:PWD@192.168.14.227..228:1234/my_schema?parameter'

Features

• SELECT, INSERT, UPDATE, DELETE statements

Notes

• Schema name and parameters are optional for the host url
• At least on Linux/Unix systems it has proven valuable to pass 'CONNECTIONLCALL=en_US.UTF-8' as a url parameter. This will make sure that the client process (Python) and the EXASOL driver (UTF-8 internal) know how to interpret code pages correctly.
• Always use all lower-case identifiers for schema, table and column names. SQLAlchemy treats all lower-case identifiers as case-insensitive, the dialect takes care of transforming the identifier into a case-insensitive representation of the specific database (in case of EXASol this is upper-case as for Oracle)
• As of Exasol client driver version 4.1.2 you can pass the flag 'INTTYPESINRESULTSIFPOSSIBLE=y' in the connection string (or configure it in your DSN). This will convert DECIMAL data types to Integer-like data types. Creating integers is a factor three faster in Python than creating Decimals.

Development & Testing

See developer guide

What is Websocket support?

In the context of SQLA and Exasol, Websocket support means that an SQLA dialect supporting the Exasol Websocket Protocol is provided.

Using the websocket based protocol instead over ODBC will provide various advantages:

• Less System Dependencies
• Easier to use than ODBC based driver(s)
• Lock free metadata calls etc.

For further details Why a Websockets API.

Example Usage(s)

from sqla import create_engine

engine = create_engine("exa+websocket://sys:exasol@127.0.0.1:8888")
with engine.connect() as con:
    ...

from sqla import create_engine

# ATTENTION:
# In terms of security it is NEVER a good idea to turn of certificate validation!!
# In rare cases it may be handy for non-security related reasons.
# That said, if you are not a 100% sure about your scenario, stick with the
# secure defaults.
# In most cases, having a valid certificate and/or configuring the truststore(s)
# appropriately is the best/correct solution.
engine = create_engine("exa+websocket://sys:exasol@127.0.0.1:8888?SSLCertificate=SSL_VERIFY_NONE")
with engine.connect() as con:
    ...

Supported Connection Parameters

Parameter	Values	Comment
ENCRYPTION	Y, Yes, N, No	Y or Yes Enable Encryption (TLS) default, N or No disable Encryption
SSLCertificate	SSL_VERIFY_NONE	Disable certificate validation

Known Issues

• Insert

  • Insert multiple empty rows via prepared statements does not work in all cases