For loop not looping in ArcPy process

MarieCline_Delgado — Sat, 11 Dec 2021 23:23:22 GMT

I put together this python script to compress databases. There seems to be an issue with the for loop where it correctly loops through the list of database names throughout the script except within the ArcPy process where a new database connection is created and the database name is used to define which database to connect to. While all "print" indicators appear to be using the desired database, the database connection is repeatedly created with only the first database in the list of database names and, therefore, the first database is the list is compressed over and over, while the remaining databases are not compressed (confirmed in the SQL sde compress log).

Any advise and help is greatly appreciated!

# Import modules
import arcpy
import datetime
import time
import smtplib
import os

# Find and store process beginning time
starttime = datetime.datetime.now()

# Local variables
saConnect = r"G:\\GISAdmin\\Database_Management\\Compress.sde"
databaseName = ["devservices", "electric", "fire", "gis", "parks", "police", "publicworks", "water_services"]

# Delete database connection file if exists
if os.path.exists(saConnect):
    os.remove(saConnect)
    print ("\n\n\nExisting database connection deleted.")
else:
    print ("\n\n\nNo database connection found. I'll just make one for all y'all.")

for d in databaseName:
    print ("\n\nBeginning compression processes for " + str(d) + " database.")

    # Create database connection file
    arcpy.CreateDatabaseConnection_management(r"G:\\GISAdmin\\Database_Management", "Compress.sde", "SQL_SERVER", "gis3", "DATABASE_AUTH", "sa", "******", "SAVE_USERNAME", d, "", "TRANSACTIONAL", "sde.DEFAULT", "")
    print ("Database connection file created for " + str(d))

    # Set the workspace
    arcpy.env.workspace = r"G:\\GISAdmin\\Database_Management\\Compress.sde"
    print ("Workspace defined")

    # Set a variable for the workspace
    workspace = arcpy.env.workspace

    # Get a list of connected users
    users = arcpy.ListUsers(workspace)
    print ("Users found")

    # Get a list of usernames of users currently connected
    viewUsers = [user.Name for user in users]
    print ("View Users")

    # Get and format connection times for each user
    connectTimeFormat = datetime.datetime.strftime(user.ConnectionTime, "%Y-%m-%d %H:%M:%S")
    viewConnectTime = [connectTimeFormat for user in users]
    print ("\n\nUSERS FOR " + str(d))
    template = "{0:4}|{1:15}"
    print template.format("USER", "CONNECTION TIME") # header
    for userWho, userWhen in zip(viewUsers, viewConnectTime):
        print userWho, userWhen

    # Block new connections to the database
    arcpy.AcceptConnections(workspace, False)
    print ("\n\nNew connections to database blocked")

    # Disconnect all users from the database
    arcpy.DisconnectUser(workspace, "ALL")
    print ("Database users disconnected")

    # Run the compress tool
    arcpy.Compress_management(workspace)
    print ("\n\nDatabase compression complete")

    # Allow the database to begin accepting connections again
    arcpy.AcceptConnections(workspace, True)
    print ("Allowing new connections to database")

    # Delete database connection file
    if os.path.exists(workspace):
        os.remove(workspace)
        print ("Connection for " + str(d) + " deleted.")

# Find and store process beginning time
endtime = datetime.datetime.now()

Re: For loop not looping in ArcPy process

JoshuaChisholm — Tue, 17 Mar 2015 14:59:54 GMT

I'm not sure this will work, but you could try changing line 30 to:

arcpy.env.workspace = r"G:\\GISAdmin\\Database_Management\\Compress.sde"+"\\"+d

Re: For loop not looping in ArcPy process

BlakeTerhune — Tue, 17 Mar 2015 16:43:35 GMT

When you make directory path strings, you can either use the double backslash to escape the special character, or you can use the r prefix to designate the string as "raw" so it will not consider a single backslash as a special character. You have combined both methods and will probably not work. You need to do one or the other. Personally, I like to use the raw string method:

arcpy.env.workspace = r"G:\GISAdmin\Database_Management\Compress.sde"

Re: For loop not looping in ArcPy process

MarieCline_Delgado — Fri, 17 Apr 2015 12:46:37 GMT

Just FYI.

arcpy.ClearWorkspaceCache_management() was the answer to the problem.

The workspace was holding onto .sde connection properties. Clearing the workspace cache resolved that.

topic Re: For loop not looping in ArcPy process in Python Questions

For loop not looping in ArcPy process

Re: For loop not looping in ArcPy process

Re: For loop not looping in ArcPy process

Re: For loop not looping in ArcPy process