Can you use arcpy to get the database connection type?

[AronBird]

Anyone know if you can use arcpy to get the database connection type?  i.e. Oracle, Application Server, SQL Server, etc.

I'm trying to find a way to identify all of the Application Server connection files in a series of folders so that I can change them to Oracle Direct Connections.  To further clarify, if you look at the connection properties for a Database Connection in ArcCatalog you will see this in the Database Platform combobox.

Aron

[BlakeTerhune]

ListUsers() has the IsDirectConnection property. I think If it's not direct connect, then it's application server.

[JoshuaBixby · Accepted solution]

ListUsers() is focused on existing/current connections to an SDE database, not SDE connection files on disk, which is what I think the OP is after.  I believe the connectionProperties of the Workspace properties of the Describe object will allow the OP to get the information.

>>> desc = arcpy.Describe(r'Database Connections\LGDB.sde')
>>> cp = desc.connectionProperties
>>> cp.instance
u'sde:sqlserver:(localdb)\\MSSQLLocalDB'

For an application server, the instance property will look something like: u'5152:SDE'

[AronBird]

Thanks Blake and Joshua.

Yes, I am trying to find the SDE connection files on disk and not those connected to the database.  This does work but unfortunately you have to enter a username and password otherwise it will fail.  It would be nice to return a 'Database Platform' value like the screenshot below rather than inferring it is an Application Server connection from the returned instance service number and it would also be nice to not have to connect to the database but either way, I think I can make it work for what I need it to do.

Thanks,

Aron

[JoshuaBixby]

Since Application Servers are dead at ArcGIS 10.3, I don't think Esri is going to invest anything in making it easier to identify them.  The "Database Platform" field you reference is relatively new-ish, starting at ArcGIS 10.1.  Although the GUI shows this new field, I don't believe there were any changes with the workspace connection properties data structure on the backend.  In the end, returning a Database Platform value would have to be from a function that processes the connection properties and not directly from the connection properties themselves.

You raise an interesting, and annoying, part of processing SDE connection files.  I primarily work with operating system authentication, so the issue of having the username/password prompt doesn't usually impact me.  That said, it is frustrating that one has to fully authenticate for ArcPy to see any of the connection properties.  When working with the GUI, a user can look at several connection properties without having to actually connect to the database.  It would be nice to see similar behavior in ArcPy if no username or password are given.

[JoshuaBixby]

If you are willing to install comtypes, then the SDE connection properties can be extracted without actually connecting to the database.  I ginned up the following function, just pass it the SDE connection file and it returns a dictionary with all of the connection properties.

def GetConnectionPropertiesFromFile(in_workspace):
    from comtypes.client import CreateObject, GetModule
    import os
    
    comDirectory = os.path.join(
        os.path.join(arcpy.GetInstallInfo()['InstallDir']), 'com'
    )
    esriDataSourcesGDB = GetModule(os.path.join(comDirectory, 'esriDataSourcesGDB.olb'))
    esriGeoDatabase = GetModule(os.path.join(comDirectory, 'esriGeodatabase.olb'))
    
    pWSF = CreateObject(esriDataSourcesGDB.SdeWorkspaceFactory,
                        interface=esriGeoDatabase.IWorkspaceFactory)
    pPropSet = pWSF.ReadConnectionPropertiesFromFile(in_workspace)
    names, values = pPropSet.GetAllProperties()
    
    connectionProperties = {}
    for i in range(len(names)):
        connectionProperties[names] = values
        
    return connectionProperties