Update Shape@X and Shape@Y for select records using updatecursor in ArcPy w/ version

[williamChappell1]

ArcMap 10.6 Python, 

I have a list of streetlight that I need ti update their location, these are stored in a text file, Pole#, X, Y.  I iterate through the list and pass a SQ:L expression for the current pole number, and then hit an UpDateCursor. However I keep getting an error return without exception. I am using SDE with a version defined in the connection file. I've tested the SQL query and it returns records, and had a print for the row's with correct values, but the cursor.update(row) seems to fail. I have correct permissions to the SDE database for editing. Any Ideas,

import arcpy

fn = "LightCoords.txt"
'''
# Example of what's in the file.
P67298, 635610.433203, 970018.33444
P63082, 670047.572717, 1056312.84006
165131, 668496.444993, 1056333.22828
'''

# Open the file that has pole, X, Y and put into a list
slights = []

with open(fn) as file:
  for item in file:
    slights.append(item.split(','))
    
# SDE Connection
SDEConn ='ELECTEST.sde'

arcpy.env.workspace = SDEConn 
lights = "Streetlight"

# Process: Start Edit
edit = arcpy.da.Editor(SDEConn )

edit.startEditing(False, True) # SystemError: error return without exception set #

edit.startOperation()

fields = ['PoleNUMBER', 'SHAPE@X', 'SHAPE@Y']

# Iterate through the list of lights	
for i in slights:
    a = (i[0].strip())    # Pole
    b = i[1]    # X
    c = i[2]    # Y

    # create a valid where clause
    sql = """{0} = '{1}' """.format(arcpy.AddFieldDelimiters(lights, 'PoleNUMBER'), a)

    try:
        # Update X, Y coordinates using an update cursor
        with arcpy.da.UpdateCursor(lights, ['PoleNUMBER', 'SHAPE@X', 'SHAPE@Y'], where_clause=sql) as cursor:
            for row in cursor:
                # Update X, Y values here
                row[1]= float(b)
                row[2]= float(c)
                cursor.updateRow(row)

    except Exception as e:
            print("An error occurred: {}".format(e))
       
   
edit.stopOperation()

# Commit edit to table
edit.stopEditing(True)

print("Done")

[rzufelt]

Not time to test, but I have code that does pretty much the same thing, except I edit the location in one feature class, and 'update' the target feature class with the geometries rather than from a text file.  But, should be able to modify easily to accommodate.  Just create the dictionary from the text file.

import arcpy

# this is the dataset I have modified/fixed the locations of for input to the searchcursor.

sourcefc = r'\\server\GISData\PW_GIS_Users\streetlightinventory.gdb\FuturaLightsUpdated'

sourceFieldsList = ["GUID","SHAPE@", "Checked" ]

# Use list comprehension to build a dictionary from a da SearchCursor
# GUID as key, geometry object as valueDict

tmpval = 'Yes'

expression =  f'"Checked" = "{tmpval}"'

valueDict = {r[0]: (r[1:]) for r in arcpy.da.SearchCursor(sourcefc, sourceFieldsList,expression)}

################################################################################
##  The FC that gets updated with new point locations
################################################################################

updatefc = r'D:\Data\Connections\GIS.sde\GIS.DBO.ElectricComponents\GIS.DBO.Light'


updateFieldsList = ["GUID","SHAPE@" ]
edit = arcpy.da.Editor(r'D:\Data\Connections\GIS.sde')

with arcpy.da.UpdateCursor(updatefc, updateFieldsList) as updateRows:
    edit.startEditing(True, True)
    edit.startOperation()
    for updateRow in updateRows:
        # store the GUID in a keyValue variable
        keyValue = updateRow[0]
        # verify that the keyValue is in the Dictionary
        if keyValue in valueDict:
            # transfer the geometry obj from dictionary to Lights FC.
            updateRow[1] = valueDict[keyValue][0]
            updateRows.updateRow(updateRow)
    edit.stopOperation()
edit.stopEditing(True)

Of course, my script uses a Geometry object (SHAPE@) so would have to modify the fields/input or create a geometry object from the coordinates first.

R_

[williamChappell1]

Almost, it was fairly easy to modify my code to use the dictionary, and you expression format, however I get the error on line 38, where the cursor updates the row,  updateRows.updateRow(updateRow)
SystemError: error return without exception set.  I used the geometry object like you did instead of the X,Y values but still have the same error.

[AlfredBaldenweck · Accepted solution]

Update Cursors and versioned feature classes really don't mix.

You either have to open an edit session in arcpy (gross) or do the easier thing, which is to go through with a search cursor, create a dictionary of correct values, and then field calculate at the very end.

[rzufelt]

I believe your issue is in the sql statement.

I replaced it with the following, and it is working as expected.

sql =  "PointID = '{0}'".format(a)  # python 2

sql =  f"PoleNUMBER = '{a}'"   # python 3

R_

[williamChappell1]

I hate to say it, I've mimicked working examples, even ones I've written in the past. I've played with all the start editing options, but when the cursor is hitting the update row it bugs out. either error without exception set, or workspace already in transaction mode. I've tried different types of SQL expressions; I get the data if I print it, but it fails on the update cursor row. Time to just do a massive field calculate.

[RhettZufelt]

That is weird.  I re-created your text input file, and used your code except for the modified sql statement and field names, and it is working as expected.

Upon further testing, I get the already in transaction mode if the featureclass is not versioned, then I just comment out the edit statements and it works.

As is, it seems to work fine for versioned, moving edits to base.  However, if I use traditional versioning, the script finishes with no errors, but the changes are not made in the featureclass until I compress the database and roll those edits into the default version.

R_