I'm trying to populate a field based on the values of another field. CalculateField was buggy, so I switched to da.UpdateCursor. This initially worked when I ran it as part of a script tool on ArcGIS Pro 2.4.3, but when I returned to this project after putting it aside for a few weeks and then making a few minor modifications to my code, ArcGIS Pro gave me an error:

 Traceback (most recent call last):
 File "Y:\AGUSPDB\States Procedures\Normalizer.py", line 126, in <module>
      for value in APN_Update:
 RuntimeError: An invalid SQL statement was used. [SELECT APN, Y:\AGUSPDB\03 States Compiled\Compiled_Ashley_AR_20_Parcels.gdb\Ashley_AR_Parcels, OBJECTID, Shape_Area, Shape_Length FROM Ashley_AR_Parcels]
 Failed to execute (Normalizer).

Incidentally, this code works in the Python IDLE shell, so I suspect the issue may have something to do with ArcGIS Pro. The offending code is prefaced with the comment "Problem begins here". Because the feature class's file path and other fields I never coded somehow ended up in the UpdateCursor SQL statement, I've added the a few other steps for context.

import arcpy

#Joins a standardized feature class template to the feature class the user loads.
FC_toNormalize = arcpy.SpatialJoin_analysis(inFeatures, normalizedTemplate, filepath,\

#Create list of fields to reverse-index from the template's attribute table
fields = [f.name for f in arcpy.ListFields(FC_toNormalize)]

#Problem begins here. fields[-13] is called "APN".
with arcpy.da.UpdateCursor(FC_toNormalize, [str(fields[-13]), "userSelected_field"]) as APN_Update:
    for value in APN_Update:
        value[0] = value[1]

As for the minor modifications I made to my code before re-running the tool, they shouldn't have caused problems, but here they are:

  • Renamed a few variables
  • Reorganized the placement of some GetParameterAsText functions within the code (but not the index positions)
  • Added a new function a few lines below the for loop in which this error occurs

Even though the code works in the Python shell, I'll obviously have to modify it so ArcGIS Pro to digest it. Any thoughts?

  • 4
    Referencing fields by hard-coded index is a recipe for disaster. The shape_length and shape_area fields are read-only, and shouldn't be in an Update. You seem to have a Windows path in the SQL statement, which is generating the error. – Vince Jan 3 '20 at 23:10
  • Unfortunately, referencing fields by hard-coded index is a necessary evil in this tool (as far as I can tell, anyway). At one point, the tool joins a standardized feature class to a feature class the user inputs. One of the fields in the standardized feature class is what's being populated here. In order to avoid having the user select the names of the fields being populated, I hard-coded the index, bearing in mind shape_length and shape_area. I can't hard code the field names, because in many cases the user's input feature class contains the same field names as the standardized feature class. – Geomni GIS Jan 3 '20 at 23:22
  • 1
    Can you please post your error messages as text so they are searchable? Where is the file path that is show in the SQL statement coming from? Is that set in a variable or parameter somewhere? You may want to post a bit more of your code. The snippet you posted looks correct, but I have no idea what kind of variable APN_input is, or how you created your fields variable. – jbalk Jan 4 '20 at 0:00
  • Please always provide errors as formatted text rather than pictures. For questions that involve code we ask that you show us where you are stuck with your own code by including a code snippet in your question. At the moment you seem to be providing just a copy/paste of a few lines from your code. – PolyGeo Jan 4 '20 at 0:01
  • 1
    We also need diagnostics on the exact contents of your input parameters. The exact Pro release is also needed. – Vince Jan 4 '20 at 0:06

Thanks everybody for your feedback. After some experimentation, I discovered the root of the problem. It appears to be an ArcPy error.

In my original script, I gathered the parameters and declared them as variables at the beginning of the script:

import arcpy, re

inFeatures = arcpy.GetParameterAsText(0)
State = re.sub('[\W_]+', '', arcpy.GetParameterAsText(1))
County = re.sub('[\W_]+', '', arcpy.GetParameterAsText(2))
Updated_Year = arcpy.GetParameterAsText(3)
Updated_Month = arcpy.GetParameterAsText(4)
APN_input = arcpy.GetParameterAsText(5)

#Rest of script goes here

When I reorganized the placement of the GetParameterAsText functions, I placed them closer to the code that used these parameters--something I figured would make the script easier to read and maintain. Well, it turns out that all the parameters declared after arcpy.AddGeometryAttributes_management(FC_toNormalize, "AREA_GEODESIC", "#", "ACRES", projection) were corrupted by the AddGeometryAttributes function and re-declared as the feature class's file path and as projection information. When I moved the GetParameterAsText functions back to the beginning of the script, the problem was resolved.

  • I'm glad you found a solution. However, I suspect that there's more to the problem than simply where in the code those get parameter lines were, and that it is probably not simply a case of their values being corrupted by the add geometry line. It's almost impossible to diagnose further though, without the complete script (both before and after versions). – Son of a Beach Jan 6 '20 at 22:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.