SQLAlchemy Dialect for EXASOL DB

How to get started

We assume you have a good understanding of (unix)ODBC. If not, make sure you read their documentation carefully - there are lot's of traps 🪤 to step into.

Meet the system requirements

On Linux/Unix like systems you need:

• Python
• An Exasol DB (e.g. docker-db or a cloud instance)
• The packages unixODBC and unixODBC-dev >= 2.2.14
• The Exasol ODBC driver
• The ODBC.ini and ODBCINST.ini configurations files setup

Turbodbc support

• 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

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

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

to use turbodbc as driver:

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
• you can even use the MERGE statement (see unit tests for examples)

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