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

[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 = "Status__New = 'Occurrence'"
arcpy.SelectLayerByAttribute_management("tourmaline", "NEW_SELECTION", where)‍‍

[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]

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]

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]

If you are using ArcPy code in an IDE, the "CURRENT" argument for MapDocument—Help | ArcGIS Desktop doesn't have any effect.

If you are running this code in an IDE, how are you determining that nothing is getting selected?

[Kevin_C]

Ah, sorry. I should've clarified. I'm writing my larger script within Pycharm. But I'm testing that each method and function works by copy-pasting them into ArcMap's native Python editor. 

I referenced the actual map document in Pycharm using the file path, and within ArcMap's python editor, I used "CURRENT", since I didn't feel like typing in the whole file path. 

I'm going to try and see if I can do the same function for a different map document and layer. I feel like the problem here is simpler than I think it is. 

To clarify your earlier comment, do I really need to be using AddLayer after creating the newly selected features? If so, how did I do it wrong in the previous comment?

Thanks for your help!

[JoshuaBixby]

I thought you wanted to add a feature class to an existing, open map document using Layer.  Testing code snippets in the interactive Python window works, most of the time, but there are some differences for how ArcPy code works from within and outside of the ArcGIS application.

If the script is going to run outside of ArcGIS Desktop, I suggest you test the code snippet outside of ArcGIS Desktop.  Taking a step back, what are you trying to accomplish that isn't working?  I know it involves selecting data.  What is the data source?  What is your selection criteria?  How are you checking whether the selection works?

[Kevin_C]

I tried using SelectLayerByAttribute on a feature class in my home desktop, and it worked fine. These were different data, so I think the problem could be with my original dataset and how I defined it. I will try it again when I get back to work.

I was trying to use SelectLayer because I wanted the map to zoom to a layer in the data frame. So ZoomToSelectedFeatures seemed the easiest way to get to that. 

[Kevin_C · Accepted solution]

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.