Using Search Cursor Dictionary/Update Cursor to Populate Many Fields Using Values from Another Table

4109
9
Jump to solution
01-30-2018 04:48 PM
TessOldemeyer
Occasional Contributor

I am trying to populate many new fields in a new table using values from fields in another table. I am hoping to use a combination of a da.search cursor dictionary and a da.update cursor to speed up this field update.

I am not receiving any errors with this code, though the field values are not populating, and the fields remain blank after that script has completed. Any suggestions on what I might need to fix?

"KEY_FIELD" is the unique identifier and is populated in both tables. 

search_feats = {f[0]:f[1] for f in arcpy.da.SearchCursor(Table_1,["key_field","field_2","field_3", "field_4", "field_5", "field_6"])}

with arcpy.da.UpdateCursor(Table_2,["KEY_FIELD","FIELD_2","FIELD_3", "FIELD_4", "FIELD_5","FIELD_6"]) as upd_cur:
     for upd_row in upd_cur:
         CodeID = upd_row[0]
         if CodeID in search_feats:
            upd_row[1] = search_feats[CodeID][0] #FIELD_2
            upd_row[2] = search_feats[CodeID][1] #FIELD_3
            upd_row[3] = search_feats[CodeID][2] #FIELD_4
            upd_row[4] = search_feats[CodeID][3] #FIELD_5
            upd_row[5] = search_feats[CodeID][4] #FIELD_6

            upd_cur.updateRow(upd_row)  

del upd_cur‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
0 Kudos
1 Solution

Accepted Solutions
RandyBurton
MVP Regular Contributor

Since you want to add several values to each dictionary key, you need to use f[0]:f[1:].   The colon is used for slicing and [1:] is equivalent to "1 to end".  You may also wish to put your field names into a variable if both the search cursor and update cursor are working with the same field names.  If the field types are the same in both tables, you shouldn't need to cast types.  Hope this helps.

flds = ["KEY_FIELD","FIELD_2","FIELD_3"]

search_feats = {f[0]:f[1:] for f in arcpy.da.SearchCursor(Table_1,flds)}

with arcpy.da.UpdateCursor(Table_2,flds) as upd_cur:‍‍‍‍‍

View solution in original post

9 Replies
DanPatterson_Retired
MVP Esteemed Contributor

Which means that "CodeID in search_feats" fails and the row doesn't get updated.

To test, put a print statement just above your if

print("{} is in {}".format(CodeId, CodeID in search_feats))
0 Kudos
TessOldemeyer
Occasional Contributor

That must be what is going on. I receive a '61005 is in False' printout for all CodeID values. I'll dig into it a bit more to see what may be going on. Thanks!

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

oh.. you are using dictionaries, you need the dictionary  'key'.  

the 'in' operator needs lists and other iterables, not dictionaries.  Keys are iterable once extracted

sc = [[1, 'a'], [2, 'b']]
dct = {f[0]:f[1] for f in sc}

dct
{1: 'a', 2: 'b'}

for row in sc:
    code = row[0]
    if code in dct.keys():  # need to get the code from the dictionary keys
        print("has code")
        
has code
has code
0 Kudos
TessOldemeyer
Occasional Contributor

It looks like the key field names are accurate and that they are both accurately populated in both tables. However, "key_field" in Table_1 has a 'double' field type, and "KEY_FIELD" in Table_2 is a text field. Is there an easy way to cast "key_field" as text in Table_1 by chance (if this might help to solve the issue)?

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

if you want to cast integers to text

str(your number)

if you want to cast text to integers... ONLY if they can be

int(your text number )  ie int('1') will return 1

0 Kudos
TessOldemeyer
Occasional Contributor

For some reason, I am really struggling with how to fix this issue. If I cast CodeID in line 5 as an integer, I am able to populate Table_2 fields with only one letter (rather than the entire text string from Table_1) before getting a 'string out of range error'. Maybe it would be best to change "key_field" in Table_1 to a text field using arcpy.TableToTable_conversion prior to creating the dictionary? I am not quite sure how to get field mappings to work for this though. Do you have any recommendations on how to most efficiently handle this?

Thank you so much for your help.

0 Kudos
RandyBurton
MVP Regular Contributor

Since you want to add several values to each dictionary key, you need to use f[0]:f[1:].   The colon is used for slicing and [1:] is equivalent to "1 to end".  You may also wish to put your field names into a variable if both the search cursor and update cursor are working with the same field names.  If the field types are the same in both tables, you shouldn't need to cast types.  Hope this helps.

flds = ["KEY_FIELD","FIELD_2","FIELD_3"]

search_feats = {f[0]:f[1:] for f in arcpy.da.SearchCursor(Table_1,flds)}

with arcpy.da.UpdateCursor(Table_2,flds) as upd_cur:‍‍‍‍‍
RandyBurton
MVP Regular Contributor

Since search_feats is set using list comprehension, you might want to check out Dan Patterson‌'s blogs on the topic:

List comprehensions... 

List comprehensions... 2

List comprehensions 3 ...

(And Dan has lots of other interesting Python postings)

TessOldemeyer
Occasional Contributor

Thank you both so much for your help. The list comprehension blog posts are great links to have, and thank you so much for sharing.

It turns out that I did need to use f[0]:f[1:] for the dictionary (hugely helpful) in addition to casting CodeID as an integer in line 5 of my initial posting.

0 Kudos