topic Re: Update table attributes from another table in Python Questions

Update table attributes from another table

CCWeedcontrol — Sun, 12 Dec 2021 04:33:31 GMT

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)) 


‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Update table attributes from another table

DanPatterson_Retired — Tue, 18 Jul 2017 22:09:10 GMT

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

Re: Update table attributes from another table

MitchHolley1 — Sun, 12 Dec 2021 04:33:33 GMT

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)

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Update table attributes from another table

CCWeedcontrol — Wed, 19 Jul 2017 15:19:50 GMT

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?

Re: Update table attributes from another table

CCWeedcontrol — Wed, 19 Jul 2017 15:22:16 GMT

I tried your code and i get ERROR 000358: Invalid expression.

How would you get it to update table 1 with the missing names?

Re: Update table attributes from another table

MitchHolley1 — Wed, 19 Jul 2017 20:49:18 GMT

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?

Re: Update table attributes from another table

CCWeedcontrol — Wed, 19 Jul 2017 21:22:44 GMT

Insert the missing names(rows) from table 2 to table 1. Table 1 is the main table that needs to be updated.

Re: Update table attributes from another table

MitchHolley1 — Sun, 12 Dec 2021 04:33:36 GMT

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‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Update table attributes from another table

CCWeedcontrol — Thu, 20 Jul 2017 15:03:56 GMT

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.

Re: Update table attributes from another table

MitchHolley1 — Thu, 20 Jul 2017 15:54:11 GMT

Okay, then remove line 19. Remove line 22. Dedent ( CTRL + [ ) lines 23 and 24.

Re: Update table attributes from another table

CCWeedcontrol — Sun, 12 Dec 2021 04:33:39 GMT

Shouldn't the insertCursor be on Table1 since that is the table i am needing to update?

I am getting an error.

insertCursor.insert(i)
AttributeError: 'da.InsertCursor' object has no attribute 'insert'

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 = ['FULLSTNAME']

#list of values from table1 to inject into table2
ids = []

with arcpy.da.SearchCursor(table2, ['FULLSTNAME']) as cursor:
    for row in cursor:
        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 insertCursor
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Update table attributes from another table

MitchHolley1 — Thu, 20 Jul 2017 18:01:34 GMT

Yes, you're correct. Change 'table1' to 'table2' in line 19.

Oops... regarding the error: change 'insertCursor.insert(i)' in line 23 to 'insertCursor.insertRow(i)'.

Re: Update table attributes from another table

CCWeedcontrol — Thu, 20 Jul 2017 18:51:04 GMT

Do you mean change 'table2' to 'table1' in line 20?

I have inserted the "Row" in insertCursor.insertRow(i)