topic Update Cursor and Joined Tables in Python Questions

Update Cursor and Joined Tables

MikeMacRae — Tue, 28 Feb 2012 21:01:18 GMT

Is it possible to access joined tables using arcpy.UpdateCursor? I have no problems access a joined table with arcpy.SearchCursor, but when I attempt it with Update, it errors out.

It errors on the arcpy.UpdateCursor(soil) I think because the update cursor doesn't like feature layers, but I change it to the feature class SOIL, the 2 str(row.getValue... syntaxes won't read the field names....I'm stumped.

import arcpy
from arcpy import env

env.workspace = r"Z:\Test.gdb"

arcpy.SpatialJoin_analysis("SOIL", "TERRAIN", "SPATIAL")

soil = "\"soil\""

arcpy.MakeFeatureLayer_management("SO_SOIL_P", soil)

arcpy.AddJoin_management(soil, "SITE_ID", "SPATIAL", "SITE_ID")

rows = arcpy.UpdateCursor(soil)

for row in rows:
        print str(row.getValue("SOIL.SITE_ID"))
        print str(row.getValue("SPATIAL.TSSD"))

Re: Update Cursor and Joined Tables

MikeMacRae — Tue, 28 Feb 2012 21:54:06 GMT

I found an older thread. It looks like this is still a known issue:

http://forums.arcgis.com/threads/9507-Update-cursor-on-joined-tables

Re: Update Cursor and Joined Tables

DouglasHall — Tue, 19 Sep 2017 21:41:21 GMT

Unfortunately, join and update cursor won't work. I wrote the following function to get around AddJoin_management and CalculateField_management. CalculateField_management kept failing on 100k record datasets.

def JoinAndUpdateField(fcTblPrimary, strJoinFldPrimary, fcTblForeign, strJoinFldForeign, strUpdateFldPrimary, strUpdateFldForeign, methodToProcess=None):
"""
Joins two tables or feature classes or combination and updates one field from another with optional passed method to update the value further.

The same result can be gotten with arcpy AddJoin_management and CalculateField_management, but CalculateField_management kept failing on 100k record datasets.

Parameters
----------
fcTblPrimary : str
Full path to a fc or table
e.g. c:/proj/data.gdb/datasetA or c:/proj/USER@VECTOR.sde/datasetB or Database Connections/USER@VECTOR.sde/datasetC
strJoinFldPrimary : str
Field of primary table to join on
fcTblForeign : str
Full path to a fc or table to join to
strJoinFldForeign : str
Field of secondary table to join on
strUpdateFldPrimary : str
Field to update
strUpdateFldForeign : str
Field to get value from for the update
methodToProcess : method
Optional Method to process the strUpdateFldForeign field before updating
"""
dictValues = dict ([(key, val) for key, val in arcpy.da.SearchCursor (fcTblForeign, [strJoinFldForeign, strUpdateFldForeign])])
boolUseMethod = False
if methodToProcess:
boolUseMethod = True
with arcpy.da.UpdateCursor(fcTblPrimary, [strJoinFldPrimary,strUpdateFldPrimary]) as cursor:
for row in cursor:
try:
if boolUseMethod:
row[1] = methodToProcess(dictValues[row[0]])
else:
row[1] = dictValues[row[0]]
except KeyError:
pass
cursor.updateRow(row)
del cursor
del dictValues