Readme¶

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.