Change query layer SQL expression using Python

Summary

When working on a map with multiple query layers that reference a database table, in some cases, the query layers' SQL expression requires an update. For example, if the referenced database table is renamed or switched to a new database table, the query layer SQL expression can be updated manually using the 'Change Query' button in the Layer Properties window. However, updating the expression manually can be tedious if there are multiple query layers. Refer to ArcMap: Modify a query layer for more information.

Procedure

The Python script and the arcpy.mapping module can be used to automate the query update process. The following steps describe how to do so.

1. Import the ArcPy module.

import arcpy

2. Create a new query layer with the necessary updates to the SQL expression in the existing map document.

sdeworkspace_path = r"D:\Connections\sample.sde"
querypath = "Select * from sample where NAME = 'Test'"
arcpy.MakeQueryLayer_management(sdeworkspace_path, "New_Sample_Layer", querypath, "OBJECTID", "POLYGON", "3735")
MXD = arcpy.mapping.MapDocument(r"D:\Data\Mxd\Sample_Query.mxd")
DF = arcpy.mapping.ListDataFrames(MXD)[0]
output_layer = arcpy.mapping.Layer("New_Sample_Layer")
arcpy.mapping.AddLayer(DF, output_layer, "AUTO_ARRANGE")
MXD.save()

3. Reference the query layer created in Step 2 as the source for the SQL expression update.

mxd2 = arcpy.mapping.MapDocument(r"D:\Data\Mxd\Sample_Query.mxd")
df2 = arcpy.mapping.ListDataFrames(mxd2, "Layers")[0]
sourceLayer = arcpy.mapping.ListLayers(mxd2, "New_Sample_Layer", df2)[0]

4. Specify the map document to implement the updates using the arcpy.mapping.UpdateLayer() function.

mxd1 = arcpy.mapping.MapDocument(r"D:\Data\Mxd\Sample_Query_1.mxd")
df = arcpy.mapping.ListDataFrames(mxd1, "Layers")[0]
updateLayer = arcpy.mapping.ListLayers(mxd1, "Sample_Query", df)[0]
arcpy.mapping.UpdateLayer(df, updateLayer, sourceLayer, False)

5. Save the edits to the existing map document, and clear all variable references to clear any .lock files.

mxd1.save()
del mxd1, mxd2
del MXD

The following is a sample of the full script:

import arcpy

sdeworkspace_path = r"D:\Connections\GEODATA1051@gisadmin.sde"
querypath = "Select * from GISADMIN.Test_Parks where NAME = 'Test'"
arcpy.MakeQueryLayer_management(sdeworkspace_path, "New_Parks_Layer", querypath, "OBJECTID_1", "POLYGON", "3735")
MXD = arcpy.mapping.MapDocument(r"D:\Data\Mxd\Parks\Parks_Query.mxd")
DF = arcpy.mapping.ListDataFrames(MXD)[0]
output_layer = arcpy.mapping.Layer("New_Parks_Layer")
arcpy.mapping.AddLayer(DF, output_layer, "AUTO_ARRANGE")
MXD.save()

mxd2 = arcpy.mapping.MapDocument(r"D:\Data\Mxd\Parks\Parks_Query.mxd")
df2 = arcpy.mapping.ListDataFrames(mxd2, "Layers")[0]
sourceLayer = arcpy.mapping.ListLayers(mxd2, "New_Parks_Layer", df2)[0]

mxd1 = arcpy.mapping.MapDocument(r"D:\Data\Mxd\Parks\Parks_Query1.mxd")
df = arcpy.mapping.ListDataFrames(mxd1, "Layers")[0]
updateLayer = arcpy.mapping.ListLayers(mxd1, "Parks_Query", df)[0]
arcpy.mapping.UpdateLayer(df, updateLayer, sourceLayer, False)

mxd1.save()
del mxd1, mxd2
del MXD