6

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
Improve this question
7
  • Can you post the error message you are getting, it will help with debugging the script.
    – sgrieve
    Commented Oct 8, 2012 at 12:58
  • Also, an example input dataset would be handy.
    – sgrieve
    Commented Oct 8, 2012 at 13:04
  • Does it always need to have the slashes like in "\123\"?
    – R.K.
    Commented Oct 8, 2012 at 13:23
  • Some more test values would be nice, too.
    – R.K.
    Commented Oct 8, 2012 at 13:35
  • @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?
    – rralbritton
    Commented Oct 8, 2012 at 13:58

1 Answer 1

Reset to default
3

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.

Improve this answer
8
  • @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
    Commented Oct 8, 2012 at 13:56
  • 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
    – rralbritton
    Commented Oct 8, 2012 at 14:03
  • Fixed. Can you try it again? Forgot to initialize whereClause.
    – R.K.
    Commented Oct 8, 2012 at 14:04
  • @ R.K. - it now runs without an error but basically gives me back the same situation as my original post : "\CO_NAME\" = '123:456'
    – rralbritton
    Commented Oct 8, 2012 at 14:56
  • Can you tell me what the value of valueList in this case is?
    – R.K.
    Commented Oct 8, 2012 at 15:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.