topic Re: SelectLayerByAttribute_management - Correct syntax for expression in Geoprocessing Questions

SelectLayerByAttribute_management - Correct syntax for expression

KatrinaBennett — Fri, 23 Mar 2012 01:39:22 GMT

I'm having some trouble running ArcGIS 10 Python scripting.

I am trying to select the attributes in my layer based on a number of expressions. I don't think I have the Syntax correct. Although, I've followed what is posted here: http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//001700000071000000.

This is the portion of my code that is giving me the error.

sel_pts_lyr is a feature layer, and the join I used to generate the data fields was done using env.qualifiedFieldNames = False.

I am running this in gp = arcgisscripting.create(9.3).

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

#Select Analysis
print "Attempting Select"
expression1 = ' \"ELEV\" <= \"Elev_metre \" + 100 AND \"ELEV\" >= \"Elev_metre\" - 100 '
expression2 = ' \"SLP\" <= \"mean_slp\"+ \"sd_slp\" AND \"SLP\" >= \"mean_slp\" - \"sd_slp\" '
expression3 = ' \"ASP\" <= \"mean_asp\" + \"sd_asp\" AND \"ASP\" >= \"mean_asp\" - \"sd_asp\" '

arcpy.SelectLayerByAttribute_management(sel_pts_lyr, "NEW_SELECTION", expression1)
arcpy.SelectLayerByAttribute_management(sel_pts_lyr, "SUBSET_SELECTION", expression2)
arcpy.SelectLayerByAttribute_management(sel_pts_lyr, "SUBSET_SELECTION", expression3)

ERROR OUTPUT

Traceback (most recent call last):
File "C:\data2\smart_clip2\model_clip.py", line 91, in <module>
arcpy.SelectLayerByAttribute_management(sel_pts_lyr, "NEW_SELECTION", expression1)
File "C:\Program Files (x86)\ArcGIS\Desktop10.0\arcpy\arcpy\management.py", line 4259, in SelectLayerByAttribute
raise e
ExecuteError: ERROR 000358: Invalid expression
Failed to execute (SelectLayerByAttribute).

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Any assistance would be very appreciated!!

Thank you,

Katrina

Re: SelectLayerByAttribute_management - Correct syntax for expression

FabianBlau — Fri, 23 Mar 2012 08:05:39 GMT

Hi!

Try it without escaping the double quotes:

expression1 = ' "ELEV" <= "Elev_metre " + 100 AND "ELEV" >= "Elev_metre" - 100 '

When mixing the different kinds of quote signs escaping should not be necessary.

Re: SelectLayerByAttribute_management - Correct syntax for expression

MikeWissner — Fri, 23 Mar 2012 13:24:17 GMT

Hi all,

I'm also trying to run a SelectLayerByAttribute_management command and I keep getting an error. My line of code is as simple as can be, I don't understand the problem.

arcpy.SelectLayerByAttribute_management("landuse", "NEW_SELECTION", " [LU_2008]= 'Cemetery' ")

Runtime error <class 'arcgisscripting.ExecuteError'>: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute).

Any help or suggestions are much appreciated,
mike

Re: SelectLayerByAttribute_management - Correct syntax for expression

MikeWissner — Fri, 23 Mar 2012 13:39:31 GMT

Ok,

I figured out that for my code I don't need the brackets around the field name.

But now when I run the code, it says its completed successfully, but no selection is visible in my data frame. Very frustrating.

Re: SelectLayerByAttribute_management - Correct syntax for expression

BruceNielsen — Fri, 23 Mar 2012 13:55:20 GMT

The field name needs to be enclosed by double quotes:

arcpy.SelectLayerByAttribute_management("landuse", "NEW_SELECTION", "\"LU_2008\" = 'Cemetery' ")

Re: SelectLayerByAttribute_management - Correct syntax for expression

MikeWissner — Fri, 23 Mar 2012 14:09:13 GMT

Thanks Bruce.

Problem solved, I wasn't escaping the double quotes around the field name.

good luck Katrina

Re: SelectLayerByAttribute_management - Correct syntax for expression

KatrinaBennett — Fri, 23 Mar 2012 16:58:30 GMT

I tried what was suggested, to remove the backslashes. I am still getting the error:

#Select Analysis
print "Attempting Select"
expression1 = ' "ELEV" <= "Elev_metre" + 100 AND "ELEV" >= "Elev_metre" - 100 '
expression2 = ' "SLP" <= "mean_slp"+ "sd_slp" AND "SLP" >= "mean_slp" - "sd_slp" '
expression3 = ' "ASP" <= "mean_asp" + "sd_asp" AND "ASP" >= "mean_asp" - "sd_asp" '

