nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to use SQL queries in arcpy.SelectLayerByAttribute?

Kevin_C

I'm trying to select features from a layer that I know exist. I'm using [SelectLayerByAttribute_management][1] in arcpy.
I have a layer that is properly defined earlier in the code named "tourmaline". I'm trying to query this layer to return all features with value of "Occurrence" in the field STATUS__NEW.

This is what I have:

where = "Status__New = 'Occurrence'"
arcpy.SelectLayerByAttribute_management(tourmaline, "", where)

No features on the map are selected when this code is run.
I've tried variations of the where clause using double quotes and square brackets around field name, but none of them work.

Using ArcMap's Select Layer By Attributes tool to select the same features using the following SQL statement selects the features correctly.

SELECT * FROM tourmaline WHERE:
Status__New = 'Occurrence'

Why is this happening? Why is my SelectLayerByAttribute statement wrong?

[1]: http://desktop.arcgis.com/en/arcmap/latest/tools/data-management-toolbox/select-layer-by-attribute.htm

Finde mehr Beiträge mit dem Tag

Accepted answers

Kevin_C

Turns out I did not properly define my tourmaline layer. I used arcpy.mapping.Layer(r"C:\tourmaline.lyr") to define it. I should've gone the route of using ListLayers and indexing to point the layer at a variable and then call that variable.

All comments

Kevin_C

Thank you for the explanation.

I am running this script from my IDE and is not intended as a script toolbox.

Referring to your reply to my other post which is of relevance to this problem too, would adding AddLayer fix it? I tried the following, but got Runtime Error.

select = arcpy.SelectLayerByAttribute_management(tourmaline, "", where)
arcpy.mapping.AddLayer(df,select)

JoshuaBixby

When it comes to geoprocessing tools, the Data Type value often refers to the type of the underlying object but you commonly reference that object via a string. For example, the where_clause parameter says the Data Type is "SQL Expression." You don't pass an actual "SQL Expression" object, but a string containing a valid SQL expression.

Seeing how you are creating your layer file object, I would need to know more about your code, such as how you are adding the layer to the map after creating the layer object. Is this being run in the interactive Python or as a script toolbox?

Kevin_C

Changing the input layer parameter from layer to string doesn't seem to work. Besides, the help docs here suggest it cannot be a string.

I defined the layer originally as a Layer object using

tourmaline = arcpy.mapping.Layer(r"C:\tourmaline.lyr")

This is the tourmaline variable I was referring to in my function.

JoshuaBixby

If the layer name is "tourmaline", then you should pass the name of the layer as a string. If you have a variable tourmaline containing a reference to the layer object or reference to the name of the layer object, then you pass it the way your code shows now.

Try:

where <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Status__New = 'Occurrence'"</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>SelectLayerByAttribute_management<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"tourmaline"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"NEW_SELECTION"</SPAN><SPAN class="punctuation token">,</SPAN> where<SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Nimm an der Unterhaltung teil

Angemeldete Mitglieder können Beiträge verfassen, Updates folgen und mehr. Neu hier? Registriere ein kostenloses Konto.

Schnellzugriffe

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.