SQLSTATE

SQLSTATE

关注
发信
关注(28)粉丝(399)

使用python/SQLAlchemy连接到DB2时出错:"0x0000"；参数值"0x0000"设置为0.不支持.SQLSTATE = 58017

本文介绍了使用python/SQLAlchemy连接到DB2时出错:"0x0000"；参数值"0x0000"设置为0.不支持.SQLSTATE = 58017的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试使用以下脚本通过SQLAlchemy连接到db2数据库:(Windows 7 x64，使用库的Python 3.6:ibm_db_sa和ibm_db_2.0.8a，z/OS上的DB2)

I'm trying to connect to a db2 database via SQLAlchemy using the following script: (Windows 7 x64, Python 3.6 using libraries: ibm_db_sa, and ibm_db_2.0.8a, DB2 on z/OS) 

import traceback
import ibm_db_sa
from sqlalchemy import create_engine
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import scoped_session, sessionmaker

if __name__ == '__main__':
    try:
        Base = automap_base()
        connection_string = 'db2+ibm_db://userName:[email protected]:6001/DB_0;'
        engine = create_engine(connection_string)
        session = scoped_session(sessionmaker(autocommit=False,
                                              autoflush=False,
                                              bind=engine))
        # reflect the tables
        Base.prepare(engine, reflect=True)
        base_sa = Base.classes

    except Exception as err:
        print(traceback.format_exc())
        engine.close()

不幸的是，我在 Base.prepare(engine，reflect = True)行上收到以下回溯/错误.这是一个概念证明代码，用于测试我是否确实可以连接到db2数据库并使用SQLAlchemy反映表.

Unfortunately I'm getting the below traceback/error on the Base.prepare(engine, reflect=True) line. This is a proof of concept code to test if I can indeed connect to a db2 database and reflect tables using SQLAlchemy.

我还尝试过运行原始SQL语句，而不是反映具有相同结果的结果:

I have also tried to run a raw SQL statement instead of reflecting that has had the same outcome:

result = engine.execute(r'SELECT * FROM fooschema.footable;')

回溯:

Connected to pydev debugger (build 171.4694.67)
Traceback (most recent call last):
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 1122, in _do_get
    return self._pool.get(wait, self._timeout)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\util\queue.py", line 145, in get
    raise Empty
sqlalchemy.util.queue.Empty

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\ibm_db_dbi.py", line 592, in connect
    conn = ibm_db.connect(dsn, '', '', conn_options)
 SQLCODE=-30073

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\engine\base.py", line 2147, in _wrap_pool_connect
    return fn()
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 328, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 766, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 516, in checkout
    rec = pool._do_get()
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 1138, in _do_get
    self._dec_overflow()
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\util\langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\util\compat.py", line 187, in reraise
    raise value
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 1135, in _do_get
    return self._create_connection()
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 333, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 461, in __init__
    self.__connect(first_connect_check=True)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 651, in __connect
    connection = pool._invoke_creator(self)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\engine\strategies.py", line 105, in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\engine\default.py", line 393, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\ibm_db_dbi.py", line 595, in connect
    raise _get_exception(inst)
ibm_db_dbi.ProgrammingError: ibm_db_dbi::ProgrammingError: [IBM][CLI Driver] SQL30073N  "0x0000" Parameter value "0x0000" is not supported.  SQLSTATE=58017\r SQLCODE=-30073

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

Traceback (most recent call last):
  File "C:/Users/1234567890/Documents/cool_app/db2_is_working.py", line 18, in <module>
    Base.prepare(engine, reflect=True)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\ext\automap.py", line 753, in prepare
    autoload_replace=False
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\sql\schema.py", line 3840, in reflect
    with bind.connect() as conn:
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\engine\base.py", line 2091, in connect
    return self._connection_cls(self, **kwargs)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\engine\base.py", line 90, in __init__
    if connection is not None else engine.raw_connection()
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\engine\base.py", line 2177, in raw_connection
    self.pool.unique_connection, _connection)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\engine\base.py", line 2151, in _wrap_pool_connect
    e, dialect, self)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\engine\base.py", line 1465, in _handle_dbapi_exception_noconnection
    exc_info
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\util\compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\engine\base.py", line 2147, in _wrap_pool_connect
    return fn()
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 328, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 766, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 516, in checkout
    rec = pool._do_get()
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 1138, in _do_get
    self._dec_overflow()
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\util\langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\util\compat.py", line 187, in reraise
    raise value
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 1135, in _do_get
    return self._create_connection()
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 333, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 461, in __init__
    self.__connect(first_connect_check=True)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 651, in __connect
    connection = pool._invoke_creator(self)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\engine\strategies.py", line 105, in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\engine\default.py", line 393, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\ibm_db_dbi.py", line 595, in connect
    raise _get_exception(inst)
