Adding query layer to MXD in ArcPy

Question

I want to add a query layer to an arcmap document so that whenever i have a db data update, it is reflected in my ma and I can update my service through python script. This is my script so far...

import arcpy
from arcpy import env
arcpy.env.overWriteOutput = 1
# define local variables
wrkspc = 'C:/ArcGIS_Workspace/'
mxd = arcpy.mapping.MapDocument(wrkspc + 'scripts/Trial.mxd')
input_db_name = "Database Connections/xxx.sde"
outLayer = "DynamicQueryLayer"
out_layer_name = wrkspc +"/shapefiles/DynamicQueryLayer.lyr"
query = "SELECT * FROM TEST_TABLE"
#unique key for the table
oid_field = "schema.TEST_TABLE.TEST_ID"
try:
 # Create a query layer
 arcpy.MakeQueryLayer_management (input_db_name, outLayer, query, oid_field)
 
 # save the created query layer
 arcpy.SaveToLayerFile_management(outLayer, out_layer_name, "ABSOLUTE")
 # Get the created layer
 lyr = arcpy.mapping.Layer(out_layer_name)
 # get the data frame of the given mxd
 data_frame = arcpy.mapping.ListDataFrames(mxd)[0]
 # Switch to data view
 mxd.activeView = data_frame.name
 
 arcpy.mapping.AddLayer(data_frame, lyr, 'TOP')
 print "Added layer to the MXD ..."
 mxd.save()
 print "MXD saved..." 
except Exception, e:
 # If an error occurred, print line number and error message
 import traceback, sys
 tb = sys.exc_info()[2]
 print "Line %i" % tb.tb_lineno
 print e.message

I get this error

ERROR 000732: Input Features: Dataset DynamicQueryLayer does not exist or is not supported.

I am sure there is a way to save the created query layer as a layer file. Any help is greatly appreciated.

Answer 1

There's no need to save an intermediate layer file and then load it.

MakeQueryLayer_management returns a "result" object:

r = arcpy.MakeQueryLayer_management (input_db_name, outLayer, query, oid_field)
print(r)
# <Result 'DynamicQueryLayer'>

This result object is indexable, and the first item happens to contain the layer object:

print(r[0])
# <map layer u'DynamicQueryLayer'>
print(type(r[0]))
# <class 'arcpy._mapping.Layer'>
# Just to see what else is in there, we can convert to a list:
print(list(r))
# [<map layer u'DynamicQueryLayer'>]
# The layer is the only element.

This object can be added to the MXD's data frame directly:

lyr = r[0]
arcpy.mapping.AddLayer(data_frame, lyr, 'TOP')
mxd.save()

_{This was done with ArcGIS 10.5.1. I can't guarantee other versions are identical, but I'd be surprised if any of this changes. Then again, lots of things in ESRI are surprising.}

Answer 2

Use the GP tool Save To Layer File (Data Management):

Creates an output layer file (.lyr) that references geographic data stored on disk.

Alternatively, you could use the .save/.saveACopy methods on the Layer object