0

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:

enter image description here

Can someone point me in the right direction with pseudocode?

  • 1
    This doesn't start to answer the switch to dictionaries, but I'm wondering if creating a unique key on both tables by concatenating the requested fields, then performing an attribute join might be faster. – smiller Mar 29 at 16:46
  • I can't change the schema of either of the tables, as the main table is in our SDE and the schema is very important. I've got to find a way to work with the fields and data available. With the code I have currently, it runs for 30 minutes than crashes the program, as I'm guessing cursors are an inefficient way to do this. – GIS_GOD Mar 29 at 17:24
  • 1
    Possible start: gis.stackexchange.com/questions/182837/… – smiller Mar 29 at 17:32
2

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])
  • Tuples are hashable, so you could add the row to the set directly without converting to string – mikewatt Mar 29 at 20:02
  • How would I deal with instances where the values are formatted slightly different in both tables, for instance table 1 = {D82CDF9F-B422-031E-5A22-A4E2D79722FB} and table 2 = d82cdf9f-b422-031e-5a22-a4e2d79722df. Sometimes the letters are all capital and there are squiggly bracket, other times the letters are all lower case and there a no squiggly brackets. I can't change the data, I have to work with what we have. – GIS_GOD Mar 29 at 20:16
  • @mikewatt Good point. I'll update the answer. – Dan Jurgella Mar 29 at 20:19
  • @GIS_GOD What are the field types you are comparing? Based on your screenshots the values in the two tables are formatted the same. Are some records in the same table formatted with curly braces and uppercase while others in the same table are no curly braces and lowercase? – Dan Jurgella Mar 29 at 20:37
  • 2
    Yeah, you'll have to write some data validation. As @mikewatt suggested, strip out the braces and convert to upper on all the string GUIDs. So in the code above you could do something like: row[0].replace("{", "").replace("}", "").upper(). – Dan Jurgella Mar 29 at 21:42
1

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.