Help with CalculateField using variable

MichaelHillig · ‎08-31-2013

I have been working on this on my own for some time & many frustrations, but it's time I ask for some help with my problem.

What I want to do is calculate two fields in my parcel lines data depending on the value of the 2 AOI's (polygons) on either side of
the parcel line. For example, if the polygon on the left of a line has an AOI value of "11111" and the polygon on the right of the line
has an AOI value of "22222", the two fields in my parcel line data would be calc'd as AOI = "11111" and SharedAOI = "22222".

I have been copying portions of my script into the python window of arcmap and everything seems to work with my search cursors,
select by attributes, and select by locations. The problem is with CalculateField. If I hardcode a value of "11111" & "22222", the
entire process works, but if I try using a variable for those values, I get an error. Can you please take a look and tell me what is
wrong. If I am going about this totally wrong, feel free to make suggestions. I am fairly new to python, so any help would be
greatly appreciated. Sorry about all the print statements and refreshes. Working on my own I keep trying to see how everything
processes.

myaoi = '69257'
fc = "AOI"

for eachaoi in aoilst:        #List of all the AOI's that surround my AOI = 69257
    sbaAOI = "AOI = '" + eachaoi + "'"
    print ("Current AOI selected = " + eachaoi)
    print sbaAOI
    arcpy.SelectLayerByAttribute_management("AOI","NEW_SELECTION",sbaAOI)
    arcpy.RefreshTOC()
    arcpy.RefreshActiveView()
    arcpy.SelectLayerByLocation_management("PLines","SHARE_A_LINE_SEGMENT_WITH","AOI")
    arcpy.RefreshTOC()
    arcpy.RefreshActiveView()
    with arcpy.da.SearchCursor(fc, "AOI") as cursor:
        for row in cursor:
            mysharedaoi = ("'" + row[0] + "'")
            print mysharedaoi
            if int(row[0]) < int(myaoi):
                print ("SharedAOI needs to be calc'd with myaoi")
                        #arcpy.CalculateField_management("PLines", "AOI",'99999')
                arcpy.CalculateField_management("PLines", "AOI", "mysharedaoi") 
                arcpy.CalculateField_management("PLines", "SharedAOI","myaoi")
                arcpy.RefreshTOC()
                arcpy.RefreshActiveView()
                print ("lines calculated")
            elif int(row[0]) > int(myaoi):
                print ("AOI needs to be calc'd with myaoi")
                arcpy.CalculateField_management("PLines", "AOI", "myaoi")
                        #arcpy.CalculateField_management("PLines", "SharedAOI",'99999')
                arcpy.CalculateField_management("PLines", "SharedAOI","mysharedaoi")
                arcpy.RefreshTOC()
                arcpy.RefreshActiveView()
                print ("lines calculated")

MichaelHillig · ‎09-01-2013

Kept working on the issue to try to simplify code. Saw no need to use cursor at all. However, the CalculateField still presents issues.

If I hardcode the values for AOI and SharedAOI within the PLines attribute table, the entire process works perfectly.

However, if I attempt to use a variable, ie: myaoi = '69257', mysharedaoi = 'value_from_current_selected_AOI', still resulting in the
values being shown as 'xxxxx', exactly like hardcoded values, then I will get an error everytime. It seems that each combination of quotes, double quotes, etc. will produce a different error. Sometimes an empty selection set, and sometimes can't acquire data lock.

#Hardcoded AOI's & SharedAOI's for CalculateField works as expected.

#This sequence uses selected aoi to grab all parcel lines sharing segment, then selects adjacent AOI's
#sharing segment with parcel lines.
arcpy.SelectLayerByLocation_management("PLines","SHARE_A_LINE_SEGMENT_WITH","AOI")
arcpy.SelectLayerByLocation_management("AOI","SHARE_A_LINE_SEGMENT_WITH","PLines")
aoilst = []
with arcpy.da.SearchCursor("AOI","AOI") as cursor:
    for row in cursor:
        print row[0]
        aoilst.append(row[0])

del cursor, row

myaoi = '69257'
fc = "AOI"

for eachaoi in aoilst:
    mysharedaoi = "'" + eachaoi + "'"
    print "mysharedaoi = " + mysharedaoi
    sbaAOI = "AOI = '" + eachaoi + "'"    
    print ("Current AOI selected = " + eachaoi)
    print sbaAOI
    arcpy.SelectLayerByAttribute_management("AOI","NEW_SELECTION",sbaAOI)
    arcpy.RefreshActiveView()
    arcpy.RefreshTOC()
    arcpy.SelectLayerByLocation_management("PLines","SHARE_A_LINE_SEGMENT_WITH","AOI")
    arcpy.RefreshActiveView()
    arcpy.RefreshTOC()
    if int(eachaoi) < int(myaoi):
        print ("SharedAOI needs to be calc'd with myaoi")
        #arcpy.CalculateField_management("PLines", "SharedAOI",myaoi)  Variables replaced with hardcoded values
        #arcpy.CalculateField_management("PLines", "AOI", mysharedaoi)  Variables replaced with hardcoded values
        arcpy.CalculateField_management("PLines", "SharedAOI",'69257')
        arcpy.CalculateField_management("PLines", "AOI", '99999')
        arcpy.RefreshTOC()
        arcpy.RefreshActiveView()
        print ("lines calculated")
    elif int(eachaoi) > int(myaoi):
        print ("AOI needs to be calc'd with myaoi")
        #arcpy.CalculateField_management("PLines", "AOI", myaoi)
        #arcpy.CalculateField_management("PLines", "SharedAOI", mysharedaoi)
        arcpy.CalculateField_management("PLines", "SharedAOI",'99999')
        arcpy.CalculateField_management("PLines", "AOI", '69257')
        arcpy.RefreshTOC()
        arcpy.RefreshActiveView()
        print ("lines calculated")

