Invalid column value with arcpy.da.UpdateCursor

CCWeedcontrol · ‎12-18-2015

I have the following code and i am getting an error on line 76 with the arcpy.da.UpdateCursor that update feature attributes to proper case . I am unsure why. I would really appreciate some help please?

Thanks.

# Import arcpy module
import arcpy
from arcpy import env


# Allow overwrite 
arcpy.env.overwriteOutput = True

# Script user input parameters
polygonLayer = "TaxParcels1" #Taxparcels
pointLayer = "CCAP8" #Point Layer
arcpy.env.workspace = r"Database Servers\15_SQLEXPRESS.gds\CCAPTEST (VERSION:dbo.DEFAULT)" #arcpy.env.workspace = r"Database Servers\15_SQLEXPRESS.gds\TEST (VERSION:dbo.DEFAULT)"
poly = "ACCOUNT_1" 
Pnt =  "Account"
sjpoints = "In_memory\sjpoints" 

parcelsCount = int(arcpy.GetCount_management(pointLayer).getOutput(0))     
    
dsc = arcpy.Describe(pointLayer) 

selection_set = dsc.FIDSet         
if len(selection_set) == 0:       
    print "There are no features selected"  
             
elif parcelsCount >= 1: 

    #Run the Spatial Join tool, using the defaults for the join operation and join type
    arcpy.SpatialJoin_analysis(pointLayer, polygonLayer, sjpoints)

    # define the field list from the spatial join
    sourceFieldsList = ["TARGET_FID", poly,"SiteAddress",'SiteNum_1', 'SiteStreet_1','SiteNumSfx_1','Predir_1','SiteStreet_1','Postdir_1', 'SiteCity_1', 'SiteZIP_1', 'OwnerName_1', 'StreetType_1']    #,'StreetType_1'

    # define the field list to the original points
    updateFieldsList = ["OID@", Pnt,"SiteAddres", 'SiteNum', 'StreetName','SiteNumSfx','Predir','SiteStreet', 'Postdir', 'SiteCity', 'SiteZip', 'OwnerName', 'StreetType'] #, 'StreetType'
    
    # Start an edit session. Must provide the workspace.    
    edit = arcpy.da.Editor(arcpy.env.workspace)    
  
    # Edit session is started without an undo/redo stack for versioned data    
    #  (for second argument, use False for unversioned data)    
    edit.startEditing(True)    
  
    # Start an edit operation    
    edit.startOperation()    
    manualFields =  ["FacltyType","GIS_STEW", "StructType", "Verified", "Status", "StructCat", "APA_CODE",'StreetName'] #,'StreetName'
           
    with arcpy.da.UpdateCursor(pointLayer, manualFields) as rows:       
        for row in rows:       
            row[0] = ("Single Family Home")
            row[1] = ("Canyon")
            row[2] = ("Primary, Private")           
            row[3] = ("Yes, GRM, TA")           
            row[4] = ("Active")           
            row[5] = ("Residential")           
            row[6] = ("1110")
            row[7] = (sourceFieldsList[4] + " " + sourceFieldsList[12])
            rows.updateRow(row)  
        del row       
        del rows 
    # populate the dictionary from the polygon
    valueDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(sjpoints, sourceFieldsList)}    

    with arcpy.da.UpdateCursor(pointLayer, updateFieldsList) as updateRows:    
        for updateRow in updateRows:    
            keyValue = updateRow[0]
            if keyValue in valueDict:    
                for n in range (1,len(sourceFieldsList)):      
                    updateRow = valueDict[keyValue][n-1]
                updateRows.updateRow(updateRow)
        del updateRows
        del updateRow

    #Update feature attributes to proper case
    fields1 = ["StreetName", "SiteStreet","SiteAddres","OwnerName","StreetType"]#,'StreetType'
    with arcpy.da.UpdateCursor(pointLayer, fields1) as cursor:
        for row in cursor:
            row[0] = row[0].title()
            row[1] = row[1].title()
            row[2] = row[2].title()
            row[3] = row[3].title()
            row[4] = row[4].title()
            cursor.updateRow(row)
    # Stop the edit operation.    
    #edit.stopOperation()
    # Stop the edit session and save the changes
    edit.stopEditing(True)

