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

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

TessOldemeyer — Sat, 11 Dec 2021 15:45:16 GMT

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?

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

DanPatterson_Retired — Mon, 29 Jan 2018 22:58:49 GMT

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

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

TessOldemeyer — Mon, 29 Jan 2018 23:04:31 GMT

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!

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

RandyBurton — Mon, 29 Jan 2018 23:09:56 GMT

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

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

DanPatterson_Retired — Mon, 29 Jan 2018 23:17:50 GMT

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

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

TessOldemeyer — Mon, 29 Jan 2018 23:51:49 GMT

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!

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

JoshuaBixby — Tue, 30 Jan 2018 02:55:09 GMT

Does FieldA in Table1 contain unique values or not?

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

RandyBurton — Sat, 11 Dec 2021 15:45:19 GMT

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.

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

TessOldemeyer — Tue, 30 Jan 2018 15:58:46 GMT

Yes, they are unique values.

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

TessOldemeyer — Sat, 11 Dec 2021 15:45:22 GMT

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:

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

RandyBurton — Tue, 30 Jan 2018 17:06:18 GMT

I believe that with an insert cursor, the field list used at line 7 should include all the field names. Assuming your table only has one field (FieldA), the list can be only 1 item.

In line 10, the "None" was the value to assign to the "MyCalc" field. Since it is not part of the field list in line 7, it should be dropped. Again, if only one field, something like :

inCursor.insertRow((row)) # field values to insert‍

Where the values in tbList what you were expecting?

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

TessOldemeyer — Tue, 30 Jan 2018 17:53:46 GMT

Gotcha. Yes, Table_2 currently has 40 empty fields. I would like to only populate FieldA. Is there a way to assign 'None' to all subsequent fields without having a long list of 'Nones'? It looks like I might be able to use (*) to read in all fields from Table_1 if this is needed for line 7 (vs. having to list out all fields).

Also, the tbList values are what I was expecting. I just wanted to make sure the list format wasn't part of the 'size of row error'.

Thanks for all of your help!

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

RandyBurton — Sat, 11 Dec 2021 15:45:24 GMT

Since you only have one field value to insert, try writing the insert cursor part like this:

rows = arcpy.InsertCursor("Table_2")
for item in tbList:
    row = rows.newRow()
    row.setValue("FieldA", item) # may need to format "item"
    rows.insertRow(row)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

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

RandyBurton — Sat, 11 Dec 2021 15:45:27 GMT

In my previous response, I used the old InsertCursor. Try the da.InsertCursor, and since it is only one field, not a tuple, just one set of () around row.

cursor = arcpy.da.InsertCursor("Table_2",['FieldA'])
for row in tbList:
    cursor.insertRow([row]) # as Bixby suggested below, using square brackets
del cursor‍‍‍‍‍‍‍‍‍‍‍‍

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

TessOldemeyer — Sat, 11 Dec 2021 15:45:30 GMT

This is great, and I like the da.InsertCursor route. I am getting a "argument must be a sequence of values" error on line 3 (line 9 in the code below).

Here is the code I have:

tbList = [] # empty list

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

cursor = arcpy.da.InsertCursor("Table_2",['FIELD_A'])
for row in tbList:
    cursor.insertRow(row)
del cursor

‍‍‍‍‍‍‍‍‍‍‍

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

JoshuaBixby — Tue, 30 Jan 2018 19:06:27 GMT

Try:

cursor.insertRow([row])

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

TessOldemeyer — Tue, 30 Jan 2018 19:12:46 GMT

That did the trick. Thank you so much!