I have a table(Table1) that i need to update on a regular basis by comparing/matching the records in the (Table1) to the table (Table2) based on field "Street" of Table1 and "FullStName" of table two. If theirs NOT a match it then updates the table1 based on the NOT matched from the table and I also need it to add "FullStName" have have "RES", "Reserved", "proposed" in the "status" field of table2. I need to add the missing names of tabel2 to table 1. Hopefully it make sense.
This is what i have but i am not sure i am doing it right...
import arcpy
from arcpy import env
arcpy.env.workspace = r"C:\Temp\Default.gdb\RoadNames"
Rtable = "table1"
table1 = "table2"
fcIncident = []
tableIncident = []
with arcpy.da.SearchCursor(Rtable, ["Street"]) as cursor:
for row in cursor:
fcIncident.append(row[0])
del row, cursor
with arcpy.da.SearchCursor(table1, ["FULLSTNAME", "STATUS"]) as cursor:
for row in cursor:
if row[1] in ("RES", "Reserved", "proposed"):
if not row[0] in fcIncident:
tableIncident.append(row[0])
del row, cursor
arcpy.MakeTableView_management(table1, "tblView")
for ID in tableIncident:
arcpy.SelectLayerByAttribute_management("tblView", "ADD_TO_SELECTION", "FULLSTNAME = " + str(ID))
Solved! Go to Solution.
First, set your arcpy.env.workspace to a database, not a feature class.
Try the code below, and edit line 3 & 15. If table1 has different field names than table2, simply create a new list variable, 'table2fields', and specify the required fields. Then, replace 'fields' with 'table2fields' on line 28. Please read more about InsertCursor and how it functions. The items in the 'ids' list must be indexed to the field lists in order for the values to be written to the proper fields.
import arcpy
arcpy.env.workspace = r'...path to database...'
table1 = 'table1'
table2 = 'table2'
table1List = []
with arcpy.da.SearchCursor(table1, ['Street']) as cursor:
for row in cursor:
table1List.append(row[0])
del cursor
#list of fields whose will need to be imported into table2
fields = ['FULLSTREET','Input_Other','Necessary_Fields','Here']
#list of values from table1 to inject into table2
ids = []
included = ["RES","Reserved","proposed"]
with arcpy.da.SearchCursor(table2, fields) as cursor:
for row in cursor:
if row[0] in included:
if row[0] not in table1List:
ids.append(row[0:])
del cursor
#create insert cursor variable
insertCursor = arcpy.da.InsertCursor(table2,fields)
#loop through items in ids list and insert into table
for i in ids:
insertCursor.insert(i)
del cursor
more to the question... did it do anything? errors?
My first thought would be to join table B to table A, run your query where they are not equal, then use a field calculate (Calculate Field) to update the table A values with them from table B
I get ERROR 000358: Invalid expression.
How do you query where they are not equal to a joined tables?
How do you update the table with only the missing names?
Try this:
EDIT: Now that I think about this more.. the below code will only select the in-memory layer by the streets that are not in table2. If you need to add the missing names to table two, like actually add the rows in the table, you'll need to use an InsertCursor—Data Access module | ArcGIS Desktop. Is this what you're trying to do?
import arcpy
arcpy.env.workspace = r'...path to database'
table1 = 'table1'
table2 = 'table2'
table1List = []
with arcpy.da.SearchCursor(table1, ['Street']) as cursor:
for row in cursor:
table1List.append(row[0])
del cursor
ids = []
included = ["RES","Reserved","proposed"]
with arcpy.da.SearchCursor(table2, ['FULLSTREET']) as cursor:
for row in cursor:
if row[0] in included:
if row[0] not in table1List:
ids.append(row[0])
del cursor
arcpy.MakeTableView_management(table1, "table1view")
qry = "{} IN ('{}')".format("FULLSTREET", "', '".join([x for x in ids]))
arcpy.SelectLayerByAttribute_management("table1view", "NEW_SELECTION",qry)
I tried your code and i get ERROR 000358: Invalid expression.
How would you get it to update table 1 with the missing names?
What exactly do you mean by "update table 1 with the missing names"? Do you mean insert the rows into the table? Or update the old street names with the new ones from table 2?
Insert the missing names(rows) from table 2 to table 1. Table 1 is the main table that needs to be updated.
First, set your arcpy.env.workspace to a database, not a feature class.
Try the code below, and edit line 3 & 15. If table1 has different field names than table2, simply create a new list variable, 'table2fields', and specify the required fields. Then, replace 'fields' with 'table2fields' on line 28. Please read more about InsertCursor and how it functions. The items in the 'ids' list must be indexed to the field lists in order for the values to be written to the proper fields.
import arcpy
arcpy.env.workspace = r'...path to database...'
table1 = 'table1'
table2 = 'table2'
table1List = []
with arcpy.da.SearchCursor(table1, ['Street']) as cursor:
for row in cursor:
table1List.append(row[0])
del cursor
#list of fields whose will need to be imported into table2
fields = ['FULLSTREET','Input_Other','Necessary_Fields','Here']
#list of values from table1 to inject into table2
ids = []
included = ["RES","Reserved","proposed"]
with arcpy.da.SearchCursor(table2, fields) as cursor:
for row in cursor:
if row[0] in included:
if row[0] not in table1List:
ids.append(row[0:])
del cursor
#create insert cursor variable
insertCursor = arcpy.da.InsertCursor(table2,fields)
#loop through items in ids list and insert into table
for i in ids:
insertCursor.insert(i)
del cursor
my apologies for not being clear and really appreciate you helping me with this. The code you posted looks to inject "RES","Reserved","proposed" fields into table 2, which is not what am after. i am needing to copy road names from table 2 field ( "FULLSTNAME") to table 1 field ( "Street") but only if they don't already exist in table 1. we can disregard the "RES","Reserved","proposed" i think its making it confusing.
Okay, then remove line 19. Remove line 22. Dedent ( CTRL + [ ) lines 23 and 24.