EXASOL dialect for SQLAlchemy
Project description
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.
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
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
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
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
Websocket support
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.
Examples 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
- Literal casts within prepared statements do not work
INSERT INTO t (x) VALUES (CAST(? AS VARCHAR(50)));
- Various conversions regarding float, decimals
Certain scenarios still yield a
string
type insteadfloat
ordecimal
type.
- Insert
Insert multiple rows via prepared statements does not work in all cases
Insert from SELECT does not work
- For some prepared statements, the wss protocol type conversion does not work properly
Error messages usually state some JSON type mismatch, e.g.: ‘… getString: JSON value is not a string …’
- Known failing tests of the SQLA compliance test suite
FAILED test/integration/sqlalchemy/test_suite.py::CastTypeDecoratorTest_exasol+exasol_driver_websocket_dbapi2::test_special_type - sqlalchemy.exc.DBAPIError: (exasol.driver.websocket._errors.Error)
FAILED test/integration/sqlalchemy/test_suite.py::ExistsTest_exasol+exasol_driver_websocket_dbapi2::test_select_exists - sqlalchemy.exc.DBAPIError: (exasol.driver.websocket._errors.Error)
FAILED test/integration/sqlalchemy/test_suite.py::ExistsTest_exasol+exasol_driver_websocket_dbapi2::test_select_exists_false - sqlalchemy.exc.DBAPIError: (exasol.driver.websocket._errors.Error)
FAILED test/integration/sqlalchemy/test_suite.py::InsertBehaviorTest_exasol+exasol_driver_websocket_dbapi2::test_empty_insert_multiple - sqlalchemy.exc.DBAPIError: (exasol.driver.websocket._errors.Error)
ERROR test/integration/sqlalchemy/test_suite.py::InsertBehaviorTest_exasol+exasol_driver_websocket_dbapi2::test_empty_insert_multiple_teardown - ERROR
FAILED test/integration/sqlalchemy/test_suite.py::InsertBehaviorTest_exasol+exasol_driver_websocket_dbapi2::test_insert_from_select - sqlalchemy.exc.DBAPIError: (exasol.driver.websocket._errors.Error)
FAILED test/integration/sqlalchemy/test_suite.py::InsertBehaviorTest_exasol+exasol_driver_websocket_dbapi2::test_insert_from_select_with_defaults - sqlalchemy.exc.DBAPIError: (exasol.driver.websocket._errors.Error)
FAILED test/integration/sqlalchemy/test_suite.py::NumericTest_exasol+exasol_driver_websocket_dbapi2::test_float_as_decimal - sqlalchemy.exc.DBAPIError: (exasol.driver.websocket._errors.Error)
FAILED test/integration/sqlalchemy/test_suite.py::NumericTest_exasol+exasol_driver_websocket_dbapi2::test_float_as_float - sqlalchemy.exc.DBAPIError: (exasol.driver.websocket._errors.Error)
FAILED test/integration/sqlalchemy/test_suite.py::NumericTest_exasol+exasol_driver_websocket_dbapi2::test_float_coerce_round_trip - AssertionError: ‘15.7563’ != 15.7563
FAILED test/integration/sqlalchemy/test_suite.py::NumericTest_exasol+exasol_driver_websocket_dbapi2::test_float_custom_scale - sqlalchemy.exc.DBAPIError: (exasol.driver.websocket._errors.Error)
FAILED test/integration/sqlalchemy/test_suite.py::NumericTest_exasol+exasol_driver_websocket_dbapi2::test_numeric_as_float - AssertionError: {‘15.7563’} != {15.7563}
FAILED test/integration/sqlalchemy/test_suite.py::NumericTest_exasol+exasol_driver_websocket_dbapi2::test_render_literal_numeric_asfloat - AssertionError: assert ‘15.7563’ in [15.7563]
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
Hashes for sqlalchemy_exasol-4.5.0-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 37b73468c6d4d98dd3c4d00071bec9be466c1f194387cf5794a19b2de18a345a |
|
MD5 | 8ae52109b2ccdfb44bc0db0b014a1741 |
|
BLAKE2b-256 | de0e2745b116cd44ce59ad26a9899684a375a43c939013774ea39d9760d5550b |