Syntax error in Where Clause using .format in arcpy.Select_analysis

Question

I'm having a little trouble understanding the syntax for a Where Clause; e.g., for use in arcpy.Select_analysis(inSHP, clipSHP, where).

input is polygon shapefile; Zone_ID field is short integer

This works:

whereClause = '"Zone_ID"= ' + str(zone)

But this doesn't:

whereClause = "{field} = '{val}'".format(field = Zone_ID, val = zone)

Throws this error:

NameError: name 'Zone_ID' is not defined

"Zone_ID" is obviously defined because it works in the first case. Any ideas what's wrong with my syntax?

Tried this also:

whereClause = "{field} = '{val}'".format(field = "Zone_ID", val = zone)

Error message:

arcgisscripting.ExecuteError: ERROR 000358: Invalid expression Zone_ID = '1'
Failed to execute (Select).

Answer 1

Use AddFieldDelimiters:

The field delimiters used in an SQL expression differ depending on the format of the queried data. For instance, file geodatabases and shapefiles use double quotation marks (" "), personal geodatabases use square brackets ([ ]), and enterprise geodatabases don't use field delimiters. The function can take away the guess work in ensuring that the field delimiters used with your SQL expression are the correct ones.

Since your field is integer you should have no " or ' surrounding the value:

fc = r'C:\data.gdb\features'
fieldname = 'ZoneID'
whereClause = """{0} = {1}""".format(arcpy.AddFieldDelimiters(fc, fieldname),123)
...

Should make your whereClause look like this:

>>whereClause
'"ZoneID" = 123'