topic Re: Cannot Acces SDE Connection File Properly using Arcpy in Python Questions

Cannot Acces SDE Connection File Properly using Arcpy

MikeMacRae — Sat, 08 Mar 2014 22:10:31 GMT

I am testing a way around accessing sde connections using arcpy. I have 2 existing sde databases connected in ArcCatalogue that require passwords anytime I go to connect to them. It seems that there is no way to access existing database connections using arcpy (that I can find anyways). You are forced to create new ones every time if you want to do this in a script using:

arcpy.CreateArcSDEConnectionFile_management (folderName, fileName, serverName, serviceName, databaseName, authType, username, password)

This seems completely redundant to do so if I already have the connection set up. There should be a function to access these existing connection so that a programmer can just build a tool that allows the user to enter their user name and password without re-creating the connection.

Oh but what. You sort of can if you set your environment workspace in your script:

import arcpy
from arcpy import env

env.workspace = r"Database Connections\blah.sde"

When you do this and run the script from the ArcGIS GUI, the regular database connection prompt will pop up which should allow you to login. The problem I have having is, the prompt will come up but anytime I click on the window, the ArcGIS GUI will override it an won't allow me to type anything into the username and password fields.

So, I tried to get creative and decided to create a new connection file and then immediately after, I tried to delete it.

import arcpy

# Set variables
folderName = r"Database Connections"
fileName = "Connection to SDE.sde"
serverName = "someserver"
serviceName = "5151"
databaseName = ""
authType = ""
username = "username"
password = "password"

arcpy.CreateArcSDEConnectionFile_management (folderName, fileName, serverName, serviceName, databaseName, authType, username, password)

arcpy.Delete_management(r"Database Connections\Connection to SDE.sde")

The thinking was that I would just build the tool to allow the user to enter their username and password, pass that into the arcpy.CreateArcSDEConnectionFile function, do some processing and then delete the connection file at the end using

arcpy.Delete_management

The problem is, it schema locks every time the script does the delete. This is weird because I simply created and deleted in one foul swoop.

I'm at a loss. I need to figure out a way to access a database connection. I need to either:

Solve the issue with the ArcGIS GUI overriding the database connections login prompt

Find a way to release the schema lock so that I can delete any database connections I create during the script

Or find some other weird and wonderful to accomplish this

Re: Cannot Acces SDE Connection File Properly using Arcpy

FilipKrál — Sun, 09 Mar 2014 22:40:10 GMT

Hi,
Assume the path to your connection file is stored in variable 'con'.

Have you tried setting env.workspace to something else and calling arcpy.ClearWorkspaceCache_management(con) before you try arcpy.Delete_management(con)?

It is not the same as disconnecting via ArcCatalog but it's worth a try.

If it doesn't help, is os.remove(con) an option?
Filip

Re: Cannot Acces SDE Connection File Properly using Arcpy

MikeMacRae — Wed, 12 Mar 2014 15:47:23 GMT

Hey Filip,

I tried clearing the workspace cache but I still annot delete the database connection. I find it very odd that on one line of code I can create a database connection, but on the very next line I cannot delete it (even after clearing the cache).

Re: Cannot Acces SDE Connection File Properly using Arcpy

MathewCoyle — Wed, 12 Mar 2014 16:48:05 GMT

As Filip suggested, use os.remove(). That is the standard method of deleting files in Python on Windows.

From the help:

If the specified item is a workspace, all contained items are also deleted.

Even though it also says.

Deleting a database connection file does not delete the ArcSDE database. A database connection file is simply a shortcut to the database.

Bottom line is I would never use arcpy.Delete_management() to delete anything but a feature class.

Re: Cannot Acces SDE Connection File Properly using Arcpy

MikeMacRae — Wed, 12 Mar 2014 16:57:54 GMT

Thanks matt. I guess I was just going by the helpfile:

http://resources.arcgis.com/en/help/main/10.1/index.html#//001700000052000000

It specifically states that you can delete a Database Connection file.

Also, I decided to hack into the validator class (and by hack I mean, press the edit button 🙂 ) and just added set the workspaces to fire up upon the tool initiation. Now instead of creating a new connection file, I just open the tool and the Database Connections prompt comes up twice for me to log into the 2 sde DB's I need to access.

I will try the os.remove method, though, because ideally I'd like to build the GUI to have built in parameters with a string for the username and an encrypted string for the password taht I can pass into a connection file so the user can do all of his/her setup in one window.

Re: Cannot Acces SDE Connection File Properly using Arcpy

MathewCoyle — Wed, 12 Mar 2014 17:03:15 GMT

You should be able to do that easily as a parameter. Are you creating a custom GUI or using Esri addins/tools?

Also, have you tried CreateDatabaseConnection_management()? I use them for the direct connects. I did have some other issues actually with creating connection files and referencing a default version. Had to use arcobjects to get around it.

Re: Cannot Acces SDE Connection File Properly using Arcpy

AndrewSmith26 — Wed, 07 Aug 2019 18:47:13 GMT

Can you show how you completed this as we have the same issue in our organization.