1 2 Previous Next 15 Replies Latest reply on Jul 22, 2016 2:43 PM by Anthony Tuininga-Oracle

    How to handle cx_Oracle.OBJECT?

    limor

      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:limo1013@10.40.33.160: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?

        • 1. Re: How to handle cx_Oracle.OBJECT?
          Anthony Tuininga-Oracle

          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/pw@tns")

          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)))

          1 person found this helpful
          • 2. Re: How to handle cx_Oracle.OBJECT?
            limor

            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???

            • 3. Re: How to handle cx_Oracle.OBJECT?
              Anthony Tuininga-Oracle

              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

              • 4. Re: How to handle cx_Oracle.OBJECT?
                limor

                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 },

                • 5. Re: How to handle cx_Oracle.OBJECT?
                  Anthony Tuininga-Oracle

                  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

                  • 6. Re: How to handle cx_Oracle.OBJECT?
                    limor

                    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"}????

                    • 7. Re: How to handle cx_Oracle.OBJECT?
                      Anthony Tuininga-Oracle

                      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

                      1 person found this helpful
                      • 8. Re: How to handle cx_Oracle.OBJECT?
                        limor

                        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/limo1013@10.40.33.160: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],[...],[...]]

                        • 9. Re: How to handle cx_Oracle.OBJECT?
                          Anthony Tuininga-Oracle

                          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 ....

                          • 10. Re: How to handle cx_Oracle.OBJECT?
                            limor

                            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]
                            • 11. Re: How to handle cx_Oracle.OBJECT?
                              Anthony Tuininga-Oracle

                              Yes, you can do it that way. Or you can put that code inside a rowfactory function if you wish to hide it away. The internal error is something you have to work around for now -- increase the array size or decrease the number of rows you are fetching at one time. Hopefully that will get resolved soon.

                              • 12. Re: How to handle cx_Oracle.OBJECT?
                                limor

                                If I take only one building (around ten polygons) it works

                                 

                                But if I will fetch some hundreds of buildings it doesn´t work. How can I solve this with array size? result = [] * 100000000 doenst work...

                                I thougt it will be is automatically increase if I append something...

                                It is for our project very import to get all buildings at once!!! I will be very happy if you have an idea...

                                • 13. Re: How to handle cx_Oracle.OBJECT?
                                  Anthony Tuininga-Oracle

                                  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!

                                  • 14. Re: How to handle cx_Oracle.OBJECT?
                                    limor

                                    Ok it works now thanks for your help

                                    1 2 Previous Next