Multi Value User Input as Variable in SQL Statement

Question

I'm attempting to write a script that takes a user's selection as a parameter and uses that as part of the SelectByAttributes function. The parameter represents field attributes and is multi value, and the user can select one or multiple.

So what I have at this point is the list of all available attributes, and each selected as the defualt.

enter image description here

Also, Here is the tool validation code and Parameter Properties:

import arcpy
class ToolValidator(object):
 """Class for validating a tool's parameter values and controlling
 the behavior of the tool's dialog."""
 def __init__(self):
 """Setup arcpy and the list of tool parameters."""
 self.params = arcpy.GetParameterInfo()
 def initializeParameters(self):
 """Refine the properties of a tool's parameters. This method is
 called when the tool is opened."""
 return
 def updateParameters(self):
 """Modify the values and properties of parameters before internal
 validation is performed. This method is called whenever a parameter
 has been changed."""
 shape = "G:\_DPD\STEVEN\FIELD.shp"
 rows = arcpy.SearchCursor(shape)
 self.params[0].filter.list = sorted(list(set(r.getValue('DPD_FLD_TY') for r in rows)))
 all = self.params[0].filter.list
 self.params[0].value = all
 del rows
 return
 def updateMessages(self):
 """Modify the messages created by internal validation for each tool
 parameter. This method is called after internal validation."""
 return

enter image description here

I want the user to have the option to run the tool with multiple attributes selected or with just one selected, and use that as part of the SQL statement within the SelectByAttribute function. For example, if the user selects "GAS" and "UNKNOWN", the SelectByAttributes function would look like

arcpy.SelectLayerByAttribute_management(lyr,"NEW_SELECTION",""""DPD_FLD_TY" = 'GAS' OR "DPD_FLD_TY" ='UNKNOWN'""")

So my main question is: what would the code be to check how many attributes are selected and based on that, have the SQL statement contain the proper amount of "OR" statements? Also, would the parameter be considered just one, or would it be multiple parameters i.e. GetParameterAsText(0) vs. GetParameterAsText(0)...GetParameterAsText(3)

I'm sorry if this is confusing, but I don't know exactly where to start with this problem.

This is how I am referencing the layer:

import arcpy
mxd = arcpy.mapping.MapDocument('CURRENT')
mm = arcpy.mapping.ListDataFrames(mxd, "Layers")[0]
lyr = arcpy.mapping.ListLayers(mxd, 'FIELD')[0]
field = arcpy.GetParameterAsText(0)

Answer 1

Use DPD IN ('Gas','Oil','') instead of multiple OR

Answer 2

As per @FelixIP's answer, use an IN operator in your where clause.

The multivalue parameter is passed as a single semicolon delimted string, i.e. "value1;value2;value3". To convert this to the required format for the IN operator use python split/list comprehension/join or replacement.

split/list comprehension/join:

fields = arcpy.GetParameterAsText(0)
where_clause = '"DPD_FLD_TY" IN ' + "({})".format(','.join(["'{}'".format(f) for f in fields.split(';')]))

replacement:

fields = arcpy.GetParameterAsText(0)
where_clause = '"DPD_FLD_TY" IN ' + "('{}')".format(fields.replace(";","','"))

This results in something like:

print where_clause
"DPD_FLD_TY" IN ('value1','value2','value3')

EDIT: If your field name contains spaces, ArcGIS will wrap it in single quotes before passing to your script. Strip the single quotes out (before adding them again to all field names) with something like:

fields = arcpy.GetParameterAsText(0)
fields=["'{}'".format(f.strip("'")) for f in fields.split(';')]
where_clause='"DPD_FLD_TY" IN ({})'.format(','.join(fields))

• Steven C: Thank you. One question: the tool results in an error if "OIL AND GAS" is one of the options selected. The tool works for all the others and their combinations. I'm assuming that it's because of the spaces in the "OIL AND GAS" option. Is there a way to account for that, i.e. make sure "OIL AND GAS" is considered as a whole instead of multiple parts delimited by spaces?
• user2856: @StevenC - ArcGIS wraps parameters that contain spaces in single quotes. I think that's a poor design decision but that's the way it they want it. You just need to strip out the single quotes, see edit to my answer.