AnsweredAssumed Answered

One to many join with cursors using dictionaries and lists - script is not inserting 'many joins' correctly

Question asked by meganywt on Feb 20, 2018
Latest reply on Feb 21, 2018 by Dan_Patterson

I am an enthusiastic dabbler in Python and I have (enthusiastically) attempted to write a script to create a one-to-many join to populate rows in the source table (Table1) from data in the join table (Table2).

(I have attached a diagram which explains this process)


The tables will have two ID values - one will have duplicates (ID1) and the other will be unique (ID2).


Table1 has unique IDs which will be duplicated after processing (ID1).

Table2 has two ID fields - there are duplicate ID1's and unique values for ID2.

I need to populate rows from table2 to table1.

The first match will populate the existing row in table1.

All subsequent matches to ID1 will be populated as new rows.


My script currently processes the one-to-many join successfully...but only when there is a maximum of 1 duplicate.

When there is more than 1 new row to insert, my script inserts all rows as duplicates of the 'final matched' row.


If there is anybody who can spot the error in my code or point me in the right direction, I would be really grateful as I have been stuck at this roadblock for a while now....


Thank you for taking the time to read about my troubles



import arcpy

arcpy.env.overwriteOutput = True
aprx ='C:\sample_aprx.aprx')
m = aprx.listMaps("Map")[0]

# Source table to populate - contains unique ID1's and no ID2 data
table1 = r'C:\sample_aprx.gdb\table1'


table1_fields = ["ID1","ID2","FIELD1","FIELD2","FIELD3","FIELD4","FIELD5"]

# Source table - dictionary with unique keys for ID1
table1_dict = {r[0]: (r) for r in arcpy.da.SearchCursor(table1,table1_fields)}

# Join table - duplicate ID1's and unique ID2's
table2 = r'C:\sample_aprx.gdb\table2'

table2_fields = ["ID1","ID2","FIELD3","FIELD4","FIELD5"]

# Join table - dictionary with unique tuples (ID1, ID2) as keys
table2_dict = {(r[:2]): (r[2:]) for r in arcpy.da.SearchCursor(table2,table2_fields)}

# List to store rows that need to be inserted
InsertRows_list = []

with arcpy.da.UpdateCursor(table1, table1_fields) as UpdateCursor:
    for row in UpdateCursor:
        rowStatus = ""
        # create list of table2 records that match to ID1 in table1
        table2match_list = [(k, table2_dict[k]) for k in table2_dict if k[0] == row[0]]

        for table2match in table2match_list:
            # if "ID2" is already populated from table2, then mark this row as a new row to insert
            if row[1] is not None:
                rowStatus = "Insert"

            # Assign data from table2 to table1
            # updating fields: ID2, FIELD3, FIELD4, FIELD5
            row[1], row[4], row[5], row[6] = table2match[0][1], table2match[1][0], table2match[1][1], table2match[1][2]
############# This part of the script doesn't work.
############# The result 'InsertRows_list' will append 'dupe' table rows to insert (it always dupes the 'last' matched row from table2match_list)
            # Data that needs to be inserted as new rows is appended to a list, to be processed later
            if rowStatus == "Insert"

            # If row has not already been populated, then update the existing row in table1

# Insert all new rows
with arcpy.da.InsertCursor(table1, table1_fields) as InsertCursor:
    for table2_row_data in InsertRows_list:
        # Retrive the existing row in table1 that matches ID1
        table1_row_data = table1_dict.get(row[0])
        # New row is joined with data from table1 and table2
        new_join_row = (table1_row_data[0],table2_row_data[1],table1_row_data[2],table1_row_data[3],table2_row_data[0],table2_row_data[1],table2_row_data[2])