ArcPy cursor: RuntimeError: An invalid SQL statement was used

Question

I'm trying to iterate through polygons in a feature class to perform zonal statistics on a raster mosaic.

I found inspiration from Iterate through features to use selection as input for ExtractByMask for my code but I get a "RuntimeError: An invalid SQL statement was used. [SELECT OBJECTID, Shape, Group FROM GROUP_RINGS_prj]" message.

Here is the code I use:

import arcpy, datetime
from arcpy import env
from arcpy.sa import *
arcpy.env.overwriteOutput = True
arcpy.CheckOutExtension("Spatial")
env.workspace = "D:/PROJECT.gdb"
aoi_prj = "GROUP_RINGS_prj"
forest_prj = "FOREST_prj"
## Stats by group
with arcpy.da.SearchCursor(aoi_prj, ["OID@", "SHAPE@", "Group"]) as cursor:
 for row in cursor:
 print(datetime.datetime.now().strftime("%Y-%m-%d %H:%M") + ": Processing: " + str(row[2]) + "...")
 zs = ZonalStatisticsAsTable(row[1], "Group", forest_prj, "D:/Projects/Results/ZS_FOREST_" + str(row[0]) + ".dbf", "NODATA", "MEAN")
 row = cursor.next()

It results with:

Traceback (most recent call last):
 File "D:\Projects\Scripts.py", line 87, in <module>
 for row in cursor:
RuntimeError: An invalid SQL statement was used. [SELECT OBJECTID, Shape, Group FROM GROUP_RINGS_prj]

Do I miss something obvious in the code?

• Bera: Are you trying to select all rows with the same Group attribute and perform one zs for each Group? Or do you want one zs for each row in the feature class?
• Marc: Each group attribute is unique so I get one zs for each row in the fc..
• Marc: yes, forest_prj is a raster in the PROJECT.gdb
• Berend: Hm, group is a reserved keyword in some types of database, maybe that's your problem. You could try to rename that field. When I try to create a field named group in a File Geodatabase, it gives me an error: The following field names are invalid: group
• Marc: That was it! I removed Group and it works now... Thanks!

Answer 1

Group can't be used as a column name, because it's a reserved keyword.

In fact, when you use ArcCatalog to add a field with that name to a featureclass, it will give an error message: The following field names are invalid: group. I wonder how you managed to create this featureclass?

• Marc: I imported it from a .csv file... But now I see "Group" is the alias and ArcGIS renamed the field "Group_" with an underscore...

Answer 2

You cant use a geometry (SHAPE@) as in_zone_data to zonal statistics, it should be a feature layer or raster layer. Try creating a feature layer using the objectid of each row instead and use feature layer as input:

with arcpy.da.SearchCursor(aoi_prj, ["OID@", "Group"]) as cursor:
 for row in cursor:
 print(datetime.datetime.now().strftime("%Y-%m-%d %H:%M") + ": Processing: " + str(row[2]) + "...")
 sql = """{0}={1}""".format(arcpy.AddFieldDelimiters(arcpy.Describe(aoi_prj).OIDFieldname),row[0])
 arcpy.MakeFeatureLayer_management(in_features=aoi_prj, out_layer='templyr', where_clause=sql)
 zs = ZonalStatisticsAsTable('templyr', "Group", forest_prj, "D:/Projects/Results/ZS_FOREST_" + str(row[0]) + ".dbf", "NODATA", "MEAN")

This line will probably make you miss every other row. To iterate over each row remove it:

row = cursor.next()

• Marc: Same error unfortunately... "RuntimeError: An invalid SQL statement was used. [SELECT OBJECTID, Group FROM GROUP_RINGS_prj]
• Marc: Thanks, your answer helped me a lot to understand how to iterate through features.