Overwrite Hosted Table with new data

2249
9
05-23-2021 06:37 PM
ChrisMalam
New Contributor II

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:

ChrisMalam_0-1621814237789.png

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!!

0 Kudos
9 Replies
KevinMacLeodCAI
Occasional Contributor II

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.

0 Kudos
jcarlson
MVP Esteemed Contributor

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())
- Josh Carlson
Kendall County GIS
0 Kudos
ChrisMalam
New Contributor II

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"

0 Kudos
jcarlson
MVP Esteemed Contributor

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

- Josh Carlson
Kendall County GIS
0 Kudos
Kara_Shindle
Occasional Contributor III

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
New Contributor III

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

JoshObrecht1
New Contributor III

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().

0 Kudos
ConradSchaefer__DOIT_
New Contributor III

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
New Contributor

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.
0 Kudos