arcpy.RefreshActiveView()
#arcpy.Delete_management(sjpoints)

CCWeedcontrol · ‎12-21-2015

I have never been able to get Editor to work without commenting out commenting out the stopOperation.

JoshuaBixby · ‎12-21-2015

As the documentation states, calling stopEditing should only be done after an edit operation has been stopped, undone, or aborted. What is the error message when you don't comment out the line?

CCWeedcontrol · ‎12-21-2015

I get the following error

Runtime error

Traceback (most recent call last):

File "<string>", line 74, in <module>

SystemError: error return without exception set

With the following code

I also attached some data in case anyone wants to look at it.

# Import arcpy module  
import arcpy  
from arcpy import env  
  
  
# Allow overwrite   
arcpy.env.overwriteOutput = True  
  
# Script user input parameters  
polygonLayer = "DSD.DBO.TaxParcels1" #Taxparcels  
pointLayer = "CCAPTEST.DBO.CCAP5" #Point Layer  
arcpy.env.workspace = r"Database Servers\DSD15_SQLEXPRESS.gds\CCAPTEST (VERSION:dbo.DEFAULT)" #arcpy.env.workspace = r"Database Servers\DSD15_SQLEXPRESS.gds\TEST (VERSION:dbo.DEFAULT)"  
poly = "ACCOUNT_1"   
Pnt =  "Account"  
sjpoints = "In_memory\sjpoints"   
  
parcelsCount = int(arcpy.GetCount_management(pointLayer).getOutput(0))       
      
dsc = arcpy.Describe(pointLayer)   
  
selection_set = dsc.FIDSet           
if len(selection_set) == 0:         
    print "There are no features selected"    
               
elif parcelsCount >= 1:   
  
    #Run the Spatial Join tool, using the defaults for the join operation and join type  
    arcpy.SpatialJoin_analysis(pointLayer, polygonLayer, sjpoints)  
  
    # define the field list from the spatial join  
    sourceFieldsList = ["TARGET_FID", poly,"SiteAddress",'SiteNum_1', 'SiteStreet_1','SiteNumSfx_1','Predir_1','SiteStreet_1','Postdir_1', 'SiteCity_1', 'SiteZIP_1', 'OwnerName_1', 'StreetType_1']    #,'StreetType_1'  
  
    # define the field list to the original points  
    updateFieldsList = ["OID@", Pnt,"SiteAddres", 'SiteNum', 'StreetName','SiteNumSfx','Predir','SiteStreet', 'Postdir', 'SiteCity', 'SiteZip', 'OwnerName', 'StreetType'] #, 'StreetType'  
      
    # Start an edit session. Must provide the workspace.      
    edit = arcpy.da.Editor(arcpy.env.workspace)      
    
    # Edit session is started without an undo/redo stack for versioned data      
    #  (for second argument, use False for unversioned data)      
    edit.startEditing(True)      
    
    # Start an edit operation      
    edit.startOperation()      
    manualFields =  ["FacltyType","GIS_STEW", "StructType", "Verified", "Status", "StructCat", "APA_CODE",'StreetName'] #,'StreetName'  
             
    with arcpy.da.UpdateCursor(pointLayer, manualFields) as rows:         
        for row in rows:         
            row[0] = ("Single Family Home")  
            row[1] = ("Canyon")  
            row[2] = ("Primary, Private")             
            row[3] = ("Yes, GRM, TA")             
            row[4] = ("Active")             
            row[5] = ("Residential")             
            row[6] = ("1110")  
            row[7] = (sourceFieldsList[4] + " " + sourceFieldsList[12])  
            rows.updateRow(row)    
        del row         
        del rows   
    # populate the dictionary from the polygon  
    valueDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(sjpoints, sourceFieldsList)}      
  
    with arcpy.da.UpdateCursor(pointLayer, updateFieldsList) as updateRows:      
        for updateRow in updateRows:      
            keyValue = updateRow[0]  
            if keyValue in valueDict:      
                for n in range (1,len(sourceFieldsList)):        
                    updateRow = valueDict[keyValue][n-1]  
                updateRows.updateRow(updateRow)  
        del updateRows  
        del updateRow  

    # Stop the edit operation.      
    edit.stopOperation()  
    # Stop the edit session and save the changes  
    edit.stopEditing(True)  
  
