2

I'm trying to make a bulk insert of a CSV file with Python and cx_Oracle into an st_geometry enabled table of an Oracle database with ArcSDE.

I could do it with the method execute() of cx_Oracle but for big files is not the faster approach. That's why I'm trying to make the method executemany() work. With executemany() I'm able to insert registers with strings, integers,... but not st_geometry fields. With st_geometry fields I get the error:      cx_Oracle.DatabaseError ORA-01036: illegal variable name/number

With that code "print cursor.bindnames()" returns ['1','2'], what it means that :3 y :4 are not recognized. Just for the sake of the argument, if I modify st_geometry('point(:3 :4)',0) to st_geometry(point(:3 :4),0) "print cursor.bindnames()" returns ['1','2','3'], what it means that :4 is not recognized.

This is the sample code I'm using right now:


Workaround found, see next answer


# Bulk insert
#
# Sample test st_geometry table
# create table test (col1 varchar2(30 byte), col2 varchar2(30 byte), shape sde.st_geometry);
#
# Sample sql insert to insert data into the table
# insert into test (col1, col2, shape) values ('val01', 'val02', sde.st_geometry('point(12.67 2.32)', 0));
#
# Sample file.csv
# val11,val12,34.22,23.22
# val21,val22,12.33,14.22
# val31,val32,13.21,18.32
#

import cx_Oracle

dsn_tns = cx_Oracle.makedsn('server', 1521, 'sid')
dbCon = cx_Oracle.connect('user', 'password', dsn_tns)
cursor = dbCon.cursor()

list = []
f = open('file.csv','r')

# srid must be a valid code in sde.st_spatial_references
cursor.prepare("""INSERT INTO test (col1,col2,shape) VALUES (:1,:2,sde.st_geometry('point(:3 :4)',0))""")
for line in f:
    pair = line.split(',')
    list.append((pair[0],pair[1],float(pair[2]),float(pair[3])))

print cursor.bindnames()
cursor.executemany(None, list)
dbCon.commit()
f.close()
cursor.close()
dbCon.close()
  • 1
    Your bind variables are not parsed inside a string. Format the geometry string separately, and bind that. Then you can go back and use a valid SRID (which will be your next issue) – Vince Dec 5 '14 at 14:55
  • Vince, thank you very much for pointing me out about de lack of SRID. I'm aware of that, this is only a test table and everybody should be careful defining SRID for every layer. Appart from this, I'm a GIS guy more than a programmer and I don't quite understand what you mean. Please, if you could give me an example I'd be really grateful. Anyway I'm concerned because, like I said in the message, when I remove the character ' inside st_geometry(), bind variable :4 is not recognized by cursor.bindnames(). Thanks in advance – Santiago Horcajo Dec 5 '14 at 16:04
  • 2
    VALUES (:1,:2,sde.ST_GeomFromText(:3,4326)) – Vince Dec 5 '14 at 16:58
  • Thank you Vince, I couldn't make your solution work (invalid argument type for ST_GeomFromText), but I found a workaround: cursor.prepare("""...VALUES(:1,:2,sde.st_geometry(:3,:4,0,0,srid)"""), which you can find in here, where you can read it's recommended when performing batch inserts of large numbers of point data. In this case srid must a valid code in sde.st_spatial_references. I proceed to edit the initial post. – Santiago Horcajo Dec 9 '14 at 9:45
  • I don't have now this need, but what if I want to bulk insert lines or polygons? – Santiago Horcajo Dec 9 '14 at 9:53
3

I found another way to bulk insert points into ArcSDE geodatabase. It's based on another way to define points presented by ESRI here, sde.st_geometry (x,y,z,m,srid), which is recommended when performing batch inserts of large numbers of point data.


# Bulk insert
#
# Sample test st_geometry table
# create table test (col1 varchar2(30 byte), col2 varchar2(30 byte), shape sde.st_geometry);
#
# Sample sql insert to insert data into the table
# insert into test (col1, col2, shape) values ('val01', 'val02', sde.st_geometry('point(12.67 2.32)', 0));
#
# Sample file.csv
# val11,val12,34.22,23.22
# val21,val22,12.33,14.22
# val31,val32,13.21,18.32
#

import cx_Oracle

dsn_tns = cx_Oracle.makedsn('server', 1521, 'sid')
dbCon = cx_Oracle.connect('user', 'password', dsn_tns)
cursor = dbCon.cursor()

list = []
f = open('file.csv','r')

# srid must be a valid code in sde.st_spatial_references
cursor.prepare("""INSERT INTO test (col1,col2,shape) VALUES (:1,:2,sde.st_geometry(:3,:4,null,null,srid))""")
for line in f:
    pair = line.split(',')
    list.append((pair[0],pair[1],float(pair[2]),float(pair[3])))

print cursor.bindnames()
cursor.executemany(None, list)
dbCon.commit()
f.close()
cursor.close()
dbCon.close()
|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.