soundstripe / iaccess Goto Github PK
View Code? Open in Web Editor NEWSQLAlchemy support for IBM Db2 for i via i Access ODBC driver
Home Page: https://python-iaccess.readthedocs.io/en/latest/
License: MIT License
SQLAlchemy support for IBM Db2 for i via i Access ODBC driver
Home Page: https://python-iaccess.readthedocs.io/en/latest/
License: MIT License
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?
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..?
SADeprecationWarning: The dbapi() classmethod on dialect classes has been renamed to import_dbapi()
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)}")
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? :)
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.