ArcPy Runtime error: Invalid SQL statement used

Question

I am trying to use a search cursor to read fields from a table, assign their values to variables, find the difference and print it. So far I keep getting a runtime error. I'll list my code and the error below. I'm using a virtual desktop which may make the path look odd.

import arcpy
fc = r'\\directory\userprofiles$\me\Documents\ArcGIS\Default.gdb\usPOPS'
arcpy.env.overwriteOutput = True
with arcpy.da.SearchCursor(fc, ['Resident population 1990', 'Resident population 2010']) as cursor:
 for row in cursor:
 pop1990 = row[0] 
 pop2010 = row[1] 
del cursor
popDiff = (pop1990 - pop2010)
print (popDiff)

Runtime error 
Traceback (most recent call last):
 File "<string>", line 12, in <module>
RuntimeError: An invalid SQL statement was used. [SELECT Resident population 1990, Resident population 2010, OBJECTID FROM usPOPS]

Any ideas? I've just been hitting dead ends.

• Vince: Spaces are not legal in column names. Use the actual column names.
• Hornbydd: 'Resident population 1990' & 'Resident population 2010' are not legal field names as @Vince says, they are your field aliases which do allow spaces. An alias is not the field name which is what the cursor needs. Also no need for del cursor as you create the cursor within a with block.
• Vince: You're also assuming exactly one row in the resulting result set. If there aren't any rows, you'll get an undefined variable error, and if multiple rows, only the last will be processed.
• Mike: @Vince Gotcha, I'll try and grab the actual names itself. Also, I'm not sure I understand what you mean by assuming exactly one row. Could you explain a bit further? Thank you for your help
• Mike: @Hornbydd Got it, I'll get the field name. With that do I just replace the aliases keeping them in between the single quotes?

Answer 1

It looks like you are using field alias not actual field names (as noted in the comments above). So something like Resident population 1990 might actually be ResidentPop1990. The SearchCursor line needs to have that as the field like in the example code below.

Also as Vince mentions in the comments "You're also assuming exactly one row ....if multiple rows, only the last will be processed"

He means since your popDiff = (pop1990 - pop2010) is outside the for statement the code only returns the difference for the last row processed. And if there are no records in the table at all the pop1990 and pop2010 variables never get defined so in that case you'd get an undefined variable error.

One final note your code also assume all the ResidentPop1990 and ResidentPop2010 are populated, that is they are not blank or NULL.

The code below is an example of what you might want it:

• has updated fields names (I just guessed at what they might be)

• checks if the the pop1990 and pop2010 are not None\Null...BUT it does assume the values in those fields are numbers

• for each row in the table prints the difference of the the two fields

Example Code

with arcpy.da.SearchCursor(fc, ['ResidentPop1990', 'ResidentPop2010']) as cursor:
 for row in cursor:
 pop1990 = row[0] 
 pop2010 = row[1] 
 if (pop1990 != None and pop2010 != None): 
 popDiff = (pop1990 - pop2010)
 print (popDiff)