21

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?

14

Another, maybe simpler, way is:

where = '"StudyID" = ' + "'%s'" %Name
  • 2
    "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 Feb 12 '13 at 17:39
  • The above comment was in response to an anonymous edit that I rolled back for correctness of this answer. – blah238 Feb 12 '13 at 18:18
  • Isn't this a SQL injection vulnerability if Name comes from user input? – jpmc26 Feb 4 '17 at 1:00
22

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.

  • 2
    Great idea. I added this function to my arcpy helper functions module so it is defined as arcpy.buildWhereClause – blord-castillo Oct 15 '13 at 17:01
3

Try this:

Name = 1
study = "StudyID"

where = '"' + study + '" = ' + "'" + str(Name) + "'"
0

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.

0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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