SQL syntax for Python API FeatureLayer.calculate

[Anonymous User]

I need to update the values of a column in an ArcGIS online feature service with the value of another row. See the table below where Length(ft) (data type short int) is the target column and the Shape__Length column (data type = double) is the source.

Since this will need to be done for thousands of records periodically I want to create a script that does this using the Python API for ArcGIS. It looks like the arcgis.features module has a FeatureLayer.calculate method that should do this.

To test it I ran the lines below which successfully updates the value of the Length (ft) column.

From arcgis import GIS, features

mygis = GIS (“arcgis.com”, username, password)

GMitem = mygis.content.get(itemid)     ###where item is a feature layer collection

test = GMitem.layers[9]                        ###get the 10^th layer in the feature layer collection (see attribute table above)

print (test.calculate(where= "1=1",calc_expression={"field": "Length", "value": "1"}))

However, when I alter the SQL to the below it throws an error saying it can’t convert “Shape__Length” to a small int so I know it’s reading my column name as text.

print (test.calculate(where= "1=1",calc_expression={"field": "Length", "value": "Shape__Length"}))

What is the correct syntax to get a field to accept the values of another field using SQL expressions in the Python API??

[DanPatterson_Retired]

Sounds like your Length fields was defined as a small integer, whereas the shapefield and hence shape_length would be a double type.  Try making a new input field of the correct type since the shape_length is probably too big and/or of the wrong format to fit into a small integer field.

[Anonymous User]

I created a new field with type double and tried but got the same result. It seems like a syntax problem where "Shape__Length" is being recognized as a nvarchar (text) rather the field name from which I want to copy values.

[DanPatterson_Retired]

I thought the double underscore __ was a typo before, so it isn't a real Shape_length as one would expect in a featureclass in a gdb then?

[Anonymous User]

Not a typo. It is the real shape length that is usually found in a geodatabase feature class. If you open the attached image you will see a snapshot of the attribute table with the field names.

[JoshuaBixby · Accepted solution]

The ArcGIS API for Python is mostly a Python wrapper for the ArcGIS REST API.  See Calculate (Feature Service/Layer)—ArcGIS REST API: Services Directory | ArcGIS for Developers 

print (test.calculate(where= "1=1",calc_expression={"field": "Length", "sqlExpression": "Shape__Length"}))‍

[Anonymous User]

Thanks, that is the documentation I hoping existed. It worked.

[JoshuaBixby]

If your question has been answered, or mostly answered, please mark one of the responses correct to close out the question.  Thanks.

[Anonymous User]

Edit - See Joshua's answer. That is the doc page I was looking for. This still works though.

You are correct in saying that it is reading the field name as a string rather than a 'field name'. So, it is trying to put that string for all the rows which it cannot do because it is an int type. Unfortunately the documentation is not great here. The docs tell us to 'See Calculate a field for more information on supported expressions' with no link to where that is! Here is my workaround though:

GMitem = mygis.content.get(itemid)
data = GMitem.layers[9].query() # Get the feature collection
df = data.df # Put it into a pandas dataframe
df['field1'] = df['field2'] # Calculate the field and apply updates in next line
GMitem.layers[9].edit_features(updates=arcgis.features.FeatureSet.from_dataframe(df))‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

If this succeeded you should see something like this returned:

{'addResults': [], 'deleteResults': [], 'updateResults': [{'globalId': 'cbb4d354-2e16-4d05-8943-f80280643188', 'objectId': 1, 'success': True},...

You can double check this by re-querying the layer and displaying it as a dataframe and checking the column, or by accessing the AGO item with a browser refresh. This is a few extra lines of code but working with pandas is fast and powerful! Hope this helps! Also, I used Jupyter Notebooks for this and that is where I tested this code.