Using Python to Update Relationship Table

BradOleson — Tue, 13 Aug 2019 16:55:37 GMT

I have a feature class that has a related table that tracks user edits. The manual way to perform the task is:
1.The user performs an edit on the polygon
2.The user adds a new record to the related table and updates attributes accordingly (type, edited by, edited date, etc.). This is done in the Attribute Editor window using the interface provided for adding a related record. The relationship is tracked via the polygon ID, which the user copies into the new related record.

I have written a Python script that automates the record creation tasks. It works as follows:
1.User edits one or more polygons
2.User runs the script
3.Script creates the new records in the edit table, enters the polygon ID and the script dialog provides the remaining update info for the script to fill in the other necessary fields

What I need to know is how, in Python, to update the relationship for the newly created records to relate them back to their respective poly using the poly ID. Currently, when the updated poly is selected and viewed the newly added record is not shown in the Attribute Editor dialog as a related record.

Can anyone provide to me where to go for the missing link? The usual Google search didn't really yield the secret to me when I started digging yesterday.

Here's the code as it exists now:

**************************************

import arcpy, arcgisscripting, sys, os, time, datetime, getpass, shutil, smtplib
from arcpy import env
from time import strftime

mxd = arcpy.mapping.MapDocument("CURRENT")

# From dialog, get the table field default values. To set different defaults, use the
# parameters dialog inside ArcMap/toolbox interface.
structuresFC = arcpy.GetParameterAsText(0)
structureEditLogTable = arcpy.GetParameterAsText(1)
qcFlag = arcpy.GetParameterAsText(2)
userComments = arcpy.GetParameterAsText(3)
modifiedBy = arcpy.GetParameterAsText(4)
modifiedDate = arcpy.GetParameterAsText(5)
lastEditedUser = arcpy.GetParameterAsText(6)
lastEditedDate = arcpy.GetParameterAsText(7)
clearSelected = arcpy.GetParameterAsText(8)

# Determine query date
queryDate = datetime.datetime.strptime(lastEditedDate, '%m/%d/%Y').strftime('%Y-%m-%d')

# Determine if any edits have been performed on the Structure Edit Log today; if so, determine the latest time of the updates
timeQuery = "LAST_EDITED_USER = '" + lastEditedUser + "' and LAST_EDITED_DATE > timestamp '" + queryDate + " 00:00:00' and MODIFIED_BY = '" + modifiedBy + "'"
arcpy.SelectLayerByAttribute_management(structureEditLogTable, "NEW_SELECTION", timeQuery)

# Determine if there is a selection; if there is, edits have been made today
describeTable = arcpy.Describe(structureEditLogTable)
if describeTable.FIDSet == '':
anyEdits = "No"
timestampList = []

    # Set the timestamp for the list to Midnight today - no edits have taken place yet
    timestampList.append("00:00:00")
    queryTime = "00:00:00"
else:
    # Edits have been made, read the times of the selection set into a list
    fieldsSEL = ["LAST_EDITED_DATE"]

timestampList = []

    with arcpy.da.SearchCursor(structureEditLogTable, fieldsSEL) as srchCursor:
        for row in srchCursor:
            timestamp = (row[0])
            timestampList.append(timestamp)
    lastEditTime = max(timestampList)
    queryTime = (lastEditTime).strftime('%H:%M:%S')

# Clear the selections to prep for next phase
arcpy.SelectLayerByAttribute_management(structuresFC, "CLEAR_SELECTION")
arcpy.SelectLayerByAttribute_management(structureEditLogTable, "CLEAR_SELECTION")

# Create select query based on current user being last edited user and edit time being greater than 00:00:00
selectionQuery = "LAST_EDITED_USER = '" + lastEditedUser + "' and LAST_EDITED_DATE > timestamp '" + queryDate + " " + queryTime + "'"

# Select records from the Structure Edit Log table that were edited by the user in this edit session
arcpy.SelectLayerByAttribute_management(structuresFC, "NEW_SELECTION", selectionQuery)
arcpy.RefreshActiveView()

# Describe the table state to determine if records are selected
describeTable = arcpy.Describe(structuresFC)

try:

    # Test to see that there is a selection set first. If not, message the user and exit.
    if describeTable.FIDSet == '':
        arcpy.AddMessage("No features are selected in the Structures Feature Class; exiting!")

else:

        # Set the parameters for the selected features *only*
        fieldsFC = ["STRUCTUREID"]
        fieldsSEL = ["STRUCTUREID", "QC_FLAG", "COMMENTS", "MODIFIED_BY", "MODIFIED_DATE"]

try:

            # Set up a cursor to get the current StructureID in the FC
            with arcpy.da.SearchCursor(structuresFC, fieldsFC) as srchCursor:
                for row in srchCursor:
                    currentStructureID = row[0]
                    with arcpy.da.InsertCursor(structureEditLogTable, fieldsSEL) as insertCursor:

                        insertCursor.insertRow((currentStructureID, "Y", userComments, modifiedBy, modifiedDate))

                del insertCursor
                del srchCursor, row

except RuntimeError as err:

            # This will run if the user is not currently in an edit session
            if "cannot be updated outside an edit session" in err.message:
                arcpy.AddMessage("Not currently in an edit session. Exit tool, start an edit session and rerun the tool!")

else:
raise err

        selectionQuery = "LAST_EDITED_USER = '" + lastEditedUser + "' and LAST_EDITED_DATE > timestamp '" + queryDate + " 00:00:00' and EDIT_ID IS NULL"
        arcpy.SelectLayerByAttribute_management(structureEditLogTable, "NEW_SELECTION", selectionQuery)
        arcpy.RefreshActiveView()

        fieldsSEL = ["OBJECTID","EDIT_ID"]
        with arcpy.da.UpdateCursor(structureEditLogTable, fieldsSEL) as updateCursor:
            for row in updateCursor:
                row[1] = row[0]
                updateCursor.updateRow(row)

del updateCursor, row

except Exception as e:
arcpy.AddMessage("Exception")
arcpy.AddError(e.args[0])

if clearSelected == "true":

    # Clear selection and refresh the map
    arcpy.SelectLayerByAttribute_management(structuresFC, "CLEAR_SELECTION")
    arcpy.SelectLayerByAttribute_management(structureEditLogTable, "CLEAR_SELECTION")
    arcpy.RefreshActiveView()
else:
    arcpy.RefreshActiveView()

End code***********

I think I may have answered my own question, but here's my sanity check...in case anyone is reading or has read this:

I need to add the requisite records to the relationship table providing the structure ID and its new related Edit_ID. I'm assuming like any other table in SDE, there shouldn't be a problem doing this via the Insert cursor, probably in the next line of my code. I'll be giving this a try, but if anyone has any caveats to this I'd appreciate your input!

Re: Using Python to Update Relationship Table

DerrickWong — Thu, 15 Aug 2019 04:21:18 GMT

Hi Brad Oleson‌,

Just had a brief look at your code, and couldn't help but notice the following bit:

I know it sounds counter-intuitive, but keep the block of code for editing short; Try to plan the layout and looping structures – find out what information you need to collect at the start of the script, add this information to dictionaries with custom classes instead of opening the cursor and then reading and performing operations on the values within the block. You should see some performance optimisation from this change.

Also, I would probably use a config file to store those column names.

Lastly, using "with" statement should release the cursor from memory reliably; you shouldn't need to delete the cursor again.

Hope that helps.

topic Using Python to Update Relationship Table in Geodatabase Questions

Using Python to Update Relationship Table

Re: Using Python to Update Relationship Table