Select to view content in your preferred language

Having trouble with da.InsertCursor. Need help.

1598
1
Jump to solution
02-17-2016 08:03 AM
JohnLay
Frequent Contributor

I'm attempting to write a script that will extract specific records from another very large table (29 million records) so that I can run a QC on the extracted data.

My plan was to create a set object containing record ID’s I'm interested in. I  would then perform a da.SearchCursor to match each of the 29 million records to the values in my set object. Any matching row would then be copied into a new table with a da.InsertCursor instance.

It didn’t work and I have no idea why. I only have issues if I try to use a conditional statement to insert rows into a new table. Any other 'then' statement works just fine. Can someone help me figure out where my problem is? Or suggest a work around?

SUMMARY_Set = ([CID, or, a, bunch, of, Community, IDs]) #interested in the ‘CID’ field
FGDB = r'D:\~Working\Developement\RISK\QC_Test.gdb'
BIG_TABLE = r'Database Connections\NC_RISK.sde\NC_RISK.sql.L_DAMAGE_SUMMARY_JURISDICTION'

new_table = os.path.join(FGDB, "L_DAMAGE_SUMMARY_JURISDICTION")
arcpy.CreateTable_management(FGDB, "L_DAMAGE_SUMMARY_JURISDICTION", BIG_TABLE)

FieldNameList = []
fieldnames = arcpy.ListFields(BIG_TABLE)
for field in fieldnames:
    FieldNameList.append(field.name)

InsertTable = arcpy.da.InsertCursor(new_table, FieldNameList)
with arcpy.da.SearchCursor(BIG_TABLE, FieldNameList) as searchRows:
    for row in searchRows:
        if row[0] in SUMMARY_Set:
            InsertTable.insertRow(row)

I used the same code without the conditional statement and the rows copied into a new table just fine. it took a while, but all the records were there.

with arcpy.da.SearchCursor(SUMMARY_LAYERS, FieldNameList) as searchRows:
    for row in searchRows:
        InsertTable.insertRow(row)

I modified the code again to only print the matching rows instead of inserting them into a new table. It worked just like I had intended it to.

>>> FieldNameList.index("CID")
0
>>> with arcpy.da.SearchCursor(BIG_TABLE, FieldNameList) as searchRows:
...     for row in searchRows:
...         if row[0] in SUMMARY_Set:
...             print row[0] + " is in SUMMARY_Set"
...         
370042 is in SUMMARY_Set
370042 is in SUMMARY_Set
370042 is in SUMMARY_Set
370042 is in SUMMARY_Set
370042 is in SUMMARY_Set
0 Kudos
1 Solution

Accepted Solutions
JohnLay
Frequent Contributor

Figured it out.

New code:

with arcpy.da.SearchCursor(BIG_TABLE, FieldNameList) as searchRows:
    with arcpy.da.InsertCursor(new_table, FieldNameList) as InsertTable:
        for row in searchRows:
            if row[0] in SUMMARY_Set:
                InsertTable.insertRow(row)

View solution in original post

0 Kudos
1 Reply
JohnLay
Frequent Contributor

Figured it out.

New code:

with arcpy.da.SearchCursor(BIG_TABLE, FieldNameList) as searchRows:
    with arcpy.da.InsertCursor(new_table, FieldNameList) as InsertTable:
        for row in searchRows:
            if row[0] in SUMMARY_Set:
                InsertTable.insertRow(row)
0 Kudos