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?

Improve this question
1
  • 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. Commented Nov 9, 2017 at 13:40

4 Answers 4

Reset to default
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)
Improve this answer
2
  • 1
    That worked perfectly well <query = "Location = '{}'".format(observer)> However, I do not understand what that expression actually means. Commented Nov 9, 2017 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. Commented Nov 9, 2017 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)
Improve this answer
1
  • 1
    'Location'), 'observer') should be 'Location'), observer) since observer is a veritable and not a string. Commented Nov 9, 2017 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])
Improve this answer
0

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

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

Improve this answer
1
  • I'm afraid it doesn't work (same error). Commented Nov 9, 2017 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.