topic Re: Nested Search/InsertCursors in Python Snippets Questions

Nested Search/InsertCursors

Nicole_Ueberschär — Sun, 12 Dec 2021 10:24:05 GMT

I need some help with some nested Search/Insert Cursors adding data to a table and to a point feature class.

What I have:
- I have one big table with stations 1-n with for example 10,000 rows for each station.
- A point feature class in a file geodatabase.
What I want:
- I want to copy the rows for each station into a newly created table (all attributes).
- AND I want to copy the first row from this new table into the point feature class but only with the relevant fields (5 attributes) adding its point to the geometry of course.
What I get:
I managed to create one new table and copy the table content into the new table. The script runs without giving an error but in the end I don't have a new point in my feature class.
When I try to let it run for more than one station I get the RuntimeError: workspace already in transaction mode (below in Row 14).
When I remove the part with creating the point I can create as many tables as I want.
Printing the values that are supposed to be inserted into the point feature class (fillValues) they look good to me.
What I also already tried:
I just tried also with removing the table relevant part (since they are all created now). Now I get an IndexError: list index out of range pointing to my FirstObject. But it first runs through the whole script - but again without adding my point 😞
I also tried without adding the OBJECTID but with the same result.

I would be very happy if someone could push me in the right direction to make this work.

Here is simplified version of my module:

table=input table
fieldnames = [field names from input table except OBJECTID]

pfields=arcpy.ListFields(points)
fillnames=[field names for point feature class, manually created, not OBJECTID, not SHAPE]

# Create cursors and insert new rows
for profile in profile_nrs:
    expression="Station="+str(profile)
    with arcpy.da.SearchCursor(table,fieldnames,expression) as sCursor:
        for sRow in sCursor:
            table_name=str(sRow[0])+"_"+str(sRow[1])+"_data1"
            with arcpy.da.InsertCursor(table_name,fieldnames) as iCursor:
                for sRow in sCursor:               -> This is where I get the RuntimeError
                    iCursor.insertRow(sRow)
                del iCursor
                del sRow
                del sCursor
        print("Table "+table_name+" filled")

# From here starts the point feature creation
        #Identify first OBJECTID used in new table
        oid_nrs=unique_values(table_name, "OBJECTID")
        FirstObject=oid_nrs[0]
          #Define expression to limit the extracted rows to the first row
        pexpression="OBJECTID="+str(oid_nrs[0])

        #Define field names used for point table
        pfieldnames=['Cruise','Station', 'Type', 'Date', 'Longitude', 'Latitude']
        
        print(pfieldnames)
        pfieldsvalues=[FirstObject]
        for pname in pfieldnames:
            with arcpy.da.SearchCursor(table_name, pname, pexpression) as pCursor:
             for pRow in pCursor:
                fillvalue=pRow[0]
                print(fillvalue)
                pfieldsvalues.append(fillvalue)
        ShapeVal=[pfieldsvalues[5],pfieldsvalues[6]]
        print(fillnames)
          #take the values extracted from the first row in the new table and put 
        #them into a new array adding OBJECTID and Shape (Geometry)
        fillValues=[pfieldsvalues[0], ShapeVal, pfieldsvalues[1], pfieldsvalues[2], pfieldsvalues[3], pfieldsvalues[4], pfieldsvalues[5], pfieldsvalues[6]]
        print(fillValues)
        newPoint=arcpy.da.InsertCursor(points, fillnames)
        newPoint.insertRow(fillValues)
        print("point added to All_points")
        del pRow
        del pCursor‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Nested Search/InsertCursors

NeilAyres — Sun, 12 Dec 2021 10:24:07 GMT

Without really trying to figure out exactly what is going on here....

with arcpy.da.SearchCursor(table,fieldnames,expression) as sCursor:
        for sRow in sCursor:
            table_name=str(sRow[0])+"_"+str(sRow[1])+"_data1"
            with arcpy.da.InsertCursor(table_name,fieldnames) as iCursor:
                for sRow in sCursor:               -> This is where I get the RuntimeError
                    iCursor.insertRow(sRow)
                del iCursor
                del sRow
                del sCursor
        print("Table "+table_name+" filled")

You are going through sCursor twice here. Then you are deleting it inside the loop.

You don't need a with statement for the InsertCursor. I would open the insert outside of the loop.

Re: Nested Search/InsertCursors

Nicole_Ueberschär — Sun, 12 Dec 2021 10:24:10 GMT

Thank you Neil for looking into this.

Although your comment makes totally sense to me it seems like I created now an endless loop that doesn't allow the module to be finished.

with arcpy.da.SearchCursor(table,fieldnames,expression) as sCursor:
        for sRow in sCursor:
            table_name=str(sRow[0])+"_"+str(sRow[1])+"_data1"
            iCursor=arcpy.da.InsertCursor(table_name,fieldnames)
            iCursor.insertRow(sRow)
            del iCursor
            del sRow
            del sCursor
        print("Table "+table_name+" filled")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

When I have the del sCursor in line 8 it says there is no sCursor, when I remove it and put it to the end then it runs without ending.

It seems to establish the table_name correctly but only that.

Any idea why my point is not created?

Re: Nested Search/InsertCursors

DanPatterson_Retired — Fri, 10 Feb 2017 11:20:41 GMT

your del statements... are they meant to be within the for loop? within the while loop? or do you simply want them deleted when you are done? From the above, dedent the del statements appropriately

Re: Nested Search/InsertCursors

Nicole_Ueberschär — Fri, 10 Feb 2017 11:56:13 GMT

Thanks for the hint. I think I indented/detended them the way it should be (from my understanding I should delete them at the end of the according loop).

okaaaaayyyyy, thinking through this I think the problem is, that it tries to create the table every time the sCursor reaches a new sRow. I guess this is why I had put an extra loop before for filling the table.

Which still doesn't explain why the point is not created...

Re: Nested Search/InsertCursors

DanPatterson_Retired — Fri, 10 Feb 2017 12:07:00 GMT

because your cursors are deleted within the loop

Re: Nested Search/InsertCursors

Nicole_Ueberschär — Fri, 10 Feb 2017 13:03:11 GMT

I think the problem is still somehow how the loops are nested.

At which point would you suggest to delete the sCursor?

Re: Nested Search/InsertCursors

IanMurray — Fri, 10 Feb 2017 14:26:47 GMT

Alternatively to using nested cursors, I suggest taking a look at Richard Fairhurst excellent blog post about using dictionaries and cursors for data manipulation. Should also give you a significant performance boost in processing time.

Re: Nested Search/InsertCursors

NeilAyres — Sun, 12 Dec 2021 10:24:13 GMT

with arcpy.da.SearchCursor(table,fieldnames,expression) as sCursor: # you open the cursor
        for sRow in sCursor: # and start reading each row
            table_name=str(sRow[0])+"_"+str(sRow[1])+"_data1" # conduct some table identifier
            iCursor=arcpy.da.InsertCursor(table_name,fieldnames) # open cursor and insert row
            iCursor.insertRow(sRow)# i hope that table_name exists and fieldnames are compatible
            del iCursor # this is okay in this context
            del sRow # but woah, why am I deleting the row, it will be recreated on next look anyway
            del sCursor # and now I have deleted the cursor itself
            # that's why you get an error on the second time round the read.
        print("Table "+table_name+" filled")‍‍‍‍‍‍‍‍‍‍

Just to get back to this little bit of code...

So, with statements are supposed to obviate the need to delete the cursor objects anyway. They are deleted auto magically after the with closes.

Basically, don't delete your cursor object while the loop is still reading it.