arcpy.mangement CalculateField in AGOL Notebook "Error in executing Tool"

[MattTarkington1]

Hi yall,

I'm just starting to learn about python and notebooks and I'm attempting to use a series of arcpy tools to  create a price calculation tool for some line layers. I won't go to far into the full details, but at the moment I'm having issue's with arcpy.management.CalculateField.

So we'll get to it. Here's my code (specifics removed to protect the innocent):

from arcgis.gis import GIS
gis = GIS("home")
from arcgis.features import FeatureLayer
import arcpy
#next cell
line_url = ("https://services8.arcgis.com/desired/line/layer/etc")
Line = FeatureLayer(line_url)
#next cell
arcpy.management.CalculateField(in_table=Line, field="$feature.Key", expression="$feature.Value1 + $feature.Value2", expression_type="ARCADE")[0]

And here is the error.

---------------------------------------------------------------------------
RuntimeError                              Traceback (most recent call last)
Input In [56], in <cell line: 9>()
      
----> 9 arcpy.management.CalculateField(in_table=Line, field="$feature.Key", expression="$feature.Value1 + $feature.Value2", expression_type="ARCADE")[0]

File /opt/conda/lib/python3.9/site-packages/arcpy/management.py:5788, in CalculateField(in_table, field, expression, expression_type, code_block, field_type, enforce_domains)
   5786     return retval
   5787 except Exception as e:
-> 5788     raise e

File /opt/conda/lib/python3.9/site-packages/arcpy/management.py:5785, in CalculateField(in_table, field, expression, expression_type, code_block, field_type, enforce_domains)
   5783 from arcpy.arcobjects.arcobjectconversion import convertArcObjectToPythonObject
   5784 try:
-> 5785     retval = convertArcObjectToPythonObject(gp.CalculateField_management(*gp_fixargs((in_table, field, expression, expression_type, code_block, field_type, enforce_domains), True)))
   5786     return retval
   5787 except Exception as e:

File /opt/conda/lib/python3.9/site-packages/arcpy/geoprocessing/_base.py:512, in Geoprocessor.__getattr__.<locals>.<lambda>(*args)
    510 val = getattr(self._gp, attr)
    511 if callable(val):
--> 512     return lambda *args: val(*gp_fixargs(args, True))
    513 else:
    514     return convertArcObjectToPythonObject(val)

RuntimeError: Object: Error in executing tool

 I'm thinking that it's an issue with how I pull the layer into the notebook, but have no idea what is actually happening.

Thanks in advance for your help!

[MobiusSnake · Accepted solution]

I haven't tried mixing the Python API's FeatureLayer type with the ArcPy CalculateField tool, but the FeatureLayer type has its own calculate() method that you might want to try.

[MattTarkington1]

Thank you for the help! It's very much appreciated.

[Clubdebambos · Accepted solution]

Hi @MattTarkington1 

You use the url for ArcPy tools when using feature service layers, in the above you are using the FeatureLayer object itself. Replace in_table = Line with in_table = line_url.

Im not sure why you have [0] at the end of ...

arcpy.management.CalculateField(in_table=Line, field="$feature.Key", expression="$feature.Value1 + $feature.Value2", expression_type="ARCADE")[0]

I also recommend using SQL as your expression type, it is the fast out of PYTHON3 and ARCADE. On large datasets SQL takes seconds to complete where the other two can take hours. Alternatively, as @MobiusSnake says, you can use the Feature Layers calculate() tool, this will also be faster than using ArcPy CalculateFields tool with PYTHON3/ARCADE expressions.

[MattTarkington1]

Thank you for the feedback! I hope you can geolocate your retirement soon!

[MattTarkington1]

So thanks to your advice, I was able to get arcpy to work. However, due to the advice given here, I've been trying to get the FeatureLayer.calculate to work and it gets stuck on the where="sql expression". I've tried a few different expressions (1 = 1, OBJECTID > 1, OBJECTID >= 1, Length > 1, etc) and everything returns the same error. It's not a very big data set. Only 179 rows.

See:

Code:

FuturePipe.calculate(where="1 = 1",
                     calc_expression={"field": "Key", "value": "str(Value1) + ' ' + str(Value2)"  } )

Error:

---------------------------------------------------------------------------
Exception                                 Traceback (most recent call last)
In  [88]:
Line 1:     FuturePipe.calculate(where="1 = 1",

File C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\arcgis\features\layer.py, in calculate:
Line 3556:  return self._con.post(

File C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\arcgis\gis\_impl\_con\_connection.py, in post:
Line 1528:  return self._handle_response(

File C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\arcgis\gis\_impl\_con\_connection.py, in _handle_response:
Line 1008:  self._handle_json_error(data["error"], errorcode)

File C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\arcgis\gis\_impl\_con\_connection.py, in _handle_json_error:
Line 1031:  raise Exception(errormessage)

Exception: 
'Invalid sql expression.' parameter is invalid
(Error Code: 400)

TIA!

PS. Just for fun (and to not bother you guys) I asked ChatGPT and it said that the where expression should be valid.

[Clubdebambos]

Hi @MattTarkington1 

The below workflow works for me.

from arcgis import GIS

## connect to AGOL
agol = GIS("home")

## access the feature service item
item = agol.content.get("FEATURE_SERVICE_ITEM_ID")

## get the layer of interest as a FeatureLayer
## the example here is the first layer in the Feature Service
fl = item.layers[0]

## use the calculate function to update a field
fl.calculate(where="OBJECTID >= 1", calc_expression={"field": "Key", "value" : "R1"})

Your problem is in  

"value": "str(Value1) + ' ' + str(Value2)"

Create the expression as below 

sql_exp = "{0} {1}".format(Value1, Value2)

FuturePipe.calculate(where="OBJECTID >= 1",
                     calc_expression={"field": "Key", "value": sql_exp})