How to programmatically change the data source for a query layer?

[Map12]

We are trying to change the data sources  for multiple query layers in multiple aprx files. We have run a test script that uses a template aprx and successfully changes the data source through the layer connection properties. However, when opening the new aprx, no data appears.

Once we open the properties for the query layer and make any edits to the query, it then "auto-validates" and data populates. This manual step of editing the query to prompt the query layer to "validate" defeats the purpose of scripting the data source change.

Does anybody have any tips on how to get the query layer to "auto-validate"/self-validate/run through whatever check it's doing before populating data?

import arcpy
aprx = arcpy.mp.ArcGISProject(r'path\to\file.aprx')
all_maps = aprx.listMaps()
for m in all_maps:
    all_layers = m.listLayers()
    for lyr in all_layers: 
        if not lyr.isGroupLayer: 
            try:
                cp = lyr.connectionProperties
                cp['connection_info']['database'] = 'db123'
                lyr.updateConnectionProperties(lyr.connectionProperties, cp)
            except: 
                print("Not Included: " + lyr.name)       
aprx.saveACopy(r"path\to\new\file.aprx")

We've also thought about starting from a blank aprx and using Make Query Layers to populate each of them. However, this function requires the layer to be copied to a feature class or other static item. Is there any way to persist the Query Layer in an aprx map?

[MobiusSnake]

I'm fairly certain there isn't a way to modify query layer data sources in ArcPy.  Although it was some years ago now, I had this come up on a project in the ArcMap days and I had to write an ArcObjects script to modify query layers in an MXD en masse, there was no ArcPy route available.  As far as I know that's still the case, except ArcObjects aren't available in Pro.

However ... In the ArcMap days layer files were binary, and nowadays layer files (LYRX) are XML, so theoretically you could save your query layer as a LYRX and use some XML editing code to update it that way.  I haven't done this but it sounds good in theory, haha.

[Bud]

Idea: Modify query layer data source using Python