topic Re: Select Layer By Attribute in Python in Python Questions

Select Layer By Attribute in Python

JohnWall — Mon, 12 Sep 2016 14:22:07 GMT

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?

Re: Select Layer By Attribute in Python

AlexanderBrown5 — Mon, 12 Sep 2016 15:07:59 GMT

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

Re: Select Layer By Attribute in Python

JohnWall — Mon, 12 Sep 2016 15:22:59 GMT

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

Re: Select Layer By Attribute in Python

AlexanderBrown5 — Sat, 11 Dec 2021 13:15:44 GMT

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

Re: Select Layer By Attribute in Python

JohnWall — Mon, 12 Sep 2016 16:14:51 GMT

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

Re: Select Layer By Attribute in Python

AlexanderBrown5 — Sat, 11 Dec 2021 13:15:47 GMT

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

Re: Select Layer By Attribute in Python

DarrenWiens2 — Mon, 12 Sep 2016 18:03:20 GMT

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.

Re: Select Layer By Attribute in Python

AlexanderBrown5 — Sat, 11 Dec 2021 13:15:50 GMT

Darren Wiens Completely forgot about AddFieldDelimiters! Thanks for mentioning.

John,

As Darren pointed out, you can just set your SQL expression, and reference as a variable in the SelectLayerByAttribute_management.

sql_exp = """{0} > {1}""".format(arcpy.AddFieldDelimiters(dissBuffs, "sumPoint_2"), int(1))‍
arcpy.SelectLayerByAttribute_management("dissBuffs_lyr", "NEW_SELECTION", sql_exp)‍‍‍‍

This would ensure that no matter where you pulled the data from, it would be formatted properly.

Re: Select Layer By Attribute in Python

DerekNelson — Thu, 14 Dec 2017 19:39:54 GMT

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?

Re: Select Layer By Attribute in Python

AlexanderBrown5 — Fri, 15 Dec 2017 13:48:29 GMT

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

Re: Select Layer By Attribute in Python

DerekNelson — Fri, 15 Dec 2017 15:19:02 GMT

https://community.esri.com/thread/206718-various-issues-with-selectlayerbyattributemanagement-within-joined-features-in-python

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