arcpy.RefreshActiveView()  
#arcpy.Delete_management(sjpoints)

DanPatterson_Retired · ‎12-21-2015

print statements please...

ie between 66 and 66 put. print("key value".format(keyValue))

in fact put a bunch in to see what you are getting. If it doesn't even get to a print statement, then the problem is before it.

CCWeedcontrol · ‎12-22-2015

To try to narrow it down i scaled the code down just to the spatial join.

I ran the following code and got this.

set([u'RD  '])
key value
key value
key value
key value
key value
key value
key value
key value
key value
key value
key value
key value
key value
Runtime error 
Traceback (most recent call last):
  File "<string>", line 69, in <module>
RuntimeError: Invalid column value [StreetType]

Code:

# Import arcpy module
import arcpy
from arcpy import env


# Allow overwrite 
arcpy.env.overwriteOutput = True

# Script user input parameters
polygonLayer = "DSD.DBO.TaxParcels1" #Taxparcels
pointLayer = "CCAPTEST.DBO.CCAP5" #Point Layer
arcpy.env.workspace = r"Database Servers\DSD15_SQLEXPRESS.gds\CCAPTEST (VERSION:dbo.DEFAULT)" #arcpy.env.workspace = r"Database Servers\DSD15_SQLEXPRESS.gds\TEST (VERSION:dbo.DEFAULT)"
poly = "ACCOUNT_1" 
Pnt =  "Account"
sjpoints = "In_memory\sjpoints" 

parcelsCount = int(arcpy.GetCount_management(pointLayer).getOutput(0))     
    
dsc = arcpy.Describe(pointLayer) 

selection_set = dsc.FIDSet         
if len(selection_set) == 0:       
    print "There are no features selected"  
             
elif parcelsCount >= 1: 

    #Run the Spatial Join tool, using the defaults for the join operation and join type
    arcpy.SpatialJoin_analysis(pointLayer, polygonLayer, sjpoints)

    # define the field list from the spatial join
    sourceFieldsList = ['TARGET_FID', poly,'SiteAddress','SiteNum_1', 'SiteStreet_1','SiteNumSfx_1','Predir_1','SiteStreet_1', 'Postdir_1', 'SiteCity_1', 'SiteZIP_1', 'OwnerName_1','StreetType_1']    #,'StreetType_1'

    # define the field list to the original points
    updateFieldsList = ['OID@', Pnt,'SiteAddres', 'SiteNum', 'StreetName', 'SiteNumSfx','Predir','SiteStreet', 'Postdir', 'SiteCity', 'SiteZip', 'OwnerName','StreetType'] #, 'StreetType'

    field = 'StreetType'
    values = [row[0] for row in arcpy.da.SearchCursor(pointLayer, (field))]
    uniqueValues = set(values)
    print(uniqueValues)
            
    # Start an edit session. Must provide the workspace.    
    edit = arcpy.da.Editor(arcpy.env.workspace)    
  
    # Edit session is started without an undo/redo stack for versioned data    
    #  (for second argument, use False for unversioned data)    
    edit.startEditing(True)    
  
    # Start an edit operation    
    edit.startOperation()    
     
    # populate the dictionary from the polygon
    valueDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(sjpoints, sourceFieldsList)}    

    with arcpy.da.UpdateCursor(pointLayer, updateFieldsList) as updateRows:    
        for updateRow in updateRows:    
            keyValue = updateRow[0]
            if keyValue in valueDict:
                print("key value".format(keyValue))
                for n in range (1,len(sourceFieldsList)):      
                    updateRow = valueDict[keyValue][n-1]
                    print("key value".format(keyValue))
                updateRows.updateRow(updateRow)
        del updateRows
        del updateRow
  
    # Stop the edit operation.    
    #edit.stopOperation()
    # Stop the edit session and save the changes
    edit.stopEditing(True)

