How to update a hosted table in AGOL using Python

6715
8
Jump to solution
08-01-2019 08:55 AM
Kara_Shindle
Occasional Contributor III

I have been attempting to automate a process where I normally download CSVs from several different places, & manually overwrite the hosted tables in AGOL.  I'd like to automate this all in a script that I can schedule to run on a nightly basis.  (As background, the data feeds several metrics dashboards viewed by Admin.)

I have yet to get the hosted table to update in AGOL.  

1. Download updated CSV & Upload to Pandas dataframe

2. remove null rows & format date columns.

3. Export dataframe to new CSV.

4. Update hosted table in AGOL with new CSV.

When I view the hosted table, there are two different updated dates showing.  Picture attached.

The first shows the date I ran the script (today), but the second shows the date I manually overwrote / updated the table (yesterday).  

I had been following https://community.esri.com/thread/183390-arcgis-online-service-overwrite-using-python-api

below is what I have been using to update.

palTable = gis.content.get('28b7f819dce24647aebad88d04ff3815')  #This ets the hosted table in AGOL
palCSV = gis.content.get('65f1e32cda224199b6ec10c4139c2f1a')    #This gets the CSV in AGOL

palCSV.update({}, data=newCSV)  #This updates the CSV on AGOL
palTable.update({}, data=newCSV)  #This updates the hosted table on AGOL‍‍‍‍‍

Update the CSV & re downloading the file shows that it correctly updated itself.

Viewing the data on the hosted table shows that it has not updated itself.

Is there a way to "refresh" the table or a better syntax?

Thanks!

1 Solution

Accepted Solutions
Kara_Shindle
Occasional Contributor III

Figured it out after several frustrating weeks!  After a while, too much documentation is as bad as not enough...

Had to use the FeatureLayerCollection class from the arcgis Features module.  

Then used the manager helper object to overwrite my existing hosted table.

# this allows me to access the manager helper object to overwrite my currently existing hosted table (commRecTable)
from arcgis.features import FeatureLayerCollection
commRec_collection = FeatureLayerCollection.fromitem(commRecTable)

# This uses the manager helper object to overwrite my existing hosted table with the new CSV my script outputed (commRecCSV)
commRec_collection.manager.overwrite(commRecCSV)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

View solution in original post

8 Replies
Kara_Shindle
Occasional Contributor III

Figured it out after several frustrating weeks!  After a while, too much documentation is as bad as not enough...

Had to use the FeatureLayerCollection class from the arcgis Features module.  

Then used the manager helper object to overwrite my existing hosted table.

# this allows me to access the manager helper object to overwrite my currently existing hosted table (commRecTable)
from arcgis.features import FeatureLayerCollection
commRec_collection = FeatureLayerCollection.fromitem(commRecTable)

# This uses the manager helper object to overwrite my existing hosted table with the new CSV my script outputed (commRecCSV)
commRec_collection.manager.overwrite(commRecCSV)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
BrianKaplan
Occasional Contributor

Hi Kara,

I'm trying the same thing and I get a job failed error

I've logged in to the organizational account in the script where I'm an administrator and here is the rest of the code

 

from arcgis.features import FeatureLayerCollection

oldcsv = gis.content.get('ecc751eadabe43c083360c07bb7cghbc')

oldcsvFLC = FeatureLayerCollection.fromitem(oldcsv)

oldcsvFLC.manager.overwrite(r'C:\path\out4.csv')

 

And the response below.  This test was done on 40 records but I eventually want to use a dataset with >100,000

Any trick you are using to get this to work. 
I'm trying to overwrite a hosted table too.
Thanks
Brian
---------------------------------------------------------------------------Exception                                 Traceback (most recent call last)<ipython-input-51-7974c8cc0fe0> in <module>----> 1 oldcsvFLC.manager.overwrite(r'C:\path\out4.csv')~\AppData\Local\ESRI\conda\envs\arcgispro-py3-clone1\lib\site-packages\arcgis\features\managers.py in overwrite(self, data_file)   1324         #region Perform overwriting   1325         if related_data_item.update(item_properties=params, data=data_file):-> 1326             published_item = related_data_item.publish(publish_parameters, overwrite=True)   1327             if published_item is not None:   1328                 return {'success': True}~\AppData\Local\ESRI\conda\envs\arcgispro-py3-clone1\lib\site-packages\arcgis\gis\__init__.py in publish(self, publish_parameters, address_fields, output_type, overwrite, file_type, build_initial_cache)   9030             return Item(self._gis, ret[0]['serviceItemId'])   9031         else:-> 9032             serviceitem_id = self._check_publish_status(ret, folder)   9033         return Item(self._gis, serviceitem_id)   9034  ~\AppData\Local\ESRI\conda\envs\arcgispro-py3-clone1\lib\site-packages\arcgis\gis\__init__.py in _check_publish_status(self, ret, folder)   9257                     #print(str(job_response))   9258                     if job_response.get("status") in ("esriJobFailed","failed"):-> 9259                         raise Exception("Job failed.")   9260                     elif job_response.get("status") == "esriJobCancelled":   9261                         raise Exception("Job cancelled.")Exception: Job failed. 
0 Kudos
by Anonymous User
Not applicable

I found a solution.  If you add the csv file to AGOL without publishing it then publish it (in my case as a table without geocoding), the overwrite method works (oldcsvFLC.manager.overwrite(r'C:\path\out4.csv').    It is when I publish the table from ArcGIS Pro (presumably ArcMap too) that the overwrite method doesn't work.  I suspect it has something to do with a service definition file which I do not see if I publish from within AGOL.

Kara_Shindle
Occasional Contributor III

my apologies for not seeing your questions and responding, I just got back from maternity leave.  Glad you got it resolved!

0 Kudos
dmacq
by
New Contributor III

In AGOL on the table's Overview page, if you click to Update the table, does it only give you the option to Append? If so, the script will fail on overwrite, IME. My solution was to set a field in the hosted table as Unique. This adds the Overwrite option in AGOL, and allows the overwrite function to work. 

The problem I'm running into now is that as soon as I use the table in a hosted join layer, the unique option switches back to No and I get an error saying there are duplicates (there aren't) when I try to switch it back. 

0 Kudos
GeofyAdmin
New Contributor III

Using ArcGIS for Python API

#Get a feature service out of your hosted feature service item
fs = AGOLConnection.content.get('your feature service item id')

#Get the table you are interested in. Assuming first one
myTable = fs.ltabless[0]

#declare empty array to hold edits

fEdits = []

#Now iterate from source data/rows and fill an array with edits

for index, row in mySourceFeatureSet.iterrows():


fEdit = {
"attributes": {
"ObjectID": row.ObjectId,
"myField1": row.Field1,
"myField2": row.Field2
}
}

fEdits.append(fEdit)

#now apply adds. If you want Edits then use (updates=fEdits)
ret = myFeatureSet.edit_features(adds=fEdits)

#ideally you should inspect the 'ret' object because it will tell you if success / failure

by Anonymous User
Not applicable

Thank you very much.  This is very helpful.  I'll give it a try.

0 Kudos
dmacq
by
New Contributor III

This worked for my issue. Thanks!

0 Kudos