为什么在oracledb/cx_Oracle(Python)中不调用outconverter进行字节转换？

ep6jt1vc 于 2023-04-20 发布在 Oracle

关注(0)|答案(1)|浏览(128)

我正在尝试使用Python从Oracle数据库中获取SDO_GEOMETRY类型列（3.11）和OracleDB库（1.3.0）。我想使用outputtypehandler将SDO_GEOMETRY示例转换为pickle编码的字节。这对于NUMBER列很有效，如果我尝试将cursor.var中的typ参数设置为typ=str，但是对于所有类型的列类型，typ=bytes和typ=oracledb.DB_TYPE_RAW都失败。SDO_GEOMETRY列总是产生错误，而不管typ参数值如何。甚至不调用外转换器，如下所示。
下面是我的示例代码：

import oracledb
import pickle

def output_type_handler(cursor, name, default_type, size, precision, scale):

    def pickle_converter(obj) -> bytes:
        print(f"Converter called for {name}.")
        return pickle.dumps(obj)

    if default_type == oracledb.DB_TYPE_OBJECT:
        return cursor.var(
            typ=oracledb.DB_TYPE_RAW, 
            size=size, 
            arraysize=cursor.arraysize, 
            outconverter=pickle_converter
        )

# Switch to thick mode
oracledb.init_oracle_client()

ora_connection = oracledb.connect(
    dsn=oracledb.makedsn("ora.local", 1521, "TST"),
    user="test",
    password="test"
)

ora_connection.outputtypehandler = output_type_handler

with ora_connection.cursor() as cursor:
    # GEOMETRIE is an SDO_GEOMETRY column
    recs = cursor.execute("SELECT GEOMETRIE FROM MV_CS_STWG1KP").fetchmany(5)
    print(recs)

输出（注意，Converter called for ...行甚至没有打印出来，所以转换器从未被调用）：

Traceback (most recent call last):
  File "/home/jannis/.config/JetBrains/PyCharmCE2023.1/scratches/tmp.py", line 28, in <module>
    num_recs = cursor.execute("SELECT GEOMETRIE FROM MV_CS_STWG1KP").fetchmany(5)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/jannis/PycharmProjects/etl_engine/venv/lib/python3.11/site-packages/oracledb/cursor.py", line 492, in fetchmany
    row = fetch_next_row(self)
          ^^^^^^^^^^^^^^^^^^^^
  File "src/oracledb/impl/base/cursor.pyx", line 397, in oracledb.base_impl.BaseCursorImpl.fetch_next_row
  File "src/oracledb/impl/thick/cursor.pyx", line 132, in oracledb.thick_impl.ThickCursorImpl._fetch_rows
  File "src/oracledb/impl/thick/utils.pyx", line 413, in oracledb.thick_impl._raise_from_odpi
  File "src/oracledb/impl/thick/utils.pyx", line 403, in oracledb.thick_impl._raise_from_info
oracledb.exceptions.DatabaseError: ORA-00932: inconsistent datatypes: expected BINARY got ADT

我必须使用密集模式连接到较旧的Oracle数据库。如何解决此问题？

oracle

来源：https://stackoverflow.com/questions/76014335/why-is-the-outconverter-not-called-for-byte-conversions-in-oracledb-cx-oracle-p

1条答案

按热度按时间

rqdpfwrv1#

在序列化之前，你需要转换为Python对象。即使删除输出处理程序并显式pickle也会产生错误：

cur.execute("select geometry from testgeometry")
r, = cur.fetchone()
p = pickle.dumps(r) # fails with error "KeyError: '__getstate__'"

相反，尝试以下方法。它使用类型转换器转换为Python对象，然后使用行工厂对其进行pickle。

class mySDO(object):
    def __init__(self, gtype, elemInfo, ordinates):
        self.gtype = gtype
        self.elemInfo = elemInfo
        self.ordinates = ordinates

obj_type = con.gettype("MDSYS.SDO_GEOMETRY")

def SDOOutputTypeHandler(cursor, name, default_type, size, precision, scale):
    def SDOOutConverter(DBobj):
        return mySDO(int(DBobj.SDO_GTYPE), DBobj.SDO_ELEM_INFO.aslist(), DBobj.SDO_ORDINATES.aslist())

    if default_type == oracledb.DB_TYPE_OBJECT:
        return cursor.var(obj_type, arraysize=cursor.arraysize, outconverter=SDOOutConverter)

cur.outputtypehandler = SDOOutputTypeHandler

cur.execute("select geometry from testgeometry")
cur.rowfactory = lambda *args: pickle.dumps(args)
p = cur.fetchone()
print(p)

然而，对于空间对象使用“众所周知的二进制”（WKB）格式会更好吗？你可以直接从DB中获取它，而不需要输出转换器或行工厂：

oracledb.defaults.fetch_lobs = False

cur = con.cursor()
cur.execute("select sdo_util.to_wkbgeometry(geometry) from testgeometry")
b = cur.fetchone()
print(b)

赞(0）回复(0）举报 2023-04-28

我来回答

为什么在oracledb/cx_Oracle(Python)中不调用outconverter进行字节转换？

1条答案

相关问题

热门标签

最新问答