Detecting failure of Data Access Insert Cursor?

Question

I have data that I have now determined is failing because the underlying Oracle table has a unique constraint that fails. When a duplicate row is inserted there are no exceptions thrown - no way currently to know that the operation has failed.

How can I force some kind of feedback? I could do a select / search after doing the insert but that seems clumsy and slow.

    with arcpy.da.InsertCursor(remoteTable, fieldnames) as iCur:
    for val in pCur:
        sr = arcpy.SpatialReference(pval[16])
        pval[16] = arcpy.PointGeometry(arcpy.Point(float(pval[9]), float(pval[10])), sr)
        iCur.insertRow(pval)
    del iCur

If this list has a row that has a duplicate key, it fails and the whole with block fails - however I don't know that it has failed.

Answer 1

Using a UNIQUE constraint is not the best way to catch expected failures. INSERT operations are optimized for efficient operation, so the underlying OCI code (implemented via the ArcSDE DirectConnect DLL) uses array insertion with autocommit. When an error occurs, it's unlikely to be in a way that you have any idea how many features were inserted before the failure caused a rollback, making restart of the loading process exceptionally challenging.

While it is theoretically possible to sock down the commit interval to 1, doing so increases the time it takes to insert a thousand rows to the time it would normally take to insert a million.

My recommendation would be to what I did for a customer who needed to insert several thousand rows in a minute, when a commit interval of 1 resulted in 3-5 minutes to insert each minute of data: Cache the unique key within the app itself, and reject duplicates before INSERTing them (I got 3000 rows into the now ancient hardware in 3-4 seconds, including the 2 seconds to cache 50k rows of "existing" data)

Fortunately, you don't need to write your own cache object in 'C', because Python has both list and set support. All you need to do is load a cache with the existing objects, then test each new object for conflict before calling InsertCursor.insertRow(list). Using a list, which tested slightly faster than set for a million initial keys (1-1000000), this would look something like:

cache = []
for row in arcpy.da.SearchCursor(table, ["unique_field"]):
    cache.append(row[0])

sr = arcpy.SpatialReference(srCode)

cursor = arcpy.da.InsertCursor(table, all_fields) # specify "Shape@" for the geom col
i = 0
for data in dataParser():
    i += 1
    if (data[3] in cache):
        print("Skipping duplicate key '{:s}' in row {:d}...".format(data[3],i))
        continue
    cache.append(data[3])

    iRow = [data[0],...,
                arcpy.PointGeometry(arcpy.Point(float(data[9]), float(data[10])), sr)]
    cursor.insertRow(iRow)
del cursor
del cache

Note that your sample code has a number of issues:

1. The SpatialReference should be created ONCE, outside the loop, since it is not legal to have more than one SR in a table. If the data really is multi-reference, you'll need to add projection logic inside the loop.
2. The with construct is more suited to a SearchCursor, but if you use it, there shouldn't be a del afterward
3. You stripped a great deal of code out to make the example (and mangled the indentation), so it isn't usable by others