Forum Stats

  • 3,770,460 Users
  • 2,253,117 Discussions
  • 7,875,465 Comments

Discussions

How to handle cx_Oracle.OBJECT?

limor
limor Member Posts: 38
edited Jul 22, 2016 10:43AM in Python

Hi,

I am working with sqlalchemy and the extension geoalchemy to connect to my database and to execute a query.

code:

from sqlalchemy import create_engine

from sqlalchemy import Table, MetaData

from sqlalchemy.sql import and_, select

from geoalchemy import Geometry, GeometryExtensionColumn

from geoalchemy import *

import cx_Oracle

import json

engine = create_engine('oracle+cx_oracle://TEST_3D:[email protected]:1521/sdetest')

metadata = MetaData(engine)

# Loading tables

building = Table(

    'building',

    metadata,

    GeometryExtensionColumn('centroid_geom', Geometry(2, srid= 431467)),

  #GeometryExtensionColumn(Geometry, Geometry(2)),

    autoload=True,

    autoload_with=engine

)

GeometryDDL(building)

thematic_surface = Table('thematic_surface', metadata, autoload=True)

surface_geometry = Table('surface_geometry', metadata, autoload=True)

objectclass = Table('objectclass', metadata, autoload=True)

connection = engine.connect()

# define the query

query = select([building.c.id, surface_geometry.c.geometry, objectclass.c.classname]  #surface_geometry.c.geometry,

).where(

    and_(

        building.c.grid_id_400 == 4158,

        building.c.id == thematic_surface.c.building_id,

        thematic_surface.c.lod2_multi_surface_id == surface_geometry.c.root_id,

        surface_geometry.c.geometry != None,

        thematic_surface.c.objectclass_id == objectclass.c.id,

    )

)

for row in connection.execute(query):

    print(row)

It works fine. At the end I get this result:

(1508351.0, <cx_Oracle.OBJECT object at 0x03099620>, u'BuildingWallSurface')

(1508351.0, <cx_Oracle.OBJECT object at 0x030A7D40>, u'BuildingWallSurface')

(1508351.0, <cx_Oracle.OBJECT object at 0x03099620>, u'BuildingWallSurface')

(1508351.0, <cx_Oracle.OBJECT object at 0x030A7D00>, u'BuildingWallSurface')

(1508351.0, <cx_Oracle.OBJECT object at 0x03099620>, u'BuildingWallSurface')

(1508351.0, <cx_Oracle.OBJECT object at 0x030A7D40>, u'BuildingWallSurface')

(1508351.0, <cx_Oracle.OBJECT object at 0x03099620>, u'BuildingWallSurface')

(1508351.0, <cx_Oracle.OBJECT object at 0x030A7D00>, u'BuildingWallSurface')

(1508351.0, <cx_Oracle.OBJECT object at 0x03099620>, u'BuildingGroundSurface')

<cx_Oracle.OBJECT object at 0x03099620== (x=... y=...)

My question is how can I convert these objects to json?

At the end I will have a json.

For standard objects I use

print json.dumps([dict(r) for r in connection.execute(query)])

But this doesn´t work with geometries (not serializable).

How can I handle this problem?

limor

Best Answer

  • Anthony Tuininga-Oracle
    Anthony Tuininga-Oracle Member Posts: 37 Employee
    edited Jul 19, 2016 12:55PM Accepted Answer

    You need to set arraysize as in

    cursor.arraysize = 500

    That will allow Oracle to retrieve up to 500 rows at one time. You can adjust that number higher or lower as needed. The default value is 50. And yes, when you fetch, the result is created automatically -- no need to populate it first!

«1

