Repeating Tuples with Insert/Search Cursor

KevinDickinson1

I am trying to use an InsertCursor/SeachCursor to transfer data from one dataset to another wile incorporating a repeating tuple. The first dataset (in the below code is "Forest_Surface_Fuels") contains all the data and has attribute fields for each record that contains data for 1hr, 10hr, and 100hr fuels. What I am trying to do as I transfer this data to a empty dataset ("1_10_100") is to not only take the values in those fields, but create a master list for all records in a project and in a new field state whether the data is a 1hr, 10hr, or 100hr.

Here is the code that I have:

# Fields that data will be inserted into in the final excel table
fields = ['Plot_', 'SurveyType', 'Transect', 'OneHour', 'TenHr', 'HunHr']

#The data to be inserted (this is the tuple that will be repeated)
fuelsTuple = [
        ('Macroplot', "Pre", "1", 'onehr_pre_1', 'tenhr_pre_1', 'hundhr_pre_1'),
        ('Macroplot', "Pre", "2", 'onehr_pre_2', 'tenhr_pre_2', 'hundhr_pre_2'),
        ('Macroplot', "Post", "1", 'onehr_post_1', 'tenhr_post_1', 'hundhr_post_1'),
        ('Macroplot', "Post", "2", 'onehr_post_2', 'tenhr_post_2', 'hundhr_post_2')
]

#make count of times to repeat the tuple
repeats = arcpy.management.GetCount("Forest_Surface_Fuels")
count = int(repeats[0])

#Insert Cursor to update fields
plotCursor = arcpy.da.InsertCursor("Forest_Surface_Fuels", fuelsTuple)
with arcpy.da.SearchCursor("1_10_100", fields) as cursor:
       for i in range(count):
             for row in cursor:
                   plotCursor.insertRow(row)
del plotCursor
del cursor

When I run this, I get this error:

Thanks!

Hayden Welch

I'd approach this by making the field tuple a bit more complex. It seems that the first records are raw data and the last few are field values. You can just break it into two mappings like this:

# First Dictionary is direct data writes, second is field values to pull from the Origin dataset
conversion_map: list[tuple[dict[str, Any], dict[str, Any]]] = [
    (
        {'Plot_': 'Macroplot', 'SurveyType': 'Pre', 'Transect': '1'}, 
        {'OneHour': 'onehr_pre_1', 'TenHr': 'tenhr_pre_1', 'HunHr': 'hundhr_pre_1'}
    ),
    (
        {'Plot_': 'Macroplot', 'SurveyType': 'Pre', 'Transect': '2'}, 
        {'OneHour': 'onehr_pre_2', 'TenHr': 'tenhr_pre_2', 'HunHr': 'hundhr_pre_2'}
    ),
    (
        {'Plot_': 'Macroplot', 'SurveyType': 'Post', 'Transect':'1'}, 
        {'OneHour': 'onehr_post_1', 'TenHr': 'tenhr_post_1', 'HunHr': 'hundhr_post_1'}
    ),
    (
        {'Plot_': 'Macroplot', 'SurveyType': 'Post', 'Transect':'2'}, 
        {'OneHour': 'onehr_post_2', 'TenHr': 'tenhr_post_2', 'HunHr': 'hundhr_post_2'}
    ),
]

That will make it clearer later what the raw data mapping is and what the extracted field values are.

When dealing with multiple cursors, I tend to like mapping the rows instead of using an index. This can be done with this incredibly simple helper function:

def as_dict(cur: SearchCursor) -> Iterator[dict[str, Any]]:
    for row in cur:
        yield dict(zip(cur.fields, row))

You can wrap a cursor with this before iterating and you'll get back a dictionary that can be updated

 Now we need to implement the mapping:

def convert(origin: str, target: str, fields: list[str], conversion_map: ConversionMap, null_value: Any=None):
    with (
        SearchCursor(origin, fields) as s_cur,
        InsertCursor(target, fields) as i_cur
    ):
        for row in as_dict(s_cur):
            for fuel_data, fuel_fields in conversion_map:
                new_row = fuel_data.copy()
                new_row.update({k: row.get(v, null_value) for k, v in fuel_fields.items()})
                i_cur.insertRow([new_row.get(f, null_value) for f in i_cur.fields])

