Overwrite Hosted Table with new data

[ChrisMalam]

Hi,

I am having a challenging time trying to write a code using the ArcGIS Python API in Notebooks to update or overwrite an existing hosted table with new data obtained via a Survey123 form. The ultimate goal will be to set this to update automatically at regular intervals.

I have tried to resolve using similar previous posts (e.g. Overwrite Hosted Table), but to no avail.

My initial steps were to run my analysis code and add the output table as a CSV to my organisational AGOL account, which I then manually published as a Hosted Table. However, when I run the below steps, the output messages appears to be fine, but when I go to check the hosted table data in my 'Content', it has not updated. Code is in the image, below:

As additional context:

• Given my analysis is all scripted, the hosted table name, properties and schema are remain the same.
• Code runs ALL Survey123 data each time, rather than just those new records submitted since last run (there's are <1000 records).

Any assistance would be much appreciated as I am still relatively new to Notebooks and python language. Happy to include additional information if needed.

Thanks!!

[KevinMacLeodCAI]

Hopefully this will get smoother but for now there seem to be various issues with replacing an item. However, an alternative strategy we have had success with is looping through and deleting all rows and then writing all new records to the item with cursors.

[jcarlson]

If you've got an input table, a target feature layer, no changes to the schema, and you're working with the ArcGIS Python API, what you need is a spatially enabled dataframe. It does appear that you're working with a dataframe already in your screenshot, is that correct? Just call .spatial on the dataframe to access the additional capabilities added in the ArcGIS Python API.

Try something like this:

new_data = agg2[(agg2.Site == 'Callumbrae')]

rabbit = gis.content.get('your-itemid')

rabbit.tables[0].manager.truncate()

rabit.tables[0].edit_features(adds=new_data.spatial.to_featureset())

[ChrisMalam]

Hi Josh,

Thanks for the suggestion - when I tried running on my spatial dataframe I get the following error on the "rabbit.tables[0].manager.truncate()" command:

"IndexError: list index is out of range"

[jcarlson]

Hm. Is it a feature layer with some geometry, and not a table? In that case, it would be rabbit.layers[0] etc etc.

[Kara_Shindle]

I've also used the following successfully - my sources vary from CSV to Excel to Google Sheets, Smartsheets, etc.

commRecTable = gis.content.get('{insert item ID here')
commRec_collection = FeatureLayerCollection.fromitem(commRecTable)
commRec_collection.manager.overwrite(commRecCSV)

  This one was created by using a spatially enabled data frame and manipulating my date fields, etc. using Pandas, then exporting it to a CSV.  I then overwrite and upload my new CSV every hour.  I do this for about 6 different items. 

[DaveAlmond]

This worked like a charm and is so much simpler than most of other material out there on this topic. Thanks Kara! 

[JoshObrecht1]

I have tried this with a hosted table, however I am receiving the below error when running overwrite. Any thoughts?

ValueError: The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

[ConradSchaefer__DOIT_]

Chris, 

Saw your post over on  Overwrite Hosted Table but it disappeared. 

I replied over there since there was already a chain of conversation.

[MarcusBodig]

I am struggling with this same issue. 

I have tried using the FeatureLayerCollection.manager.overwrite() but that just appears to reread .sd files and restore all the original features without actually overwriting. Lots of posts having the same issue. Using the processes described here I have gotten closer but not all the way.  The features all get added but with no attributes.  Not sure what I am missing but have struggled for too long on this. 

Here is my simple code: 

This same query was used to to create a FGD feature class. I then published this using ArcGIS Pro as a hosted FeatureLayer
gis = GIS(url='https://ArcGIS Enterprise Portal Server', username=portalUser, password=portalUserPass)
existingFS_item = gis.content.get("294c153f5ad64856b275b162eb78731b")
sql_query=pd.read_sql_query("""SELECT ....
******very long SQL query******* """)
sdf=pd.DataFrame.spatial.from_xy(df=sql_query,x_column='Longitude',y_column='Latitude',sr=4326)
existingFS_item.layers[0].manager.truncate()
test = existingFS_item.layers[0].edit_features(adds=sdf)

I have tried passing a single feature converted to a dictionary object as well as writing this to a fileGDB and passing this in as an argument GeoAccessor.from_featureclass(FileGDB\FeatureClass).

All of these methods get the same result - all 500 records get created but no attributes get populated.  Any help would be appreciated.

Thanks.