topic Re: arcpy.da.UpdateCursor on joined tables? in Python Questions

arcpy.da.UpdateCursor on joined tables?

ErikMartin — Wed, 01 May 2013 13:05:21 GMT

Do data access update cursors work on joined tables? This thread indicates that Search Cursors do, but I keep getting a "cannot update join table" error. I have confirmed that I am trying to update the original table, not the joined table. Here's the pertinent code:

arcpy.MakeFeatureLayer_management(FC, "FC_lyr") FC_lyr = "FC_lyr" arcpy.AddJoin_management(FC_lyr, "joinField", joinedFCTable, "joinField")  fields =("{}.DataField".format(FC_lyr), "{}.OtherDataFiedl".format(joinedFCTable)) where = '"{}.OBJECTID" IS NOT NULL'.format(joinedFCTable)  with arcpy.da.UpdateCursor(FC_lyr, fields, where) as rows:     for row in rows:         row[0] = row[0] + row[1]

As an aside, I am trying to use the da.UpdateCursor because it is far faster than using field the calculator (code that I already have working) & I am trying to optimize speed.

Thanks,
-Erik

Re: arcpy.da.UpdateCursor on joined tables?

MathewCoyle — Wed, 01 May 2013 13:24:51 GMT

I've had nothing but problems trying to update fields in a joined table. I would recommend reading this post and examining the attached scripts for ideas on how to get around these issues.

http://forums.arcgis.com/threads/71572-Using-Python-Dictionaries-with-Update-Cursors-instead-of-quot-Join-and-Calculate-quot

I also have a post from a while back using similar methodology in a more specific case example.

http://forums.arcgis.com/threads/55099-Update-cursor-with-joined-tables-work-around-w-dictionaries

Re: arcpy.da.UpdateCursor on joined tables?

ChrisSnyder — Sat, 11 Dec 2021 09:11:14 GMT

Using a dictionary and an update cursor in tandem is the solution.... It's actually pretty straight forward:

A basic example in v10.1 syntax that "joins" two fields from the lutTbl to the targetTbl:

lutDict = dict([(r[0], (r[1], r[2])) for r in arcpy.da.SearchCursor(lutTbl, ["SOURCEJOINFIELD","SOURCEFIELD1","SOURCEFIELD2"])])
updateRows = arcpy.da.UpdateCursor(targetTbl, ["TARGETJOINFIELD","TARGETFIELD1","TARGFETFIELD2"])
for updateRow in updateRows:
    joinFieldValue = updateRow[0]
    updateRow[1] = lutDict[joinFieldValue][0]
    updateRow[2] = lutDict[joinFieldValue][1]
    updateRows.updateRow(updateRow)
del updateRow, updateRows

Re: arcpy.da.UpdateCursor on joined tables?

ErikMartin — Wed, 01 May 2013 15:32:58 GMT

Thank you both for your prompt responses. These examples are very helpful. I've been able to get this working to update field values using joined values, but my understanding of dictionaries is pretty rudimentary at this point... Do either of you have any hints or example of how to do some simple arithmetic? My two calculations are simply

updateValue = joinValue + updateValue

and

updateValue = joinValue * updateValue

I have yet to get the syntax right using the dictionary method. Thanks,
-Erik

Re: arcpy.da.UpdateCursor on joined tables?

ErikMartin — Wed, 01 May 2013 15:34:52 GMT

Thank you both for your prompt responses. These examples are very helpful. I've been able to get this working to update field values using joined values, but my understanding of dictionaries is pretty rudimentary at this point... Do either of you have any hints or example of how to do some simple arithmetic? My two calculations are simply

updateValue = joinValue + updateValue

and

updateValue = joinValue * updateValue

I have yet to get the syntax right using the dictionary method. Thanks,
-Erik

Would I make 2 dictionaries from 2 SearchCursors? One on the update data and a second on the joined data?

Re: arcpy.da.UpdateCursor on joined tables?

MathewCoyle — Wed, 01 May 2013 15:54:45 GMT

