EXASOL dialect for SQLAlchemy
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:
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
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:
SELECT, INSERT, UPDATE, DELETE statements
you can even use the MERGE statement (see unit tests for examples)
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
Release history Release notifications | RSS feed
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Hashes for sqlalchemy_exasol-4.0.0-py3-none-any.whl