sqlalchemy.exc.ProgrammingError: (ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: [IBM][CLI Driver] SQL30073N  "0x0000" Parameter value "0x0000" is not supported.  SQLSTATE=58017\r SQLCODE=-30073

Traceback (most recent call last):
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 1122, in _do_get
    return self._pool.get(wait, self._timeout)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\util\queue.py", line 145, in get
    raise Empty
sqlalchemy.util.queue.Empty

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\ibm_db_dbi.py", line 592, in connect
    conn = ibm_db.connect(dsn, '', '', conn_options)
 SQLCODE=-30073

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\engine\base.py", line 2147, in _wrap_pool_connect
    return fn()
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 328, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 766, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 516, in checkout
    rec = pool._do_get()
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 1138, in _do_get
    self._dec_overflow()
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\util\langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\util\compat.py", line 187, in reraise
    raise value
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 1135, in _do_get
    return self._create_connection()
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 333, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 461, in __init__
    self.__connect(first_connect_check=True)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 651, in __connect
    connection = pool._invoke_creator(self)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\engine\strategies.py", line 105, in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\engine\default.py", line 393, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\ibm_db_dbi.py", line 595, in connect
    raise _get_exception(inst)
ibm_db_dbi.ProgrammingError: ibm_db_dbi::ProgrammingError: [IBM][CLI Driver] SQL30073N  "0x0000" Parameter value "0x0000" is not supported.  SQLSTATE=58017\r SQLCODE=-30073

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

Traceback (most recent call last):
  File "C:/Users/1234567890/Documents/cool_app/db2_is_working.py", line 18, in <module>
    Base.prepare(engine, reflect=True)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\ext\automap.py", line 753, in prepare
    autoload_replace=False
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\sql\schema.py", line 3840, in reflect
    with bind.connect() as conn:
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\engine\base.py", line 2091, in connect
    return self._connection_cls(self, **kwargs)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\engine\base.py", line 90, in __init__
    if connection is not None else engine.raw_connection()
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\engine\base.py", line 2177, in raw_connection
    self.pool.unique_connection, _connection)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\engine\base.py", line 2151, in _wrap_pool_connect
    e, dialect, self)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\engine\base.py", line 1465, in _handle_dbapi_exception_noconnection
    exc_info
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\util\compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\engine\base.py", line 2147, in _wrap_pool_connect
    return fn()
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 328, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 766, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 516, in checkout
    rec = pool._do_get()
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 1138, in _do_get
    self._dec_overflow()
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\util\langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\util\compat.py", line 187, in reraise
    raise value
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 1135, in _do_get
    return self._create_connection()
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 333, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 461, in __init__
    self.__connect(first_connect_check=True)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\pool.py", line 651, in __connect
    connection = pool._invoke_creator(self)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\engine\strategies.py", line 105, in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\sqlalchemy\engine\default.py", line 393, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "C:\Users\1234567890\Documents\cool_app\.env\lib\site-packages\ibm_db_dbi.py", line 595, in connect
    raise _get_exception(inst)
sqlalchemy.exc.ProgrammingError: (ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: [IBM][CLI Driver] SQL30073N  "0x0000" Parameter value "0x0000" is not supported.  SQLSTATE=58017\r SQLCODE=-30073

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\1234567890\AppData\Roaming\JetBrains\PyCharm 2017.1.4\helpers\pydev\pydevd.py", line 1591, in <module>
    globals = debugger.run(setup['file'], None, None, is_module)
  File "C:\Users\1234567890\AppData\Roaming\JetBrains\PyCharm 2017.1.4\helpers\pydev\pydevd.py", line 1018, in run
    pydev_imports.execfile(file, globals, locals)  # execute the script
  File "C:\Users\1234567890\AppData\Roaming\JetBrains\PyCharm 2017.1.4\helpers\pydev\_pydev_imps\_pydev_execfile.py", line 18, in execfile
    exec(compile(contents+"\n", file, 'exec'), glob, loc)
  File "C:/Users/1234567890/Documents/cool_app/db2_is_working.py", line 23, in <module>
    engine.close()
AttributeError: 'Engine' object has no attribute 'close'

推荐答案

问题是我没有正确的主机/用户/密码.我通过创建DSN，测试连接并使用pyodbc进行连接解决了该问题.我仍然无法使SQLAlchemy与该连接一起使用，但是将为此发布一个单独的问题.

The issue was that I didn't have the correct host/user/password. I resolved the issue by creating a DSN, testing the connection and then using pyodbc to connect. I still haven't been able to get SQLAlchemy to work with the connection but will post a separate question for that.

更新:SQLAlchemy当前不支持z/OS上的DB2.

Update: DB2 on z/OS is currently not supported by SQLAlchemy.

这篇关于使用python/SQLAlchemy连接到DB2时出错:"0x0000"；参数值"0x0000"设置为0.不支持.SQLSTATE = 58017的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

08-14 12:29
Powered by LMLPHP ©2024 SQLSTATE 0.035726