Changing Query Layer's SQL Expression with Python

3448
9
04-28-2016 11:55 AM
AlexZhuk
Occasional Contributor III

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!

0 Kudos
9 Replies
WesMiller
Regular Contributor III

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

AlexZhuk
Occasional Contributor III

Thank you! I will look into this.

0 Kudos
BlakeTerhune
MVP Regular Contributor

Could you use the definitionQuery property of Layer​?

0 Kudos
AlexZhuk
Occasional Contributor III

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

0 Kudos
BruceLang
Occasional Contributor III

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
AlexZhuk
Occasional Contributor III

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.

0 Kudos
BruceLang
Occasional Contributor III

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!

0 Kudos
AlexZhuk
Occasional Contributor III

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!

0 Kudos
LiesbethDe_Groote
New Contributor

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

0 Kudos