Once again, thank you for any assistance with this problem.

RichardFairhurst · ‎09-01-2013

Kept working on the issue to try to simplify code. Saw no need to use cursor at all. However, the CalculateField still presents issues.

If I hardcode the values for AOI and SharedAOI within the PLines attribute table, the entire process works perfectly.

However, if I attempt to use a variable, ie: myaoi = '69257', mysharedaoi = 'value_from_current_selected_AOI', still resulting in the
values being shown as 'xxxxx', exactly like hardcoded values, then I will get an error everytime. It seems that each combination of quotes, double quotes, etc. will produce a different error. Sometimes an empty selection set, and sometimes can't acquire data lock.

#Hardcoded AOI's & SharedAOI's for CalculateField works as expected.

#This sequence uses selected aoi to grab all parcel lines sharing segment, then selects adjacent AOI's
#sharing segment with parcel lines.
arcpy.SelectLayerByLocation_management("PLines","SHARE_A_LINE_SEGMENT_WITH","AOI")
arcpy.SelectLayerByLocation_management("AOI","SHARE_A_LINE_SEGMENT_WITH","PLines")
aoilst = []
with arcpy.da.SearchCursor("AOI","AOI") as cursor:
    for row in cursor:
        print row[0]
        aoilst.append(row[0])

del cursor, row

myaoi = '69257'
fc = "AOI"

for eachaoi in aoilst:
    mysharedaoi = "'" + eachaoi + "'"
    print "mysharedaoi = " + mysharedaoi
    sbaAOI = "AOI = '" + eachaoi + "'"    
    print ("Current AOI selected = " + eachaoi)
    print sbaAOI
    arcpy.SelectLayerByAttribute_management("AOI","NEW_SELECTION",sbaAOI)
    arcpy.RefreshActiveView()
    arcpy.RefreshTOC()
    arcpy.SelectLayerByLocation_management("PLines","SHARE_A_LINE_SEGMENT_WITH","AOI")
    arcpy.RefreshActiveView()
    arcpy.RefreshTOC()
    if int(eachaoi) < int(myaoi):
        print ("SharedAOI needs to be calc'd with myaoi")
        #arcpy.CalculateField_management("PLines", "SharedAOI",myaoi)  Variables replaced with hardcoded values
        #arcpy.CalculateField_management("PLines", "AOI", mysharedaoi)  Variables replaced with hardcoded values
        arcpy.CalculateField_management("PLines", "SharedAOI",'69257')
        arcpy.CalculateField_management("PLines", "AOI", '99999')
        arcpy.RefreshTOC()
        arcpy.RefreshActiveView()
        print ("lines calculated")
    elif int(eachaoi) > int(myaoi):
        print ("AOI needs to be calc'd with myaoi")
        #arcpy.CalculateField_management("PLines", "AOI", myaoi)
        #arcpy.CalculateField_management("PLines", "SharedAOI", mysharedaoi)
        arcpy.CalculateField_management("PLines", "SharedAOI",'99999')
        arcpy.CalculateField_management("PLines", "AOI", '69257')
        arcpy.RefreshTOC()
        arcpy.RefreshActiveView()
        print ("lines calculated")

Once again, thank you for any assistance with this problem.

At least part of your problem is that you are not specifying that your calculations are to be done using Python_9.3 as the Field Calculator parser. Therefore your calculations are defaulting to using VB Script instead, which only supports double quotes for strings. Specify that you want Python_9.3 as the parser so you can use Python syntax throughout your code. I.e.:

arcpy.CalculateField_management("PLines", "SharedAOI", mysharedaoi, "PYTHON_9.3")

The fact that you did not get an error with the hardcoded values is probably because the hardcoded '99999' is being sent to the calculator as a number, which VB will convert to a string. But it won't accept a string of '99999' itself, i.e., "'99999'" will fail. Your variable is passing in the latter value, not the former. The failure is due the the fact that the single quotes around a number is all VB sees, which is not a number or a string as far as VB is concerned. It is strictly an invalid entry to VB.

Anyway, only Python is agnostic about single and double quotes for strings, not VB Script, and in this case it would be best to eliminate VB Script from the workflow so you only need to follow Python rules.

You may still need to add some kind of test for NULL values if you think any exist, but your problems are probably due to SQL requiring single quoted strings, while VB wants double quotes strings, so nothing you do will satisfy both with a single value.

MichaelHillig · ‎09-01-2013

Thank you for your reply. After continuing to work on the problem, I discovered the same problem myself. So I changed all my
CalculateField lines adding the critical "PYTHON_9.3" to the end of my line. After a lot of testing, I can confirm that this fix was
exactly what was needed and it works perfectly.

RichardFairhurst · ‎09-01-2013

Thank you for your reply. After continuing to work on the problem, I discovered the same problem myself. So I changed all my
CalculateField lines adding the critical "PYTHON_9.3" to the end of my line. After a lot of testing, I can confirm that this fix was
exactly what was needed and it works perfectly.

Glad you fixed it, fellow Riv. Co. coworker. Have a happy Labor Day holiday.