I have many polygons to insert. This code is ready to use.

geom_str = '"MDSYS"."SDO_GEOMETRY"(2003,NULL,NULL,"MDSYS"."SDO_ELEM_INFO_ARRAY"(1,1003,1),"MDSYS"."SDO_ORDINATE_ARRAY"(-735342,-1010550,-735347,-1010551,-735347,-1010563,-735342,-1010563,-735342,-1010550))'
insert = u'''INSERT INTO poly_gr (geomtype, sdo_geom ) VALUES ("TARGET"."LIDS_GEOMTYPE"(1002,6) ,%s)''' %geom_str 

But this is vary lazy. I would like use variables in execute statement. Like this

insert = u'''INSERT INTO poly_gr (geomtype, sdo_geom ) VALUES (:1 ,:2)''' 
cur.execute(insert, ('"TARGET"."LIDS_GEOMTYPE"(1002,6)', geom_str)

Do you know how can I insert geometry or own datatype variable with cx_Oracle module? I would like use executemany() in next step for more powerfull aplication.


This is now available in the code checked in to the source repository (and will be made available in 5.3 when it is released):

connection = cx_Oracle.Connection("user/pw@tns")
typeObj = connection.gettype("SDO_GEOMETRY")
elementInfoTypeObj = connection.gettype("SDO_ELEM_INFO_ARRAY")
ordinateTypeObj = connection.gettype("SDO_ORDINATE_ARRAY")
obj = typeObj.newobject()
obj.SDO_GTYPE = 2003
obj.SDO_ELEM_INFO = elementInfoTypeObj.newobject()
obj.SDO_ELEM_INFO.extend([1, 1003, 3])
obj.SDO_ORDINATES = ordinateTypeObj.newobject()
obj.SDO_ORDINATES.extend([1, 1, 5, 7])

cursor = connection.cursor()
cursor.execute("insert into TestGeometry values (1, :obj)", obj = obj)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.