通过cx_Oracle从用户定义的类型中提取数据 [英] Extract data from User defined type via cx_Oracle

问题描述

我正在尝试通过cx_Oracle从用户定义类型(UDT)中提取数据.这是UDT结构:

I am trying to extract data from User defined type (UDT) via cx_Oracle. Here is the UDT structure:

CREATE OR REPLACE TYPE graphic_component AS OBJECT (
   type             NUMBER(6),
   source_type      NUMBER(4),
   meta_type_id     VARCHAR2(50 CHAR),
   name             VARCHAR2(100 CHAR),
   extension_info   VARCHAR2(500 CHAR),
   symbology_tokens VARCHAR2(2000 CHAR)
);

CREATE OR REPLACE TYPE graphic_component_array AS
      VARRAY (10000) OF graphic_component;

以下是使用Python的示例:

Here is an example using Python:

>>>insert = cursor.execute("SELECT COMPLEX FROM GRAPHIC WHERE ID=48662511087446403855368")
>>>complex = insert.fetchall()
[(<cx_Oracle.Object SCHEMA.GRAPHIC_COMPONENT_ARRAY at 0x33d71d0>,)]
>>>dir(complex)
['__class__', '__delattr__', '__dir__', '__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__le__', '__lt__', '__ne__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', 'append', 'aslist', 'copy', 'delete', 'exists', 'extend', 'first', 'getelement', 'last', 'next', 'prev', 'setelement', 'size', 'trim', 'type']
>>>complex.first()
0

您可以看到有一个很好的用户定义类型，但是我无法从此或任何其他UDT中提取数据.我试图检查对象的所有属性，然后找到方法first()，getelement()和last().但是这些函数仅返回0.

You can see there is a good user defined type but I can't extract data from this or any other UDT. I tried to check all properties of the object and I found methods first(), getelement() and last(). But these functions returns only 0.

有没有办法通过cx_Oracle从UDT提取数据，或者有其他选择如何获取数据.

Is there any way how to extract data from UDT via cx_Oracle or is there any other option how to get data.

更新

<cx_Oracle.Object GRAPHIC_COMPONENT_ARRAY at 0x26c9f10>
Traceback (most recent call last):
  File "C:/Users/petr.silhak/PycharmProjects/migration-to-postgresql/test_parsing.py", line 34, in <module>
    print(ObjectRepr(complex[0][0]))
  File "C:/Users/petr.silhak/PycharmProjects/migration-to-postgresql/test_parsing.py", line 9, in ObjectRepr
    value = ObjectRepr(value)
  File "C:/Users/petr.silhak/PycharmProjects/migration-to-postgresql/test_parsing.py", line 14, in ObjectRepr
    value = getattr(obj, attr.name)
cx_Oracle.DatabaseError: DPI-1014: conversion between Oracle type 2010 and native type 3000 is not implemented

推荐答案

此处是在cx_Oracle中处理复杂类型的一段代码.
摘录:

Here is the piece of code that handles complex types in cx_Oracle.
Excerpt:

def ObjectRepr(obj):
    if obj.type.iscollection:
        returnValue = []
        for value in obj.aslist():
            if isinstance(value, cx_Oracle.Object):
                value = ObjectRepr(value)
            returnValue.append(value)
    else:
        returnValue = {}
        for attr in obj.type.attributes:
            value = getattr(obj, attr.name)
            if value is None:
                continue
            elif isinstance(value, cx_Oracle.Object):
                value = ObjectRepr(value)
            returnValue[attr.name] = value
    return returnValue  

您当然会像ObjectRepr(complex[0][0])一样使用它，如果是len(complex)>0

You use it like ObjectRepr(complex[0][0]), if len(complex)>0, of course

这篇关于通过cx_Oracle从用户定义的类型中提取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！