CODEWITHSUNDEEP
cx-oracle
bevanj
bevanj

Reputation: 254

cx_Oracle and SDO_GEOMETRY

I have a cx_Oracle connection that is happy returning data. I am however having trouble with the geometry. It is being returned as a cx_Oracle object e.g. cx_Oracle.OBJECT at 0x3afc320. But I cannot access it's attributes as below

import cx_Oracle
query = '''
select geometry from table
'''
cx_Oracle.makedsn(...)
db_conn = cx_Oracle.connect(...)
cursor = db_conn.cursor()
cursor.execute(query)
columns = [i[0].lower() for i in cursor.description]
results = []
for row in cursor:
    results.append(dict(zip(columns, row)))
db_conn.close()
print (results[0]['geometry'])
print results[0]['geometry'].SDO_ORDINATES
print results[0]['geometry'].SDO_GTYPE
print results[0]['geometry'].SDO_ELEM_INFO

Because? these attributes are not available?

import inspect
inspect.getmembers(results[0]['geometry'])
[('__class__', cx_Oracle.OBJECT),
('__delattr__',
<method-wrapper '__delattr__' of cx_Oracle.OBJECT object at 0x03AFC320>),
('__doc__', None),
('__format__', <function __format__>),
('__getattribute__',
<method-wrapper '__getattribute__' of cx_Oracle.OBJECT object at 0x03AFC320>),
('__hash__',
<method-wrapper '__hash__' of cx_Oracle.OBJECT object at 0x03AFC320>),
('__init__',
<method-wrapper '__init__' of cx_Oracle.OBJECT object at 0x03AFC320>),
('__new__', <function __new__>),
('__reduce__', <function __reduce__>),
('__reduce_ex__', <function __reduce_ex__>),
('__repr__',
<method-wrapper '__repr__' of cx_Oracle.OBJECT object at 0x03AFC320>),
('__setattr__',
<method-wrapper '__setattr__' of cx_Oracle.OBJECT object at 0x03AFC320>),
('__sizeof__', <function __sizeof__>),
('__str__',
<method-wrapper '__str__' of cx_Oracle.OBJECT object at 0x03AFC320>),
('__subclasshook__', <function __subclasshook__>),
('type', <cx_Oracle.ObjectType MDSYS.SDO_GEOMETRY>)]

When I use sql developer and look at the table in question, the field 'geometry' is of type 'SDO_GEOMETRY()'. Any help appreciated.

Upvotes: 1

Views: 3054

Answers (2)

Anthony Tuininga
Anthony Tuininga

Reputation: 7086

If you have a table that looks like the following:

create table TestGeometry (
    IntCol number(9) not null,
    Geometry sdo_geometry not null
);

and you populate it with data like the following:

insert into TestGeometry
values (1, sdo_geometry(2003, null, null, sdo_elem_info_array(1, 1003, 3),
    sdo_ordinate_array(1, 1, 5, 7)));

then the following script will access the data you are looking for:

connection = cx_Oracle.Connection("user/pw@tns")
cursor = connection.cursor()
cursor.execute("""
        select Geometry
        from TestGeometry
        where IntCol = 1""")
obj, = cursor.fetchone()
print(obj.SDO_ORDINATES)
print(obj.SDO_GTYPE)
print(obj.SDO_ELEM_INFO)

You can always find out which attributes are available using this code:

for attr in obj.type.attributes:
    print(attr.name)

Upvotes: 2

jatal
jatal

Reputation: 840

I was encountering similar issues. The sdo_geometry object does not get transferred to the requesting client. I believe the reason is that the internal geometry is stored as a LOB.

In my case, I needed to simply access the geometry so I convert sdo_geometry (SDO_UTIL.TO_WKTGEOMETRY) to character data, which then came down.

There may be other methods for converting the object to sub-components.

Hope it helps, J

Upvotes: 0

Related Questions