SQLAlchemy Dialect for EXASOL DB
================================
.. image:: https://github.com/exasol/sqlalchemy-exasol/actions/workflows/ci-cd.yml/badge.svg?branch=master&event=push
:target: https://github.com/exasol/sqlalchemy-exasol/actions/workflows/ci-cd.yml
:alt: CI Status
.. image:: https://img.shields.io/pypi/v/sqlalchemy_exasol
:target: https://pypi.org/project/sqlalchemy-exasol/
:alt: PyPI Version
.. image:: https://img.shields.io/pypi/pyversions/sqlalchemy-exasol
:target: https://pypi.org/project/sqlalchemy-exasol
:alt: PyPI - Python Version
.. image:: https://img.shields.io/badge/exasol-7.1.9%20%7C%207.0.18-green
:target: https://www.exasol.com/
:alt: Exasol - Supported Version(s)
.. image:: https://img.shields.io/badge/code%20style-black-000000.svg
:target: https://github.com/psf/black
:alt: Formatter - Black
.. image:: https://img.shields.io/badge/imports-isort-ef8336.svg
:target: https://pycqa.github.io/isort/
:alt: Formatter - Isort
.. image:: https://img.shields.io/badge/pylint-6.4-yellowgreen
:target: https://github.com/PyCQA/pylint
:alt: Pylint
.. image:: https://img.shields.io/pypi/l/sqlalchemy-exasol
:target: https://opensource.org/licenses/BSD-2-Clause
:alt: License
.. image:: https://img.shields.io/github/last-commit/exasol/sqlalchemy-exasol
:target: https://pypi.org/project/sqlalchemy-exasol/
:alt: Last Commit
.. image:: https://img.shields.io/pypi/dm/sqlalchemy-exasol
:target: https://pypi.org/project/sqlalchemy-exasol
:alt: PyPI - Downloads
How to get started
------------------
Currently, sqlalchemy-exasol supports multiple dialects. The core difference
being if the dialect is :code:`odbc` or :code:`websocket` based.
Generally, we advise to use the websocket based Dialect, because odbc
based dialects require a good understanding of (unix)ODBC and the setup is
significant more complicated.
Turbodbc support
````````````````
.. warning::
Maintenance of this feature is on hold. Also it is very likely that turbodbc support will be dropped in future versions.
- 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 <test/test_update.py>`_ for an example
Meet the system requirements
````````````````````````````
- Python
- An Exasol DB (e.g. `docker-db <test_docker_image_>`_ or a `cloud instance <test_drive_>`_)
ODBC-based dialects additionally require the following to be available and set up:
- The packages unixODBC and unixODBC-dev >= 2.2.14
- The Exasol `ODBC driver <odbc_driver_>`_
- The ODBC.ini and ODBCINST.ini configurations files setup
Setup your python project and install sqlalchemy-exasol
```````````````````````````````````````````````````````
.. code-block:: shell
$ pip install sqlalchemy-exasol
for turbodbc support:
.. code-block:: shell
$ pip install sqlalchemy-exasol[turbodbc]
Talk to the EXASOL DB using SQLAlchemy
``````````````````````````````````````
**Websocket based Dialect:**
For more details regarding the websocket support checkout the section: "What is Websocket support?"
.. code-block:: python
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()
**Pyodbc (ODBC based Dialect):**
.. code-block:: python
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):**
.. code-block:: python
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:
.. list-table::
* - 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.
.. _developer guide: https://github.com/exasol/sqlalchemy-exasol/blob/master/doc/developer_guide/developer_guide.rst
.. _odbc_driver: https://docs.exasol.com/db/latest/connect_exasol/drivers/odbc/odbc_linux.htm
.. _test_drive: https://www.exasol.com/test-it-now/cloud/
.. _test_docker_image: https://github.com/exasol/docker-db
Development & Testing
`````````````````````
See `developer guide`_
What is Websocket support?
``````````````````````````
In the context of SQLA and Exasol, Websocket support means that an SQLA dialect
supporting the `Exasol Websocket Protocol <https://github.com/exasol/websocket-api>`_
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 <https://github.com/exasol/websocket-api#why-a-websockets-api>`_.
Example Usage(s)
++++++++++++++++++
.. code-block:: python
from sqla import create_engine
engine = create_engine("exa+websocket://sys:exasol@127.0.0.1:8888")
with engine.connect() as con:
...
.. code-block:: python
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
+++++++++++++++++++++++++++++++
.. list-table::
* - 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
++++++++++++
* Insert
- Insert multiple empty rows via prepared statements does not work in all cases
Raw data
{
"_id": null,
"home_page": "",
"name": "sqlalchemy-exasol",
"maintainer": "",
"docs_url": null,
"requires_python": ">=3.8,<4.0",
"maintainer_email": "",
"keywords": "exasol,sql,sqlalchemy,data science,database",
"author": "Exasol AG",
"author_email": "opensource@exasol.com",
"download_url": "https://files.pythonhosted.org/packages/fd/95/a80aa2872dcef82987659cf39bfcab185197760cb0499f48bf917e7d2a5b/sqlalchemy_exasol-4.6.1.tar.gz",
"platform": null,
"description": "SQLAlchemy Dialect for EXASOL DB\n================================\n\n\n.. image:: https://github.com/exasol/sqlalchemy-exasol/actions/workflows/ci-cd.yml/badge.svg?branch=master&event=push\n :target: https://github.com/exasol/sqlalchemy-exasol/actions/workflows/ci-cd.yml\n :alt: CI Status\n\n.. image:: https://img.shields.io/pypi/v/sqlalchemy_exasol\n :target: https://pypi.org/project/sqlalchemy-exasol/\n :alt: PyPI Version\n\n.. image:: https://img.shields.io/pypi/pyversions/sqlalchemy-exasol\n :target: https://pypi.org/project/sqlalchemy-exasol\n :alt: PyPI - Python Version\n\n.. image:: https://img.shields.io/badge/exasol-7.1.9%20%7C%207.0.18-green\n :target: https://www.exasol.com/\n :alt: Exasol - Supported Version(s)\n\n.. image:: https://img.shields.io/badge/code%20style-black-000000.svg\n :target: https://github.com/psf/black\n :alt: Formatter - Black\n\n.. image:: https://img.shields.io/badge/imports-isort-ef8336.svg\n :target: https://pycqa.github.io/isort/\n :alt: Formatter - Isort\n\n.. image:: https://img.shields.io/badge/pylint-6.4-yellowgreen\n :target: https://github.com/PyCQA/pylint\n :alt: Pylint\n\n.. image:: https://img.shields.io/pypi/l/sqlalchemy-exasol\n :target: https://opensource.org/licenses/BSD-2-Clause\n :alt: License\n\n.. image:: https://img.shields.io/github/last-commit/exasol/sqlalchemy-exasol\n :target: https://pypi.org/project/sqlalchemy-exasol/\n :alt: Last Commit\n\n.. image:: https://img.shields.io/pypi/dm/sqlalchemy-exasol\n :target: https://pypi.org/project/sqlalchemy-exasol\n :alt: PyPI - Downloads\n\n\nHow to get started\n------------------\n\nCurrently, sqlalchemy-exasol supports multiple dialects. The core difference\nbeing if the dialect is :code:`odbc` or :code:`websocket` based.\n\nGenerally, we advise to use the websocket based Dialect, because odbc\nbased dialects require a good understanding of (unix)ODBC and the setup is\nsignificant more complicated.\n\n\nTurbodbc support\n````````````````\n\n.. warning::\n\n Maintenance of this feature is on hold. Also it is very likely that turbodbc support will be dropped in future versions.\n\n- You can use Turbodbc with sqlalchemy_exasol if you use a python version >= 3.8.\n- Multi row update is not supported, see\n `test/test_update.py <test/test_update.py>`_ for an example\n\n\nMeet the system requirements\n````````````````````````````\n- Python\n- An Exasol DB (e.g. `docker-db <test_docker_image_>`_ or a `cloud instance <test_drive_>`_)\n\nODBC-based dialects additionally require the following to be available and set up:\n\n- The packages unixODBC and unixODBC-dev >= 2.2.14\n- The Exasol `ODBC driver <odbc_driver_>`_\n- The ODBC.ini and ODBCINST.ini configurations files setup\n\n\nSetup your python project and install sqlalchemy-exasol\n```````````````````````````````````````````````````````\n\n.. code-block:: shell\n\n $ pip install sqlalchemy-exasol\n\nfor turbodbc support:\n\n.. code-block:: shell\n\n $ pip install sqlalchemy-exasol[turbodbc]\n\nTalk to the EXASOL DB using SQLAlchemy\n``````````````````````````````````````\n\n**Websocket based Dialect:**\n\nFor more details regarding the websocket support checkout the section: \"What is Websocket support?\"\n\n.. code-block:: python\n\n\tfrom sqlalchemy import create_engine\n\turl = \"exa+websocket://A_USER:A_PASSWORD@192.168.1.2..8:1234/my_schema?CONNECTIONLCALL=en_US.UTF-8\"\n\te = create_engine(url)\n\tr = e.execute(\"select 42 from dual\").fetchall()\n\n\n**Pyodbc (ODBC based Dialect):**\n\n.. code-block:: python\n\n\tfrom sqlalchemy import create_engine\n\turl = \"exa+pyodbc://A_USER:A_PASSWORD@192.168.1.2..8:1234/my_schema?CONNECTIONLCALL=en_US.UTF-8&driver=EXAODBC\"\n\te = create_engine(url)\n\tr = e.execute(\"select 42 from dual\").fetchall()\n\n**Turbodbc (ODBC based Dialect):**\n\n.. code-block:: python\n\n\tfrom sqlalchemy import create_engine\n\turl = \"exa+turbodbc://A_USER:A_PASSWORD@192.168.1.2..8:1234/my_schema?CONNECTIONLCALL=en_US.UTF-8&driver=EXAODBC\"\n\te = create_engine(url)\n\tr = e.execute(\"select 42 from dual\").fetchall()\n\n\nThe dialect supports two types of connection urls creating an engine. A DSN (Data Source Name) mode and a host mode:\n\n.. list-table::\n\n * - Type\n - Example\n * - DSN URL\n - 'exa+pyodbc://USER:PWD@exa_test'\n * - HOST URL\n - 'exa+pyodbc://USER:PWD@192.168.14.227..228:1234/my_schema?parameter'\n\nFeatures\n++++++++\n\n- SELECT, INSERT, UPDATE, DELETE statements\n\nNotes\n+++++\n\n- Schema name and parameters are optional for the host url\n- 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.\n- 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)\n- 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.\n\n.. _developer guide: https://github.com/exasol/sqlalchemy-exasol/blob/master/doc/developer_guide/developer_guide.rst\n.. _odbc_driver: https://docs.exasol.com/db/latest/connect_exasol/drivers/odbc/odbc_linux.htm\n.. _test_drive: https://www.exasol.com/test-it-now/cloud/\n.. _test_docker_image: https://github.com/exasol/docker-db\n\nDevelopment & Testing\n`````````````````````\nSee `developer guide`_\n\nWhat is Websocket support?\n``````````````````````````\nIn the context of SQLA and Exasol, Websocket support means that an SQLA dialect\nsupporting the `Exasol Websocket Protocol <https://github.com/exasol/websocket-api>`_\nis provided.\n\nUsing the websocket based protocol instead over ODBC will provide various advantages:\n\n* Less System Dependencies\n* Easier to use than ODBC based driver(s)\n* Lock free metadata calls etc.\n\nFor further details `Why a Websockets API <https://github.com/exasol/websocket-api#why-a-websockets-api>`_.\n\nExample Usage(s)\n++++++++++++++++++\n\n.. code-block:: python\n\n from sqla import create_engine\n\n engine = create_engine(\"exa+websocket://sys:exasol@127.0.0.1:8888\")\n with engine.connect() as con:\n ...\n\n.. code-block:: python\n\n from sqla import create_engine\n\n # ATTENTION:\n # In terms of security it is NEVER a good idea to turn of certificate validation!!\n # In rare cases it may be handy for non-security related reasons.\n # That said, if you are not a 100% sure about your scenario, stick with the\n # secure defaults.\n # In most cases, having a valid certificate and/or configuring the truststore(s)\n # appropriately is the best/correct solution.\n engine = create_engine(\"exa+websocket://sys:exasol@127.0.0.1:8888?SSLCertificate=SSL_VERIFY_NONE\")\n with engine.connect() as con:\n ...\n\nSupported Connection Parameters\n+++++++++++++++++++++++++++++++\n.. list-table::\n\n * - Parameter\n - Values\n - Comment\n * - ENCRYPTION\n - Y, Yes, N, No\n - Y or Yes Enable Encryption (TLS) default, N or No disable Encryption\n * - SSLCertificate\n - SSL_VERIFY_NONE\n - Disable certificate validation\n\n\nKnown Issues\n++++++++++++\n* Insert\n - Insert multiple empty rows via prepared statements does not work in all cases\n",
"bugtrack_url": null,
"license": "BSD",
"summary": "EXASOL dialect for SQLAlchemy",
"version": "4.6.1",
"project_urls": {
"Changelog": "https://exasol.github.io/sqlalchemy-exasol/changelog.html",
"Documentation": "https://exasol.github.io/sqlalchemy-exasol/",
"Homepage": "https://www.exasol.com/",
"Issues": "https://github.com/exasol/sqlalchemy-exasol/issues",
"Source": "https://github.com/exasol/sqlalchemy-exasol"
},
"split_keywords": [
"exasol",
"sql",
"sqlalchemy",
"data science",
"database"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "d1bd18c0f6c41644225c8c2247ed4ec88ab3fbfbec8195a0b2c11cd1444270e8",
"md5": "b24836f64c272f15da020c3e0fb82ef6",
"sha256": "8db8ce933fc8d02acee3c696dc2f98b3799b32336da0000f8fbb2ca9299faef5"
},
"downloads": -1,
"filename": "sqlalchemy_exasol-4.6.1-py3-none-any.whl",
"has_sig": false,
"md5_digest": "b24836f64c272f15da020c3e0fb82ef6",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.8,<4.0",
"size": 46458,
"upload_time": "2023-11-06T07:50:30",
"upload_time_iso_8601": "2023-11-06T07:50:30.824811Z",
"url": "https://files.pythonhosted.org/packages/d1/bd/18c0f6c41644225c8c2247ed4ec88ab3fbfbec8195a0b2c11cd1444270e8/sqlalchemy_exasol-4.6.1-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "fd95a80aa2872dcef82987659cf39bfcab185197760cb0499f48bf917e7d2a5b",
"md5": "503619491fe28e6c79237bde75f4caf5",
"sha256": "5ca883b9d7208715fb889fac0f8d43dcb1b4101daaf9e52b6cf38c7acbe02016"
},
"downloads": -1,
"filename": "sqlalchemy_exasol-4.6.1.tar.gz",
"has_sig": false,
"md5_digest": "503619491fe28e6c79237bde75f4caf5",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.8,<4.0",
"size": 39375,
"upload_time": "2023-11-06T07:50:32",
"upload_time_iso_8601": "2023-11-06T07:50:32.637937Z",
"url": "https://files.pythonhosted.org/packages/fd/95/a80aa2872dcef82987659cf39bfcab185197760cb0499f48bf917e7d2a5b/sqlalchemy_exasol-4.6.1.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-11-06 07:50:32",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "exasol",
"github_project": "sqlalchemy-exasol",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "sqlalchemy-exasol"
}
JSON