arcpy.SelectLayerByAttribute_management(sel_pts_lyr, "NEW_SELECTION", expression1)
arcpy.SelectLayerByAttribute_management(sel_pts_lyr, "SUBSET_SELECTION", expression2)
arcpy.SelectLayerByAttribute_management(sel_pts_lyr, "SUBSET_SELECTION", expression3)

Traceback (most recent call last):
File "C:\data2\smart_clip2\model_clip.py", line 91, in <module>
arcpy.SelectLayerByAttribute_management(sel_pts_lyr, "NEW_SELECTION", expression1)
File "C:\Program Files (x86)\ArcGIS\Desktop10.0\arcpy\arcpy\management.py", line 4259, in SelectLayerByAttribute
raise e
ExecuteError: ERROR 000358: Invalid expression
Failed to execute (SelectLayerByAttribute).

Re: SelectLayerByAttribute_management - Correct syntax for expression

BruceNielsen — Sat, 11 Dec 2021 17:47:01 GMT

I wonder if this might make a difference:

expression1 = 'ABS("ELEV" - "Elev_metre") <= 100 '
expression2 = 'ABS("SLP" - "mean_slp") <= "sd_slp" '
expression3 = 'ABS("ASP" - "mean_asp") <= "sd_asp" '

Re: SelectLayerByAttribute_management - Correct syntax for expression

KatrinaBennett — Fri, 23 Mar 2012 21:51:17 GMT

Ok, it worked with some minor modification! In the end I had to make a new column for ELEV because the original column had different precision and scale, which apparently matters in the Python version of the Select Layer By Attribution, but not the Tool version.

Also, I combined these together using the ADD because the SUBSET_SELECTION did not appear to be working. I kept getting a zero selection in my shapefile, which was incorrect.

Thank you for you help!

Final code lines used are as follows:

expression1 = 'ABS("Elev2" - "Elev_metre") <= 100 AND ABS("SLP" - "mean_slp") <= "sd_slp" AND ABS("ASP" - "mean_asp") <= "sd_asp" '
#expression2 = 'ABS("SLP" - "mean_slp") <= 2*"sd_slp" '
#expression3 = 'ABS("ASP" - "mean_asp") <= 2*"sd_asp" '

arcpy.SelectLayerByAttribute_management(sel_pts_lyr, "NEW_SELECTION", expression1)

Katrina

Re: SelectLayerByAttribute_management - Correct syntax for expression

TerrenceDolan — Sat, 31 Mar 2012 18:39:15 GMT

Question regarding arcpy.SelectLayerByAttribute_management:

I can not figure out the problem with the following syntax:

arcpy.SelectLayerByAttribute_management("Faults_2","NEW_SELECTION"," [names2]='Wabash Valley liquefaction features' ")

Result: Runtime error <class 'arcgisscripting.ExecuteError'>: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute).

The layer "Faults_2" is a geodatabase feature class. I have zero experience with Python, any help is much appreciated!

-Terry

Re: SelectLayerByAttribute_management - Correct syntax for expression

AdelItani — Sat, 11 Dec 2021 17:47:04 GMT

Hy Katrina,
Just a notice:
did you include the next line:

import arcpy  # or argisscripting
# ...

Re: SelectLayerByAttribute_management - Correct syntax for expression

BruceNielsen — Mon, 02 Apr 2012 12:40:40 GMT

Terry,

This should work for you:

arcpy.SelectLayerByAttribute_management("Faults_2", "NEW_SELECTION", "\"names2\" = 'Wabash Valley liquefaction features'")

Field names in a GeoDB need to be surrounded in double quote, not brackets.

Re: SelectLayerByAttribute_management - Correct syntax for expression

TonyEnberg — Wed, 08 Aug 2012 05:33:47 GMT

I am having problems with a similar piece of Python code to Terry's but my field is long integer and value is a variable.

Using Terry's code could anyone show me the correct syntax if the field value is a variable of long integer type?

I have found a few examples via ESRI and other sites but non seem to work for me. Data is in a file GDB. ArcGIS version 10.

Thanks

Tony Enberg

Re: SelectLayerByAttribute_management - Correct syntax for expression

BruceNielsen — Wed, 08 Aug 2012 12:12:12 GMT

If the field type is integer, you don't enclose the expected value in single quotes. That allows you to simplify the expression even more by using the single quotes to encompass the entire expression. Use Python's string substitution to insert the value of the variable:

arcpy.SelectLayerByAttribute_management("FCname", "NEW_SELECTION", '"integerField" = %i' % variableName)