topic Re: arcpy.SelectLayerByAttribute returns a subset of the "truth" in Geoprocessing Questions

arcpy.SelectLayerByAttribute returns a subset of the "truth"

LaneGelhorn — Fri, 07 Oct 2011 21:44:42 GMT

Hi,

I'm trying to write a python script which selects a number of cases from an attribute table (table view within a file geodatabase) to allow further processing but am getting curious results. It seems as though the query stops once it fullfills the latest condition, regardless of preceeding conditions. For example:

arcpy.SelectLayerByAttribute_management ("view", "NEW_SELECTION",
(""" "SP10" = 'WS' """ or """ "SP10" = 'BS' """ or """ "SP10" = 'JP' """ or """ "SP10" = 'BF' """ or """ "SP10" = 'TL' """) and (""" "SP11" = 'TA' """ or """ "SP11" = 'BP' """ or """ "SP11" = 'WB' """ or """ "SP11" = 'WE' """ or """ "SP11" = 'MM' """ or """ "SP11" = 'GA' """ ))

will return only those records where SP11 = TA, regardless of the value of SP10. In this case 13 records are returned. I can find those same 13 records from using the where condition "SP11" = 'TA'
in the ArcGIS select by attributes dialogue box. Placing the full set of conditions in the select by attributes dialogue box I get a different set of 14 (correct) selected records.

Does anyone have any advice for dealing with this? Others found similar results?

Re: arcpy.SelectLayerByAttribute returns a subset of the "truth"

KimOllivier — Sun, 09 Oct 2011 02:16:28 GMT

Use a simpler SQL expression like

sql expr = "SP10 in ('WS','BS','AP') AND SP11 in ('MM','GA')"

If your logic is hard to follow, make subsets and select multiple times using the 'SELECT_FROM...' options.

Check each sql query produces the desired values by running it interactively in ArcMap table selections.

Also get rid of all the extra double quotes. If you use valid field names you don't ever need to quote them. (ie start with a character, no spaces or punctuation characters, no reserved words)
[You do need square brackets for MS Access, but that doesn't count]

Re: arcpy.SelectLayerByAttribute returns a subset of the "truth"

LaneGelhorn — Tue, 11 Oct 2011 14:21:49 GMT

Thank you kimo. I had forgotten about 'in' as an SQL operator (i'm a recovering SAS programmer). This makes the code much more clear. Using a new select, then select from.. as you suggest simplifies things as well.

It appears that what I was missing is that the arguments to the arcpy function all have to be within a single quoted string. The following line returns the 14 expected values.

arcpy.SelectLayerByAttribute_management ("view", "NEW_SELECTION", """ "SP10" in ('WS','BS','JP','BF','TL') and "SP11" in ('TA','BP','WB','WE','GA','MM') """)