Creating WHERE clause from list using ArcPy

Question

I am creating a new shapefile (a subset of a current shapefile) such that it has all the features whose values in a field ("fclass") is not contained in the field_names list.

field_names = ['bridleway','cycleway','footway','living_street','pedestrian','residential','tertiary_link','service','unclassified','steps','path','tertiary']
def get_where_clause(field_values):
 where_clause = ""
 for value in field_values:
 where_clause = where_clause + """"fclass" <> '{0}'""".format(value) + " AND "
 return where_clause[:-5] #removing the last "AND"
arcpy.MakeFeatureLayer_management(path, feature_layer, get_where_clause(field_names)

Problem: The output somehow selects all the features and exports everything.

• Marcelo Villa: What about using where_clause = "fclass NOT IN {}".format(field_names)? If you try this, make sure field_names is a tuple and not a list.

Answer 1

You're using AND when you should be using OR. A value can't equal all the values in field_names. 5, as an example, can't equal 5 AND 7 AND 3 AND 23 AND...

I like to make a string out of my list of values using join, separated by single-quote, comma, single-quote. Then I'll use NOT IN for a case like yours.

field_names = ['bridleway','cycleway','footway','living_street','pedestrian','residential','tertiary_link','service','unclassified','steps','path','tertiary']
>>> fldStr = "', '".join (field_names)
>>> fldStr
"bridleway', 'cycleway', 'footway', 'living_street', 'pedestrian', 'residential', 'tertiary_link', 'service', 'unclassified', 'steps', 'path', 'tertiary"
>>> sql = "fclass NOT IN ('{}')".format (fldStr)
>>> sql
"fclass NOT IN ('bridleway', 'cycleway', 'footway', 'living_street', 'pedestrian', 'residential', 'tertiary_link', 'service', 'unclassified', 'steps', 'path', 'tertiary')"
>>>