sqlalchemy-ingres


Namesqlalchemy-ingres JSON
Version 0.0.4 PyPI version JSON
download
home_pagehttps://github.com/ActianCorp/sqlalchemy-ingres
SummarySQLAlchemy dialect for Actian databases; Actian Data Platform (nee Avalanche), Actian X, Ingres, and Vector
upload_time2024-02-12 19:38:40
maintainerMichael Habiger
docs_urlNone
authorChris Clark
requires_python
licenseApache-2.0
keywords
VCS
bugtrack_url
requirements No requirements were recorded.
Travis-CI No Travis.
coveralls test coverage No coveralls.
            Avalanche, Actian X, Ingres, and Vector dialect for [SQLAlchemy](https://github.com/sqlalchemy/sqlalchemy) CPython 3.x (and 2.7).

For more information about SQLAlchemy see:

  * https://github.com/sqlalchemy/sqlalchemy
  * https://www.sqlalchemy.org/
  * https://pypi.org/project/SQLAlchemy/

Originally developed to work against SQLAlchemy 0.6 and Ingres 9.2. Current work-in-progress with:

  * SQLAlchemy 1.3.16 and 1.4.36
  * Avalanche, Ingres 11.x, Vector 5.1, and Vector 6.x - via ODBC

The current Ingres dialect code is built to work with SQLAlchemy version `1.x` and will produce runtime errors if attempting to use it with the forth-coming `2.x` version of SQLAlchemy (for more information see [issue 5](https://github.com/ActianCorp/sqlalchemy-ingres/issues/5)).

It is important to be aware of which version of SQLAlchemy is installed. Version pinning provides the ability to install the desired version explicitly.
Version pinning examples:
```
    python -m pip install 'sqlalchemy < 2.0.0'
    python -m pip install sqlalchemy==1.4.42
 ```
 
Jython/JDBC support is currently untested, as the current code relies on zxjdbc it is not recommended this be used (see https://hg.sr.ht/~clach04/jyjdbc for as an alternative that includes full Decimal datatype support).

Known to work with:

  * https://github.com/cloudera/hue
  * https://github.com/apache/superset (see https://github.com/clach04/incubator-superset/tree/vector)
  * https://github.com/catherinedevlin/ipython-sql / Jupyter/IPython notebooks (see https://github.com/catherinedevlin/ipython-sql/pull/196 - or use `%config SqlMagic.autocommit=False`
      * Until ipython-sql 0.4.1 is released, to avoid workaround issue; `pip install git+https://github.com/catherinedevlin/ipython-sql.git`
  * https://github.com/wireservice/csvkit (see https://github.com/wireservice/agate-sql/pull/36)

--------------------------------------------------------

- [Quickstart](#quickstart)
  * [Install from PyPi](#install-from-pypi)
  * [Install latest from GitHub, without a source code checkout](#install-latest-from-github--without-a-source-code-checkout)
  * [Install latest from GitHub, with a source code checkout](#install-latest-from-github--with-a-source-code-checkout)
- [Development instructions](#development-instructions)
  * [Quick python test](#quick-python-test)
  * [Troubleshooting](#troubleshooting)
  * [Running SA test suite](#running-sa-test-suite)

--------------------------------------------------------

## Quickstart

TL;DR

### Install from PyPi


    python -m pip install pyodbc sqlalchemy-ingres

### Install latest from GitHub, without a source code checkout

    python -m pip install -e git+https://github.com/ActianCorp/sqlalchemy-ingres.git

Alternatively, for a `named_branch`:

    python -m pip install -e git+https://github.com/ActianCorp/sqlalchemy-ingres.git@named_branch#egg=sqlalchemy-ingres

### Install latest from GitHub, with a source code checkout

Install/setup:

    python -m pip install pyodbc sqlalchemy
    git clone https://github.com/ActianCorp/sqlalchemy-ingres.git
    cd sqlalchemy-ingres
    python -m pip install -e .


Demo Sample / Sanity check:

Linux/Unix

    export SQLALCHEMY_INGRES_ODBC_DRIVER_NAME=INGRES Y1
    # or what ever the ODBC Driver name is; Actian, Ingres, etc.
    # Only needed if program/environment is unable to identify the correct driver.

Windows:

    SET SQLALCHEMY_INGRES_ODBC_DRIVER_NAME=INGRES Y1
    REM or what ever the ODBC Driver name is; Actian, Ingres, etc.
    REM Only needed if program/environment is unable to identify the correct driver.

NOTE ODBC Driver should be the same bitness as the Python interpreter. That is:

  * for 64-bit Python ensure a 64-bit ODBC driver is available.
  * For 32-bit Python ensure a 32-bit ODBC driver is available.

Assuming local DBMS, python session:

    Python 3.7.3 (v3.7.3:ef4ec6ed12, Mar 25 2019, 22:22:05) [MSC v.1916 64 bit (AMD64)] on win32
    Type "help", "copyright", "credits" or "license" for more information.
    >>> import sqlalchemy
    >>> engine = sqlalchemy.create_engine('ingres:///iidbdb')  # local DBMS
    >>> connection = engine.connect()
    >>> for row in connection.execute(sqlalchemy.text('SELECT count(*) FROM iidatabase')):
    ...     print(row)
    ...
    (33,)


## Development instructions

Right now this is for dev purposes so install SQLAlchemy as per normal, for example:

    python -m pip install sqlalchemy

or for dev testing and modifying/running tests:

    git clone -b rel_1_4_42 https://github.com/sqlalchemy/sqlalchemy.git
    cd sqlalchemy
    #pip install -e .
    python -m pip install -e .   # https://adamj.eu/tech/2020/02/25/use-python-m-pip-everywhere/

Ingres dialect tested with pyodbc and pypyodbc (pypyodbc useful for debugging, to see what SQLAlchemy is calling with):

    pip install pyodbc

Download Ingres dialect for SQLAlchemy:

    git clone https://github.com/ActianCorp/sqlalchemy-ingres.git

Setup for dev use:

    cd sqlalchemy-ingres
    python -m pip install -e .

Demo/Test:

Ensure Ingres ODBC driver is available, Ingres sqlalchemy defaults to using "Ingres" ODBC driver, ensure this is the same bit-age as the Python interpreter. For example, for 64-bit Python, ensure ODBC Driver called "Ingres" is also 64-bit.

ODBC Driver name can be overridden via environment variable `SQLALCHEMY_INGRES_ODBC_DRIVER_NAME`, for example:

Windows 64-bit:

    set SQLALCHEMY_INGRES_ODBC_DRIVER_NAME=Ingres CS
    REM Only needed if program/environment is unable to identify the correct driver.

Windows 32-bit:

    set SQLALCHEMY_INGRES_ODBC_DRIVER_NAME=Ingres CR
    REM Only needed if program/environment is unable to identify the correct driver.

Under Linux/Unix check ODBC settings and if using UnixODBC, check how wide-char support was built, recommendation for out-of-box Linux distributions:

```shell
# Most Linux distros build UnixODBC with non-default build options
export II_ODBC_WCHAR_SIZE=2

# set variables for ODBC config
export ODBCSYSINI=$II_SYSTEM/ingres/files
export ODBCINI=$II_SYSTEM/ingres/files/odbc.ini
```

### Quick python test

```python
import sys
import sqlalchemy
#import sqlalchemy-ingres

print('Python %s on %s' % (sys.version, sys.platform))
print('SQLAlchemy %r' % sqlalchemy.__version__)
con_str = 'ingres:///demodb'  # local demodb
#con_str = 'ingres://dbuser:PASSWORD@HOSTNAME:27832/db'  # remote database called "db"
print(con_str)
# If the next line is uncommented, need to also uncomment: import sqlalchemy-ingres
#print(sqlalchemy-ingres.base.dialect().create_connect_args(url=sqlalchemy.engine.make_url(con_str)))

engine = sqlalchemy.create_engine(con_str)
connection = engine.connect()
query = 'SELECT * FROM iidbconstants'
for row in connection.execute(sqlalchemy.text(query)):
    print(row)
```

### Troubleshooting

Getting error:

    sqlalchemy.exc.DatabaseError: (pypyodbc.DatabaseError) ('08004', '[08004] [Actian][Ingres ODBC Driver][INGRES]Requested association partner is unavailable')

1. DBMS may not be running or accesible (e.g. network error).
2. Could be using a Driver name that is not available (or the wrong number of bits, e.g. 32-bit versus 64-bit or vice-versa), or wrong environment (e.g. multiple DBMS/client installations). Solution, make use of environment variable `SQLALCHEMY_INGRES_ODBC_DRIVER_NAME` either in the environment or in Python code, e.g:

    ```python
    import os; os.environ['SQLALCHEMY_INGRES_ODBC_DRIVER_NAME'] = 'Ingres X2'  # Etc. where X2 is the installation id (output from, "ingprenv II_INSTALLATION")
    ```

### Running SA test suite

NOTE below is for Python 2.7 and 3.4, can remove version pin for current python. Mock appears to be a dependency that is not pulled in for py2.7

    python -m pip  install --upgrade pip
    python -m pip  install tox "pytest==4.6"
    python -m pip  install mock

Setup test config

    $ cat test.cfg
    # test.cfg file
    # see README.unittests.rst
    #       pytest --db sqlite_file
    [db]
    sqlite_file=sqlite:///querytest.sqlite3

    # local
    ingres_odbc=ingres:///sa

Code change needed to SA:

    diff --git a/test/requirements.py b/test/requirements.py
    index cf9168f5a..fcc4f37a0 100644
    --- a/test/requirements.py
    +++ b/test/requirements.py
    @@ -394,6 +394,9 @@ class DefaultRequirements(SuiteRequirements):
             elif against(config, "oracle"):
                 default = "READ COMMITTED"
                 levels.add("AUTOCOMMIT")
    +        elif against(config, "ingres"):
    +            default = "READ COMMITTED"
    +            levels.add("AUTOCOMMIT")  # probably needed, not sure what this is though - assuming tests are not commiting and expecting autocommit semantics
             else:
                 raise NotImplementedError()

Run (all) tests:

    pytest --db ingres_odbc --junit-xml=all_results_junit.xml --maxfail=12000

            

Raw data

            {
    "_id": null,
    "home_page": "https://github.com/ActianCorp/sqlalchemy-ingres",
    "name": "sqlalchemy-ingres",
    "maintainer": "Michael Habiger",
    "docs_url": null,
    "requires_python": "",
    "maintainer_email": "michael.habiger@hcl-software.com",
    "keywords": "",
    "author": "Chris Clark",
    "author_email": "Chris.Clark@actian.com",
    "download_url": "https://files.pythonhosted.org/packages/56/ee/87136ee96c6de693443a4a28e26998dd64361b6a2520377d6c7dea07bd41/sqlalchemy-ingres-0.0.4.tar.gz",
    "platform": "any",
    "description": "Avalanche, Actian X, Ingres, and Vector dialect for [SQLAlchemy](https://github.com/sqlalchemy/sqlalchemy) CPython 3.x (and 2.7).\r\n\r\nFor more information about SQLAlchemy see:\r\n\r\n  * https://github.com/sqlalchemy/sqlalchemy\r\n  * https://www.sqlalchemy.org/\r\n  * https://pypi.org/project/SQLAlchemy/\r\n\r\nOriginally developed to work against SQLAlchemy 0.6 and Ingres 9.2. Current work-in-progress with:\r\n\r\n  * SQLAlchemy 1.3.16 and 1.4.36\r\n  * Avalanche, Ingres 11.x, Vector 5.1, and Vector 6.x - via ODBC\r\n\r\nThe current Ingres dialect code is built to work with SQLAlchemy version `1.x` and will produce runtime errors if attempting to use it with the forth-coming `2.x` version of SQLAlchemy (for more information see [issue 5](https://github.com/ActianCorp/sqlalchemy-ingres/issues/5)).\r\n\r\nIt is important to be aware of which version of SQLAlchemy is installed. Version pinning provides the ability to install the desired version explicitly.\r\nVersion pinning examples:\r\n```\r\n    python -m pip install 'sqlalchemy < 2.0.0'\r\n    python -m pip install sqlalchemy==1.4.42\r\n ```\r\n \r\nJython/JDBC support is currently untested, as the current code relies on zxjdbc it is not recommended this be used (see https://hg.sr.ht/~clach04/jyjdbc for as an alternative that includes full Decimal datatype support).\r\n\r\nKnown to work with:\r\n\r\n  * https://github.com/cloudera/hue\r\n  * https://github.com/apache/superset (see https://github.com/clach04/incubator-superset/tree/vector)\r\n  * https://github.com/catherinedevlin/ipython-sql / Jupyter/IPython notebooks (see https://github.com/catherinedevlin/ipython-sql/pull/196 - or use `%config SqlMagic.autocommit=False`\r\n      * Until ipython-sql 0.4.1 is released, to avoid workaround issue; `pip install git+https://github.com/catherinedevlin/ipython-sql.git`\r\n  * https://github.com/wireservice/csvkit (see https://github.com/wireservice/agate-sql/pull/36)\r\n\r\n--------------------------------------------------------\r\n\r\n- [Quickstart](#quickstart)\r\n  * [Install from PyPi](#install-from-pypi)\r\n  * [Install latest from GitHub, without a source code checkout](#install-latest-from-github--without-a-source-code-checkout)\r\n  * [Install latest from GitHub, with a source code checkout](#install-latest-from-github--with-a-source-code-checkout)\r\n- [Development instructions](#development-instructions)\r\n  * [Quick python test](#quick-python-test)\r\n  * [Troubleshooting](#troubleshooting)\r\n  * [Running SA test suite](#running-sa-test-suite)\r\n\r\n--------------------------------------------------------\r\n\r\n## Quickstart\r\n\r\nTL;DR\r\n\r\n### Install from PyPi\r\n\r\n\r\n    python -m pip install pyodbc sqlalchemy-ingres\r\n\r\n### Install latest from GitHub, without a source code checkout\r\n\r\n    python -m pip install -e git+https://github.com/ActianCorp/sqlalchemy-ingres.git\r\n\r\nAlternatively, for a `named_branch`:\r\n\r\n    python -m pip install -e git+https://github.com/ActianCorp/sqlalchemy-ingres.git@named_branch#egg=sqlalchemy-ingres\r\n\r\n### Install latest from GitHub, with a source code checkout\r\n\r\nInstall/setup:\r\n\r\n    python -m pip install pyodbc sqlalchemy\r\n    git clone https://github.com/ActianCorp/sqlalchemy-ingres.git\r\n    cd sqlalchemy-ingres\r\n    python -m pip install -e .\r\n\r\n\r\nDemo Sample / Sanity check:\r\n\r\nLinux/Unix\r\n\r\n    export SQLALCHEMY_INGRES_ODBC_DRIVER_NAME=INGRES Y1\r\n    # or what ever the ODBC Driver name is; Actian, Ingres, etc.\r\n    # Only needed if program/environment is unable to identify the correct driver.\r\n\r\nWindows:\r\n\r\n    SET SQLALCHEMY_INGRES_ODBC_DRIVER_NAME=INGRES Y1\r\n    REM or what ever the ODBC Driver name is; Actian, Ingres, etc.\r\n    REM Only needed if program/environment is unable to identify the correct driver.\r\n\r\nNOTE ODBC Driver should be the same bitness as the Python interpreter. That is:\r\n\r\n  * for 64-bit Python ensure a 64-bit ODBC driver is available.\r\n  * For 32-bit Python ensure a 32-bit ODBC driver is available.\r\n\r\nAssuming local DBMS, python session:\r\n\r\n    Python 3.7.3 (v3.7.3:ef4ec6ed12, Mar 25 2019, 22:22:05) [MSC v.1916 64 bit (AMD64)] on win32\r\n    Type \"help\", \"copyright\", \"credits\" or \"license\" for more information.\r\n    >>> import sqlalchemy\r\n    >>> engine = sqlalchemy.create_engine('ingres:///iidbdb')  # local DBMS\r\n    >>> connection = engine.connect()\r\n    >>> for row in connection.execute(sqlalchemy.text('SELECT count(*) FROM iidatabase')):\r\n    ...     print(row)\r\n    ...\r\n    (33,)\r\n\r\n\r\n## Development instructions\r\n\r\nRight now this is for dev purposes so install SQLAlchemy as per normal, for example:\r\n\r\n    python -m pip install sqlalchemy\r\n\r\nor for dev testing and modifying/running tests:\r\n\r\n    git clone -b rel_1_4_42 https://github.com/sqlalchemy/sqlalchemy.git\r\n    cd sqlalchemy\r\n    #pip install -e .\r\n    python -m pip install -e .   # https://adamj.eu/tech/2020/02/25/use-python-m-pip-everywhere/\r\n\r\nIngres dialect tested with pyodbc and pypyodbc (pypyodbc useful for debugging, to see what SQLAlchemy is calling with):\r\n\r\n    pip install pyodbc\r\n\r\nDownload Ingres dialect for SQLAlchemy:\r\n\r\n    git clone https://github.com/ActianCorp/sqlalchemy-ingres.git\r\n\r\nSetup for dev use:\r\n\r\n    cd sqlalchemy-ingres\r\n    python -m pip install -e .\r\n\r\nDemo/Test:\r\n\r\nEnsure Ingres ODBC driver is available, Ingres sqlalchemy defaults to using \"Ingres\" ODBC driver, ensure this is the same bit-age as the Python interpreter. For example, for 64-bit Python, ensure ODBC Driver called \"Ingres\" is also 64-bit.\r\n\r\nODBC Driver name can be overridden via environment variable `SQLALCHEMY_INGRES_ODBC_DRIVER_NAME`, for example:\r\n\r\nWindows 64-bit:\r\n\r\n    set SQLALCHEMY_INGRES_ODBC_DRIVER_NAME=Ingres CS\r\n    REM Only needed if program/environment is unable to identify the correct driver.\r\n\r\nWindows 32-bit:\r\n\r\n    set SQLALCHEMY_INGRES_ODBC_DRIVER_NAME=Ingres CR\r\n    REM Only needed if program/environment is unable to identify the correct driver.\r\n\r\nUnder Linux/Unix check ODBC settings and if using UnixODBC, check how wide-char support was built, recommendation for out-of-box Linux distributions:\r\n\r\n```shell\r\n# Most Linux distros build UnixODBC with non-default build options\r\nexport II_ODBC_WCHAR_SIZE=2\r\n\r\n# set variables for ODBC config\r\nexport ODBCSYSINI=$II_SYSTEM/ingres/files\r\nexport ODBCINI=$II_SYSTEM/ingres/files/odbc.ini\r\n```\r\n\r\n### Quick python test\r\n\r\n```python\r\nimport sys\r\nimport sqlalchemy\r\n#import sqlalchemy-ingres\r\n\r\nprint('Python %s on %s' % (sys.version, sys.platform))\r\nprint('SQLAlchemy %r' % sqlalchemy.__version__)\r\ncon_str = 'ingres:///demodb'  # local demodb\r\n#con_str = 'ingres://dbuser:PASSWORD@HOSTNAME:27832/db'  # remote database called \"db\"\r\nprint(con_str)\r\n# If the next line is uncommented, need to also uncomment: import sqlalchemy-ingres\r\n#print(sqlalchemy-ingres.base.dialect().create_connect_args(url=sqlalchemy.engine.make_url(con_str)))\r\n\r\nengine = sqlalchemy.create_engine(con_str)\r\nconnection = engine.connect()\r\nquery = 'SELECT * FROM iidbconstants'\r\nfor row in connection.execute(sqlalchemy.text(query)):\r\n    print(row)\r\n```\r\n\r\n### Troubleshooting\r\n\r\nGetting error:\r\n\r\n    sqlalchemy.exc.DatabaseError: (pypyodbc.DatabaseError) ('08004', '[08004] [Actian][Ingres ODBC Driver][INGRES]Requested association partner is unavailable')\r\n\r\n1. DBMS may not be running or accesible (e.g. network error).\r\n2. Could be using a Driver name that is not available (or the wrong number of bits, e.g. 32-bit versus 64-bit or vice-versa), or wrong environment (e.g. multiple DBMS/client installations). Solution, make use of environment variable `SQLALCHEMY_INGRES_ODBC_DRIVER_NAME` either in the environment or in Python code, e.g:\r\n\r\n    ```python\r\n    import os; os.environ['SQLALCHEMY_INGRES_ODBC_DRIVER_NAME'] = 'Ingres X2'  # Etc. where X2 is the installation id (output from, \"ingprenv II_INSTALLATION\")\r\n    ```\r\n\r\n### Running SA test suite\r\n\r\nNOTE below is for Python 2.7 and 3.4, can remove version pin for current python. Mock appears to be a dependency that is not pulled in for py2.7\r\n\r\n    python -m pip  install --upgrade pip\r\n    python -m pip  install tox \"pytest==4.6\"\r\n    python -m pip  install mock\r\n\r\nSetup test config\r\n\r\n    $ cat test.cfg\r\n    # test.cfg file\r\n    # see README.unittests.rst\r\n    #       pytest --db sqlite_file\r\n    [db]\r\n    sqlite_file=sqlite:///querytest.sqlite3\r\n\r\n    # local\r\n    ingres_odbc=ingres:///sa\r\n\r\nCode change needed to SA:\r\n\r\n    diff --git a/test/requirements.py b/test/requirements.py\r\n    index cf9168f5a..fcc4f37a0 100644\r\n    --- a/test/requirements.py\r\n    +++ b/test/requirements.py\r\n    @@ -394,6 +394,9 @@ class DefaultRequirements(SuiteRequirements):\r\n             elif against(config, \"oracle\"):\r\n                 default = \"READ COMMITTED\"\r\n                 levels.add(\"AUTOCOMMIT\")\r\n    +        elif against(config, \"ingres\"):\r\n    +            default = \"READ COMMITTED\"\r\n    +            levels.add(\"AUTOCOMMIT\")  # probably needed, not sure what this is though - assuming tests are not commiting and expecting autocommit semantics\r\n             else:\r\n                 raise NotImplementedError()\r\n\r\nRun (all) tests:\r\n\r\n    pytest --db ingres_odbc --junit-xml=all_results_junit.xml --maxfail=12000\r\n",
    "bugtrack_url": null,
    "license": "Apache-2.0",
    "summary": "SQLAlchemy dialect for Actian databases; Actian Data Platform (nee Avalanche), Actian X, Ingres, and Vector",
    "version": "0.0.4",
    "project_urls": {
        "Homepage": "https://github.com/ActianCorp/sqlalchemy-ingres"
    },
    "split_keywords": [],
    "urls": [
        {
            "comment_text": "",
            "digests": {
                "blake2b_256": "56ee87136ee96c6de693443a4a28e26998dd64361b6a2520377d6c7dea07bd41",
                "md5": "f4189a82ca3476cb7f12d406bb2a1acf",
                "sha256": "3e366bc686cc3055c3c4a5798a56d37f1e3a35b1556a99d4ac43aef7757fc702"
            },
            "downloads": -1,
            "filename": "sqlalchemy-ingres-0.0.4.tar.gz",
            "has_sig": false,
            "md5_digest": "f4189a82ca3476cb7f12d406bb2a1acf",
            "packagetype": "sdist",
            "python_version": "source",
            "requires_python": null,
            "size": 19263,
            "upload_time": "2024-02-12T19:38:40",
            "upload_time_iso_8601": "2024-02-12T19:38:40.693797Z",
            "url": "https://files.pythonhosted.org/packages/56/ee/87136ee96c6de693443a4a28e26998dd64361b6a2520377d6c7dea07bd41/sqlalchemy-ingres-0.0.4.tar.gz",
            "yanked": false,
            "yanked_reason": null
        }
    ],
    "upload_time": "2024-02-12 19:38:40",
    "github": true,
    "gitlab": false,
    "bitbucket": false,
    "codeberg": false,
    "github_user": "ActianCorp",
    "github_project": "sqlalchemy-ingres",
    "travis_ci": false,
    "coveralls": false,
    "github_actions": false,
    "lcname": "sqlalchemy-ingres"
}
        
Elapsed time: 0.21777s