Skip to main content

EXASOL dialect for SQLAlchemy

Project description

https://github.com/exasol/sqlalchemy_exasol/workflows/CI/badge.svg?branch=master PyPI Version PyPI - Python Version Exasol - Supported Version(s) Formatter - Black Formatter - Isort Pylint License Last Commit PyPI - Downloads

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

Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

sqlalchemy_exasol-3.2.2.tar.gz (23.6 kB view hashes)

Uploaded Source

Built Distribution

sqlalchemy_exasol-3.2.2-py3-none-any.whl (26.8 kB view hashes)

Uploaded Python 3

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page