Community
Select to view content in your preferred language

Delete or Clean existing UN data from SDE using Python script

43
0
2 hours ago
Ashok
by
Regular Contributor
2 hours ago

I am working on a requirement that I need to export Electric UN data from one sde (stag environment) to another sde (client specific) using Python. And its a daily basis task, so I need to run this script daily at sometime using Task scheduler. 

As part of this process, I divided whole script into 2 scripts. 

Script 1 blocks new connections, disconnects users, finds the utility network in the target geodatabase, deletes the utility network, then deletes the remaining feature datasets, feature classes, and tables in the target workspace. - Having Issue

Script 2 Deletes the existing xml from folder. Export the source SDE UN data as xml workspace. Imports the xml workspace at target SDE - Working

Script 1 when I run manually from ArcGIS Pro python window, sometimes working as expected and sometimes not.

My Script1 code is below 

import arcpy
import os
import sys
import traceback
import datetime
import time
import gc
from contextlib import suppress

# =========================================================
# INPUTS
# =========================================================

target_sde = r"C:\Export\ExportUN_08052026\SQLServer-svrwp-oms-gis_sqlexpress-DBmigration(gismigrationuser).sde"

admin_sde = r"C:\Export\ExportUN_08052026\SQLServer-svrwp-oms-gis_sqlexpress-DBmigration(gisadmin).sde"

log_file = r"C:\Export\ExportUN_08052026\UNReplica.log"

# =========================================================
# LOGGING
# =========================================================

def log(msg):

    timestamp = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    message = f"[{timestamp}] {msg}"

    print(message)

    with open(log_file, "a", encoding="utf-8") as f:
        f.write(message + "\n")


# =========================================================
# HELPERS
# =========================================================

def clear_cache(ws=None):

    with suppress(Exception):

        if ws:
            arcpy.management.ClearWorkspaceCache(ws)
        else:
            arcpy.management.ClearWorkspaceCache()


def force_release():

    try:

        arcpy.env.workspace = None

        gc.collect()

        clear_cache()

        time.sleep(5)

    except:
        pass


# =========================================================
# VALIDATE WORKSPACE
# =========================================================

def validate_workspace(workspace, retries=5):

    for attempt in range(1, retries + 1):

        try:

            force_release()

            arcpy.env.workspace = workspace

            datasets = arcpy.ListDatasets("*", "Feature") or []

            desc = arcpy.Describe(workspace)

            log(
                f"Workspace validation attempt {attempt} - datatype: {desc.dataType}"
            )

            if desc.dataType == "Workspace":

                log(f"Workspace datasets: {datasets}")

                return True

        except Exception as ex:

            log(f"Workspace validation failed: {ex}")

        # ------------------------------------------------
        # Sometimes SDE becomes File temporarily
        # ------------------------------------------------

        try:

            log("Temporarily enabling connections")

            arcpy.AcceptConnections(admin_sde, True)

            time.sleep(10)

            log("Blocking connections again")

            arcpy.AcceptConnections(admin_sde, False)

            time.sleep(10)

        except Exception as ex:

            log(f"Connection reset failed: {ex}")

    return False


# =========================================================
# LIST USERS
# =========================================================

def list_users():

    try:

        users = arcpy.ListUsers(admin_sde)

        if not users:

            log("No connected users found")

            return []

        log("Current connected users:")

        for user in users:

            try:

                log(
                    f"User={user.Name}, "
                    f"Client={user.ClientName}, "
                    f"Connected={user.ConnectionTime}"
                )

            except:
                pass

        return users

    except Exception as ex:

        log(f"Failed listing users: {ex}")

        return []


# =========================================================
# DISCONNECT USERS
# =========================================================

def disconnect_users():

    users = list_users()

    protected_users = ["dbo", "gisadmin"]

    for user in users:

        try:

            user_name = getattr(user, "Name", "").lower()

            if user_name in protected_users:

                log(f"Skipping protected user: {user_name}")

                continue

            log(f"Disconnecting user: {user_name}")

            arcpy.DisconnectUser(admin_sde, user.ID)

        except Exception as ex:

            log(f"Failed disconnecting user: {ex}")

    time.sleep(5)

    # Verify remaining users

    log("Verifying remaining users/locks")

    remaining = arcpy.ListUsers(admin_sde)

    if remaining:

        for user in remaining:

            try:
                log(f"Remaining user: {user.Name}")
            except:
                pass
    else:
        log("No remaining users")


# =========================================================
# FIND UTILITY NETWORK
# =========================================================

def find_un(workspace):

    try:

        arcpy.env.workspace = workspace

        desc = arcpy.Describe(workspace)

        if hasattr(desc, "children"):

            for child in desc.children:

                try:

                    if getattr(child, "dataType", "") == "FeatureDataset":

                        if "UtilityNetwork" in child.name:

                            log(
                                f"Utility Network found inside {child.catalogPath}"
                            )

                            return child.catalogPath

                except:
                    pass

        return None

    except Exception as ex:

        log(f"Failed finding utility network: {ex}")

        return None


