Using Defintion Query in a Python Script

Question

I have a script that is using a parcel feature class from SDE.  This parcel feature has Right-of-Way (ROW) information.  I would like to use a definition query so that when I do a select by location, the ROW features are not in the selection.for layer in arcpy.mapping.ListLayers(mxd, "*"):
     if layer.name == "DOUGLAS_GIS.GISDATA.Parcel_Boundary":
          layer.definitionQuery = "'TAG' IS NOT \'ROW %\'"

Could someone please look at this syntax and let me know if there is something wrong?

This is the definition query that I use in an MXD.

TAG NOT LIKE 'ROW %'

Thanks,

Chad

T__WayneWhitley · Answer

That'll be fine, although really not as efficient...you get a point regardless for ingenuity.  Still, if you run across this type thing again, a def query could be your ticket and is worth the practice.  Also, if you're just interested in working with in-memory layers rather than messing with your ArcMap TOC layer objects, you could use Make Feature Layer with a query (or copy to in_memory workspace), etc...and you're back to the need for query syntax again.  Nice to know that there are various legitimate options!

Glad you got it!

Enjoy,
Wayne

ChadFoster · Answer

Wayne,

Thanks for the info, I will give it a try.

In the meantime, instead of using the definiton query I just added another select statement after the initial select, "REMOVED_FROM_SELECTED" the ROW's.  Should have thought of that first.

Thanks,

Chad

T__WayneWhitley · Answer

Good info, and I'd just like to add escaping quotes can be excruciating, so maybe do it this way: layer.definitionQuery = "{0} NOT LIKE '{1} %'".format(arcpy.AddFieldDelimiters(layer, 'TAG'), 'ROW') To further explain, the query expression is contained in: "{0} NOT LIKE '{1} %'" {0} and {1} are variables for substitution using the '.format' function, so... {0} is what is evaluated by arcpy.AddFieldDelimiters(layer, 'TAG') {1} is the string 'ROW' ...which yields a query string (for an SDE source): "TAG" NOT LIKE 'ROW %' You have to be careful with the multiple character wildcard, and if that query yields unexpected results, then you could simply 'lop off' that nonsense with a minor adjustment and include the 'not equal to' operator: layer.definitionQuery = "{0} <> '{1}'".format(arcpy.AddFieldDelimiters(layer, 'TAG'), 'ROW') ...which of course the query string would be (for an SDE source): "TAG" <> 'ROW' Hope that clears things up some...this is a fairly powerful technique when it's right - always a pain to trip on just the syntax of it. Enjoy, Wayne EDIT - A final comment about SQL syntax, note that you have a space in your value, 'ROW %' --- for me this does not filter any values out if I have no characters after the 'W'; however, using this value, 'ROW%' performs as expected. So a rule of thumb to follow (and it does look like you have your bases covered) is to make certain your SQL is sound i.e. returning what you intended (and only what you intended). Otherwise code your field values a little 'tighter', but that's really a separate topic of data integrity.