2

I'm quite new with arcpy and I'm finding selecting by attribute a bit of a nightmare. There's a problem with my query on the script below [query = "\'Location\' = observer"], but I can't tell what it is. 'Location' is a field of the feature class I'm dealing with (fc_copy).

with arcpy.da.UpdateCursor(fc_copy,field_names) as cursor:   
    for row in cursor:
        observer = row[0]
        arcpy.MakeFeatureLayer_management(fc_copy, 'fl_copy_OBS')
        arcpy.MakeFeatureLayer_management(fc_copy, 'fl_copy_TAR')
        query = "\'Location\' = observer"
        arcpy.SelectLayerByAttribute_management('fl_copy_OBS', 'NEW_SELECTION', query)
        arcpy.SelectLayerByAttribute_management('fl_copy_TAR', 'NEW_SELECTION', query, invert_where_clause = 'INVERT')

This is the error I get => Invalid expression Failed to execute (SelectLayerByAttribute).

Is there any documentation to learn the rules to follow to build queries for arcpy?

  • Which is the column name? The convention is to test "column_name = constant". String constants should be quoted. Adding unnecessary backslashes is a Python programming error. – Vince Nov 9 '17 at 13:40
4

The expression format depends on the data type and value being queried:

Shapefile querying text field:

query = "\"Location\" = '{}'".format(observer)

Geodatabase feature class querying text field:

query = "Location = '{}'".format(observer)
  • 1
    That worked perfectly well <query = "Location = '{}'".format(observer)> However, I do not understand what that expression actually means. – Pitrako Junior Nov 9 '17 at 14:48
  • .format() is a string formatter. The {} characters is a place holder for the value in the observer variable. Essentially, it is an easier way to add a variable within a string. – artwork21 Nov 9 '17 at 14:57
3

Use AddFieldDelimiters:

Adds field delimiters to a field name to allow for use in SQL expressions.

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.

And Format String Syntax (instead of + to combine strings):

Format strings contain “replacement fields” surrounded by curly braces {}. Anything that is not contained in braces is considered literal text, which is copied unchanged to the output.

And Three double quotes. Like this:

query = """{0} = {1}""".format(arcpy.AddFieldDelimiters('fl_copy_OBS','Location'), observer)
  • 1
    'Location'), 'observer') should be 'Location'), observer) since observer is a veritable and not a string. – artwork21 Nov 9 '17 at 15:00
2

The page Specifying a query in Python is probably what you're looking for.

Important rules:

  • Delimiters for field names depend on the workspace. Unless you work with personal geodatabase feature classes/tables (.mdb), these delimiters can be omitted. Otherwise, the arcpy.AddFieldDelimiters helper function will make sure the proper delimiters are added, no matter the workspace your dataset is in.

  • Delimiters for field values are single quotes for everything that is not a numeric, and are not necessary for numerics.

I've totally adopted the tip in the page (i.e. wrap your query with triple quotes). This makes dealing with embedded pairs of quotes much easier. Just write your query as you would do it in e.g. the query builder, then wrap everything with those triple quotes. Combining these quotes with the string.format() function will make it even more easy if you're working with variables, especially numerics (no need to convert them to string).

So in your case you could write (assuming that you're not working with a mdb dataset, and that observer/row[0] is a string):

 query = """Location = '{}'""".format(row[0])
0

Try to look at this ArcGIS help for SQL making. And try to format your query like:

query = '"Location" =' + str(observer)

  • I'm afraid it doesn't work (same error). – Pitrako Junior Nov 9 '17 at 13:32

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.