UpdateCursor With a Hosted Table

258
7
Jump to solution
07-08-2020 07:59 AM
JaredPilbeam2
MVP Regular Contributor

I'm trying to populate the 'Description' field in the Recycling Locations View layer (a hosted Feature Layer) with the text from the 'Description' field in the Green Descriptions layer (a hosted Table). The 'Category' field from both have the same values.

The script I have uses the UpdateCursor to populate a field based on another field from the same Feature Layer. I think I can create a list with the text from the 'Description' field, but that seems redundant seeing as though there is probably a way to bring it in straight from the hosted table?

import arcpy, requests, json

#disable warnings
requests.packages.urllib3.disable_warnings()

#Recycling Locations Feature layer
fc = survey_item

#text to populate the Description field of the Recycling Locations layer
descriptions = ['Refrigerators, stoves, dishwashers, water heaters and other large appliances are considered white goods and are banned from landfills. Hazardous components such as mercury, CFCs and PCBs are dangerous and require proper handling and disposal. When purchasing an appliance, request the company to take your “old” appliance or contact your city/village. A list of certified Freon removal specialists is not currently available. The U.S. EPA administers a program to certify technicans. Their requirements can be found at http://www.epa.gov/Ozone/title6/608/608fact.html#techcert',
'ASBESTOS - Asbestos is a naturally occurring mineral found in certain rocks. Asbestos was commonly used in home building materials before the mid-1970s and occasionally until the late 1980s because it is strong, fire- and corrosion-resistant and a good insulator. If asbestos is in good condition and left in place, it should not present health risks. However, if a building is going to be demolished, renovated, or remodeled, care should be taken to prevent the release of asbestos fibers into the air. Inhalation of microscopic asbestos fibers can cause health risks. You may search for Licensed Asbestos Professionals in the Yellow Pages or call the Illinois Dept. of Public Health.',
'In most cases older vehicles are traded-in or brought to an auto salvage yard; or you may choose to donate them for parts reuse and recycling. Contact the following organizations to make arrangements, get paid or ask about tax deduction information.',
... etc.
]

#fields from the Recycling Locations layer
fields = ['Description', 'Category']

#use the updatecursor to populate the Description field from the descriptions list
with arcpy.da.UpdateCursor(fc, fields) as cursor:
for row in cursor:
if row[1] == 'Appliance Recycling':
row[0] = descriptions[0]
elif row[1] == 'Asbestos Info / Removal Service':
row[0] = descriptions[1]
elif row[1] == 'Automobile & Boat Reuse Recycling':
row[0] = descriptions[2]
cursor.updateRow(row)
del cursor‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
Reply
0 Kudos
1 Solution

Accepted Solutions
JoshuaSharp-Heward
Occasional Contributor

Hi Jared,

Glad to hear that worked out! Just to clarify here, Arcpy and the ArcGIS API for Python are two separate things. The script you posted above is using the Arcpy python package, whereas the ArcGIS API for Python is imported using

import arcgis

and is used for automating things in AGOL/Portal.

So if I'm understanding correctly you want to take the categories and descriptions straight from the table web service, then use that in your update cursor? If so, see the code below for an example of this, running over the table first to make a dictionary of category:description pairs, then updating the features with those values. Also I stand corrected about the cursors running over services.

import arcpy, requests, json

# disable warnings
requests.packages.urllib3.disable_warnings()

# Recycling Locations Feature layer
fc = survey_item
table = table_url

# fields from the Recycling Locations layer and Green Description
fields = ['Description', 'Category']

# make a dictionary of the categories and descriptions
description_dict = {}

with arcpy.da.SearchCursor(table, fields) as cursor:
for row in cursor:
description_dict[row[1]] = row[0]
# makes a structure like {"Appliance Recycling": "blahblahblah"}

# use the updatecursor to populate the Description field from the descriptions list
with arcpy.da.UpdateCursor(fc, fields) as cursor:
for row in cursor:
# for each feature, looks up description from the description_dict, using the category as the key
row[0] = description_dict[row[1]]
cursor.updateRow(row)

View solution in original post

7 Replies
JoshuaSharp-Heward
Occasional Contributor

Hi,

