How to update a hosted table in AGOL using Python

[Kara_Shindle]

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!

[Kara_Shindle · Accepted solution]

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]

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. 

[Anonymous User]

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]

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

[dmacq]

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. 

[MichelleMcGill]

Thank you @Kara_Shindle !!! You are my hero!  I, too, have struggled for several weeks to "update" a hosted feature table (no geographic data) to no avail but THIS solved my problem.  Somehow we must get this thread elevated as this resolved my issue on the first try.  Again THANK YOU! 

[MichelleMcGill]

Hosted Table (no features).  Sorry...brain is not fully functioning due to relief.

[GeofyAdmin]

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

[Anonymous User]

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