Compare field values from 2 different tables, Locate value from table 2 in that row, use UpdateCursor to Update field in table 1

[MPach]

I'm kind of new to this, but kind of not. I just don't use it as often as I would like in some cases. With that out of the way here's what I'm attempting to do. 

I have two tables that I am working with 1) Manhole Table 2) Gravity Mains Table. The important rows for this script are in the manhole table: INVERTELEV (invert elevation) and FACILITYID (an identifier). From the Gravity Mains table there are 2 important rows: FROMMH (this will be a manhole Facility ID #) and UPSTREAMIN (the upstream invert of the gravity main).

What I am attempting to do is with all the Gravity Main FROMMH values in a list take each value from that list and compare it to a value in the Manhole table FACILITYID field until I find a match. When I find a matching value I want to copy the value in the INVERTELEV field from the manhole table and update the UPSTREAMIN value in the Gravity Mains table. It seems simple enough, but I think I'm making this more complicated than it has to be and I'm confusing myself with nested loops. 

I've attach a python file that I'm working on with some notes. Any help on how to do this would be greatly appreciated. 

FYI, one thing I am kind of unclear about is  with the Update cursor. Does this UpdateCursor object update the data at the current position, meaning the current row it's working on. What exactly does that mean? Does it mean that if I find the row with the matching FACILITYID and FROMMH that when I go to use the row.SetValue()  method it will update that row. That's what it sounded like when I read it, but I'm just trying to clarify. I know I could probably setup a sample to test this, but I figured I might as well ask while I'm posting this request for help.  

Thanks 

[Anonymous User · Accepted solution]

I would think about creating dictionaries of values from the two tables first, and then perform your match operation on dictionaries to get one list (could be a list of tuples, or a dictionary) of values.  Then open one cursor per table to update destination tables using that list/dictionary value in a sql query within your update cursor so its updating that row's value. I might have got things mixed in the example code, but hopefully it will give you an idea.

#Read mainsList one at a time and search for the value in the MH table. Then update 
for row in cursorWriteValue:
    FIDMHRowValue = (row.getValue(fieldInMHTable))
    MHFacIDList.append(FIDMHRowValue)

for FacID in mainFacIdList:
    if FacID in MHFacIDList:  
       sql = """FACILITYID = FROMMH value (FacID)?"""
       with arcpy.UpdateCursor(updateFieldInMainTable, [ UPSTREAMIN], sql) as cur:
            for row in cur:
               row[0] = FacID[1] #value from the tuple or dictionary, however you want to do it.
               cur.updateRow()

[DavidPike]

import arcpy

#set workspace to folder of shapefiles or geodatabase
arcpy.env.workspace = r"C:\Users\MarkPa\Desktop\GIS_Data\Projects\Sewer\Harmon\OutfallStats\Depth of mains" 


gravity_main_fc = r"C:\Users\MarkPa\Desktop\GIS_Data\Projects\Sewer\Harmon\OutfallStats\Depth of mains\GravityMainTestSet.shp" 
manhole_fc = r"C:\Users\MarkPa\Desktop\GIS_Data\Projects\Sewer\Harmon\OutfallStats\Depth of mains\MHTestSet.shp"


#create a list of lists, [[facility id, invert elev], [facility id, invert elev] ...]
manhole_list = []

#run a cursor to append to the manhole_list
#row is a tuple of values for each row within the fields specified in the cursor
#row[0] is facility id, row[1] is invertelev
with arcpy.da.SearchCursor(manhole_fc, ["FACILITYID", "INVERTELEV"]) as cursor:
    for row in cursor:
        manhole_list.append([row[0], row[1])


#update gravity main fc
#row[0] is from mh, row[1] is upstream in
with arcpy.da.UpdateCursor(gravity_main_fc, ["FROMMH", "UPTREAMIN"]) as cursor:
    for row in cursor:
        for item in manhole_fc:
            if row[0] == item[0]:
                row[1] = item[1]

        cursor.updateRow(row)

[JoeBorgione · Accepted solution]

+1 on the dictionary approach suggested by @Anonymous User .  I keep this as a bookmark and refer back to it all the time:

https://community.esri.com/t5/python-blog/turbo-charging-data-manipulation-with-python-cursors-and/ba-p/884079

[MPach]

@JoeBorgione  Oh  man, thank you for that. After reading @RichardFairhurst article I realized that I don't even need to write a python script, but it might help out a bit. Why didn't I just think to use a join and make one fields values equal to the other, duh. I knew I was over complicating things. That always seems to be a thing for me. I was looking for more difficult solution instead of the one that was right in front of me. Probably just because I really do need to start using Python more again, it's fun. 

Anyway, I appreciate the responses and I will try both of those approaches @Anonymous User and @DavidPike . Thank you both very much.

Mark 

[JoeBorgione]

I knew I was over complicating things. That always seems to be a thing for me.

I thought I had a corner on that market...

That's the beauty of this forum; enough of us have forehead bruises from banging our heads against a screen and like to keep others from doing the same!

[MPach]

That's why I posted. I was done banging my head, lol. 🤣

[DavidPike]

I've removed my post as a solution as I think the dictionary method is much better.  Why I thought looping through a list each time to find matching values is pretty unforgivable.

[Anonymous User]

Glad it helped and sorry for not leaving a better example using dictionaries.  I planned to come back with some sample code that I use in my scripts but couldn't make it in time.  After I saw that @JoeBorgione shared the link to some awesome examples, mine would just be redundant.

I don't think anyone is scoffing at the list iteration idea, at least they better not be.  Lists may be easier for some to follow, dictionaries easier for others.  If the author can understand it and it produces the same result, I think its a valid solution.  I'll be honest and admit it took me a while to grasp working with dictionaries.  Once I got it though, there were quite a few scripts that were updated.

[DanDeegan]

Your article changed how I approach most things in python. I use them to store field details and later when adding fields. I don't use joins. More fun overall.