# =========================================================
# DELETE WITH RETRY
# =========================================================

def delete_with_retry(path, label, retries=5):

    for attempt in range(1, retries + 1):

        try:

            force_release()

            log(f"Deleting {label}: {path}")

            arcpy.management.Delete(path)

            log(f"{label} deleted successfully")

            return True

        except Exception as ex:

            log(
                f"Delete attempt {attempt} failed for {label}: {ex}"
            )

            time.sleep(10)

    return False


# =========================================================
# CLEANUP
# =========================================================

def cleanup_target():

    # ----------------------------------------------------
    # USE ONLY ADMIN CONNECTION
    # ----------------------------------------------------

    log("Initializing ADMIN workspace connection")

    arcpy.env.workspace = admin_sde

    if not validate_workspace(admin_sde):

        raise Exception(
            "Target SDE connection is not a valid Workspace."
        )

    # ----------------------------------------------------
    # BLOCK CONNECTIONS
    # ----------------------------------------------------

    log("Blocking new connections")

    arcpy.AcceptConnections(admin_sde, False)

    # ----------------------------------------------------
    # DISCONNECT USERS
    # ----------------------------------------------------

    disconnect_users()

    # ----------------------------------------------------
    # VALIDATE AGAIN
    # ----------------------------------------------------

    if not validate_workspace(admin_sde):

        raise Exception(
            "Target workspace became invalid after blocking connections."
        )

    # ----------------------------------------------------
    # DELETE UN
    # ----------------------------------------------------

    un_path = find_un(admin_sde)

    if un_path:

        log(f"Preparing to delete Utility Network Dataset: {un_path}")

        success = delete_with_retry(
            un_path,
            "Utility Network Dataset"
        )

        if not success:

            raise Exception(
                "Failed deleting Utility Network Dataset after retries."
            )

    else:

        log("Utility Network dataset not found")

    # ----------------------------------------------------
    # DELETE FEATURE DATASETS
    # ----------------------------------------------------

    arcpy.env.workspace = admin_sde

    datasets = arcpy.ListDatasets("*", "Feature") or []

    for ds in datasets:

        path = os.path.join(admin_sde, ds)

        delete_with_retry(path, "Feature Dataset")

    # ----------------------------------------------------
    # DELETE FEATURE CLASSES
    # ----------------------------------------------------

    fcs = arcpy.ListFeatureClasses() or []

    for fc in fcs:

        path = os.path.join(admin_sde, fc)

        delete_with_retry(path, "Feature Class")

    # ----------------------------------------------------
    # DELETE TABLES
    # ----------------------------------------------------

    tables = arcpy.ListTables() or []

    for tbl in tables:

        path = os.path.join(admin_sde, tbl)

        delete_with_retry(path, "Table")

    log("Target cleanup completed")


# =========================================================
# MAIN
# =========================================================

try:

    start = datetime.datetime.now()

    log("====================================")
    log("UTILITY NETWORK CLEANUP")
    log("====================================")

    arcpy.env.overwriteOutput = True

    cleanup_target()

    duration = datetime.datetime.now() - start

    log(f"Process completed in {duration}")

except Exception as e:

    log("ERROR OCCURRED")

    log(str(e))

    log(traceback.format_exc())

    sys.exit(1)

finally:

    try:

        arcpy.AcceptConnections(admin_sde, True)

        log("Connections re-enabled")

    except Exception as ex:

        log(f"Failed to re-enable connections: {ex}")

    log("Script finished")

And my log is here. data is not getting cleaned. 

[2026-05-14 04:00:05] ====================================
[2026-05-14 04:00:05] UTILITY NETWORK CLEANUP
[2026-05-14 04:00:05] ====================================
[2026-05-14 04:00:05] Initializing ADMIN workspace connection
[2026-05-14 04:00:15] Workspace validation attempt 1 - datatype: Workspace
[2026-05-14 04:00:15] Workspace datasets: ['GISMIGRATIONUSER.UtilityNetwork', 'GISMIGRATIONUSER.ElectricDataset']
[2026-05-14 04:00:15] Blocking new connections
[2026-05-14 04:00:15] Current connected users:
[2026-05-14 04:00:15] User=DBO, Client=SVRWP-GIS4:13.5.5.57366, Connected=2026-05-14 04:00:13
[2026-05-14 04:00:15] Skipping protected user: dbo
[2026-05-14 04:00:20] Verifying remaining users/locks
[2026-05-14 04:00:20] Remaining user: DBO
[2026-05-14 04:00:27] Workspace validation attempt 1 - datatype: Workspace
[2026-05-14 04:00:27] Workspace datasets: ['GISMIGRATIONUSER.UtilityNetwork', 'GISMIGRATIONUSER.ElectricDataset'].

 

Kindly help me in resolving this issue.

Thanks,

Ashok 

0 Kudos
0 Replies