Delete vs deactivate layer definition query

[JeffThomasILM]

I'm trying to automate clearing an existing layer definition query and setting a new one. The code I wrote does successfully set the new def query, but it deactivates the old query rather than actually deleting it. The Layer class code sample 2 shows how it presumably would be done, but it doesn't actually delete it. ArcGIS Pro - Modifying Layer Definition query via ArcPY also essentially asks the same question, but it was never fully answered.

for map in aprx.listMaps():
    lyr = map.listLayers("Zoning History")[0]
    lyr.definitionQuery = None  ## have also tried ""
    lyr.definitionQuery = "CaseNumber = '" + case + "'"‍‍‍‍‍‍‍‍‍‍‍‍

I've tried setting it as None and "" - both result in deactivating rather than deleting the old query. It's not a showstopper, but it's kind of weird. If I change the def query again, it will overwrite the old-new query, but the old-old query (which was not set using ArcPy, if it matters) still remains present but inactive.

I started thinking about other scenarios, such as what if I wanted to deactivate rather than delete a query? I haven't found any documentation for how to do that properly (rather than unintentionally) with ArcPy.

[RogerDunnGIS]

So, the layer has to be a Layer object, and typically these are obtained by getting a reference to layer within a map within a project.  The sqlFilter object is a string and must contain an SQL statement that functions on the layer, given its workspace type.  So, here's an example of how that might be used where the layer is in an enterprise geodatabase and quotes around field names are unnecessary.  The fields CHILLS, CONTROL, and SUPPLIED_POWER are numeric fields and it is hoped that they are non-null.

proj = arcpy.mp.ArcGISProject("CURRENT") # or "C:\Grease.aprx", etc.
my_map = proj.activeMap # or proj.listMaps("Map"). Do not create a variable named map because map is a certain function in Python
my_layer = my_map.ListLayers("TheOneThatIWant")
my_filter = "CHILLS * CHILLS - CONTROL = SUPPLIED_POWER" # Do not create a variable called filter because filter is a certain function in Python
setDefinitionQuery(my_layer, my_filter)

Once this code executes, the layer named TheOneThatIWant inside the active map (or the map named "Map") inside the currently-open project (or the project at C:\Grease.aprx) will now only show features in the map and in the attribute table where a given feature's CHILLS value squared minus its CONTROL value are equal to its SUPPLIED_POWER field.

This is perfect for when you've got chills, they're multiplyin', and you're losing control, because the power you're supplying, is electrifying.  Your map will really shape up after this.  Let me know if you have any more questions.

[edward33]

I am not super familiar with functions so I ended up going this route based on @RogerDunnGIS tip above

#Set definition query for Main Map
map_obj = aprx.listMaps(map_name)[0]
layer_obj = map_obj.listLayers(layer_name)[0]
definition_query = f"{field_name} = '{query_value}'"
layer_obj.updateDefinitionQueries([
    {'name': 'Query 1', 'sql': definition_query, 'isActive': True}
    ])

[BobCapezza]

Worked like a charm.  Thanks !

[Shauna-RaeBrown]

I finally figured out how to REMOVE/DELETE the definition query from a specific layer in a specific map.  Ultimately, I used the Cartographic Information Model (cim) to access the definitionExpression. 

I read through the Python CIM access web page. https://pro.arcgis.com/en/pro-app/2.9/arcpy/mapping/python-cim-access.htm

In my aprx file, I right-clicked the map's name and selected "Save As Map File" the map(s), and them opened the *.mapx file as a *.json file.  Next, I searched through the file for the "definitionExpression" field.  After trying a bunch of things, I finally found that I want to change the layers "featureTable.definitionExpression" to equal "".

Here's my code.

import arcpy
# Reference a project, map, and layer using arcpy.mp
aprx = arcpy.mp.ArcGISProject('current')
m = aprx.listMaps("Inset")[0]
lyr = m.listLayers("AZ811 BlueStake")[0]
# Return the layer's CIM definition
cim_lyr = lyr.getDefinition('V2')
# Modify the featureTable.definitionExpression
cim_lyr.featureTable.definitionExpression = ""
# Push the changes back to the layer object
lyr.setDefinition(cim_lyr)

[RogerDunnGIS]

Oh, I didn't know you wanted to remove the definition query; I thought you wanted to do what the OP was asking: set a new definition query and forget the old ones.  To remove definition queries on a layer, you set the layer's definitionQuery property to None.  If you're new to Python, I would advise staying away from the Cartographic Information Model.  Even with years of Python experience under my belt, and even more in other languages, I stay away from it myself.  It's very sensitive to prodding and changes.

[Shauna-RaeBrown]

I tried to set the definitionQuery property to "" and None.  All that happened was the layer's definition query was made inactive and the new definition query was created and made active.   Thanks for your advice.  I will just have to accept that I will have to delete the old definition query from within the aprx.

[Joseph_Kinyon]

This is great stuff, thanks for sharing. 
Another thanks to Roger Dunn and Joshua Bixby
While it isn't the prettiest or even complete, I shoe horned it into a script tool in model builder to make use of the Iterate Multivalue tool in ArcGIS 3
The script is called Definition Query Update in the model image.
I thought I'd add back how it worked for my needs.

"""
Script documentation
- Tool parameters are accessed using arcpy.GetParameter() or 
                                     arcpy.GetParameterAsText()
- Update derived parameter values using arcpy.SetParameter() or
                                        arcpy.SetParameterAsText()
"""
import arcpy
def script_tool(param0, param1, param3):
    """Script code goes below"""
    return
#get the input paramaters passed from the model output and variables
param0 = arcpy.GetParameterAsText(0)
param1 = arcpy.GetParameterAsText(1)
param3 = arcpy.GetParameterAsText(3)
aprx = arcpy.mp.ArcGISProject('current')
m = aprx.listMaps()[0]
l = m.listLayers()[0]
#limit changes to the list of layers selected, in this version layers cannot be in a layer group to work
for l in m.listLayers(param0):
    if l.supports('DefinitionQuery'):
       #Get the list of definition queries
        dql = l.listDefinitionQueries()
       #Clear active definition queries otherwise the update will fail if there is already an active query
        for dq in dql:
            dq['isActive'] = False
       #Create a new definition query and append it to the list
        dql.append({'name': param3, 'sql' : param1, 'isActive': True})
       #Update the definition queries with the newly modified list
        l.updateDefinitionQueries(dql) 
    
arcpy.SetParameterAsText(2, param1)
arcpy.AddMessage(param0)
arcpy.AddMessage(param1)
arcpy.AddMessage(param3)
#consider inserting a file picker variable that allows you to select an existing definition query .exp file for the sql input