Esri python arcpy.da.UpdateCursor - create cursor has failed (script error)

[HalWatson]

Hi,

I'm working with the arcpy.da module for the first time and just trying to get a handle on how it operates.  I have a PostgreSQL database with a simple table that includes these fields:  id, field1, field2, field3, field4. At this point the table is not registered with the geodatabase or SDE (I want to do simple unversioned table edits).  For the most part I've been trying to follow the ArcGIS 10.1 help examples.

I have been able to successfully use arpy.da.SearchCursor to list records.  This works: 

# List records using search cursor (snippet) table_to_select_from = r"/home/engine/scripts/connections/ARCSDE101-DEV1-PG-DC-GDRS_DEV-GDRS-DEFAULT.sde\gdrs_dev.gdrs.test_arcpy_da" field_list = ["id", "field1", "field2", "field3", "field4"] try:     with arcpy.da.SearchCursor(table_to_select_from, field_list) as search_cursor:         for row in search_cursor:             print("{0}, {1}, {2}, {3}, {4}".format(row[0], row[1], row[2], row[3], row[4])) except Exception as err:     print(err)     exit(1)

I can also successfully insert new records using arcpy.da.InsertCursor:

# Insert record (snippet): table_to_insert_into = r"/home/engine/scripts/connections/ARCSDE101-DEV1-PG-DC-GDRS_DEV-GDRS-DEFAULT.sde\gdrs_dev.gdrs.test_arcpy_da" field_list = ["field1", "field2", "field3", "field4"] values_list = ["Test arcpy.da.insertRow", "Hello World!", 10, "2013-03-23"] try:     with arcpy.da.InsertCursor(table_to_insert_into, field_list) as insert_cursor:         insert_cursor.insertRow(values_list) except Exception as err:     print(err)     exit(1)

So far so good.  However when I attempt to update rows, I cannot successfully create an update cursor.  The exception message: "create cursor has failed" is returned.  I'm sure this is something simple, but I've been banging my head on it and hope you can help point me in the right direction. In this example I attempt to multiply the value in an integer field by 2:

table_to_update = r"/home/engine/scripts/connections/ARCSDE101-DEV1-PG-DC-GDRS_DEV-GDRS-DEFAULT.sde\gdrs_dev.gdrs.test_arcpy_da" field_list = ["field3"] try:     with arcpy.da.UpdateCursor(table_to_update, field_list) as update_cursor:         for row in update_cursor:             row[0] = row[0] * 2             update_cursor.updateRow(row)         del row, update_cursor except Exception as err:     print(err)

the string object err which is printed is:  "create cursor has failed"

I tried placing this block inside an arcpy.da.Editor block, but no change in the error result.

Any thoughts?  Thank you!

[ChrisSnyder]

1. Are you sue there is no lock on the table when you were trying to run the update cursor (you aren't looking at it in ArcCatalog, etc. at the same time you are trying to run your update)?


2. Can you execute this line successfully: arcpy.da.UpdateCursor(table_to_update, ["*"]). If so, maybe it's an issue with your specified field name?

[HalWatson]

Thank you, this is helpful. 

1) I don't believe there are any locks on the table.  ArcMap, PgAdmin are both closed and I checked on the database cluster from the back end and there are no connections to this database prior to running the script.

2) Your code worked. 

3)  I definitely have a field called "field3" in my table.

Here's what I did to test your idea.  I commented out my code block and ran this instead:

try:
    arcpy.da.UpdateCursor(table_to_update, ["*"])
except Exception as err:
    print(err)

and did not get an error returned.

So then I tried reformatting that into a with block which is how I was doing it before:

try:
    with arcpy.da.UpdateCursor(table_to_update, ["*"]) as update_cursor:
        pass
except Exception as err:
    print(err)

Which also does not return an error.  Hurray!. So I thought I would step down one more level using ["*"] like you did.  I'm not sure using pass is OK here, but I thought it was worth trying:

    try:
        with arcpy.da.UpdateCursor(table_to_update, ["*"]) as update_cursor:
            for row in update_cursor:
                pass
    except Exception as err:
        print(err)

this block returns the error:  create cursor has failed

Just to check that I hadn't put a lock on the table somehow, I re-ran the 2nd block above again and it still got no error.

Thanks again, I'm learning.

[ChrisSnyder]

Not sure what's going on exactly, but some ideas that might help:

1. Ditch the "with" statement stuff... I think this was implemented by ESRI to reduce confusion about cursorsstill retaining a lock on the database upon failure. It's nice, but not critical... I do not use it... but maybe I should... Anyway the more simple "old way" syntax is then something like:

updateRows = arcpy.da.UpdateCursor(myTbl, ["MY_FIELD"])
for updateRow in updateRows:
    fieldValue = updateRow[0]
    field value = fieldValue + 10
    updateRows.updateRow(updateRow)
del updateRow, updateRows

2. This might be useful info: http://forums.arcgis.com/threads/70773-Row-objects-in-the-Data-Access-module?p=248106&viewfull=1#pos.... It presents a more "user friendly" way of setting/getting the field values (by field name and not directly by index). So the code above would then look like this:

updateRows = arcpy.da.UpdateCursor(myTbl, ["MY_FIELD"])
for updateRow in updateRows:
    fieldValue = updateRow[updateRows.fields.index("MY_FIELD")] 
    field value = fieldValue + 10
    updateRows.updateRow(updateRow)
del updateRow, updateRows

Hope some of this helps.

[HalWatson]

Great, l'll give it a try.  If someone else out there wants to advocate for the "with" block syntax, I'm curious.  In the meantime I'll test this approach.

[MathewCoyle]

The with statement is bugged currently. I'd avoid it for now.
http://forums.arcgis.com/threads/79647-Clarifying-when-cursor-locks-are-released?p=280350&viewfull=1...

[HalWatson · Accepted solution]

Just an "update"  😛  to close out this thread.  The 2nd round of suggested code did not work, so I figured the code approach was probably fine and something else completely was the problem.

Going in another direction, I decided to register my table with the enterprise geodatabase (SDE 10.1 over PostgreSQL).  After doing that, the update cursor works fine.  I did not register the table as versioned, so I am making unversioned edits directly to the base table, which is what I wanted in the first place.

What tripped me up was that insert cursors and search cursors did not require that the table be registered with the geodatabase, but it looks like update cursors are different, which is understandable.

Thanks very much for your suggestions, which had a lot of good code and hints that I took away and helped move me forward.

Hal

If anyone knows how to mark this thread as solved, feel free.