Updating a table in arcpy without affecting its relationship classes

twhammond — Mon, 23 Oct 2023 14:41:39 GMT

I am experiencing trouble in my script and I can't pin down what the issue is. The goal is to update a table from an excel file. The existing table has Relationship Classes relating it to each feature class. I have a function to check which feature classes need Relationship Classes (if any, in this example all feature classes already have a Relationship Class). If there is a relationship class name containing a specified keyword (in this case 'wrqst'), then a new relationship does not need to be created. Otherwise, a different function will create the necessary relationships. Here is the snippet giving me issues:

def checkRelationshipClasses(dataSource): # NOTE: dataSource should be a sting like 'wrqst' or 'wrcpt'. Also, Relationship Class Names should always include either wrqst or wrcpt in the name! results = {} # Dictionary to hold list of feature classes and list of boolean values bools = [] # List of boolean values indicating whether relate exists for feature class arcpy.env.workspace = r"C:\Users\twhammond\Documents\tommy_misc\dataProject\testFiles\mygdb.gdb\\" featureClasses = arcpy.ListFeatureClasses() # List of feature classes results['featureClasses'] = featureClasses # add list of feature classes to results dictionary for fc in featureClasses: print(f"fc: {fc}") desc = arcpy.Describe(fc) relationshipClasses = desc.relationshipClassNames print(f"relationship classes: {relationshipClasses}") relationshipStr = (' '.join(relationshipClasses)).lower() # Get all relationship class names as lowercase string if dataSource in relationshipStr: print(f"data src -{dataSource}- is in string -{relationshipStr}-") bools.append(True) # if 'dataSource' is in the string, the value is True, indicating there is a 'dataSource' relationship class else: print(f"data src -{dataSource}- is NOT in string -{relationshipStr}-") bools.append(False) results['bools'] = bools # else the value is False, indicating there is no 'dataSource' relationship class print(f"CheckRelationshipClasses results: {results}") classesThatNeedRelates = [] # List of classes where bool is false for i in range(len(results['featureClasses'])): if results['bools'][i] == True: pass #print(f"{results['featureClasses'][i]} already has a {dataSource} join") else: classesThatNeedRelates.append(results['featureClasses'][i]) # Add this feature class to list of classesThatNeedRelates print(f"Classes that need relates: {classesThatNeedRelates}") return classesThatNeedRelates

Here I set some variables, update my table from the excel file, call my Relationship Class checking function, and call another function that creates relationship classes if needed (which is not needed in this case). If I run this code with lines 9 and 10 commented as seen below, it works as I would expect.

if __name__ == "__main__": # variables originPath = r"C:\Users\twhammond\Documents\tommy_misc\dataProject\testFiles\mygdb.gdb\\" workspace = r"C:\Users\twhammond\Documents\tommy_misc\dataProject\testFiles\mygdb.gdb\\" # Update table from excel file --These Lines Break The Script-- #arcpy.env.overwriteOutput = True # To avoid error 000725 (output table already exists) #arcpy.conversion.ExcelToTable(r"C:\\Users\\twhammond\\Documents\\tommy_misc\\XL\\temp.xlsx",r"C:\Users\twhammond\Documents\tommy_misc\dataProject\testFiles\mygdb.gdb\\WRQSTTable") # Turn temp excel file into arcGIS table called WRQSTTable # Check if relates need to be created classesThatNeedWRQST = checkRelationshipClasses('wrqst') for i in range(len(classesThatNeedWRQST)): classesThatNeedWRQST[i] = originPath + classesThatNeedWRQST[i] if len(classesThatNeedWRQST) > 0: relateWRQSTData( classesThatNeedWRQST, originPrimaryKey='PNUMDATA', originForeignKey='PARCEL' ) else: print('no new WRQST relationship needed')

Here is the output, looks good to me.

However, if I uncomment those lines to update the table, this is what I get:

It seems to me that overwriting the table using ExcelToTable breaks the relationship class, so I guess the real question is, can I somehow populate the related table with all new entries in such a way that preserves the relationship class? Or will I need to recreate every relationship class every time the table is updated?

topic Updating a table in arcpy without affecting its relationship classes in Developers Questions

Updating a table in arcpy without affecting its relationship classes