I'm trying to use the arcpy.da.SearchCursor and arcpy.da.InsertCursor objects to scan through a table with more than 1000 records and create multiple geodatabase tables with a maximum of 1000 records in each. These tables will be used to pass through to ESRI's geocoding service.
For example, I have a geodatabase table with 2500 rows. I need to break this table into 3 tables, two tables containing 1000 records and another table containing 500 records (this can be hard-coded, but I need it to be dynamic as it is part of a nightly script). Therefore, if I had 4030 records, I would need 5 tables, etc.
with arcpy.da.SearchCursor(well_address, "*") as cursor: tab_list =  #this list is used for a later process in the workflow rec_cnt = 0 #count the number of records table_count = 1 while rec_cnt != int(arcpy.GetCount_management(well_address).getOutput(0)): counter = 0 table = arcpy.CreateTable_management(final_gdb, "addrTab_" + str(table_count), template = well_address) insert = arcpy.da.InsertCursor(table, "*") for row in cursor: insert.insertRow(row) counter += 1 rec_cnt += 1 if counter > 999: table_count += 1 break else: print("Moving to next record for the table...") tab_list.append(table)
The problem with this script is when i execute it, it will populate the first 2 tables with 1000 records each, however the last table is empty...when I run arcpy.GetCount_management on the third table it says it has 500 records, but the geodatabase table is empty. If I execute the code snippet again (using a different starting number for the table_count variable) it will actually write the rows to the last table called.
For example, when I run the code the first time, I get two tables with 1000 records each and a third table that is empty. Then if I change the table_count variable to 10 and run the code again, table_3 fills with the 500 records from before and the tables 10 and 11 are populated each with 1000 records. However, table 12 is empty. This pattern continues on as I keep changing the table_count variable and executing. It's acting as if the insert statement is not commited but I'm using with, which should not cause any open cursor problems.