Edit a Query Layer with Arcpy

Question

In ArcGIS Desktop 10.4, is it possible to edit programmatically with Python the SQL query of a Query Layer? I'm not referring to the Definition Query of the layer, but to the window that is manually accessible with the button "Change Query".

In fact, this is the only way to change the table used in the Query Layer, since it seems that it's not possible to use the replaceDataSource method with this kind of layer.

enter image description here

• Alex Tereshenkov: Could you please add to the question body why do you need query layers? Is there any specific reason for that? Thanks!
• Andreampa: Yes, the specific reason is that I need to replace programmatically the table used in the query layer, and the replaceDataSource method seems not to work with Query Layers.
• Alex Tereshenkov: Would you be comfortable modifying the SQL of a database view? No need to involve Esri bits then. Google for Psycopg.

Answer 1

Unfortunately, there is no way to edit the query table after it was created in ArcMap using arcpy. You will need to use ArcObjects either using .NET/Java/C++ or using comtypes in Python to work the COM interfaces.

You have a couple of options:

1. Use a regular feature layer and set its definition query using Layer object using arcpy.

2. Create a database view using SQL tools or ArcGIS Create Database View GP tool and then modify its definition as needed.

Answer 2

At least there is a official convoluted guide now how to do such simple thing in about million steps:

How To: Change query layer SQL expression using Python

In brief you need to get all the info about current query layer (existing query, geodatabase connection information, spatial reference, geometry column name, geometry type, identifier - typically OBJECTID), create new query layer based on that info with new query, update the layer you want to update with the new layer, and remove the new layer. Voila, you have updated SQL on query layer.