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
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-exasolfor turbodbc support:
$ pip install sqlalchemy-exasol[turbodbc]Talk to the EXASOL DB using SQLAlchemy
from sqlalchemy import create_engine
url = "exa+pyodbc://A_USER:[email protected]: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:[email protected]: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:[email protected]: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