Answers

  • Anthony Tuininga-Oracle
    Anthony Tuininga-Oracle Member Posts: 37 Employee
    edited Jul 15, 2016 11:49AM

    This is using the (as yet unreleased) version of cx_Oracle which supports binding of objects and other more advanced uses of objects. Using the sample provided with cx_Oracle for demonstrating the insertion of geometry, the following code will transform the object created in that way into JSON. The ObjectRepr() function included below should work for any object returned from Oracle. It simply reads the metadata on the object and turns the object into a dictionary of attributes or a list of values.

    import cx_Oracle

    import json

    connection = cx_Oracle.Connection("user/[email protected]")

    typeObj = connection.gettype("SDO_GEOMETRY")

    cursor = connection.cursor()

    cursor.execute("""

            select Geometry

            from TestGeometry

            where IntCol = 1""")

    obj, = cursor.fetchone()

    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

    print("JSON:", json.dumps(ObjectRepr(obj)))

    limor
  • limor
    limor Member Posts: 38
    edited Jul 18, 2016 7:33AM

    Thanks for your answer.

    My result of the sql statement is an cx_Oracle.OBJECT.

    For which reason did you define typeObj ? For me I get an error: AttributeError: 'cx_Oracle.Connection' object has no attribute 'gettype'


    Ok regarding the function the first part is not working:

    if obj.type.iscollection:

            returnValue = []

            for value in obj.aslist():

                if isinstance(value, cx_Oracle.Object):

                    value = ObjectRepr(value)

                returnValue.append(value

    I get this error message: AttributeError: 'cx_Oracle.ObjectType' object has no attribute 'iscollection'.


    The second part works fine.


    This example is focused on one object of type cx_Oracle.OBJECT in the result set. I have a structure like this one:

    (Number, cx_Oracle.OBJECT, String),

    (Number, cx_Oracle.OBJECT, String),

    (Number, cx_Oracle.OBJECT, String),

    (...)

    How can I convert this structure in an GeoJSON like this:

    {{"Number": 12, "geometry": [3500983.087, 5394211.455, 473.82800000000003, 3500 978.97, 5394211.04, 469.069, 3500979.85, 5394201.47, 468.482, 3500984.777, 53942 02.055, 474.192, 3500983.087, 5394211.455, 473.82800000000003], "Type": Roof },

    {"Number": 13, "geometry": [3500983.087, 5394211.455, 473.82800000000003, 3500 978.97, 5394211.04, 469.069, 3500979.85, 5394201.47, 468.482, 3500984.777, 53942 02.055, 474.192, 3500983.087, 5394211.455, 473.82800000000003], "Type": Wall },

    {"Number": 14, "geometry": [3500983.087, 5394211.455, 473.82800000000003, 3500 978.97, 5394211.04, 469.069, 3500979.85, 5394201.47, 468.482, 3500984.777, 53942 02.055, 474.192, 3500983.087, 5394211.455, 473.82800000000003], "Type": Wall }}

    ...

    I hope you can help me because I have to do it with cx_Orace there are no real alternatives???

  • Anthony Tuininga-Oracle
    Anthony Tuininga-Oracle Member Posts: 37 Employee
    edited Jul 18, 2016 9:25AM

    Hi,

    Note the first sentence of my original answer: this is with an as yet *UNRELEASED* version of cx_Oracle. In other words, if you use cx_Oracle 5.2.1 (the last released version) you'll get the errors you are experiencing! You need to get the latest source version and compile it for yourself. The source can be found here:

    https://bitbucket.org/anthony_tuininga/cx_oracle

    If you run into any difficulties with building and using that version, please let me know.

    Anthony

  • limor
    limor Member Posts: 38
    edited Jul 18, 2016 10:17AM

    Ok thanks for helping me...

    I installed  the source code of the unreleased version. After this I installed it by using the commands python setup.py build, python setup.py install

    Now your example above is working without errors.

    The only thing is where I get an error message is at the beginning : typeObj = connection.gettype("SDO_GEOMETRY")

    error: cx_Oracle.DatabaseError: ORA-04043: Objekt SDO_GEOMETRY is not available

    Do you know why this error occurs?

    Another thing is maybe you know an example or some other help. In my project I have a sql statement where I get a tupel (number, geometry, type)

    cursor.execute("""

            select number,Geometry,type

            from TestGeometry

            where IntCol = 1""")

    How can I convert this in a JSON like this:

    {{"Number": 12, "geometry": [3500983.087, 5394211.455, 473.82800000000003, 3500 978.97, 5394211.04, 469.069, 3500979.85, 5394201.47, 468.482, 3500984.777, 53942 02.055, 474.192, 3500983.087, 5394211.455, 473.82800000000003], "Type": Roof },

  • Anthony Tuininga-Oracle
    Anthony Tuininga-Oracle Member Posts: 37 Employee
    edited Jul 18, 2016 10:42AM

    Regarding the error on gettype() you can try using "SYS.SDO_GEOMETRY". If a describe works in SQL*Plus, though, it should work in cx_Oracle! What version of the Oracle client and server are you using?

    Regarding the conversion to JSON, you can simply use this:

    import json

    json.dumps(yourValue)

    This works so long as yourValue refers to types that Python knows how to convert to JSON (number, strings, tuples, lists, etc.)

    Anthony

  • limor
    limor Member Posts: 38
    edited Jul 18, 2016 11:12AM

    I am using Oracle Database 11g Release 11.2.0.4.0 - 64bit Production and SQL*Plus: Release 11.2.0.1.0.

    I found a solution. If I take MDSYS.SDO_GEOMETRY instead of SDO_GEOMETRY it works fine !

    Regarding my problem. To get all geometries from the database I can use this loop

    for row in cursor:

      print ObjectRepr(row[0])


    {'SDO_ORDINATES': [3500989.62, 5394202.63, 468.97200000000004, 3500987.21, 5394211.87, 469.401, 3500983.087, 5394211.455, 473.82800000000003, 3500984.777, 5394202.055, 474.192, 3500989.62, 5394202.63, 468.97200000000004],

    'SDO_GTYPE': 3003.0, 'SDO_ELEM_INFO': [1.0, 1003.0, 1.0], 'SDO_SRID': 31467.0}

    {'SDO_ORDINATES': [3500979.85, 5394201.47, 458.404, 3500984.777, 5394202.055, 458.404, 3500984.777, 5394202.055, 474.192, 3500979.85, 5394201.47, 468.482, 3500979.85, 5394201.47, 458.404], 'SDO_GTYPE': 3003.0, 'SDO_ELEM_INFO': [1.0, 1003.0,1.0], 'SDO_SRID': 31467.0}

    To get other values (no geometries) I can use this:

    print json.dumps([dict(r) for r in result])

    {"building_nr": 1508161, "polygon_typ": "BuildingWallSurface"},

    {"building_nr": 1508161, "polygon_typ": "BuildingWallSurface"},

    {"building_nr": 1508161, "polygon_typ": "BuildingWallSurface"},

    {"building_nr": 1508161, "polygon_typ": "BuildingWallSurface"},

    ...

    What is the fastest method to combine them??

    {"building_nr": 1508161,'SDO_ORDINATES': [3500989.62, 5394202.63, 468.97200000000004, 3500987.21, 5394211.87, 469.401, 3500983.087, 5394211.455, 473.82800000000003, 3500984.777, 5394202.055, 474.192, 3500989.62, 5394202.63, 468.97200000000004],'SDO_GTYPE': 3003.0, 'SDO_ELEM_INFO': [1.0, 1003.0, 1.0], 'SDO_SRID': 31467.0, "polygon_typ": "BuildingWallSurface"}????

  • Anthony Tuininga-Oracle
    Anthony Tuininga-Oracle Member Posts: 37 Employee
    edited Jul 18, 2016 12:52PM

    You have a few options available to you. You can use dict.update() to update the contents of one dictionary with the other, as in the following:

    myDict = {"building_nr": 1508161, "polygon_typ": "BuildingWallSurface"}

    geomDict = {'SDO_ORDINATES': [3500989.62, 5394202.63, 468.97200000000004, 3500987.21, 5394211.87, 469.401, 3500983.087, 5394211.455, 473.82800000000003, 3500984.777, 5394202.055, 474.192, 3500989.62, 5394202.63, 468.97200000000004], 'SDO_GTYPE': 3003.0, 'SDO_ELEM_INFO': [1.0, 1003.0, 1.0], 'SDO_SRID': 31467.0}


    myDict.update(geomDict)


    You can also use cursor.rowfactory to do this -- but sqlalchemy may get in the way. You can take a look at the "RowsAsInstance" sample provided with cx_Oracle or you can look at https://bitbucket.org/anthony_tuininga/cx_pygenlib/src/73a7bb0b096839ccdeb3c1385df1205be5b3990c/ceDatabase.py?fileviewer=file-view-default which is a more advanced method of doing the same thing.

    Hopefully one of those should help you do what you wish to do!

    Anthony

    limorlimor
  • limor
    limor Member Posts: 38
    edited Jul 19, 2016 5:51AM

    Ok thanks

    I will use only one query to get the result and so did an update: My aim is to create a webservice!

    I am using now RowAsInstace and I use only SDO_ORDINATE!!!

    import cx_Oracle

    import json

    import web

    urls = (

      "/", "index",

      "/bbox", "bbox",

      )

    app = web.application(urls, globals())

    web.config.debug = True

    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)

      if attr.name == "SDO_ORDINATES":

      returnValue['"type":"Polygon","coordinates"'] = value

        return returnValue

    class Test(object):

        def __init__(self, a, b, c):

            self.a = a

            self.b = b

            self.c = c

    connection = cx_Oracle.Connection("TEST_3D/[email protected]:1521/sdetest")

    typeObj = connection.gettype("MDSYS.SDO_GEOMETRY")

    class index:

        def GET(self):

           return "hallo moritz "

    class bbox:

      def GET(self):

           web.header('Access-Control-Allow-Origin',      '*')

           web.header('Access-Control-Allow-Credentials', 'true')

           web.header('Content-Type', 'application/json')

           cursor = connection.cursor()

           cursor.execute("""SELECT a.id ,c.Geometry  , d.Classname  FROM   building a, THEMATIC_SURFACE b, SURFACE_GEOMETRY c, OBJECTCLASS d  WHERE  a.grid_id_400 = 4158 AND a.id = b.BUILDING_ID AND b.LOD2_MULTI_SURFACE_ID = c.ROOT_ID AND                          c.GEOMETRY IS NOT NULL AND b.OBJECTCLASS_ID = d.ID""")

           cursor.rowfactory = Test

           for row in cursor:

                return json.dumps(('"building_nr": %s, "geometry": %s, "polygon_typ": %s"' % (row.a, ObjectRepr(row.b), row.c)))

    if __name__ == "__main__":

           app.run()

    Result in the Browser:

    "\"building_nr\": 1318140, \"geometry\": {'\"type\":\"Polygon\",\"coordinates\"': [3500983.087, 5394211.455, 473.82800000000003, 3500978.97, 5394211.04, 469.069, 3500979.85, 5394201.47, 468.482, 3500984.777, 5394202.055, 474.192, 3500983.087, 5394211.455, 473.82800000000003]}, \"polygon_typ\": BuildingRoofSurface\""

    In the console I get this error message at the end:

    cx_Oracle.DatabaseError: ORA-21500: Interner Fehlercode, Argumente: [%s], [%s],

    [%s], [%s], [%s], [%s], [%s], [%s]


    Does it refer to the fetch size?

    My result is not only one polygon I have a lot of them. Why is only one displayed?

    And second question can I convert the Coordinates in this way: geometry:[[x,y,z],[x,y,z],[...],[...]]

  • Anthony Tuininga-Oracle
    Anthony Tuininga-Oracle Member Posts: 37 Employee
    edited Jul 19, 2016 9:20AM

    The error you got is an internal error. There is a bug that is causing this to occur. The only workaround at the moment is to adjust the array size or reduce the number of rows that you are querying at one time. I am hoping to correct this soon!

    As for your second question, you can convert it any way you like! The ObjectRepr() function I provided is a generic one. You can another function very specific to SDO_GEOMETRY like this:

    def GeometryRepr(obj):

        coordinates = []

        sourceCoordinates = obj.SDO_ORDINATES.aslist()

        for i in range(len(sourceCoordinates) // 3):

           coordinates.append(sourceCoordinates[i * 3:i * 3 + 3])

        return ....

  • limor
    limor Member Posts: 38
    edited Jul 19, 2016 9:56AM

    If I use your generic function I get this result "building_nr": 936948, "geometry": None, "polygon_typ": BuildingRoofSurface"

    So I get no values for SDO_ORDINATES.

    Do you know why???

    Can I do it also in this way???:

    cursor = connection.cursor()

      cursor.execute("""SELECT a.id , c.geometry, d.Classname FROM   building a, THEMATIC_SURFACE b, SURFACE_GEOMETRY c, OBJECTCLASS d  WHERE  a.grid_id_400 = :1 AND a.id = b.BUILDING_ID AND b.LOD2_MULTI_SURFACE_ID = c.ROOT_ID AND c.GEOMETRY IS NOT NULL AND b.OBJECTCLASS_ID = d.ID""", [4148])

       result = []

       for id, geometry, classname in cursor:

       result.append({

       "building_nr": id, "geometry": {

       "type": "polygon",

       "coordinates": geometry.SDO_ORDINATES.aslist()

       }, "polygon_typ": classname,

       })

       return json.dumps(result)

    Based on geometry there occurs the same error :

    ORA-21500: Interner Fehlercode, Argumente: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]
This discussion has been closed.