Skip navigation
All Places > ArcGIS API for Python > Blog > Author: Underscore

ArcGIS API for Python

1 Post authored by: Underscore

Our Storm Water personnel maintain several feature classes in an enterprise geodatabase, which is currently at version 10.6.  Each of these feature classes is related to two tables: one for inspections and one for events.  Each feature class has a field which represents the number of days since the feature was visited, either by inspection or event.  They symbolize their layers by this field, using graduated colors, to show how long it's been since the feature was visited.  For example, red features haven't been visited in a long time while green ones have been visited recently.  Storm Water wanted this field updated automatically every night.


I wrote a Python script which performs the update.  I recently extrapolated it to be very general, to apply to any geodatabase layer and any number of related tables.  I call the method once per feature class.  I apologize if the documentation is not in a format to your liking, but I followed some examples I found on-line.  Since it is just a script, you're free to modify it to your whim:


# Script written by Roger Dunn, Information Technology Division, City Manager's Office, City of Orem, Utah
# February 2019
# Requires ArcGIS Desktop Standard or Advanced.
# Written with ArcPy for Desktop 10.6.1
# Target geodatabase is a 10.6 geodatabase affectionately called Knight, but really called OREMEGDB

# Use ArcPy
import arcpy

# DateTime functions needed
import datetime

def UpdateMasterFeatureClassDayRangeFieldFromMostRecentDetailEventTable(\
    arcSDEConnectionFileName \
    , masterFeatureClassName \
    , masterKeyFieldName \
    , masterDayRangeFieldName \
    , detailEventTableNames \
    , detailEventForeignKeyFieldNames \
    , detailEventDateFieldNames \
    , valueIfNoHistoryFound
    Updates an ArcGIS geodatabase feature class field which represents the number of days
    since something happened in a detail event table (which may or may not be related via
    a relationship class in ArcGIS).  This method doesn't return a value.

    :param str arcSDEConnectionFileName: The name of an .sde connection file.  If this
    script is running in ArcGIS Desktop, then it suffices to use 'Database Connections\'
    followed by the name of the file.  If this is automated, then the .sde file should
    be in the same directory as this script.
    :param str masterFeatureClassName: The name of the feature class to be modified.  The
    connection information in arcSDEConnectionFileName should have the user name and
    password (saved) of a user with permissions to modify masterFeatureClassName.  Note
    that masterFeatureClassName does not include the name of the parent feature dataset.

    :param str masterKeyFieldName: The name of the primary key field in the master feature

    :param str masterDayRangeFieldName: The name of the field in the master feature class
    which will hold the date range from today's date back to the most recent event in any
    of the detailEventTableNames.

    :param str[] detailEventTableNames: The names of tables related to the master feature

    :param str[] detailEventForeignKeyFieldNames: For each table listed in detailEventTableNames,
    include the name of the field in that table that corresponds to the master feature class'
    primary key field.  Therefore, this list is as long as detailEventTableNames.

    :param str[] detailEventDateFieldNames: For each table listed in detailEventTableNames,
    include the name of the field in that table that contains the event date.  Therefore, this
    list is as long as detailEventTableNames.

    :param var valueIfNoHistoryFound: The default value to insert in the master feature class'
    DayRangeField if no history can be found in any of the detailEventTableNames for that
    particular feature.

    # Change the environment workspace, and allow previous temporary datasets be overridden
    arcpy.env.workspace = arcSDEConnectionFileName
    arcpy.env.overwriteOutput = True
    # Store "today" (don't let it change during the script's iterations
    scriptToday =
    # Create a dictionary from the detail tables where the key is the unique ID of the master record
    # and the value is the most recent date for a detail record.
    # Initialize the dictionary
    valueDict = {}
    for detailTableIndex in range(len(detailEventTableNames)):
        detailFields = [\
            detailEventForeignKeyFieldNames[detailTableIndex] \
            , detailEventDateFieldNames[detailTableIndex] \
        with arcpy.da.SearchCursor(detailEventTableNames[detailTableIndex], detailFields) as readRows:
            for readRow in readRows:
                masterID = readRow[0]
                dateVal = readRow[1]
                if not masterID is None and not dateVal is None:
                    dateOnly =
                    if not masterID in valueDict:
                        valueDict[masterID] = dateOnly
                        if dateOnly > valueDict[masterID]:
                            valueDict[masterID] = dateOnly
                    del dateOnly
                del masterID
                del dateVal
                del readRow
        del detailFields
    del detailTableIndex
    #print valueDict

    # With valueDict now populated, it's time to modify the masterFeatureClass' DayRangeField with
    # what we've found
    masterFields = [masterKeyFieldName, masterDayRangeFieldName]
    edit = arcpy.da.Editor(arcSDEConnectionFileName)
    # Edit session is started without an undo/redo stack for versioned data (for second argument,
    # use False for unversioned data)
    edit.startEditing(False, True)
        with arcpy.da.UpdateCursor(masterFeatureClassName, masterFields) as masterRows:
            for masterRow in masterRows:
                masterID = masterRow[0]
                if masterID in valueDict:
                    daysSince = (scriptToday - valueDict[masterID]).days
                    daysSince = valueIfNoHistoryFound
                # If the calculated value for the date range doesn't equal what's already in the
                # field, modify it.  We only want delta rows where there's really a change.
                if masterRow[1] != daysSince:
                    # Start an edit operation
                        masterRow[1] = daysSince
                        #print '{0} {1} updated. Field {2} = {3}'.format(masterKeyField, masterID, masterEditField, daysSince)
                        # Stop the edit operation.
                del daysSince
                del masterID
                del masterRow
        # Stop the edit session and save the changes
    del edit
    del valueDict
    del scriptToday
    del masterFields


I call the function like this, but I'm not going to give out the real database, feature class, and table names:


from UpdateLayersFromTables import UpdateMasterFeatureClassDayRangeFieldFromMostRecentDetailEventTable

# Update Feature Class 1
UpdateMasterFeatureClassDayRangeFieldFromMostRecentDetailEventTable( \
    'MyConnection.sde' \
    , 'MySQLDB.SchemaOwner.FeatureClass1' \
    , 'UniqueID' \
    , 'DaysSince' \
    , ['MySQLDB.SchemaOwner.Class1Inspections', 'MySQLDB.SchemaOwner.Class1Events'] \
    , ['FeatClass1UniqueID', 'FeatClass1UID'] \
    , ['InspectionDate', 'EventDate'] \
    , 10000 \

# Update Feature Class 2
UpdateMasterFeatureClassDayRangeFieldFromMostRecentDetailEventTable( \
    'MyConnection.sde' \
    , 'MySQLDB.SchemaOwner.FeatureClass2' \
    , 'UID' \
    , 'DaysSince' \
    , ['MySQLDB.SchemaOwner.Class2Inspections', 'MySQLDB.SchemaOwner.Class2Events'] \
    , ['FeatClass2UniqueID', 'FeatClass2UID'] \
    , ['InspectionDate', 'EventDate'] \
    , 10000 \