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
Solved! Go to Solution.
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)
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)