Here we create a context block with two cursors, one to search through the origin table ("Forest_Surface_Fuels") and one to insert into the target table ("1_10_100"). 

1. (line: 2) Create a context block with 2 cursors for the Target and Origin
2. (line: 6) Iterate dictionary mappings of the origin rows
3. (line: 7) Iterate all defined row conversions
4. (line: 8 ) Create a copy of the template row data
5. (line: 9) Get the field value of the current origin row for each mapped target field
6. (line: 10) Create a row in the correct shape by iterating the insert fields and grabbing the new_row values

The null_value here shouldn't ever trigger, since it'll only exist if the fieldname doesn't exist in the origin table. If you don't have any 0 values, you can re-map nulls by using an or instead:

new_row.update({k: row.get(v) or null_value for k, v in fuel_fields.items()})

This abuses pythons truthiness, so any value that's considered falsey will get the null_value ('', 0, False, None, etc.)

Finally, we need to put all this together into a script that doesn't pollute the global namespace with cursor references. Basically just put your logic into an if __name__ == '__main__' block:

from collections.abc import Iterator
from typing import Any
from arcpy.da import SearchCursor, InsertCursor

ConversionMap = list[tuple[dict[str, Any], dict[str, Any]]]

def as_dict(cur: SearchCursor) -> Iterator[dict[str, Any]]:
    for row in cur:
        yield dict(zip(cur.fields, row))

def convert(origin: str, target: str, fields: list[str], conversion_map: ConversionMap, null_value: Any=None):
    with (
        SearchCursor(origin, fields) as s_cur,
        InsertCursor(target, fields) as i_cur
    ):
        for row in as_dict(s_cur):
            for fuel_data, fuel_fields in conversion_map:
                new_row = fuel_data.copy()
                new_row.update({k: row.get(v, null_value) for k, v in fuel_fields.items()})
                i_cur.insertRow([new_row.get(f, null_value) for f in i_cur.fields])

if __name__ == '__main__':
    # Fields that data will be inserted into in the final excel table
    FIELDS = ['Plot_', 'SurveyType', 'Transect', 'OneHour', 'TenHr', 'HunHr']
    
    ORIGIN_DATA = "Forest_Surface_Fuels"
    TARGET_DATA = "1_10_100"

    # Update this with the Null Value you want to use, e.g. ['' or -1 or None]
    NULL_VALUE = None

        # First Dictionary is direct data writes, second is field values to pull from the Origin dataset
    CONVERSION_MAP: ConversionMap = [
        (
            # Direct Data Insertion
            {'Plot_': 'Macroplot', 'SurveyType': 'Pre', 'Transect': '1'},
            # Field Value Map from Origin
            {'OneHour': 'onehr_pre_1', 'TenHr': 'tenhr_pre_1', 'HunHr': 'hundhr_pre_1'}
        ),
        (
            {'Plot_': 'Macroplot', 'SurveyType': 'Pre', 'Transect': '2'}, 
            {'OneHour': 'onehr_pre_2', 'TenHr': 'tenhr_pre_2', 'HunHr': 'hundhr_pre_2'}
        ),
        (
            {'Plot_': 'Macroplot', 'SurveyType': 'Post', 'Transect':'1'}, 
            {'OneHour': 'onehr_post_1', 'TenHr': 'tenhr_post_1', 'HunHr': 'hundhr_post_1'}
        ),
        (
            {'Plot_': 'Macroplot', 'SurveyType': 'Post', 'Transect':'2'}, 
            {'OneHour': 'onehr_post_2', 'TenHr': 'tenhr_post_2', 'HunHr': 'hundhr_post_2'}
        ),
    ]

    convert(ORIGIN_DATA, TARGET_DATA, FIELDS, CONVERSION_MAP, NULL_VALUE)