Would I make 2 dictionaries from 2 SearchCursors? One on the update data and a second on the joined data?

I would only look at creating a dictionary of your reference data. You can then cursor through your table you want to update referencing the key in your dictionary with the join field to access the dictionary value. If you don't have much experience with dictionaries I'm sure it is very confusing, but once you try it out they are quite simple.

Re: arcpy.da.UpdateCursor on joined tables?

ChrisSnyder — Sat, 11 Dec 2021 09:11:17 GMT

Not sure why you would need to make two dictionaries (maybe if you had two look up tables?).

Dictionaries are a great way to store tabular information... with the concept that you have a "key" field and then a bunch of other values that are associated with the key... sounds like a RDBMS table, right?

dict = {}
dict["cat"] = [1, "fish", 5.7654]
dict["dog"] = [3, "garbage", 15.5]

In this case the key is a string "cat", which is associated with a list of values (age, diet, and weight maybe).
Keys must be unique, but can be strings, integers, floats, or even "tuples" such as a value of (1,2). Tuple keys can be extreemly usefull.

You retreive values like this:

#get the diet of "cat"
>>> print dict["cat"][1]
'fish'

Dictionaries are great since they are EXTREEMLY fast to access, but a downside is that their size is limited by how much RAM you have.

Anyway to do math per my earlier example:

lutDict = dict([(r[0], (r[1], r[2])) for r in arcpy.da.SearchCursor(lutTbl, ["SOURCEJOINFIELD","SOURCEFIELD1","SOURCEFIELD2"])])
updateRows = arcpy.da.UpdateCursor(targetTbl, ["TARGETJOINFIELD","TARGETFIELD1","TARGFETFIELD2"])
for updateRow in updateRows:
    joinFieldValue = updateRow[0]
    updateRow[1] = updateRow[1] + lutDict[joinFieldValue][0] #assuming these values are numeric and not strings or anything!
    updateRow[2] = updateRow[2] * lutDict[joinFieldValue][1] #assuming these values are numeric and not strings or anything!
    updateRows.updateRow(updateRow)
del updateRow, updateRows

Re: arcpy.da.UpdateCursor on joined tables?

ErikMartin — Sat, 11 Dec 2021 09:11:20 GMT

Not sure why you would need to make two dictionaries (maybe if you had two look up tables?).

Dictionaries are a great way to store tabular information... with the concept that you have a "key" field and then a bunch of other values that are associated with the key... sounds like a RDBMS table, right?

dict = {}
dict["cat"] = [1, "fish", 5.7654]
dict["dog"] = [3, "garbage", 15.5]

In this case the key is a string "cat", which is associated with a list of values (age, diet, and weight maybe).
Keys must be unique, but can be strings, integers, floats, or even "tuples" such as a value of (1,2). Tuple keys can be extreemly usefull.

You retreive values like this:

#get the diet of "cat"
>>> print dict["cat"][1]
'fish'

Dictionaries are great since they are EXTREEMLY fast to access, but a downside is that their size is limited by how much RAM you have.

Anyway to do math per my earlier example:
lutDict = dict([(r[0], (r[1], r[2])) for r in arcpy.da.SearchCursor(lutTbl, ["SOURCEJOINFIELD","SOURCEFIELD1","SOURCEFIELD2"])])
updateRows = arcpy.da.UpdateCursor(targetTbl, ["TARGETJOINFIELD","TARGETFIELD1","TARGFETFIELD2"])
for updateRow in updateRows:
    joinFieldValue = updateRow[0]
    updateRow[1] = updateRow[1] + lutDict[joinFieldValue][0] #assuming these values are numeric and not strings or anything!
    updateRow[2] = updateRow[2] * lutDict[joinFieldValue][1] #assuming these values are numeric and not strings or anything!
    updateRows.updateRow(updateRow)
del updateRow, updateRows

This makes perfect sense... thanks to you both!
-Erik

Re: arcpy.da.UpdateCursor on joined tables?

ChrisSnyder — Sat, 11 Dec 2021 09:11:22 GMT

This makes perfect sense

