arcpy – Troubleshooting Column Not Exist Error in Select Layer By Attribute Tool

arcgis-10.3arcpyerror-999999select-by-attribute

I have shapefiles in a folder that were created by using the Raster to Polygon tool to convert reclassified rasters to polygon shapefiles. Each has a field named 'GRIDCODE'. I now want to select features from another layer by these new polygon layers (layers created for all shapefiles), but I keep getting the error 'A column was specified that does not exist'. I think it is an issue with the where clause; however, I did a test of this process in ArcMap before starting to build the stand alone script, copying the Python snippets from the Results window to check how the query was written. The process worked fine in ArcMap, but it is not working in a stand alone script.

The script:

query = '"GRIDCODE" = 1'
fcs = arcpy.ListFeatureClasses()
for fc in fcs:
    try:
        arcpy.MakeFeatureLayer_management(fc, "splyr")
        print "Layer created for " + fc
    except:
        print "splyr already exists."
    arcpy.SelectLayerByAttribute_management("splyr", selection_type='NEW_SELECTION', where_clause=query)

I have also tried these other formats for the where_clause:

query = """"GRIDCODE = 1""" # This was what the snippet copied from the ArcMap Results produced

and

# taken from an answer to a similar question found on this site
field_name = 'GRIDCODE'
value = 1
query = """{0} = {1}""".format(field_name, value)

The error I get every time is this:

Traceback (most recent call last):
File "C:\Python27\ArcGIS10.3\Lib\site-packages\pythonwin\pywin\framework\scriptutils.py", line 326, in RunScript
  exec codeObject in __main__.__dict__
File "C:\Users\ldugan\Documents\Python\counties_from_rasters.py", line 53, in <module>
  arcpy.SelectLayerByAttribute_management("splyr", selection_type='NEW_SELECTION', where_clause=query)
File "C:\Program Files\ArcGIS\Desktop10.3\ArcPy\arcpy\management.py", line 7221, in SelectLayerByAttribute
  raise e
ExecuteError: ERROR 999999: Error executing function.
A column was specified that does not exist.
A column was specified that does not exist.
Failed to execute (SelectLayerByAttribute).

Line 53 that is mentioned in the Traceback refers to the SelectLayerByAttribute line.

I have read all the other questions and corresponding answers (that I could fine) in here related to this type of error as well as the ArcGIS documentation for the SelectLayerByAttribute tool and on building queries, and I still can't figure it out.

Here is the code I am currently working with after incorporating the some of the suggestions below:

fcs = arcpy.ListFeatureClasses()
for fc in fcs:
    fcfields = arcpy.ListFields(fc)
    for fcfield in fcfields:
        if fcfield.name == 'gridvalue':
            print "Field gridvalue already exists."
        else:
            arcpy.AddField_management(fc, 'gridvalue', 'SHORT')
    field_name = 'gridvalue'
    arcpy.CalculateField_management(fc, field_name, "!GRIDCODE!", 'PYTHON')
    value = 1
    query = '"{0}" = {1}'.format(field_name, value)
    try:
        arcpy.MakeFeatureLayer_management(fc, "splyr")
        print "Layer created for " + fc
    except:
        print "splyr already exists."
    arcpy.SelectLayerByAttribute_management("splyr", selection_type='NEW_SELECTION', where_clause=query)

And here is the error I get:

Traceback (most recent call last):
  File "C:\Python27\ArcGIS10.3\Lib\site-packages\pythonwin\pywin\framework\scriptutils.py", line 326, in RunScript
    exec codeObject in __main__.__dict__
  File "C:\Users\ldugan\Documents\Python\counties_from_rasters.py", line 63, in <module>
    arcpy.SelectLayerByAttribute_management("splyr", selection_type='NEW_SELECTION', where_clause=query)
  File "C:\Program Files\ArcGIS\Desktop10.3\ArcPy\arcpy\management.py", line 7221, in SelectLayerByAttribute
    raise e
ExecuteError: ERROR 999999: Error executing function.
A column was specified that does not exist.
A column was specified that does not exist.
Failed to execute (SelectLayerByAttribute).

And adding 

print(arcpy.GetMessage(0))

returned

Executing: SelectLayerByAttribute splyr NEW_SELECTION ""gridvalue" = 1"

which makes me think it's a single/double quotation issue.
Line 63 refers to the SelectLayerByAttribute call. Also, as an FYI, I checked, and the Field Calculator correctly populated the field gridvalue, so there are values of 1 in this field.

Best Answer

Tested this on a shapefile with an integer field called GRIDCODE.

Ensure this is the actual field name in your shapefile.

Shapefiles use double quotes for field names

Use:

query = '"{0}" = {1}'.format(field_name, value)

You should be able to remove the create new field steps and just query GRIDCODE Working script below

def calc(fc):
arcpy.env.overwriteOutput = True
fcfields = arcpy.ListFields(fc)
field_name = 'gridvalue'
# Your code was trying to add a field each time the field name did not match...
#This is better
names = [fcfield.name.lower() for fcfield in fcfields]
if field_name in names:
    print "Field gridvalue already exists."
else:
    arcpy.AddField_management(fc, 'gridvalue', 'SHORT')

arcpy.CalculateField_management(fc, field_name, "!GRIDCODE!", 'PYTHON')
arcpy.MakeFeatureLayer_management(fc, "splyr")
print "Layer created for " + fc  
query = '"{0}" = {1}'.format(field_name, 1)

arcpy.SelectLayerByAttribute_management("splyr", selection_type='NEW_SELECTION', where_clause=query)
Related Question