topic Re: update attribute table from another table based on a query in Python Questions

update attribute table from another table based on a query

Henryobaseki — Thu, 16 Nov 2017 08:55:26 GMT

Here is my script

import arcpy
... from arcpy import env
... env.workspace = "C:\\Data"
... print 'Processing...'
... fc = "Updated_subset.dbf"
... cursor = arcpy.da.SearchCursor(fc, ["OID"])
... for row in cursor:
...     SQL_stat= "OID = "+ str(row[0])
...     fc2 = "airports_old.dbf"
...     cursor2 = arcpy.da.SearchCursor(fc, ["STATE"], SQL_stat)
...     for row2 in cursor2:
...         UpdatedValue = row2[0]
...         cursor3 = arcpy.da.UpdateCursor(fc2, ["STATE"],SQL_stat)
...         for row3 in cursor3:
...             row3[0] = UpdatedValue
...             cursor3.updateRow(row3)
... del row
... del cursor
... del row2
... del cursor2
... del row3
... del cursor3
...
... print "Done"

At the moment the script below the OID is coneverted to string

SQL_stat= "OID = "+ str(row[0])

How do I rewrite the SQL stat if the OID Field is already a string, hence, don't need to convert to string

Thanks for your help!

Re: update attribute table from another table based on a query

DanPatterson_Retired — Sat, 11 Dec 2021 18:47:26 GMT

python parser... not sure about how sql handles them but print statements use \ as an literal escape character.

row0 = 'hello'

sql = '"{}" = \'{}\''.format("field", row0)
print(sql)

"field" = 'hello'‍‍‍‍‍‍

Re: update attribute table from another table based on a query

Henryobaseki — Thu, 16 Nov 2017 15:26:49 GMT

Hi Dan,

so how do I add this script to the above one.

Thanks for your help

Re: update attribute table from another table based on a query

DanPatterson_Retired — Thu, 16 Nov 2017 15:34:54 GMT

you replace/modify your SQL_stat = ... line

replace "field" with "OID" and row0 with row[0]

Re: update attribute table from another table based on a query

Henryobaseki — Thu, 16 Nov 2017 15:57:13 GMT

import arcpy
... from arcpy import env
... env.workspace = "C:\Users\L0505857\Data"
... print 'Processing...'
... fc = "Updated_subset.dbf"
... cursor = arcpy.da.SearchCursor(fc, ["OID"])
... for row in cursor:
...     SQL_stat= '"{}" = \'{}\''.format("field", row0)
...     fc2 = "airports_old.dbf"
...     cursor2 = arcpy.da.SearchCursor(fc, ["STATE"], SQL_stat)
...     for row2 in cursor2:
...         UpdatedValue = row2[0]
...         cursor3 = arcpy.da.UpdateCursor(fc2, ["STATE"],SQL_stat)
...         for row3 in cursor3:
...             row3[0] = UpdatedValue
...             cursor3.updateRow(row3)
... del row
... del cursor
... del row2
... del cursor2
... del row3
... del cursor3
...
... print "Done"

Re: update attribute table from another table based on a query

RebeccaStrauch__GISP — Thu, 16 Nov 2017 16:02:46 GMT

Henry, it helps if you post you code using tips /blogs/dan_patterson/2016/08/14/script-formatting?sr=search&searchId=75ece207-9a50-4ffd-8c5e-182bd0b476f8&searchIndex=1‌

This helps everyone to refer to the same line number, and also to see if the indentation is correct in your script. You can edit your post and the above comment to replace the code.

Re: update attribute table from another table based on a query

DanPatterson_Retired — Thu, 16 Nov 2017 16:07:56 GMT

row[0]

note the slicing, since the first, and only entry in the row object is the value for the oid field. Things in python are 0 based when counting

Re: update attribute table from another table based on a query

Henryobaseki — Thu, 16 Nov 2017 16:52:41 GMT

Hi Dan,

I have Table 1 with fields - Top, Base, Names, and State all have contents

I have Table 2 with fields - similar contents Top, Base, Names, and State, but the state needs to be updated with the state content in table 1

A script to update state in table 2 if Top, Base and Names Match with table 1 if no match do something

not sure if the sql statement above will help

Re: update attribute table from another table based on a query

DanPatterson_Retired — Thu, 16 Nov 2017 16:57:53 GMT

not sure what the script is doing, partly because of the format. I was just trying to point out a possible solution to the question you posted in the last lines. If the script is functioning as designed, could you provide a listing of some input data and an expected output as example

Re: update attribute table from another table based on a query

Henryobaseki — Thu, 16 Nov 2017 17:21:39 GMT

From the script below

can my sql statement be to update State in fc2 = "airports_old.dbf" if Field 1 and Field 2 in ... fc = "Updated_subset.dbf" matches withField 1 and Field 2 in airports_old..dbf"

import arcpy
... from arcpy import env
... env.workspace = "C:\\Data"
... print 'Processing...'
... fc = "Updated_subset.dbf"
... cursor = arcpy.da.SearchCursor(fc, ["OID"])

... for row in cursor:
...     SQL_stat= "OID = "+ str(row[0])
...     fc2 = "airports_old.dbf"
...     cursor2 = arcpy.da.SearchCursor(fc, ["STATE"], SQL_stat)
...     for row2 in cursor2:
...         UpdatedValue = row2[0]
...         cursor3 = arcpy.da.UpdateCursor(fc2, ["STATE"],SQL_stat)
...         for row3 in cursor3:
...             row3[0] = UpdatedValue
...             cursor3.updateRow(row3)
... del row
... del cursor
... del row2
... del cursor2
... del row3
... del cursor3
...

