Updating a table in arcpy without affecting its relationship classes

329
0
10-17-2023 02:31 PM
twhammond
New Contributor II

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.

twhammond_2-1697488663319.png

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

twhammond_3-1697577172065.png

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?

 

0 Kudos
0 Replies