Select Layer By Attribute in Python

8286
10
09-12-2016 07:22 AM
JohnWall
Occasional Contributor

I have the following code snippet:

arcpy.SelectLayerByAttribute_management("dissBuffs_lyr", "NEW_SELECTION", '[sumPoint_2] > 1')

Where dissBuffs_lyr is a layer which has been converted from a feature class stored within a File Geodatabase with a variety of attributes one of which is sumPoint_2. I've tried so many variations of '[sumPoint_2] > 1' that I'm running out of ideas does anyone have any idea what I might be doing wrong?

0 Kudos
10 Replies
AlexanderBrown5
Occasional Contributor II

John,

Try this:

arcpy.SelectLayerByAttribute_management("dissBuffs_lyr", "NEW_SELECTION", '"sumPoint_2" > 1')

Since you are trying to select from a file geodatabase and not a full RDMS, you cannot put the field name in brackets.  They need to be in quotes.

~Alex

JohnWall
Occasional Contributor

Hey Alex,

I entered: 

arcpy.SelectLayerByAttribute_management("dissBuffs_lyr", "NEW_SELECTION", '"sumPoint_2" > 1')

and got:

Traceback (most recent call last):
File "C:\Python27\ArcGISx6410.3\Lib\site-packages\pythonwin\pywin\framework\scriptutils.py", line 326, in RunScript
exec codeObject in __main__.__dict__
File "C:\gis\hpboyce02\tools\hydropath\part03a_minPoints_troubleshoot.py", line 56, in <module>
arcpy.SelectLayerByAttribute_management("dissBuffs_lyr", "NEW_SELECTION", '"sumPoint_2" > 1')
File "C:\ArcGIS\Desktop10.3\ArcPy\arcpy\management.py", line 7221, in SelectLayerByAttribute
raise e
ExecuteError: ERROR 000358: Invalid expression
Failed to execute (SelectLayerByAttribute).

-John

0 Kudos
AlexanderBrown5
Occasional Contributor II

John, 

Are you running the code snippet directly in the Python window in ArcMap or as a stand a lone script?  You are going to need the full path to the dissBuffs_lyr if you are not running it in the ArcMap python window.


You can try this as well (Only if your field is an integer):

arcpy.SelectLayerByAttribute_management("dissBuffs_lyr", "NEW_SELECTION", "sumPoint_2 > 2")

If you are running it in ArcMap (can you paste the additional messages shown below the console window).  

Example:

