Using and Search and Update Data Access Cursors to Populate a Field in a New Table

[TessOldemeyer]

I'm assuming this is pretty straightforward, and I am missing something fairly simple. 

I am attempting to bring in a column of data values, 'FieldA', from a table, 'Table_1', to populate an empty field, 'FieldA', in a new, empty table, 'Table_2'. These 'FieldA' values will be used as a key in 'Table 2' for subsequent field calculations. Is there a direct way to do this?

I have been attempting to use a search cursor and update cursor combination to write the field values to the new table, though I am not sure how to successfully employ the cursors to update the single field without an existing key between the two tables (where I would use a dictionary). 

Here is my unsuccessful, botched code:

search_feats = arcpy.da.SearchCursor(Table_1,["FieldA"])


i = 0
with arcpy.da.UpdateCursor(Table_2,["FieldA"]) as upd_cur:
     for upd_row in upd_cur:
         
         i=i+1
         upd_row[0] = search_feats (i)
         upd_cur.updateRow(upd_row)  
         
del upd_cur
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

What may be a better way to approach this?

[DanPatterson_Retired]

if there is a 1:1 relationship, just use 'join' and calculate field, from the script examples in their respective tools

[TessOldemeyer]

This might be the straightforward way  I was hoping to keep the existing 'Table_2' structure (without having to add and reorder fields), but deleting the new field after a quick join and calculation might be the best way to go. 

Thanks!

[DanPatterson_Retired]

I tend to use numpy and arcpy.da.ExtendTable, it still is a join, but your calculations can be vectorized and the join is permanent

[RandyBurton]

You might check out: Turbo Charging Data Manipulation with Python Cursors and Dictionaries.  I think Example 2 might work for you.

[TessOldemeyer]

That blog post is AWESOME. I have been using it for many of my field updates lately, and it has been a game changer for speeding up scripts. I was actually hoping to integrate something similar for this particular issue but became stumped when I didn't have a key for both tables. I am also not quite sure if a dictionary is helpful, if the search cursor is only reading values from one field.

If you have suggestions as to how I might make this method work for updating one existing field on a new table that does not already have a key (the new field that is being populated here will act as the key for subsequent field calculations), I will gladly take them. I'm a python novice and am looking for recommendations anywhere I can get them. Thank you so much!

[JoshuaBixby]

Does FieldA in Table1 contain unique values or not?

[TessOldemeyer]

Yes, they are unique values.

[RandyBurton]

If you want to get the distinct values from Table_1.FieldA and insert them into a new, empty table in FieldA, you can use a search cursor to retrieve the values and an insert cursor to put them in the new table.  Perhaps something like:

tbList = [] # empty list

for row in arcpy.da.SearchCursor('Table_1', ["FieldA"]):
    if row[0] not in tbList:
        tbList.append(row[0]) # copy distinct rows to list

inCursor = arcpy.da.InsertCursor('Table_2', ["FieldA","MyCalc"]) # list of all fields in new table

for row in tbList:
     inCursor.insertRow((row,None)) # field values to insert

del inCursor‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

You can create your calculation field ("MyCalc" for example) when you create your table or add it later.  Hope this helps.

[TessOldemeyer]

I think this is exactly what I am looking for. 

However, I am getting a "sequence size must match size of the row" error on line 10. I did not include a "myCalc" field though, as I have already added all of the necessary fields to the new table (i.e. Table_2). (Maybe I am wrong in excluding it?) 

tbList = [] # empty list

for row in arcpy.da.SearchCursor('Table_1', ["FieldA"]):
    if row[0] not in tbList:
        tbList.append(row[0]) # copy distinct rows to list

inCursor = arcpy.da.InsertCursor('Table_2', ["FieldA"]) # list of all fields in new table

for row in tbList:
     inCursor.insertRow((row,None)) # field values to insert

del inCursor‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

If I print tbList, this is what I currently get: