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 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?

share|improve this question
add comment

3 Answers

up vote 7 down vote accepted

Another, maybe simpler, way is:

where = '"StudyID" = ' + "'%s'" %Name
share|improve this answer
    
"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
add comment

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.

share|improve this answer
1  
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
add comment

Try this:

Name = 1
study = "StudyID"

where = '"' + study + '" = ' + "'" + str(Name) + "'"
share|improve this answer
add comment

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.