soundstripe / iaccess Goto Github PK

View Code? Open in Web Editor NEW

3.0 3.0 2.0 11.76 MB

SQLAlchemy support for IBM Db2 for i via i Access ODBC driver

Home Page: https://python-iaccess.readthedocs.io/en/latest/

License: MIT License

Makefile 6.45% Python 93.55%

sqlalchemy db2 db2-for-i ibmioss sqlalchemy-dialects

iaccess's Introduction

i Access Db2 SQLAlchemy Driver

Provides support for DB2 for iSeries for remote python clients using IBM i Access Client Solutions ODBC driver. On up-to-date systems (7.2+ with latest PTFs) you can also use this driver locally on your i Series machine, making local development of server code more feasible.

Other work in this area includes the ibm_db and ibm_db_sa projects, which also provide access to DB2 and support SQLAlchemy. Those two packages rely on and automatically install a binary driver for DB2, while this package relies on you already having install the i Access Client Solutions ODBC driver available on IBM's support/download site.

Free software: MIT license

Installation

Pre-requisites

UnixODBC (if on Linux or in PASE for i): Windows ships with ODBC installed, but on Linux you'll need unixodbc. On Ubuntu you can install this with apt-get install unixodbc. You may also need to apt-get install unixodbc-dev if you get errors about a missing sql.h file.
i Access Client Solutions ODBC driver: You'll need this driver available. There are repos available for major Linux distributions.
IBM i 7.2+ on your target system: Older versions of the OS may work but I have not tested against them. If you do not happen to have an IBM Power system sitting around your house (although who doesn't?!) you may be able to get an account to play with at Pub400.com.

Install via pip

Use pip to download and install the latest released version of this tool.:

pip install iaccess

Install via setup.py

Download or clone this repo and install via setup.py:

python setup.py install

Quickstart

If you know what you're doing with SQLAlchemy this package should be nearly invisible to you with the exception of the URI used to connect to the database.:

# connect via hostname / ip address
>>> from sqlalchemy import create_engine
>>> engine = create_engine('iaccess+pyodbc://user:password@hostname/dummy?DBQ=DEFAULT_SCHEMA')  # `dummy` can be any string

# connect via named ODBC DSN
>>> from sqlalchemy import create_engine
>>> engine = create_engine('iaccess+pyodbc://user:password@dsn_name')

Credits

This package was created with Cookiecutter and the audreyr/cookiecutter-pypackage project template.

The IBM i Access ODBC Driver is developed and licensed separately by IBM.

Continuous integration testing is performed against the system available from the great guys at Pub400.com.

iaccess's People

Contributors

Stargazers

Watchers

Forkers

kawiri richardschoen

iaccess's Issues

SADeprecationWarning: The dbapi() classmethod on dialect classes has been renamed to import_dbapi()

i Access Db2 SQLAlchemy Driver version: iaccess==0.4.2; SQLAlchemy==2.0.22
Python version: 3.12
Operating System: Microsoft Windows 11 Enterprise

Description

SADeprecationWarning: The dbapi() classmethod on dialect classes has been renamed to import_dbapi()

What I Did

I ran this script:

from urllib.parse import quote_plus
from sqlalchemy import create_engine

username = "test"
password = "password"
driver = "iSeries Access ODBC Driver"

create_engine(f"iaccess+pyodbc://{username}:{password}@CDR400/AS400?DRIVER={quote_plus(driver)}")

I got this warning message:

C:\Temp\test_sqlalchemy.py:8: SADeprecationWarning: The dbapi() classmethod on dialect classes has been renamed to import_dbapi().  Implement an import_dbapi() classmethod directly on class <class 'iaccess.dialect.IAccessDialect'> to remove this warning; the old .dbapi() classmethod may be maintained for backwards compatibility.
  create_engine(f"iaccess+pyodbc://{username}:{password}@CDR400/AS400?DRIVER={quote_plus(driver)}")

Error trying to fetch data from iSeries DB2

i Access Db2 SQLAlchemy Driver version: 0.6.0
Python version: 3.11.8
Operating System: Arch Linux
iSeries OS: V7R3

Description

Trying to connect to DB, errors out, below is a snippet:

import sqlalchemy as db
from sqlalchemy import Engine
engine: Engine = db.create_engine('iaccess+pyodbc://sqluser:sqlpass@ipaddress/MYDB?DBQ=DEFAULT_SCHEMA')
connection = engine.connect()
metadata = db.MetaData()
mytable = db.Table('MYTABLE', metadata, autoload_with=engine)
query = db.select([mytable]).where(mytable.columns.MYCOL == '1240425')
ResultProxy = connection.execute(query)
ResultSet = ResultProxy.fetchall()
print(ResultSet)

results in error:

Traceback (most recent call last):
  File ".venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1971, in _exec_single_context
    self.dialect.do_execute(
  File ".venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
    cursor.execute(statement, parameters)
pyodbc.ProgrammingError: ('42S02', '[42S02] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0204 - Kirjaston SQLUSER *FILE-lajista objektia SYSCST ei lÃ¶ydy (-204) (SQLPrepare)')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "main.py", line 6, in <module>
    mytable = db.Table('MYTABLE', metadata, autoload_with=engine)
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<string>", line 2, in __new__
  File ".venv/lib/python3.11/site-packages/sqlalchemy/util/deprecations.py", line 281, in warned
    return fn(*args, **kwargs)  # type: ignore[no-any-return]
           ^^^^^^^^^^^^^^^^^^^
  File ".venv/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 431, in __new__
    return cls._new(*args, **kw)
           ^^^^^^^^^^^^^^^^^^^^^
  File ".venv/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 485, in _new
    with util.safe_reraise():
  File ".venv/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File ".venv/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 481, in _new
    table.__init__(name, metadata, *args, _no_init=False, **kw)
  File ".venv/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 861, in __init__
    self._autoload(
  File ".venv/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 893, in _autoload
    conn_insp.reflect_table(
  File ".venv/lib/python3.11/site-packages/sqlalchemy/engine/reflection.py", line 1526, in reflect_table
    _reflect_info = self._get_reflection_info(
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File ".venv/lib/python3.11/site-packages/sqlalchemy/engine/reflection.py", line 2009, in _get_reflection_info
    pk_constraint=run(self.get_multi_pk_constraint),
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File ".venv/lib/python3.11/site-packages/sqlalchemy/engine/reflection.py", line 1992, in run
    res = meth(filter_names=_fn, **kw)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File ".venv/lib/python3.11/site-packages/sqlalchemy/engine/reflection.py", line 1008, in get_multi_pk_constraint
    return dict(
           ^^^^^
  File ".venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 1096, in _default_multi_reflect
    single_tbl_method(
  File "<string>", line 2, in get_pk_constraint
  File ".venv/lib/python3.11/site-packages/sqlalchemy/engine/reflection.py", line 97, in cache
    ret = fn(self, con, *args, **kw)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File ".venv/lib/python3.11/site-packages/iaccess/dialect.py", line 316, in get_pk_constraint
    r = connection.execute(s)
        ^^^^^^^^^^^^^^^^^^^^^
  File ".venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1422, in execute
    return meth(
           ^^^^^
  File ".venv/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 514, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File ".venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1644, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File ".venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1850, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File ".venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1990, in _exec_single_context
    self._handle_dbapi_exception(
  File ".venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2357, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File ".venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1971, in _exec_single_context
    self.dialect.do_execute(
  File ".venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42S02', '[42S02] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0204 - Kirjaston SQLUSER *FILE-lajista objektia SYSCST ei lÃ¶ydy (-204) (SQLPrepare)')
[SQL: SELECT "SYSCSTCOL"."CONSTRAINT_NAME", "SYSCSTCOL"."COLUMN_NAME"
FROM "SYSCST" JOIN "SYSCSTCOL" ON "SYSCST"."TABLE_SCHEMA" = "SYSCSTCOL"."TABLE_SCHEMA" AND "SYSCST"."TABLE_NAME" = "SYSCSTCOL"."TABLE_NAME" AND "SYSCST"."CONSTRAINT_NAME" = "SYSCSTCOL"."CONSTRAINT_NAME"
WHERE "SYSCST"."CONSTRAINT_TYPE" = ? AND "SYSCST"."TABLE_NAME" = ? AND "SYSCST"."TABLE_SCHEMA" = ?]
[parameters: ('PRIMARY KEY', 'MYTABLE', 'SQLUSER')]
(Background on this error at: https://sqlalche.me/e/20/f405)

What am I doing wrong? :)

iAccess ODBC driver not found on Linux

Hello @soundstripe , I finally had the courage to test out your iAccess python wrapper! Too bad I'm having some problems using it, could you maybe help sort it out?

i Access Db2 SQLAlchemy Driver version: iaccess==0.6.0, SQLAlchemy==2.0.29
Python version: Python 3.11.8
Operating System: Arch Linux

Description

Tried to run the example program;

from sqlalchemy import create_engine

engine = create_engine('iaccess+pyodbc://user:pass@my_as400')

Output:

/home/jussi/iaccess_test/.venv/lib/python3.11/site-packages/iaccess/connector.py:21: IAccessDriverMissingWarning: driver "IBM i Access ODBC Driver" not found by pyodbc
install from https://www.ibm.com/support/pages/odbc-driver-ibm-i-access-client-solutions or specify an alternate driver name
available drivers: None
  warnings.warn(f'driver "{selected_driver}" not found by pyodbc\n'

However, I have the (latest) iAccess driver installed (acsbundle.jar, installed via install_acs_64);

(.venv) [jussi@jussi iaccess_test]$ ls /opt/ibm/
iAccessClientSolutions
(.venv) [jussi@jussi iaccess_test]$ ls /usr/share/applications/
 htop.desktop  'IBM i Access Client Solutions.desktop'

Should I be using some other version of this driver or something..?

Recommend Projects

React

A declarative, efficient, and flexible JavaScript library for building user interfaces.
Vue.js

🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
Typescript

TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
TensorFlow

An Open Source Machine Learning Framework for Everyone
Django

The Web framework for perfectionists with deadlines.
Laravel

A PHP framework for web artisans
D3

Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

javascript

JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
web

Some thing interesting about web. New door for the world.
server

A server is a program made to process requests and deliver data to clients.
Machine learning

Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Visualization

Some thing interesting about visualization, use data art
Game

Some thing interesting about game, make everyone happy.

Recommend Org

Facebook

We are working to build community through open source technology. NB: members must have two-factor auth.
Microsoft

Open source projects and samples from Microsoft.
Google

Google ❤️ Open Source for everyone.
Alibaba

Alibaba Open Source for everyone
D3

Data-Driven Documents codes.
Tencent

China tencent open source team.