Switching from Nested Search Cursors to Dictionaries

Question

I'm working with a table that has in excess of 800K records and my method of using nested cursors is not practical. I've been reading and it looks like I should switch to dictionaries, but after much research, I'm not sure how to replicate the functionality of the code below using dictionaries.

import arcpy

sdeFiberSplice = r'C:\Users\broadhurstn\Documents\ArcGIS\Default.gdb\FIBERSPLICE'
asBuiltFIBERSPLICE = r'C:\Users\broadhurstn\Documents\ArcGIS\Default.gdb\ASBUILTFIBERSPLICE'

sdeFiberSpliceFields = ["asegmentidfkey", "zsegmentidfkey", "afiberid", "zfiberid", "bv_asbuilt_gdb_uploaded"]
asBuiltFiberSpliceFields = ["ASEGMENTIDFKEY", "BSEGMENTIDFKEY", "AFIBERNUMBER", "BFIBERNUMBER"]


with arcpy.da.SearchCursor(asBuiltFIBERSPLICE, asBuiltFiberSpliceFields) as cursor:
    for row in cursor:
        asegkey = row[0]
        zsegkey = row[1]
        asibid = row[2]
        zfibid = row[3]

        with arcpy.da.UpdateCursor(sdeFiberSplice, sdeFiberSpliceFields) as cursor:
            for row in cursor:
                if asegkey == row[0] and zsegkey == row[1] and asibid == row[2] and zfibid == row[3]:
                    print asegkey
                    row[4] = "Y"
                    cursor.updateRow(row)
print "Done."

Basic Logic of Code:

If combination of "ASEGMENTIDFKEY" and "BSEGMENTIDFKEY" and "AFIBERNUMBER" and "BFIBERNUMBER" in table "asBuiltFIBERSPLICE" == combination of "asegmentidfkey" and "zsegmentidfkey" and "afiberid" and "zfiberid" in table "FIBERSPLICE" than populate "bv_asbuilt_gdb_uploaded" with string "Y".

Visual Representation of Logic:

Can someone point me in the right direction with pseudocode?

Answer 1

If you can create unique keys and speed is an issue, a set would probably be the best choice since it is considerably faster to check a set than a list for a specific item. Loop through table1 using the 4 fields concatenated as the key, and add each key to the keys set. Then loop through table2, create the key the same way, and check if each key is in the set.

table1Keys = set()
with arcpy.da.SearchCursor(table1, [field0, field1, field2, field3]) as search:
    for row in search:
        key = "{0},{1},{2},{3}".format(row[0], row[1], row[2], row[3])
        table1Keys.add(key)

with arcpy.da.UpdateCursor(table2, [field0, field1, field2, field3, field4]) as update:
    for row in update:
        key = "{0},{1},{2},{3}".format(row[0], row[1], row[2], row[3])
        if key in table1keys:
            row[4] = "Y"
            update.updateRow(row)

As pointed out in a comment, since tuples are hashable, you could add the values directly to the set without converting to strings.

So to get table1 keys:

for row in search:
    table1Keys.Add(row)

And to compare table2 rows:

for row in update:
    key = (row[0], row[1], row[2], row[3])

Answer 2

Store all values from table 2 in a list then for each row in table 1 check if in the list:

import arcpy

table1 = "table1"
fields1 = ["checkfield1","checkfield2","checkfield3","checkfield4", "field_to_update"]  

table2 =  "table2"
fields2 = ["checkfield1","checkfield2","checkfield3","checkfield4"]
all_records_table2 = [i for i in arcpy.da.SearchCursor(table2,fields2)]

with arcpy.da.UpdateCursor(table1,fields1) as cursor:
    for row in cursor:
        if row[:-1] in all_records_table2:
            row[-1] = 'Y'
            cursor.updateRow(row)