Skip to main content

EXASOL dialect for SQLAlchemy

Project description

https://github.com/exasol/sqlalchemy-exasol/actions/workflows/ci-cd.yml/badge.svg?branch=master&event=push PyPI Version PyPI - Python Version Exasol - Supported Version(s) Formatter - Black Formatter - Isort Pylint License Last Commit PyPI - Downloads

Getting Started with SQLAlchemy-Exasol

SQLAlchemy-Exasol supports multiple dialects, primarily differentiated by whether they are ODBC or Websocket based.

Choosing a Dialect

We recommend using the Websocket-based dialect due to its simplicity. ODBC-based dialects demand a thorough understanding of (Unix)ODBC, and the setup is considerably more complex.

System Requirements

Setting Up Your Python Project

Install SQLAlchemy-Exasol:

$ pip install sqlalchemy-exasol

Using SQLAlchemy with EXASOL DB

Websocket based Dialect:

from sqlalchemy import create_engine
url = "exa+websocket://A_USER:A_PASSWORD@192.168.1.2..8:1234/my_schema?CONNECTIONLCALL=en_US.UTF-8"
e = create_engine(url)
r = e.execute("select 42 from dual").fetchall()

Examples:

from sqlalchemy import create_engine

engine = create_engine("exa+websocket://sys:exasol@127.0.0.1:8888")
with engine.connect() as con:
    ...
from sqlalchemy 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:
    ...

Pyodbc (ODBC based Dialect):

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()

Turbodbc (ODBC based Dialect):

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()

Features

  • SELECT, INSERT, UPDATE, DELETE statements

General 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.

Known Issues

  • Insert
    • Insert multiple empty rows via prepared statements does not work in all cases

Development & Testing

See developer guide

Project details


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-5.1.0.tar.gz (23.7 kB view details)

Uploaded Source

Built Distribution

sqlalchemy_exasol-5.1.0-py3-none-any.whl (25.7 kB view details)

Uploaded Python 3

File details

Details for the file sqlalchemy_exasol-5.1.0.tar.gz.

File metadata

  • Download URL: sqlalchemy_exasol-5.1.0.tar.gz
  • Upload date:
  • Size: 23.7 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: poetry/2.0.1 CPython/3.12.3 Linux/6.8.0-1021-azure

File hashes

Hashes for sqlalchemy_exasol-5.1.0.tar.gz
Algorithm Hash digest
SHA256 062d4f2571f43c5a342ca1b3f8a5a87c597848dd008fa04a65700af541d34307
MD5 951d190e39d38e2c42ee3d4c595c88e4
BLAKE2b-256 4f5da1cf2b94dc4914285405735aca1b4c477c5b3b97ac009af0950764a0ac68

See more details on using hashes here.

File details

Details for the file sqlalchemy_exasol-5.1.0-py3-none-any.whl.

File metadata

  • Download URL: sqlalchemy_exasol-5.1.0-py3-none-any.whl
  • Upload date:
  • Size: 25.7 kB
  • Tags: Python 3
  • Uploaded using Trusted Publishing? No
  • Uploaded via: poetry/2.0.1 CPython/3.12.3 Linux/6.8.0-1021-azure

File hashes

Hashes for sqlalchemy_exasol-5.1.0-py3-none-any.whl
Algorithm Hash digest
SHA256 3d4dae0616f19b6b0d5dee428e5081058520c4502776e50a32000ad85c47acb2
MD5 f3d4907cadca0ba972e8c014a4908bd2
BLAKE2b-256 9c937433f8d0119fb2534c272ea22ba31806357237fa8cbc1b29e5e590290fc6

See more details on using hashes here.

Supported by

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