Insert SDO_GEOMETRY in cx_oracle

Question

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 
cur.execute(insert)

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.

Answer 1

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)