nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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 <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>Table_1<SPAN class="punctuation token">,</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="string token">"FieldA"</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>


i <SPAN class="operator token">=</SPAN> <SPAN class="number token">0</SPAN>
<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>UpdateCursor<SPAN class="punctuation token">(</SPAN>Table_2<SPAN class="punctuation token">,</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="string token">"FieldA"</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> upd_cur<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> upd_row <SPAN class="keyword token">in</SPAN> upd_cur<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; i<SPAN class="operator token">=</SPAN>i<SPAN class="operator token">+</SPAN><SPAN class="number token">1</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; upd_row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> search_feats <SPAN class="punctuation token">(</SPAN>i<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; upd_cur<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN>upd_row<SPAN class="punctuation token">)</SPAN>&nbsp; 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
<SPAN class="keyword token">del</SPAN> upd_cur
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

What may be a better way to approach this?

Najděte více příspěvků označených jako

Accepted answers

RandyBurton

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 <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>InsertCursor<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"Table_2"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="string token">'FieldA'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> tbList<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; cursor<SPAN class="punctuation token">.</SPAN>insertRow<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>row<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># as Bixby suggested below, using square brackets</SPAN>
<SPAN class="keyword token">del</SPAN> cursor‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

All comments

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!

RandyBurton

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

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

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?

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 <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="comment token"># empty list</SPAN>

<SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Table_1'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">"FieldA"</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">if</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">not</SPAN> <SPAN class="keyword token">in</SPAN> tbList<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tbList<SPAN class="punctuation token">.</SPAN>append<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># copy distinct rows to list</SPAN>

inCursor <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>InsertCursor<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Table_2'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">"FieldA"</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">"MyCalc"</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># list of all fields in new table</SPAN>

<SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> tbList<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;inCursor<SPAN class="punctuation token">.</SPAN>insertRow<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">,</SPAN>None<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># field values to insert</SPAN>

<SPAN class="keyword token">del</SPAN> inCursor‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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

TessOldemeyer

Yes, they are unique values.

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 <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="comment token"># empty list</SPAN>

<SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Table_1'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">"FieldA"</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">if</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">not</SPAN> <SPAN class="keyword token">in</SPAN> tbList<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tbList<SPAN class="punctuation token">.</SPAN>append<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># copy distinct rows to list</SPAN>

inCursor <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>InsertCursor<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Table_2'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">"FieldA"</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># list of all fields in new table</SPAN>

<SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> tbList<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp; inCursor<SPAN class="punctuation token">.</SPAN>insertRow<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">,</SPAN>None<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># field values to insert</SPAN>

<SPAN class="keyword token">del</SPAN> inCursor‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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

RandyBurton

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<SPAN class="punctuation token">.</SPAN>insertRow<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># field values to insert</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

Where the values in tbList what you were expecting?

TessOldemeyer

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!

Připojte se ke konverzaci

Přihlášení členové mohou přispívat, sledovat aktualizace a další. Jste tu noví? Zaregistrujte si bezplatný účet.

Rychlé odkazy

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.