Actian Data Platform, 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. Recent work with:
* SQLAlchemy 1.4.51 and 2.0.27
* Actian Data Platform, Ingres 11.x, Vector 5.x, 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.27'
python -m pip install sqlalchemy==1.4.51
```
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
Doc ref: [pip install](https://pip.pypa.io/en/stable/cli/pip_install/)
### Install from PyPi, along with required packages
python -m pip install "sqlalchemy-ingres[all]"
### Install latest from GitHub, without a source code checkout
python -m pip install -e git+https://github.com/ActianCorp/sqlalchemy-ingres.git#egg=sqlalchemy-ingres
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/44/93/e731b616650841ad32f4acc26ded9a6730c5710f28c8a3fee00ea77efc9f/sqlalchemy-ingres-0.0.6.tar.gz",
"platform": "any",
"description": "Actian Data Platform, 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. Recent work with:\r\n\r\n * SQLAlchemy 1.4.51 and 2.0.27\r\n * Actian Data Platform, Ingres 11.x, Vector 5.x, 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.27'\r\n python -m pip install sqlalchemy==1.4.51\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\nDoc ref: [pip install](https://pip.pypa.io/en/stable/cli/pip_install/)\r\n\r\n### Install from PyPi, along with required packages\r\n\r\n\r\n python -m pip install \"sqlalchemy-ingres[all]\"\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#egg=sqlalchemy-ingres\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.6",
"project_urls": {
"Homepage": "https://github.com/ActianCorp/sqlalchemy-ingres"
},
"split_keywords": [],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "4493e731b616650841ad32f4acc26ded9a6730c5710f28c8a3fee00ea77efc9f",
"md5": "3e31200a7cc07447846fbe3aa45bbd8b",
"sha256": "b076744070801393b6973f6e53a7f84a89e37183714a86f570ce17f7bce7f034"
},
"downloads": -1,
"filename": "sqlalchemy-ingres-0.0.6.tar.gz",
"has_sig": false,
"md5_digest": "3e31200a7cc07447846fbe3aa45bbd8b",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 19322,
"upload_time": "2024-03-11T18:28:09",
"upload_time_iso_8601": "2024-03-11T18:28:09.891857Z",
"url": "https://files.pythonhosted.org/packages/44/93/e731b616650841ad32f4acc26ded9a6730c5710f28c8a3fee00ea77efc9f/sqlalchemy-ingres-0.0.6.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-03-11 18:28:09",
"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"
}
JSON
