1

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.

  • Strange but true, arcgis has issues with field name called "gridcode". Move values to another integer field – FelixIP Dec 22 '16 at 17:27
  • I was wondering if it was something like this. To move the values to another field, though, won't I have to use the field name 'GRIDCODE' also? For example, if I create a new field and populate it using CalculateField, I will have to use 'GRIDCODE' in the CalculateField expression, right?. A thought I just had - could I possibly create the query variable by indexing the fields to get the GRIDCODE field? E.g., fields = arcpy.ListFields(fc) --> field = fields[3].name --> build query using this field variable? – Laura Dec 22 '16 at 17:44
  • Check out AddFieldDelimiters: pro.arcgis.com/en/pro-app/arcpy/functions/… – BERA Dec 22 '16 at 17:50
  • UPDATE: I tried to create a new field in the shapefile and populate it using this line: arcpy.CalculateField_management(fc, 'gridvalue', "!GRIDCODE!", 'PYTHON'), and I got the same error but for this new line now. I am assuming this is because of using GRIDCODE here too. Is there a way to name the fields when the raster is converted to a polygon that could circumvent this issue from the beginning? – Laura Dec 22 '16 at 18:04
  • Somehow, the FieldCalculator worked (not sure what I changed to make it work), so now I have a short integer field called gridvalue in the shapefile populated with the values from GRIDCODE, but I am still getting the same error about a column being specified that does not exist when I try to select by attribute. – Laura Dec 22 '16 at 18:30
0

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)
  • As the answer above suggests, I would make sure you aren't using the field alias rather than field name, and that your capitalization matches... I make this mistake when I don't double check. Also good to know about the " " required on shape files, I didn't realize that having worked only with feature classes which don't require any quotes on the field name – maverickGIS Dec 22 '16 at 18:07
  • Thanks to both of you for responding. When I check the field name and alias name using arcpy.Describe, both are 'GRIDVALUE'. – Laura Dec 22 '16 at 18:11
  • so did that resolve your issue? – Ben S Nadler Dec 22 '16 at 18:21
  • My issue is different now. The field was indeed named GRIDCODE, but somehow, where the FieldCalculator wasn't working before, it just did, so now I have a field named 'gridvalue', but I'm getting the same error in the SelectLayerByAttribute line whether I try using the formatting you suggested or the actual field name and value, i.e., '"gridvalue" = 1'. I am sure it is something simple that I'm missing. I just can't see it. – Laura Dec 22 '16 at 18:28
  • update your question with the new code and error details. Usually a second pair of eyes can help a lot. Use arcpy.GetMessages() after the failure to get mode details on the errror. – Ben S Nadler Dec 22 '16 at 18:32

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.