Practice makes perfect... It took me a while to get proficient with dictonaries (aka "hash tables").

However, once I "got it"... one of the most powerfull tricks I have, and allows for some pretty cool stuff.

Some very basic usefull dictionary methods:

dict = {}
dict[1] = ("cat","Elvis")
dict[2] = ("dog","Barky")

>> len(dict) #how many keys?
2

>>> dict.keys() #dumps the keys out to a list
[2,1]

>>> "monkey" in dict #test for the existence of a certain key
False

#print each key and it's 2nd associated value in the dictionary (2nd value is index 1)
for key in dict:
    print str(key) + " - " + str(dict[key][1])

1 - Elvis
2 - Barky

Re: arcpy.da.UpdateCursor on joined tables?

ErikMartin — Sat, 11 Dec 2021 09:11:26 GMT

I'm having some trouble getting this to work. I'm getting a "'dict' object is not callable error when I use the simple code below:

lutTbl = "New_Shapefile2"
targetTbl = "New_Shapefile"

lutDict = dict([(r[0], (r[1], r[2])) for r in arcpy.da.SearchCursor(lutTbl, ["ID","value","value2"])])
updateRows = arcpy.da.UpdateCursor(targetTbl, ["ID","value","value2"])
for updateRow in updateRows:
    joinFieldValue = updateRow[0]
    updateRow[1] = updateRow[1] + lutDict[joinFieldValue][0] #assuming these values are numeric and not strings or anything!
    updateRow[2] = updateRow[2] * lutDict[joinFieldValue][1] #assuming these values are numeric and not strings or anything!
    updateRows.updateRow(updateRow)
del updateRow, updateRows

I've tried lots of combinations of square brackets and paraentheses, but to no avail so far... is there some simple syntax i'm missing?