arcpy.RefreshActiveView()
#arcpy.Delete_management(sjpoints)

RebeccaStrauch__GISP · ‎12-21-2015

I'm not sure if this could cause issues or not, but I notices you have quite a mix of single and double quotes. Is there a reason? Can you set them to be one or the other, at least within a particular list?

# define the field list from the spatial join    
    sourceFieldsList = ["TARGET_FID", poly,"SiteAddress",'SiteNum_1', 'SiteStreet_1','SiteNumSfx_1','Predir_1','SiteStreet_1','Postdir_1', 'SiteCity_1', 'SiteZIP_1', 'OwnerName_1', 'StreetType_1']    #,'StreetType_1'    
# define the field list to the original points    
    updateFieldsList = ["OID@", Pnt,"SiteAddres", 'SiteNum', 'StreetName','SiteNumSfx','Predir','SiteStreet', 'Postdir', 'SiteCity', 'SiteZip', 'OwnerName', 'StreetType'] #, 'StreetType'

CCWeedcontrol · ‎12-22-2015

Ya i knew it wasn't clean, been messing with it for a bit. i cleaned her up. Still not able to get past the Invalid column value [StreetType]

Thanks.

DanPatterson_Retired · ‎12-22-2015

oops my bad

print("key value {}".format(keyValue)) forgot the { } in the line. but at least you know one is bad

CCWeedcontrol · ‎12-22-2015

Looks like the problem was that the tax parcels "StreetType" field had 5 spaces after the text (Rd).

by putting in the print statement.

field = 'StreetType'
    values = [row[0] for row in arcpy.da.SearchCursor(pointLayer, (field))]
    uniqueValues = set(values)
    print(uniqueValues)

it printed

