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
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.
.. warning::
The maintenance of Turbodbc support is currently paused, and it may be phased out in future versions.
We are also planning to phase out the pyodbc support in the future.
System Requirements
-------------------
- Python
- An Exasol DB (e.g. `docker-db <test_docker_image_>`_ or a `cloud instance <test_drive_>`_)
.. note::
For ODBC-Based Dialects, additional libraries required for ODBC are necessary
(for further details, checkout the `developer guide`_).
Setting Up Your Python Project
------------------------------
Install SQLAlchemy-Exasol:
.. code-block:: shell
$ pip install sqlalchemy-exasol
.. note::
To use an ODBC-based dialect, you must specify it as an extra during installation.
.. code-block:: shell
pip install "sqlalchemy-exasol[pydobc]"
pip install "sqlalchemy-exasol[turbodbc]"
Using SQLAlchemy with EXASOL DB
-------------------------------
**Websocket based Dialect:**
.. 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()
Examples:
.. code-block:: python
from sqlalchemy import create_engine
engine = create_engine("exa+websocket://sys:exasol@127.0.0.1:8888")
with engine.connect() as con:
...
.. code-block:: python
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):**
.. 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()
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.
.. _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
Known Issues
------------
* Insert
- Insert multiple empty rows via prepared statements does not work in all cases
Development & Testing
---------------------
See `developer guide`_
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/47/e2/4b4c592f8269cbc572197721188e30376f8df17cc8f9ff53a00802ebdcc4/sqlalchemy_exasol-5.0.0.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\nGetting Started with SQLAlchemy-Exasol\n--------------------------------------\nSQLAlchemy-Exasol supports multiple dialects, primarily differentiated by whether they are ODBC or Websocket based.\n\nChoosing a Dialect\n++++++++++++++++++\n\nWe 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.\n\n.. warning::\n\n The maintenance of Turbodbc support is currently paused, and it may be phased out in future versions.\n We are also planning to phase out the pyodbc support in the future.\n\n\n\nSystem Requirements\n-------------------\n- Python\n- An Exasol DB (e.g. `docker-db <test_docker_image_>`_ or a `cloud instance <test_drive_>`_)\n\n.. note::\n\n For ODBC-Based Dialects, additional libraries required for ODBC are necessary\n (for further details, checkout the `developer guide`_).\n\nSetting Up Your Python Project\n------------------------------\n\nInstall SQLAlchemy-Exasol:\n\n.. code-block:: shell\n\n $ pip install sqlalchemy-exasol\n\n.. note::\n\n To use an ODBC-based dialect, you must specify it as an extra during installation.\n\n .. code-block:: shell\n\n pip install \"sqlalchemy-exasol[pydobc]\"\n pip install \"sqlalchemy-exasol[turbodbc]\"\n\n\nUsing SQLAlchemy with EXASOL DB\n-------------------------------\n\n**Websocket based Dialect:**\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\nExamples:\n\n.. code-block:: python\n\n from sqlalchemy 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 sqlalchemy 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\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\nFeatures\n--------\n\n- SELECT, INSERT, UPDATE, DELETE statements\n\nGeneral Notes\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\nKnown Issues\n------------\n* Insert\n - Insert multiple empty rows via prepared statements does not work in all cases\n\nDevelopment & Testing\n---------------------\nSee `developer guide`_\n\n\n",
"bugtrack_url": null,
"license": "BSD",
"summary": "EXASOL dialect for SQLAlchemy",
"version": "5.0.0",
"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": "aa3d9a443f967cadac4398c5947b064685a1813a3f94ad5d64312d7ce55678f3",
"md5": "9617375c967cbff8668e7d1caf64d159",
"sha256": "60345d8e0a0f7d68977376b59892142aa99149d635e3f044926ed12942c41060"
},
"downloads": -1,
"filename": "sqlalchemy_exasol-5.0.0-py3-none-any.whl",
"has_sig": false,
"md5_digest": "9617375c967cbff8668e7d1caf64d159",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.8,<4.0",
"size": 46246,
"upload_time": "2024-02-02T08:21:39",
"upload_time_iso_8601": "2024-02-02T08:21:39.108272Z",
"url": "https://files.pythonhosted.org/packages/aa/3d/9a443f967cadac4398c5947b064685a1813a3f94ad5d64312d7ce55678f3/sqlalchemy_exasol-5.0.0-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "47e24b4c592f8269cbc572197721188e30376f8df17cc8f9ff53a00802ebdcc4",
"md5": "10b52e208b46af07f2e11631fbcd303e",
"sha256": "901ff0a7990238c25791f6b9116b6b999661c2ecc16f59245d51358ee8b7bc12"
},
"downloads": -1,
"filename": "sqlalchemy_exasol-5.0.0.tar.gz",
"has_sig": false,
"md5_digest": "10b52e208b46af07f2e11631fbcd303e",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.8,<4.0",
"size": 37857,
"upload_time": "2024-02-02T08:21:40",
"upload_time_iso_8601": "2024-02-02T08:21:40.748934Z",
"url": "https://files.pythonhosted.org/packages/47/e2/4b4c592f8269cbc572197721188e30376f8df17cc8f9ff53a00802ebdcc4/sqlalchemy_exasol-5.0.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-02-02 08:21:40",
"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
