2

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.

  • Add table_count += 1 before you break when your count reaches 1000. That way, it will create the next table. – Fezter Aug 23 '18 at 4:26
  • Perhaps write to temp table in memory and dump it to disk table when get to 1000. Similar thing happened to me. – FelixIP Aug 23 '18 at 8:47
  • Deleting insert cursor might help as well – FelixIP Aug 23 '18 at 8:53
  • I changed the location of the table counter and that cleaned up the table_counter variable to be 3 instead of 4. Thanks Feztler! I tried to dump the results to disk, but when I did it created a blank table with 2 other tables with 1000 records each. – Robert Hadsell Aug 23 '18 at 14:07
1

As an alternative you can list OIDs, split list into chunks of 1000,1000,...,and whatever number is left, and use an SQL query to select and create tables using TableSelect_analysis:

import arcpy,os
input_table = r'C:\data.gdb\table'
output_workspace = r'C:\outdata.gdb'

def chunks(l, n): #https://stackoverflow.com/questions/312443/how-do-you-split-a-list-into-evenly-sized-chunks
    """Yield successive n-sized chunks from l."""
    for i in range(0, len(l), n):
        yield l[i:i + n]

oidfield = arcpy.Describe(input_table).OIDFieldName
all_oids = [i[0] for i in arcpy.da.SearchCursor(input_table,oidfield)]

count = 1
for chunk in chunks(all_oids,1000):
    sql = """{0} IN({1})""".format(arcpy.AddFieldDelimiters(input_table,oidfield),','.join([str(r) for r in chunk]))
    arcpy.TableSelect_analysis(input_table, os.path.join(output_workspace,"Table_{0}".format(count)), sql)
    count+=1

enter image description here

  • 1
    This is great idea! The only issue is I need the output to be a geodatabase table to pass to ESRI's gecoding service using the arcpy.GeocodeAddresses_geocoding tool. – Robert Hadsell Aug 23 '18 at 15:53
  • @RobertHadsell, Why is that an issue? (I have no experience of geocoding. ) – BERA Aug 23 '18 at 17:52
  • no worries! This method would work had I built the logic in the script to handle a list rather than a geodatabase table. However, using the arcpy.GeocodeAddresses_gecoding tool, the input parameter is a geodatabase table/table view. Therefore, I would have to write the lists to a geodatabase table in order to get the script to work. – Robert Hadsell Aug 23 '18 at 20:30
  • It is creating geodatabase tables – BERA Aug 24 '18 at 3:54
  • 1
    you're correct, this script does create geodatabase tables! I reran the code this morning and got the same result. Thanks for the help! – Robert Hadsell Aug 24 '18 at 13:59
0

I figured it out! The insert cursor was in-fact keeping a lock on the last set of records, so I changed the syntax such that the with statement would ensure there was no lock on the cursor. Once I made the change, it gave me three tables, one with 500 records and 2 with 1000 records each.

with arcpy.da.SearchCursor(well_address, "*") as cursor:
tab_list = [] #will store all tables in here to be merged later on in the script
rec_cnt = 0 #record counter for while loop condition...total will equal total number of records in the source table
table_count = 1 #counter used to alter the table name for each new table
while rec_cnt != int(arcpy.GetCount_management(well_address).getOutput(0)): #check to see if the record count is less than the number of records in the table
    counter = 0 #counter used to keep track of the number of records in each child table
    table = arcpy.CreateTable_management(final_gdb, "addrTab_" + str(table_count), template = well_address) #create the table to hold the records
    #insert = arcpy.da.InsertCursor(table, "*") #create insert cursor to insert the row from the original table into the newly created table
    with arcpy.da.InsertCursor(table, "*") as insert:
        for row in cursor:
            insert.insertRow(row)
            counter += 1
            rec_cnt += 1
            if counter > 999:
                table_count += 1 #increase the number for the table after exiting the for loop
                break
            else:
                print("Inserting record for table: " + str(table_count))
        tab_list.append(table)

Thank you all for the help, I hope this will be useful to someone else! These tables are useful for ESRI's geocoding service, as they only allow a maximum of 1000 records per table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.