Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

1 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.

share|improve this answer
    
@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 Oct 8 '12 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 Oct 8 '12 at 14:03
    
Fixed. Can you try it again? Forgot to initialize whereClause. –  R.K. Oct 8 '12 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 Oct 8 '12 at 14:56
    
Can you tell me what the value of valueList in this case is? –  R.K. Oct 8 '12 at 15:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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