Insert cursor only copies 80% of data due to lock?

Question

I have two tables:

1) feature class (i.e. table in code) = sites under "SiteID" and mean july data under "JulyMean". The sites have duplicates, therefore there are several groups of same sites. There are other fields in this table. There are 280 sites(rows) in total.

2) summary statistics table (i.e. stat_table in code) = summary statistics table containing the sites under "SiteID" with max mean July data under "MAX_JulyMean". There are 23 sites(rows) in total.

I'd like to create a new feature class (i.e. newtable_path) which contains all the sites identified in stat_table with the maximum mean data, however i'd like to include the other fields from the feature class table. I used search cursors to match the site id from the stat table with the feature class table. Then i insert the feature class table's data into the new feature class table. The insert cursor only copies 19/23 sites into the new table (all 19 rows are in order meaning the insertcursor is writing as should). I've read that the problem is due to locks etc... but I don't know how to release the lock.

How do I have all 23 rows copied instead of just 19?

#import packages 
import arcpy from arcpy 
import * from arcpy 
import env from arcpy.sa 
import * import os
arcpy.CheckOutExtension("Spatial")

#Input 
workspace = r"C:\Research\Water_Temperature_Analysis\Water_Temperature_80PercentOfJuly.gdb"
table =r"C:\Research\Water_Temperature_Analysis\Water_Temperature_80PercentOfJuly.gdb\DFOSLC_80andabove"
field = "SiteID" # field containing groups 
field1 = "JulyMean" #field with max and min 
stat_table = r"C:\Research\Water_Temperature_Analysis\Water_Temperature_80PercentOfJuly.gdb\DFOSLC_80andabove_extremeMaxMin"
    newtable = "DFOSLC_80andabove_ExtremeMeans"

    #enter workspace folder
    env.workspace = workspace
    arcpy.env.overwriteOutput = True

    #Find max and min
    #arcpy.Statistics_analysis(table, stat_table, [[field1, "MAX"],[field1,"MIN"]], field)
    # Create table with max and min 
    arcpy.CreateFeatureclass_management(workspace, newtable, "POINT", table) 
    newtable_path= workspace + "\\" + newtable

    # Copy Max Values to new table 
    cursor = arcpy.SearchCursor(table) 
    maxfield = "MAX_"+ str(field1) 
    cursor1 = arcpy.SearchCursor(stat_table,[field,maxfield]) 
    cursor2 = arcpy.InsertCursor(newtable_path)
    for row1 in cursor1:    
        for row in cursor:      
            if row1.getValue(field) == row.getValue(field):
                if row1.getValue(maxfield) == row.getValue(field1):
                    cursor2.insertRow(row)
                    row1 = cursor1.next() 
    del cursor2 
    del row, cursor
    del row1, cursor1

Answer 1

This line will produce an error because you're mixing the older version of the cursor with the newer data access version of the cursor:

cursor1 = arcpy.SearchCursor(stat_table,[field,maxfield])

Your best bet is to break up your various cursors into their own iterations instead of having all three opened at once. Make use of sets and lists to store data in memory.

Maybe something like this:

#enter workspace folder
env.workspace = workspace
arcpy.env.overwriteOutput = True

#Find max and min
#arcpy.Statistics_analysis(table, stat_table, [[field1, "MAX"],[field1,"MIN"]], field)
# Create table with max and min 
arcpy.CreateFeatureclass_management(workspace, newtable, "POINT", table) 
newtable_path= workspace + "\\" + newtable

#empty set to store site ID and mean/max values
values = set ()
#cursor and store valuves in set
cursor = arcpy.SearchCursor (stat_table)
for row in cursor:
    #get values
    siteId = row.getValue (field)
    maxVal = row.getValue (maxfield)
    vals = (siteId, maxVal)
    #update set
    values.add (vals)
del row, cursor

#store rows that match criteria
rows = []
cursor = arcpy.SearchCursor(table)
for row in cursor:
    siteId = row.getValue (field)
    maxVal = row.getValue (field1)
    vals = (siteId, maxVal)
    #check if vals are in set
    if vals in values:
        #if yes, add row to list of rows
        rows += [row]
del row, cursor

#insert cursor
cursor = arcpy.InsertCursor(newtable_path)
#iterate rows
for row in rows:
    cursor.insertRow (row)

del cursor

Answer 2

Unless you need to skip a row, row1 = cursor1.next() isn't needed when you're already in a loop like for row1 in cursor1:. The loop will automatically go to the next record for you.

Next problem is that your cursor cursor will complete its iteration after the first run of the cursor1 loop, so it needs to be reset so it starts over from the beginning. So replace row1 = cursor1.next() with cursor.reset().

for row1 in cursor1:    
    for row in cursor:      
        if row1.getValue(field) == row.getValue(field):
            if row1.getValue(maxfield) == row.getValue(field1):
                cursor2.insertRow(row)
    cursor.reset()

As it is written with the next() and no reset, it's going to do something like:

1. Load the first row of cursor1
2. Load the first row of cursor
3. Check to see if the values in field match
4. Check to see if the values in maxfield and field1 match
5. If they match, insert a row, and advance cursor1 to the second row
6. Load the second row of cursor to do the same checks
7. Oops! We're not on the first row of cursor1 anymore!
8. Every time we have we match, another row of cursor1 gets skipped
9. Eventually, we finish all the rows of cursor
10. So the for loop advances to the next row if cursor1, which isn't the second row since it's been advanced by every match.
11. So now with what I assume is the 19th row of cursor1:
12. We want to loop the values of cursor with the for-loop
13. But the cursor has already been run to the end in the first loop
14. The for-loop has nothing left to loop over so it skips the checks.

Answer 3

An alternative to doing the looping would be to use a sql query in one of the search cursors (untested):

with arcpy.da.InsertCursor(newtable_path) as output_cursor:
    with arcpy.da.SearchCursor(stat_table, [field, maxfield]) as stat_cursor: 
        for stat_row in stat_cursor:    
            field_value = stat_row[0]
            max_value = stat_row[1]
            SQL = " %s = '%s' AND %s = '%s' " % (field, field_value, field1, max_value )
            print SQL # can paste this into the Select By Attributes window to troubleshoot
            with arcpy.da.SearchCursor(table, '*', where_clause=SQL) as site_cursor:
                 for site in site_cursor:
                     output_cursor.insertRow(site)

Answer 4

Thank you everyone who helped me. I modified Emil Brundage's code and took advantage of the different ways of writing the same cursor to circumvent... The working code is:

.
.
.
# create list of site id and max mean
stat_data = set()
cursor = arcpy.da.SearchCursor (stat_table, [field, maxfield])
for row in cursor:
    siteid= row[0]
    maxmean = row[1]
    valset = (siteid,maxmean)
    stat_data.add(valset)

del row, cursor

print stat_data

# find rows that match criteria and copy
cursor = arcpy.SearchCursor(table)
cursor1 = arcpy.InsertCursor(newtable_path)

for row in cursor:
    siteid_o = row.getValue(field)
    maxmean_o = row.getValue(field1)
    valset_o = (siteid_o, maxmean_o)
    #check if vals are in set
    if valset_o in stat_data:
        cursor1.insertRow(row)
del row, cursorenter code here