Community
Select to view content in your preferred language

Delete or Clean existing UN data from SDE using Python script

437
2
3 weeks ago
Ashok
by
Regular Contributor
3 weeks 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
2 Replies
HaydenWelch
by MVP Regular Contributor
MVP Regular Contributor
3 weeks ago

So what exactly is the issue here? I'm not seeing any exceptions thrown by your current script. Is it finding the Utility Network dataset every time? That's where your logging stops. It's not logging anything about the Utility Network

0 Kudos
TonyAlmeida
by MVP Regular Contributor
MVP Regular Contributor
3 weeks ago

A few things, 

  • find_un() is looking for "UtilityNetwork" in the dataset name, but your dataset is named GISMIGRATIONUSER.UtilityNetwork — the child name check may be inconsistent.
  • The DBO connection is holding locks
  • You're mixing target_sde and admin_sde — target_sde is never used in cleanup. Minor, but confusing.

Untested

# =========================================================
# FIND UTILITY NETWORK - Fixed
# =========================================================

def find_un(workspace):
    """
    Returns the catalog path of the UtilityNetwork dataset,
    which lives INSIDE a FeatureDataset, not at the root.
    """
    try:
        arcpy.env.workspace = workspace
        datasets = arcpy.ListDatasets("*", "Feature") or []

        for ds in datasets:
            ds_path = os.path.join(workspace, ds)
            try:
                arcpy.env.workspace = ds_path
                # List networks inside the feature dataset
                networks = arcpy.ListDatasets("*", "Network") or []
                for net in networks:
                    net_path = os.path.join(ds_path, net)
                    try:
                        net_desc = arcpy.Describe(net_path)
                        if net_desc.dataType == "UtilityNetworkLayer" or \
                           "UtilityNetwork" in net_desc.dataType:
                            log(f"Utility Network found: {net_path}")
                            return net_path
                    except:
                        pass

                # Fallback: describe the dataset itself
                desc = arcpy.Describe(ds_path)
                if hasattr(desc, "children"):
                    for child in desc.children:
                        if "UtilityNetwork" in getattr(child, "dataType", ""):
                            log(f"Utility Network found via children: {child.catalogPath}")
                            return child.catalogPath

            except Exception as ex:
                log(f"Error inspecting dataset {ds}: {ex}")

        log("No Utility Network found in any feature dataset")
        return None

    except Exception as ex:
        log(f"Failed finding utility network: {ex}")
        return None


# =========================================================
# CLEANUP - Fixed ordering and cache clearing
# =========================================================

def cleanup_target():

    log("Blocking new connections")
    arcpy.AcceptConnections(admin_sde, False)
    time.sleep(5)

    disconnect_users()

    # Full cache clear before any deletes
    clear_cache(admin_sde)
    arcpy.env.workspace = None
    time.sleep(5)

    if not validate_workspace(admin_sde):
        raise Exception("Target workspace invalid after blocking connections.")

    arcpy.env.workspace = admin_sde

    # ---- DELETE UTILITY NETWORK FIRST ----
    un_path = find_un(admin_sde)
    if un_path:
        log(f"Deleting Utility Network: {un_path}")
        if not delete_with_retry(un_path, "Utility Network"):
            raise Exception("Failed deleting Utility Network after retries.")
    else:
        log("No Utility Network found — skipping UN delete")

    # ---- RESET WORKSPACE AFTER UN DELETE ----
    clear_cache(admin_sde)
    arcpy.env.workspace = admin_sde
    time.sleep(5)

    # ---- DELETE FEATURE DATASETS ----
    datasets = arcpy.ListDatasets("*", "Feature") or []
    log(f"Feature datasets to delete: {datasets}")
    for ds in datasets:
        path = os.path.join(admin_sde, ds)
        delete_with_retry(path, "Feature Dataset")
        clear_cache(admin_sde)
        time.sleep(3)

    # ---- RESET WORKSPACE ----
    arcpy.env.workspace = admin_sde

    # ---- DELETE STANDALONE FEATURE CLASSES ----
    fcs = arcpy.ListFeatureClasses() or []
    log(f"Standalone feature classes to delete: {fcs}")
    for fc in fcs:
        path = os.path.join(admin_sde, fc)
        delete_with_retry(path, "Feature Class")

    # ---- DELETE TABLES ----
    tables = arcpy.ListTables() or []
    log(f"Tables to delete: {tables}")
    for tbl in tables:
        path = os.path.join(admin_sde, tbl)
        delete_with_retry(path, "Table")

    log("Target cleanup completed")
0 Kudos