Arcpy Select Analysis – Including Variable in WHERE Clause of Arcpy.Select_analysis()

Question

I am trying to loop through a shapefile, selecting each feature in turn and copying it to a temporary shapefile to by included in a union analysis. I'm using a cursor to find the ID name for each feature which I'm setting to a varible 'Name'. Whenever I try to use this variable as part of the where clause in arcpy.Select_analysis I get an error:

ExecuteError: ERROR 999999: Error executing function.
An invalid SQL statement was used.
An invalid SQL statement was used.
Failed to execute (Select).

The code I'm using is:

Name = 101
where = "\'\"StudyID\" = \\'"+str(Name)+"\\'\'"
arcpy.Select_analysis("C:\\input.shp", "C:\\output.shp", where)

If I type it out without using the variables:

arcpy.Select_analysis("C:\\input.shp", "C:\\output.shp", '"StudyID" = \'101\'')

it works fine

What to I need to do to fit the variable into the sql statement?

Answer 1

One thing that makes writing WHERE clauses a lot easier is to use the AddFieldDelimiters function, which automatically adds the correct, DBMS-specific delimiters for field identifiers, such as double-quotes for FGDB and brackets for PGDB.

The other thing you have to consider is whether the value is a number, string, or other data type. Specifically, strings are wrapped in single quotes while numbers are not. You could check the field type and add single quotes if it is a string field.

E.g.:

import arcpy

def buildWhereClause(table, field, value):
    """Constructs a SQL WHERE clause to select rows having the specified value
    within a given field and table."""

    # Add DBMS-specific field delimiters
    fieldDelimited = arcpy.AddFieldDelimiters(table, field)

    # Determine field type
    fieldType = arcpy.ListFields(table, field)[0].type

    # Add single-quotes for string field values
    if str(fieldType) == 'String':
        value = "'%s'" % value

    # Format WHERE clause
    whereClause = "%s = %s" % (fieldDelimited, value)
    return whereClause

if __name__ == "__main__":
    inputfc = r"C:\input.shp"
    outputfc = r"C:\output.shp"
    fieldname = "StudyID"
    fieldvalue = 101
    whereclause = buildWhereClause(inputfc, fieldname, fieldvalue)
    arcpy.Select_analysis(inputfc, outputfc, whereclause)

See also the function in this answer for a multi-value version of the above function.