ERROR 000358: Invalid expression
An invalid SQL statement was used.
An invalid SQL statement was used. [dissBuffs_lyr]
An invalid SQL statement was used. [SELECT OBJECTID FROM dissBuffs_lyr WHERE 'sumPoint_2' > 2]
Failed to execute (SelectLayerByAttribute).
Failed at Mon Sep 12 11:31:55 2016 (Elapsed Time: 0.01 seconds‍‍‍‍‍‍

~Alex

JohnWall
Occasional Contributor

Hi Alex,

I am running the code within PythonWin as a standalone Python script.

I was under the impression that I did not need to provide the path name if I created the layer within the same script (see below). Am I incorrect?

dissBuffs = "C:/gis/area/data/fgd.gdb/dissBuffs"

arcpy.MakeFeatureLayer_management(dissBuffs, "dissBuffs_lyr")
arcpy.AddJoin_management("dissBuffs_lyr", "OBJECTID", sumPoints, "OBJECTID", "KEEP_COMMON")
arcpy.CopyFeatures_management("dissBuffs_lyr", "jointedcountpolys.shp")
arcpy.SelectLayerByAttribute_management("dissBuffs_lyr", "NEW_SELECTION", "sumPoint_2 > 2")
arcpy.CopyFeatures_management("dissBuffs_lyr", correctPolys)

I tried "sumPoint_2 > 2" and still got the same result.

Thanks,

John

0 Kudos
AlexanderBrown5
Occasional Contributor II

John,

Your code snippet above helps a lot, there are a few items I would like to point out.

Properly setting your workspace:

Current Workspace (Environment setting)—Help | ArcGIS for Desktop 

Example 1 (without setting workspace and exporting a shapefile):

arcpy.CopyFeatures_management("dissBuffs_lyr", "C:/gis/area/data/jointedcountpolys.shp")‍‍

Example 2 (set workspace to a folder, export shapefile):

arcpy.env.workspace = "C:/gis/area/data"‍‍‍
arcpy.CopyFeatures_management("dissBuffs_lyr", "jointedcountpolys.shp")‍‍‍‍‍‍

Example 3 (set workspace to file geodatabase, export to feature class):

arcpy.env.workspace = "C:/gis/area/data/fgd.gdb"‍‍‍
arcpy.CopyFeatures_management("dissBuffs_lyr", "jointedcountpolys")‍‍‍‍‍‍‍‍

Learning to set proper workspaces will be beneficial if you have more complex code that will run loops or iterate over various datasets.  You do not have to specifically reference the full path if you have preset variables already identified.  Although, utilizing workspaces is better practice when working with arcpy.

If you want to use the variable name correctPolys without quotes, it needs to be declared as a variable before CopyFeatures.

Example 4 (utilizing declared correctPolys variable name, set to feature class):

correctPolys = "C:/gis/area/data/fgd.gdb/correctPolys"
arcpy.CopyFeatures_management("dissBuffs_lyr", correctPolys)‍‍‍‍‍‍

Or you can put quotes around "correctPolys", but you need to declare your workspace first:

Example 5 (declare workspace, utilize quotes around output feature class name):

arcpy.env.workspace = "C:/gis/area/data/fgd.gdb"
arcpy.CopyFeatures_management("dissBuffs_lyr", "correctPolys")‍‍‍

I created a test feature class in a file geodatabase called "dissBuffs" and added the sumPoints_2 field as integer.  I also populated the sumPoints_2 field with dummy data.  

I don't know when you populate sumPoint_2, you have a join to a table or feature class called sumPoints.  I was able to successfully run this on my test version:

import arcpy
arcpy.env.workspace = "C:/gis/area/data/fgd.gdb"
dissBuffs = "C:/gis/area/data/fgd.gdb/dissBuffs"
arcpy.MakeFeatureLayer_management(dissBuffs, "dissBuffs_lyr")
arcpy.SelectLayerByAttribute_management("dissBuffs_lyr", "NEW_SELECTION", "sumPoint_2 > 1")
arcpy.CopyFeatures_management("dissBuffs_lyr", "correctPolys")

That should output correctPolys into your a geodatabase; assuming the sumPoint_2 filed is already populated.  I want to try and isolate your problems with the selection using select by attribute.

If sumPoint_2 is not populated, can you add some test data and run my last snippet to see if it works?

~Alex

DerekNelson
New Contributor II

Hey Alex,

I have posted a similar type question to the GeoNet today but since this is in the same vein I figured I would ask you directly. This doesn't answer the question about selecting features by attribute though, especially when joined in-memory...is it not possible? What is the "full path" you recommend to a feature layer in memory...is that a thing?

0 Kudos
AlexanderBrown5
Occasional Contributor II

Derek,

The SQL at the end of the SelectLayerByAttribute_management call is where you are selecting based on attributes.  In this example 

arcpy.SelectLayerByAttribute_management("dissBuffs_lyr", "NEW_SELECTION", "sumPoint_2 > 1")

You are selecting all records that have the attribute "sumPoint_2" that is greater than 1.  The function then stores this in memory as whatever you want to call the variable; in this case "NEW_SELECTION."

Can you post a link to your other post with details? Are you trying to join multiple tables? I need to know more context before I can provide you an appropriate answer.

~Alex

0 Kudos
DerekNelson
New Contributor II

https://community.esri.com/thread/206718-various-issues-with-selectlayerbyattributemanagement-within... 

Think I got it figured with the help of others...something weird about referencing table name and then ".fieldname". I was ref layername...currently testing when feature classes have same names now though because I still suspect issues..thanks for any assist

0 Kudos
DarrenWiens2
MVP Honored Contributor

Arcpy comes with a handy function called AddFieldDelimiters to alleviate these SQL syntax errors - it encloses your field name properly within quotes, brackets, or none depending on the data source.