Re: update attribute table from another table based on a query

XanderBakker — Sat, 11 Dec 2021 18:47:29 GMT

From the code you posted it looks like you are trying to do something like this:

import arcpy
from arcpy import env
env.workspace = r"C:\Data"

tbl1 = "Updated_subset.dbf"
tbl2 = "airports_old.dbf"

fld_oid = "OID"
fld_state = "STATE"

dct = {r[0]: r[1] for r in arcpy.da.SearchCursor(tbl1, (fld_oid, fld_state))}

with arcpy.da.UpdateCursor(tbl2, (fld_oid, fld_state)) as curs:
    for row in curs:
        oid = row[0]
        state = row[1]
        if oid in dct:
            row[1] = dct[oid]
            curs.updateRow(row)

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

However, it doesn't make much sense to me. The OID in both tables correspond and using the OID you want to update the State in tbl2 with the one in tbl1?

Re: update attribute table from another table based on a query

Henryobaseki — Mon, 20 Nov 2017 11:08:50 GMT

Hi Xander,

I keep getting runtime errors

>>> import arcpy

... from arcpy import env

... env.workspace = r"C:\Users\L0505857\Data"

...

... tbl1 = "Updated_subset.dbf"

... tbl2 = "airports_old.dbf"

...

... fld_oid = "OID"

... fld_state = "state"

...

... dct = {r[0]: r[1] for r in arcpy.da.SearchCursor(tbl1, (fld_oid, fld_state))}

...

... with arcpy.da.UpdateCursor(tbl2, (fld_oid, fld_state)) as curs:

... for row in curs:

... oid = row[0]

... state = row[1]

... if oid in dct:

... row[1] = dct[oid]

... curs.updateRow(row)

...

Runtime error

Traceback (most recent call last):

File "<string>", line 11, in <module>

File "<string>", line 11, in <dictcomp>

RuntimeError: A column was specified that does not exist.

>>>

Re: update attribute table from another table based on a query

XanderBakker — Mon, 20 Nov 2017 11:35:23 GMT

This error indicates that either "OID" or "state" is not a field that you have in your table "Updated_subset.dbf". Can you check?

Re: update attribute table from another table based on a query

Henryobaseki — Mon, 20 Nov 2017 11:48:08 GMT

The two tables have state and OID RESID attributes

I want to use the state field attributes in Updated-subset.dbf to update the state field attribute in airport.dbf which is currently empty

I want the update to occur if the OID and the RESID in both tables match.

I m still getting the same runtime error

Thanks for your help

Re: update attribute table from another table based on a query

XanderBakker — Sat, 11 Dec 2021 18:47:32 GMT

Strange. Can you run the snippet below and post back what it prints?

import arcpy
tbl1 = r"C:\Users\L0505857\Data\Updated_subset.dbf"
for fld in arcpy.ListFields(tbl1):
    print(fld.name)‍‍‍‍

Re: update attribute table from another table based on a query

Henryobaseki — Mon, 20 Nov 2017 12:14:12 GMT

OID

OBJECTID

FIELD_NAME

KEY_WELL

WATER_DEPT

BASIN_NAME

TOT_RECOVE

HC_TYPE

OVP_bar

Res_Age

Formation

Lithology

RESID

Regional_s

>>>

Re: update attribute table from another table based on a query

Henryobaseki — Mon, 20 Nov 2017 12:23:12 GMT

Thanks Xander, I was looking at the wrong table.

Code works!

Re: update attribute table from another table based on a query

XanderBakker — Sat, 11 Dec 2021 18:47:34 GMT

There you go, there is no "state" field in the table. This is what causes the error on line 11:

dct = {r[0]: r[1] for r in arcpy.da.SearchCursor(tbl1, (fld_oid, fld_state))}‍

On this line, a dictionary is used reading the state field, but if it doesn't exist, it will return an error.

You also mention a field called "RESID" that should be used in the code, but it is not referenced anywhere in the code. Do I understand it correctly that the field "RESID" from Updated_subset.dbf should match the field "OID" from airports_old.dbf?

Can you also list the field from table airports_old.dbf with this snippet and post these:

import arcpy
tbl2 = r"C:\Users\L0505857\Data\airports_old.dbf"
for fld in arcpy.ListFields(tbl2):
    print fld.name‍‍‍‍

And please explain what field in which table needs to be updated by what information when which condition is met. In case a field should be added before the update, please indicate that too.

Re: update attribute table from another table based on a query

XanderBakker — Mon, 20 Nov 2017 12:24:59 GMT

OK, great, then ignore the additional questions I just posted.

Re: update attribute table from another table based on a query

Henryobaseki — Mon, 20 Nov 2017 12:37:36 GMT

If I want to add another field RESID to the search cursor

I ' m stuck at dct

see below ......

import arcpy
from arcpy import env
env.workspace = r"C:\Users\L0505857\Data"
tbl1 = "Updated_subset.dbf"
tbl2 = "airports_old.dbf"
fld_oid = "OID"
fld_FIELD_NAME = "FIELD_NAME"
fld_RESID = "RESID"
dct = {r[0]: r[1] for r in arcpy.da.SearchCursor(tbl1, (fld_oid, fld_FIELD_NAME,fld_RESID))}
with arcpy.da.UpdateCursor(tbl2, (fld_oid, fld_FIELD_NAME,fld_RESID)) as curs:
    for row in curs:
        oid = row[0]
        FIELD_NAME = row[1]
        if oid in dct:
            row[1] = dct[oid]
            curs.updateRow(row)