If this is going to be a one-off operation you might want to consider just adding both layer to a map in ArcGIS Pro, joining the Green Descriptions table to the Recycling Locations View layer on "Category", then just using the field calculator to copy the description field from the joined table to the feature layer.

If you do need to run it frequently, and you want to be able to automate this let me know. As far as I understand from the cursor documentation you can't run a cursor over a web service, but there is an analogous workflow using the ArcGIS API for Python, and I could probably dig up a script you could adapt to your needs.

JaredPilbeam2
MVP Regular Contributor

Hi Joshua,

I did try the join and then the field calculator in Pro before this, and for some reason it didn't work. Now I tried again and it did work! Thanks for the idea.

It now seems like overkill to go the automation route, but yes, I am still curious how it can be done. I know you can use a cursor on a feature service using the Python API, as I have used that very script (above) before. That's why I'm posting in this group.

Reply
0 Kudos
JoshuaSharp-Heward
Occasional Contributor

Hi Jared,

Glad to hear that worked out! Just to clarify here, Arcpy and the ArcGIS API for Python are two separate things. The script you posted above is using the Arcpy python package, whereas the ArcGIS API for Python is imported using

import arcgis

and is used for automating things in AGOL/Portal.

So if I'm understanding correctly you want to take the categories and descriptions straight from the table web service, then use that in your update cursor? If so, see the code below for an example of this, running over the table first to make a dictionary of category:description pairs, then updating the features with those values. Also I stand corrected about the cursors running over services.

import arcpy, requests, json

# disable warnings
requests.packages.urllib3.disable_warnings()

# Recycling Locations Feature layer
fc = survey_item
table = table_url

# fields from the Recycling Locations layer and Green Description
fields = ['Description', 'Category']

# make a dictionary of the categories and descriptions
description_dict = {}

with arcpy.da.SearchCursor(table, fields) as cursor:
for row in cursor:
description_dict[row[1]] = row[0]
# makes a structure like {"Appliance Recycling": "blahblahblah"}

# use the updatecursor to populate the Description field from the descriptions list
with arcpy.da.UpdateCursor(fc, fields) as cursor:
for row in cursor:
# for each feature, looks up description from the description_dict, using the category as the key
row[0] = description_dict[row[1]]
cursor.updateRow(row)

View solution in original post

JaredPilbeam2
MVP Regular Contributor

Joshua,

That looks like exactly what I was thinking. Making a dictionary of the categories and descriptions and then using the category as the key was 'key'. I ran into a little problem, though, that I'm currently looking into. The SearchCursor is not accepting the in_table parameter. What I thought was a table is actually a 'Feature Layer Collection', which is what is causing the error I believe.

Overview of Green Descriptions Table (created from a CSV).

Jupyter Notebook calls the Table a 'Feature Layer Collection' I found out.

Script with error.

I started a new thread about this: 'in_table' is not a table or a featureclass 

Reply
0 Kudos
JoshuaSharp-Heward
Occasional Contributor

Hi Jared,

Try passing the table's rest url into the table parameter, instead of the item. It should look something like "https://<catalog-url>/<serviceName>/FeatureServer/0" with 0 indicating the index of the layer (if there's only one layer in the service, it will be 0).

Reply
0 Kudos
JaredPilbeam2
MVP Regular Contributor

That worked. Thanks a lot. The script worked until it ran into a KeyError. I think that may have to do with the fact that the 'Compost Facilities/Landscape Waste Transfer Stations' field is NULL. I'll fix that and try again. In the meantime i'll mark your answer correct!

---------------------------------------------------------------------------
KeyError Traceback (most recent call last)
<ipython-input-32-9dbca838b891> in <module>
23 for row in cursor:
24 # for each feature, looks up description from the description_dict, using the category as the key
---> 25 row[0] = description_dict[row[1]]
26 cursor.updateRow(row)

KeyError: 'Compost Facilities/Landscape Waste Transfer Stations'‍‍‍‍‍‍‍‍‍
Reply
0 Kudos
JoshuaSharp-Heward
Occasional Contributor

Parfait! I should have added an if statement to check whether that category is in the dictionary anyway, see line 3 below.

with arcpy.da.UpdateCursor(fc, fields) as cursor:
for row in cursor:
if row[1] in description_dict:
row[0] = description_dict[row[1]]
cursor.updateRow(row)‍‍‍‍‍

Then it should just skip over those values!