topic Re: layer.updateConnectionProperties Does Not Update Relates in Python Questions

layer.updateConnectionProperties Does Not Update Relates

JeffPickles2 — Tue, 10 Oct 2023 21:50:53 GMT

In ArcPy 3.7 (ArcPro 2.9 environment), I am looking to promote an Aprx from one environment to another, changing datasets accordingly. I am using layer.updateConnectionProperties to switch from QA SDE layers to Prod SDE layers. This works as expected for the layers, themselves, but I cannot get the related table (also SDE based) to also update to Prod data sources.

auto_update_joins_and_relates doesn't work, nor does spelling out the new environmental parameters by updating the connection properties dictionary.

currentDb = QA_SDE outputDb = Prod_SDE currentEnv = QA_RegisteredDir outputEnv = Prod_RegisteredDir inputAprx = QA_Aprx targetAprx = Prod_Aprx aprx = arcpy.mp.ArcGISProject(inputAprx) maps = aprx.listMaps() for map in maps: layers = map.listLayers() for layer in layers: if not layer.supports('dataSource'): continue elif currentDb in layer.dataSource: newProps = layer.connectionProperties oldProps = layer.connectionProperties newProps['connection_info']['db_connection_properties'] = outputDb newProps['connection_info']['instance'] = 'sde:sqlserver:' + outputDb newProps['connection_info']['server'] = outputDb newProps['connection_info']['authentication_mode'] = 'OSA' if 'relates' in layer.connectionProperties: if isinstance(newProps['relates'], list): newProps['relates'][0]['connection']['connection_info']['db_connection_properties'] = outputDb newProps['relates'][0]['connection']['connection_info']['instance'] = 'sde:sqlserver:' + outputDb newProps['relates'][0]['connection']['connection_info']['server'] = outputDb newProps['relates'][0]['connection']['connection_info']['authentication_mode'] = 'OSA' layer.updateConnectionProperties(current_connection_info = oldProps, new_connection_info = newProps, auto_update_joins_and_relates = True) elif currentEnv in layer.dataSource: layer.updateConnectionProperties(current_connection_info = currentEnv, new_connection_info = outputEnv) aprx.saveACopy(targetAprx)

When run, newProps dictionary updates accordingly to reflect the Prod sourced related table. However, when updateConnectionProperties is run, the layer source is in Prod but the relate is still QA sourced. This behavior occurs both when auto_update_joins_and_relates is set to True and to False.

What am I missing here?

Re: layer.updateConnectionProperties Does Not Update Relates

SzymAdamowski — Mon, 16 Oct 2023 16:34:39 GMT

Probably you're not missing anything. Function updateConnectionProperties is not very reliable (putting it politely). Last resort is using CIM. I was able to replicate your problem using FileGeodatabase (so it is probably not related to connection type). You can rely entirely on CIM or allow updateConnectionProperties to update feature class data source and then use CIM to update relate data source.

Following piece of code will update relate connection for FileGDB:

path=r'E:\GIS_DATA\GDB1.gdb' lyrCIM = layer.getDefinition("V3") dc = lyrCIM.featureTable.relates[0].dataConnection #works only for the first relate dc.workspaceConnectionString = fr"DATABASE={path}" #there must be no spaces layer.setDefinition(lyrCIM)

What you can do is to save one of your layers as .lyrx file and then investigate JSON structure (by changing extension to .json) - as described in Python CIM Access

When you hack necessary elements of workspaceConnectionString (which are probably different in your case), you're code could be like this:

currentDb = QA_SDE outputDb = Prod_SDE currentEnv = QA_RegisteredDir outputEnv = Prod_RegisteredDir inputAprx = QA_Aprx targetAprx = Prod_Aprx aprx = arcpy.mp.ArcGISProject(inputAprx) maps = aprx.listMaps() for map in maps: layers = map.listLayers() for layer in layers: if not layer.supports('dataSource'): continue elif currentDb in layer.dataSource: newProps = layer.connectionProperties oldProps = layer.connectionProperties newProps['connection_info']['db_connection_properties'] = outputDb newProps['connection_info']['instance'] = 'sde:sqlserver:' + outputDb newProps['connection_info']['server'] = outputDb newProps['connection_info']['authentication_mode'] = 'OSA' layer.updateConnectionProperties(current_connection_info = oldProps, new_connection_info = newProps, auto_update_joins_and_relates = True) if 'relates' in layer.connectionProperties: lyrCIM = layer.getDefinition("V2") dc = lyrCIM.featureTable.relates[0].dataConnection dc.workspaceConnectionString = "?"##Adjust it accordingly layer.setDefinition(lyrCIM) elif currentEnv in layer.dataSource: layer.updateConnectionProperties(current_connection_info = currentEnv, new_connection_info = outputEnv) aprx.saveACopy(targetAprx)

Good luck.

Re: layer.updateConnectionProperties Does Not Update Relates

JeffPickles2 — Mon, 16 Oct 2023 20:52:46 GMT

That is indeed what I was missing. Thank you!

For anyone else running into a similar issue in the future, I took Szym's suggestion and combined it with a ModifyRelateProperties script that is within the ESRI supplied CIM_Examples_Pro25_v1 found here: https://www.arcgis.com/home/item.html?id=8772f61319584882bb697ba003030636.

Here's my working code:

currentDb = QA_SDE outputDb = Prod_SDE currentEnv = QA_RegisteredDir outputEnv = Prod_RegisteredDir inputAprx = QA_Aprx targetAprx = Prod_Aprx aprx = arcpy.mp.ArcGISProject(inputAprx) maps = aprx.listMaps() for map in maps: layers = map.listLayers() for layer in layers: if not layer.supports('dataSource'): continue elif currentDb in layer.dataSource: newProps = layer.connectionProperties oldProps = layer.connectionProperties newProps['connection_info']['db_connection_properties'] = outputDb newProps['connection_info']['instance'] = 'sde:sqlserver:' + outputDb newProps['connection_info']['server'] = outputDb newProps['connection_info']['authentication_mode'] = 'OSA' layer.updateConnectionProperties(current_connection_info = oldProps, new_connection_info = newProps) if 'relates' in layer.connectionProperties: lyrCIM = layer.getDefinition('V2') dc = lyrCIM.featureTable.relates[0].dataConnection dc.workspaceConnectionString = dc.workspaceConnectionString.replace(currentDb, outputDb) layer.setDefinition(lyrCIM) elif currentEnv in layer.dataSource: layer.updateConnectionProperties(current_connection_info = currentEnv, new_connection_info = outputEnv) aprx.saveACopy(targetAprx)