topic Re: Changing Query Layer's SQL Expression with Python in Python Questions

Changing Query Layer's SQL Expression with Python

AlexZhuk — Thu, 28 Apr 2016 18:55:09 GMT

I use query layers in ArcMap 10.4. The maps need to be done/repeated periodically. The easiest way is to copy the map, change the source's SQL expression (e.g. from select * from <tbl> where Period = '2016-March' to select * from <tbl> where Period = '2016-April') and that's it. But the expressions are long and hard to edit. Then I have many similar maps, where I have to make edits manually. Is there a way to do this with Python?

P.S. Using the combination of query layer and definition query is not doable in my case.

Thanks!

Re: Changing Query Layer's SQL Expression with Python

WesMiller — Fri, 29 Apr 2016 15:14:47 GMT

Have you looked at this tool Make Query Layer—Help | ArcGIS for Desktop

Re: Changing Query Layer's SQL Expression with Python

BlakeTerhune — Fri, 29 Apr 2016 16:05:03 GMT

Could you use the definitionQuery property of Layer?

Re: Changing Query Layer's SQL Expression with Python

BruceLang — Sat, 11 Dec 2021 08:37:28 GMT

This is the way I handled it - maybe someone can streamline this.

#
# Modify definition query to specific layers in map document and
# save the modified document to disk.
#


import arcpy


# map document to modify
mapdoc = r"\\hcgis2\e\Arc\Projects\GIS\basemap - All districts for selected year\basemap.mxd"


oldyr = "2014"  # modify this...
newyr = "2015"  # ...to this...


# ...within these layers - make all lower case
lyrs = {"airport",
        "community college",
        "city",
        "fire",
        "school",
        "hospital",
        "library",
        "mass transit",
        "multi twp assess",
        "park",
        "road district",
        "sanitary",
        "special",
        "tiff"}


# open map document to modify
mxd = arcpy.mapping.MapDocument(mapdoc)


# scan through all map layers
for maplyr in arcpy.mapping.ListLayers(mxd):
    ml = maplyr.name.lower()


    # if layer to modify...
    if ml in lyrs:
        # ...then update the year within the definition query
        print "Processing: ", ml
        maplyr.definitionQuery = maplyr.definitionQuery.replace(oldyr, newyr)


# save changes to map document
mxd.save()
    
# clean up
print "Finished"
del mxd, oldyr, newyr, lyrs, ml

Re: Changing Query Layer's SQL Expression with Python

AlexZhuk — Fri, 29 Apr 2016 16:53:06 GMT

Thanks! Unfortunately, in my case, for certain reasons, use of the Definition Query is not an option.

Re: Changing Query Layer's SQL Expression with Python

AlexZhuk — Fri, 29 Apr 2016 16:53:41 GMT

Thank you! I will look into this.

Re: Changing Query Layer's SQL Expression with Python

AlexZhuk — Fri, 29 Apr 2016 16:56:51 GMT

Thank you for the thorough post! Do I understand correctly that you use the regular layer (based on a shapefile or File GDB)? I, however, use the Query Layer, and it has the SQL expression as a part of its source. Unfortunately, in my case, for certain reasons, use of a Definition Query is not an option.

Re: Changing Query Layer's SQL Expression with Python

BruceLang — Fri, 29 Apr 2016 17:54:00 GMT

Yes, using the layer's "Definition Query" property where each layer is joined to a SQL View of "Taxing Districts" containing data for multiple years.

All layers "point" to the same parcel polygon feature and the "Definition Query" selects the appropriate "year" for that particular layer.

So, in my case, a parcel (a polygon) can only be in one fire district so the definition query is; parcel_year =2015 AND unit_type_code = 'FRD'

My code changes the year for each layer while keeping the district type unchanged. I hope that helps someone!

Re: Changing Query Layer's SQL Expression with Python

AlexZhuk — Fri, 29 Apr 2016 17:59:00 GMT

Yes, I wish it had been so simple/easy/straightforward in my case. And maybe it is. I just need to look more into it. Thank you!

Re: Changing Query Layer's SQL Expression with Python

LiesbethDe_Groote — Fri, 18 Aug 2017 08:50:08 GMT

Hi Alex,

We have the same problem. I would like to adapt the query layer within the data source tab of a layer. i need to do this for more than 500 layers, and woul like to do this by script, because otherwise it would take me about 2 days.

Did you find a way to do this?

Kind regards,

Liesbeth