updateConnectionProperties not working correct

[shay-geo]

Hello,

Following this guide, https://pro.arcgis.com/en/pro-app/3.3/arcpy/mapping/updatingandfixingdatasources.htm

Trying to update the connection properties of multiple files. The new way works for the majority of the datasets (including FCs, Raster, FC with joins in them) except when it has tables. This works fine from one gdb to another gdb but it breaks for TABLES when gdb to sde.
       

        aprx = arcpy.mp.ArcGISProject(r'some_aprx_path')

        aprx.updateConnectionProperties(

                    local_gdb_path, # local gdb file where data currently reside

                    'sde_connection_file.sde', # SDE connection file

                    auto_update_joins_and_relates=True,

                    validate=True,

                    ignore_case=True

                )

When I go to table and give a proper connection info (user, pwd, host etc) like below, it works.

  old = table.connectionProperties.copy()
  new = {
      "authentication_mode": "dbms",
      "database": 'db', 

     "dbclient": "sql", 
     "db_connection_properties": host, # The server
     "instance": "instance with sde",
     "server": host,
    "user": 'usr',
    "version": "", 

    "password": '****pwd',
   }
  table.updateConnectionProperties(old, new)


I was hoping all the dataset works the same way and not having to pass the connection info, instead just SDE ConnectionFile. It makes unit tests works better, since updateConnectionProperties from one gdb to another gdb works just file. Now I have to have a different path for the integration with connection string.

Thanks.

[TonyAlmeida · Accepted solution]

Tables require explicit connection properties rather than just the connection file path and tables don't connection information the same way as feature layers do. 

untested

# Configuration
old_gdb_path = r"C:\Temp\database.gdb"
sde_connection_file = r"C:\Temp\connection.sde"

# Hardcoded SDE connection properties
sde_props = {
    "authentication_mode": "dbms",
    "database": "your_database_name",  # e.g. "sde" for SQL Server
    "dbclient": "sql",  # or "oracle", "postgresql" depending on your DBMS
    "db_connection_properties": "your_server_name",
    "instance": "sde:sqlserver:your_instance",  # adjust for your DBMS
    "server": "your_server_name",
    "user": "your_username",
    "version": "SDE.DEFAULT",
    "password": "your_password"  # Consider secure storage methods
}

# Load project
aprx = arcpy.mp.ArcGISProject("CURRENT")  # or specify path to .aprx

# Process all items
for m in aprx.listMaps():
    print(f"Processing map: {m.name}")
    
    # Process layers
    for lyr in m.listLayers():
        if lyr.supports("DATASOURCE"):
            old_conn = lyr.connectionProperties
            if old_conn.get('database', '').lower() == old_gdb_path.lower():
                print(f"  Updating layer: {lyr.name}")
                lyr.updateConnectionProperties(old_conn, {"database": sde_connection_file})
    
    # Process tables 
    for table in m.listTables():
        old_conn = table.connectionProperties
        if old_conn.get('database', '').lower() == old_gdb_path.lower():
            print(f"  Updating table: {table.name}")
            table.updateConnectionProperties(old_conn, sde_props)

[HaydenWelch]

Is there a reason for this? Seems odd that only tables require a connection dictionary to be passed to `updateConnectionProperties`. Especially when the type signature and docstring of the function doesn't make that clear:

# ..\arcpy\_mp.py

class Table(_ObjectWithoutInitCall):
    ...
    def updateConnectionProperties(self, current_connection_info, new_connection_info, auto_update_joins_and_relates=True, validate=True, ignore_case=False):
        """Table.updateConnectionProperties(current_connection_info,
        new_connection_info, {auto_update_joins_and_relates}, {validate},
        {ignore_case})


        The updateConnectionProperties method replaces connection properties
        using a dictionary or a path to a workspace.


        current_connection_info(String):
        A string that represents the workspace path or a Python dictionary that
        contains connection properties to the source you want to update. If an
        empty string or None is used in current_connection_info, all connection
        properties will be replaced with the new_workspace_info, depending on
        the value of the validate parameter.


        new_connection_info(String):
        A string that represents the workspace path or a Python dictionary that
        contains connection properties with the new source information.
        """
        ...

 Shouldn't all that information live in the .sde connection file?

Edit: To be clear, it does say "or a Python dictionary", but it is the same for all the `updateConnectionProperties` docstrings so it's not clear that Table is treated differently.

Requiring Table connections to be handled with hardcoded/interpolated strings seems like a massive vulnerability.

[shay-geo]

I thought , it was odd that Table only works with Connection String and rest (FC, FC, Raster etc) works with the Connection File.
It will be nice, if all works the same way and consistent. 

I needed it mainly for Unit Testing so I don't have to choose conditional tests where Table is this way and rest of it another way. But it doesn't work the way I had expected so Conn String works for now.

[HaydenWelch]

Yeah, ideally it would work on the project level so you could use a pattern like this for testing:

from contextlib import contextmanager
from typing import (
    Optional,
    Generator,
)
from arcpy._mp import (
    ArcGISProject,
)

class ConnectionHandler:
    """Manage datasource switching for a projcect"""
    
    def __init__(self, original_source: str, project: Optional[ArcGISProject]=None) -> None:
        self.project = project or ArcGISProject("CURRENT")
        self.original_source = original_source
    
    def _replace(self, old_conn: str, new_conn: str) -> None:
        # Update all but tables
        self.project.updateConnectionProperties(old_conn, new_conn)
        self.project.save()
        
    @contextmanager
    def source_as(self, new_source: str) -> Generator[ArcGISProject, None, None]:
        self._replace(self.original_source, new_source)
        try:
            yield self.project
        except Exception as e:
            e.add_note(f'Failure with {new_source}, resetting to {self.original_source}')
            raise e
        finally:
            self._replace(new_source, self.original_source)
            
def main():
    BASE = 'sde0'
    TESTS = ['sde1', 'sde2', 'sde3']
    
    for test in TESTS:
        with ConnectionHandler(BASE).source_as(test):
            do_tests()        
            ...

Now you have to store the hardcoded properties alongside the SDE files and that kinda defeats the purpose...

[shay-geo]

Thanks @TonyAlmeida . Through testings, I came to the same conclusion that Table does really need connection string to work correct. I swapped my implementation to use connection strings for all of it.