Python script for constructing a WHERE clause from user input

Question

I'm trying to construct a where clause from user input that will eventually be passed through as a SQL statement in the select tool or the select by attributes tool. I've started a script thats been adapted from some previous posts, however, I still don't have it correct. the way its currently written the resulting SQL statement will look something like:

"123" = 'ABC' OR 'DEF'

I need the resulting statement to look more like

"123円\" = 'ABC' OR "123円\"='DEF'

Important: the values (i.e. ABC DEF) will be user inputs and the number of values entered as arguments may vary from user to user. My current script is below

 def whereClause(table, field, values):
 """Takes a semicolon-delimited list of values and constructs a SQL WHERE
 clause to select those values within a given field and table."""
 # Add field delimiters
 fieldDelimited = arcpy.AddFieldDelimiters(arcpy.Describe(table).path, field)
 # Split multivalue at semicolons and strip quotes
 valueList = [value[1:-1]
 if (value.startswith("'") and value.endswith("'"))
 else value for value in values.split(';')]
 # Determine field type
 fieldType = arcpy.ListFields(table, field)[0].type
 # Add single-quotes for string field values
 if str(fieldType) == 'String':
 valueList = ["'%s'" % value for value in valueList]
 # Format WHERE clause in the form of an IN statement
 whereClause = "%s = %s" % (fieldDelimited, 'OR' .join(valueList))
 return whereClause
def outName(input,post="Out",fileExtension="shp"):
 """Returns output name."""
 outName=os.path.basename(input).split(".")[0]+post+"."+fileExtension
 return outName
# Create SQL Statement
InputFC = arcpy.GetParametersAsText(0)
outputFC=outName(InputFC,"_Select")
Field ="CO_NAME"
Values = arcpy.GetParameterAsText(1) #user input
SQL=whereClause(InputFC,Field,Values) 
print SQL

• sgrieve: Can you post the error message you are getting, it will help with debugging the script.
• sgrieve: Also, an example input dataset would be handy.
• R.K.: Does it always need to have the slashes like in "123円\"?
• R.K.: Some more test values would be nice, too.
• rralbritton: @R.K. yes, according to formatting rules in ArcGIS 10.0 the field from which the attributes are being selected needs to follow this format. As far as supplying test dat, I'm new to this forum - how do i do that?

Answer 1

Making lots of assumptions here. Here's my edits.

 def whereClause(table, field, values):
 """Takes a semicolon-delimited list of values and constructs a SQL WHERE
 clause to select those values within a given field and table."""
 # Add field delimiters
 fieldDelimited = arcpy.AddFieldDelimiters(arcpy.Describe(table).path, field)
 #adds the slashes
 fieldDelimited = '"\\%s\\"' % (fieldDelimited[1:-1])
 # Split multivalue at semicolons and strip quotes
 valueList = [value[1:-1]
 if (value.startswith("'") and value.endswith("'"))
 else value for value in values.split(';')]
 # Determine field type
 fieldType = arcpy.ListFields(table, field)[0].type
 # Add single-quotes for string field values
 if str(fieldType) == 'String':
 valueList = ["'%s'" % value for value in valueList]
 # Format WHERE clause in the form of an IN statement
 whereClause = ''
 for value in valueList:
 if whereClause == '':
 whereClause = "%s = %s" % (fieldDelimited, value)
 continue 
 else:
 prevClause = str(whereClause)
 whereClause = "%s = %s" % (fieldDelimited, value)
 whereClause = "%s OR %s" % (prevClause, whereClause)
 return whereClause
def outName(input,post="Out",fileExtension="shp"):
 """Returns output name."""
 outName=os.path.basename(input).split(".")[0]+post+"."+fileExtension
 return outName
# Create SQL Statement
InputFC = arcpy.GetParametersAsText(0)
outputFC=outName(InputFC,"_Select")
Field ="CO_NAME"
Values = arcpy.GetParameterAsText(1) #user input
SQL=whereClause(InputFC,Field,Values) 
print SQL

Do try it on your test data and see if it works.

• rralbritton: @sgrieve if I try and run the current script through the select by attribute tool the error message reads: "ExecuteError: ERROR 000210: Cannot create output CountyBoundary_Select.shp" This makes sense since the format of the SQL statement is incorrect (as noted in my orginal post)
• rralbritton: thank you for working on this, unfortunately, it doesn't work. I get the following error message: Traceback (most recent call last): File "C:\Python26\ArcGIS10.0\Lib\site-packages\pythonwin\pywin\framework\scriptutils.py", line 326, in RunScript exec codeObject in main.__dict__ File "C:\GIS540_Project\sql_RKedits.py", line 44, in <module> SQL=whereClause(InputFC,Field,Values) File "C:\GIS540_Project\sql_RKedits.py", line 25, in whereClause if whereClause == "": UnboundLocalError: local variable 'whereClause' referenced before assignment
• R.K.: Fixed. Can you try it again? Forgot to initialize whereClause.
• rralbritton: @ R.K. - it now runs without an error but basically gives me back the same situation as my original post : "\CO_NAME\" = '123:456'
• R.K.: Can you tell me what the value of valueList in this case is?