When I use my actual code (which has far more fields, so I'll spare the confusion) I'm actually getting a "can't concatenate 'str' and 'int' objects error. This error does NOT have to do with my field type, but

Re: arcpy.da.UpdateCursor on joined tables?

ChrisSnyder — Sat, 11 Dec 2021 09:11:28 GMT

Hmmm.... I might have missed a bracket or something in my example.... Here's an "real" example that I know works:

slopeDict = dict([(r[0], (r[1], r[2], int(r[3] + .5))) for r in arcpy.da.SearchCursor(slopePctZoneStatTbl, ["VALUE","MIN","MAX","MEAN"])])

Another way to write this without so many confounded ([])()([[]]) symbols:

slopeDict = {}
searchRows = arcpy.da.SearchCursor(slopePctZoneStatTbl, ["VALUE","MIN","MAX","MEAN"])
for searchRow in searchRows:
    keyValue = searchRow[0]
    minValue = searchRow[1]
    maxValue = searchRow[2]
    meanValue = int(searchRow[3] + .5)
    slopeDict[keyValue] = (minValue, maxValue, meanValue)
del searchRow, searchRows

BTW: This assumes that your key values are all unique (I should have said that up front).

As for your concatenation issue, this illustration might help:

>>> print "hello" + 1
ERROR!
>>> print "hello" + str(1)
"hello1"
>>> print int("1") + float("1.1")
2.1
>>> print "hello" + "-" + str(1.1234)
hello-1.1234

Re: arcpy.da.UpdateCursor on joined tables?

ErikMartin — Wed, 01 May 2013 19:59:52 GMT

OK, thanks, I'll keep digging and will surely find the problem at some point. Thanks for posting the alternate syntax -- it is definitely much easier to read.

Thanks again,
-Erik

Re: arcpy.da.UpdateCursor on joined tables?

ErikMartin — Thu, 02 May 2013 11:26:07 GMT

So I'm using this code in a couple places in my script. I have it working successfully two of the times it is used, but two other times it is used, I keep getting an error. I have been through the code character by character and there are no syntax errors -- what works in one place doesn't work in the other place. The different instances of the code all use FCs that are derived from the same source FC -- a simple point layer (subsets are made from this source layer and I'm trying to "join" between these subsets)-- so all of the field types are identical between the occurrences where it is working and where it isn't working. The only difference I can find between the occasions where it is working and where it isn't working is that where it is working there are the same number of records in the update and join tables (one record in each table). Where it isn't working, the join table only has a few matches in the update table (so this would be equivalent to joining and field calculating only on the selected records where ("joinTable.OBJECTID" is not null).

The error I am given is the "cannot concatenate 'str' and 'int' objects". It is thrown whenever I try to access the join table dictionary with this line

updateRow[1] =  updateRow[1] + joinDict[joinFieldValue][0]

or, in fact, any time I try to use: joinDict[joinFieldValue][0]

so even this throws the same concatenation error:

arcpy.AddMessage(joinDict[joinFieldValue][0])

The joinFieldValue is successfully obtained from updateRow[0] and even if I hard code a joinvalue, like:

arcpy.AddMessage(joinDict[4220][0])

I get the same error

Do I need to add a conditional in somehow so that it only tries to access the dictionary if there is a match (is 'none' being returned and being treated as a string?) Is there something else I am missing? Again, I am confident there are no syntax errors and I am not trying to actually concatenate ints and strings.

Thanks for the continued support!
-Erik

Re: arcpy.da.UpdateCursor on joined tables?

MathewCoyle — Thu, 02 May 2013 13:56:34 GMT

Not sure why you are getting that issue. It may be a case of nulls coming across as an empty string. You can get around this by simply adding int() around each argument when you make your calculations. You could alternatively use float() if there are decimal numbers.

updateRow[1] =  int(updateRow[1]) + int(joinDict[joinFieldValue][0])

Re: arcpy.da.UpdateCursor on joined tables?

ErikMartin — Thu, 02 May 2013 14:25:19 GMT

Thanks, Matthew, but no luck. The problem occurs before I even try to make a calculation. So even just this line throws the str/int error:

arcpy.AddMessage(joinDict[joinFieldValue][0])

Adding in int here didn't help (nor would I expect it to).

arcpy.AddMessage(int(joinDict[joinFieldValue][0]))

Again, "joinFieldValue" is correctly populating with the join key (which happens to be an int).

I'll keep circling the problem... thanks for your help.

Re: arcpy.da.UpdateCursor on joined tables?

MathewCoyle — Thu, 02 May 2013 14:32:44 GMT

Can you print out a line of your dict showing the key and values?

Re: arcpy.da.UpdateCursor on joined tables?

ErikMartin — Thu, 02 May 2013 14:35:25 GMT

Here's a line of it:

{4931: (1466.31859047, 0.7298187853203999, 0.181307540288, 0.0, 0.0, 0.0, 0.0, 0.0, 1.45650000000222, 280800.0, 266454.070051103, 0, 3, 2524.95936472164, 312.0, 1303.0, 214200.0, 72900.0, 0)}

Re: arcpy.da.UpdateCursor on joined tables?

ErikMartin — Thu, 02 May 2013 14:43:08 GMT

Here's a line of it:

{4931: (1466.31859047, 0.7298187853203999, 0.181307540288, 0.0, 0.0, 0.0, 0.0, 0.0, 1.45650000000222, 280800.0, 266454.070051103, 0, 3, 2524.95936472164, 312.0, 1303.0, 214200.0, 72900.0, 0)}

Note that there is only ever 1 key/value pair in the "join" dictionary at a time. In one case where I'm having problems, there is only one record (of many) that it will join to in the "update" table. I still cannot get this working. Later, there is another instance where there are potentially >1 records that the single "join" key would join to. Will cross that bridge when I get to it...

Re: arcpy.da.UpdateCursor on joined tables?

MathewCoyle — Thu, 02 May 2013 14:49:49 GMT

You could try str() for your print messages though that shouldn't be an issue. Do you get this for any record in your dict or does it only pop up after a while?

Re: arcpy.da.UpdateCursor on joined tables?

ErikMartin — Thu, 02 May 2013 14:57:01 GMT

Every value in the dict throws the same error... can't get it to work.