nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Changing Query Layer's SQL Expression with Python

AlexZhuk

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!

Find more posts tagged with

Accepted answers

All comments

LiesbethDe_Groote

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

AlexZhuk

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!

BruceLang

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!

AlexZhuk

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.

AlexZhuk

Thank you! I will look into this.

AlexZhuk

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

BruceLang

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

blake.terhune

Could you use the definitionQuery property of Layer?

WesMiller

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

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.