arcpy: Select layer by attribute with SWITCH_SELECTION seems not to work

Question

I have a large feature classes, where a field named "gridcode" contains integers values from 6 to 17. I would like to get rid of features, where the gridcode = 6, 9 or 15.

Therefore, I have created a query where gridcode = 6 OR gridcode = 9 OR gridcode = 15.

Further, I wanted to apply tool Select layer by Attribute and switch my selection, i.e. end up only with field "gridcode" does not contain neither 6, 9 or 15. I wanted to apply this tool, as it takes "SWITCH_SELECTION" argument.

I have save up my selection, just to verify if the process was accurate. And it seems that the selection or the dropping of my variables did not occur.

Could there be a problem with my coding?

How otherwise I can drop my rows by attribute?

My code:

---

# Import modules
import arcpy, os
import itertools
# Allow files to overwrite
arcpy.env.overwriteOutput = True
# Set working environment
inWD = "C:/Projects/2018_deforestData"
arcpy.env.workspace = os.path.join(inWD, "input.gdb")
# Define outPath
outPath = os.path.join(inWD, "output.gdb")
# Define input variables
inSAO = "AllSAO"
# create clauses
field = "gridcode"
# Select correct years from SAO
# Create attribute query for SAO data:
# drop years yearsSAO = [6, 9, 15]
# Create layers and apply atribute query
querySAOall = field + "=" + str(6) + " OR " + field + "=" + str(9) + " OR " + field + "=" + str(15) 
# Make feature layer from my input data
arcpy.MakeFeatureLayer_management(inSAO, "SAOLayerAll")
# Select by attribute and switch the selection
arcpy.SelectLayerByAttribute_management ("SAOLayerAll", "SWITCH_SELECTION", querySAOall)
# Save file to make sure the correct years are used
outSAOcorrectYears = os.path.join(outPath, "SAO_correctYears")
arcpy.CopyFeatures_management("SAOLayerAll", outSAOcorrectYears)

• Michael Stimson: That's not how switch selection works. When you say switch selection the query is ignored.. put the query in your make feature layer and it should work. It would be easier to read if your query was ''{0} = {1} or {0} = {2} or {0} = {3}".format(field,6,9,15).
• PolyGeo: I'd use the Select tool in place of the three tools that you are using - it takes a where clause.
• maycca: Awesome @MichaelStimson, I will definitelly use your query stile in my further work! But, the reason I wanted to apply "SWITCH_SELECTION" is because I don't know how to otherwise drop those values from my feature class? I am looking for something simple as in r: a != c but I can't figure it out in python..
• maycca: Thank you for your comments, I have combined the Select tool and specified my query as: querySAOall = "NOT {0} = {1} and NOT {0} = {2} and NOT {0} = {3}".format(field,6,9,15) and it works!! :)) Please, can you post it as an answer that I can accept it?

Answer 1

When you use "SWITCH_SELECTION" as a selection type in Select Layer by Attributes the SQL statement is ignored, so you can either use:

# Make feature layer from my input data
arcpy.MakeFeatureLayer_management(inSAO, "SAOLayerAll")
# Select by attribute and switch the selection
arcpy.SelectLayerByAttribute_management ("SAOLayerAll", "NEW_SELECTION", querySAOall)
arcpy.SelectLayerByAttribute_management ("SAOLayerAll", "SWITCH_SELECTION")

or:

# Make feature layer from my input data
arcpy.MakeFeatureLayer_management(inSAO, "SAOLayerAll", querySAOall)
# Select by attribute and switch the selection
arcpy.SelectLayerByAttribute_management ("SAOLayerAll", "SWITCH_SELECTION")

Whichever way you prefer the result is the same.

A suggestion: instead of

querySAOall = field + "=" + str(6) + " OR " + field + "=" + str(9) + " OR " + field + "=" + str(15) 

Consider string formatting:

# string formatting, easier to read
querySAOall = '{0} = {1} or {0} = {2} or {0} = {3}'.format(field,6,9,15)

Inequality is <>, not the C type !=, in SQL so you could specify your switch directly in your SQL where clause but there is a better way, try querySAOall = 'field not in (6,9,15)' which effectively gets expanded internally to a long list of not equal (<>) statements which can be a bit slow to select in large feature classes but is way easier to type - especially if you have more than 20 values.