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?

• GIS Data Butcher: try expression='"{}"'.format(var1) OR expression ="""" GIIDs IN (1,2,3) """" arcpy.Select_analysis(r"memory\xx", "xxx1", expression )

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.

• blord-castillo: Great idea. I added this function to my arcpy helper functions module so it is defined as arcpy.buildWhereClause

Answer 2

Another, maybe simpler, way is:

where = '"StudyID" = ' + "'%s'" %Name

• blah238: "When using ArcGIS 10 (or, presumably, later), field names do not need to be quoted" This is incorrect; the field delimiters must be specified according to the underlying DBMS's syntax rules. See my answer.
• blah238: The above comment was in response to an anonymous edit that I rolled back for correctness of this answer.
• jpmc26: Isn't this a SQL injection vulnerability if Name comes from user input?

Answer 3

Try this:

Name = 1
study = "StudyID"
where = '"' + study + '" = ' + "'" + str(Name) + "'"

Answer 4

I like to use triple quotes. I think they are the easiest to read. For example,

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

Depending on the type(name) you may or may not need the ' around %s. For numbers, you do need the ' but not for text.

Answer 5

For me, this solution works the best as I can substitute both a variable to the field of interest and value criteria.

field = "Sport"
value = "Basketball"
where = """"{}" = '{}'""".format(field,value)