set([u'RD

'])

After removing the spaces the code runs without an error but the only other issue is that the StreetName field is not being populated with line

row[7] = (sourceFieldsList[4] + " " + sourceFieldsList[12])

CCWeedcontrol · ‎12-22-2015

The following code

# Import arcpy module
import arcpy
from arcpy import env


# Allow overwrite 
arcpy.env.overwriteOutput = True

# Script user input parameters
polygonLayer = "DSD.DBO.TaxParcels1" #Taxparcels
pointLayer = "CCAPTEST.DBO.CCAP5" #Point Layer
arcpy.env.workspace = r"Database Servers\DSD15_SQLEXPRESS.gds\CCAPTEST (VERSION:dbo.DEFAULT)" #arcpy.env.workspace = r"Database Servers\DSD15_SQLEXPRESS.gds\TEST (VERSION:dbo.DEFAULT)"
poly = "ACCOUNT_1" 
Pnt =  "Account"
sjpoints = "In_memory\sjpoints" 

parcelsCount = int(arcpy.GetCount_management(pointLayer).getOutput(0))     
    
dsc = arcpy.Describe(pointLayer) 

selection_set = dsc.FIDSet         
if len(selection_set) == 0:       
    print "There are no features selected"  
             
elif parcelsCount >= 1: 

    #Run the Spatial Join tool, using the defaults for the join operation and join type
    arcpy.SpatialJoin_analysis(pointLayer, polygonLayer, sjpoints)

    # define the field list from the spatial join
    sourceFieldsList = ['TARGET_FID', poly,'SiteAddress','SiteNum_1', 'SiteStreet_1','SiteNumSfx_1','Predir_1','SiteStreet_1', 'Postdir_1', 'SiteCity_1', 'SiteZIP_1', 'OwnerName_1','StreetType_1']    #,'StreetType_1'

    # define the field list to the original points
    updateFieldsList = ['OID@', Pnt,'SiteAddres', 'SiteNum', 'StreetName', 'SiteNumSfx','Predir','SiteStreet', 'Postdir', 'SiteCity', 'SiteZip', 'OwnerName','StreetType'] #, 'StreetType'

    field = 'StreetType'
    values = [row[0] for row in arcpy.da.SearchCursor(pointLayer, (field))]
    uniqueValues = set(values)
    print(uniqueValues)
            
    # Start an edit session. Must provide the workspace.    
    edit = arcpy.da.Editor(arcpy.env.workspace)    
  
    # Edit session is started without an undo/redo stack for versioned data    
    #  (for second argument, use False for unversioned data)    
    edit.startEditing(True)    
  
    # Start an edit operation    
    edit.startOperation()    
     
    # populate the dictionary from the polygon
    valueDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(sjpoints, sourceFieldsList)}    

    with arcpy.da.UpdateCursor(pointLayer, updateFieldsList) as updateRows:    
        for updateRow in updateRows:    
            keyValue = updateRow[0]
            if keyValue in valueDict:
                print("key value".format(keyValue))
                for n in range (1,len(sourceFieldsList)):      
                    updateRow = valueDict[keyValue][n-1]
                    print("key value".format(keyValue))
                updateRows.updateRow(updateRow)
        del updateRows
        del updateRow

    manualFields =  ['FacltyType','GIS_STEW', 'StructType', 'Verified', 'Status', 'StructCat', 'APA_CODE','StreetName'] #,'StreetName'
           
    with arcpy.da.UpdateCursor(pointLayer, manualFields) as rows:       
        for row in rows:       
            row[0] = ("Single Family Home")
            row[1] = ("Canyon")
            row[2] = ("Primary, Private")           
            row[3] = ("Yes, GRM, TA")           
            row[4] = ("Active")           
            row[5] = ("Residential")           
            row[6] = ("1110")
            row[7] = (sourceFieldsList[4] + " " + sourceFieldsList[12])
            rows.updateRow(row)  
        del row       
        del rows 
    # populate the dictionary from the polygon
    valueDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(sjpoints, sourceFieldsList)}    

    with arcpy.da.UpdateCursor(pointLayer, updateFieldsList) as updateRows:    
        for updateRow in updateRows:    
            keyValue = updateRow[0]
            if keyValue in valueDict:
                print("key value {}".format(keyValue))
                for n in range (1,len(sourceFieldsList)):      
                    updateRow = valueDict[keyValue][n-1]
                updateRows.updateRow(updateRow)
        del updateRows
        del updateRow

    #Update feature attributes to proper case
    fields1 = ['StreetName', 'SiteStreet','SiteAddres','OwnerName','StreetName']
    with arcpy.da.UpdateCursor(pointLayer, fields1) as cursor:
        for row in cursor:
            row[0] = row[0].title()
            row[1] = row[1].title()
            row[2] = row[2].title()
            row[3] = row[3].title()
            row[4] = row[4].title()
            cursor.updateRow(row)
            
    # Stop the edit operation.    
    edit.stopOperation()
    # Stop the edit session and save the changes
    edit.stopEditing(True)

arcpy.RefreshActiveView()
#arcpy.Delete_management(sjpoints)

Give me

set([None])
key value
key value
key value
key value
key value
key value
key value
key value
key value
key value
key value
key value
key value
key value 6408

6408 is the points features ObjectID that i have selected to update.

After removing the spaces the code runs without an error but the only other issue is that the StreetName field is not being populated with line

row[7] = (sourceFieldsList[4] + " " + sourceFieldsList[12]). So i guess i am still having issues with the StreetType except for the fact that it doesn't give an error?