Overwrite a hosted feature using SDE View

[ITAdmin]

I am attempting to overwrite a Hosted Feature layer using SDE View. First, I get the layer ID from the portal and connect it to the portal. Secondly, I connect to my SQL and select the view I want. Thirdly, I create a new temporary view with the query and attempt to overwrite my hosted feature layer. Once the layer is overwritten, I delete the temporary view. My code is working fine, and I can see the updated item in the portal. Still, when I check the attribute data, it is not updated. It still shows the total number of attributes when I published it manually. My old view had 51 rows, and my new temp view had 57 rows. The code is working without any error, and the layer is updated, but the layer still shows the old data (51 rows). The service definition file is also updated in the portal, but I'm not sure if I need to do it in the code as well.

The code works, but it is not actually updating the data, and I am not sure what to do. Please see my attached code for reference -

#Import Modules
import arcpy
from arcgis.gis import GIS
from arcgis.features import FeatureLayerCollection
import pyodbc
import traceback
import arcpy.mp

# Connect to ArcGIS Online organization or Portal for ArcGIS

gis = GIS("Service URL", "Username", "Password")
un = gis.properties.user.username
# confirm that the user has logged in successfully
print('Logged in as: {}'.format(un))

#Variables - Web layers
itemid = "e5008676768687d"

# Set the connection properties
server_name = "ABC"
database_name = "XYZ"
username = "EEE"
password = "EEEE"
instance = "TD-G20\\ESRI"
sde_connection_path = r"C:\\Users\\ABC\\AppData\\Roaming\\Esri\\Desktop10.8\\ArcCatalog\\SQL.sde"
connection = arcpy.ArcSDESQLExecute(sde_connection_path)
view_to_select = "Cities"
arcpy.env.workspace = sde_connection_path

# Check if the view is selected
if arcpy.Exists(view_to_select):
    print("SDE View Selected Successfully.")
else:
    print("View does not exist")

#Creating a query on Cities View
#query = "SELECT CAST(Status AS smallint) FROM Cities WHERE Status = 1";
#result = connection.execute(query) # Execute the query

view_name = "Cities_QueryviewTemp"  # Temporary View with a query

# Check if the view already exists
if arcpy.Exists(view_name):
    print("View already exists.")
else:
    # Create the view with the query applied
    arcpy.management.MakeTableView("Cities",view_name, where_clause="Status =1")
    result = arcpy.management.GetCount("CEP_QueryviewTemp")
    row_count = int(result.getOutput(0))
    print("Total number of rows:", row_count)

# Verify if the view was created successfully
if arcpy.Exists(view_name):
    print("Temporary View selected successfully.")
else:
    print("Failed to create the view.")

# Overwrite Hosted Feature Layer
arcpy.env.overwriteOutput = True
dataitem = gis.content.get(itemid)
flayercol = FeatureLayerCollection.fromitem(dataitem) #Establishing a feature collection
flayercol.manager.overwrite(view_name)

print('Cities Web Layer Overwrite Successfully!')

# Delete the temporary view
arcpy.Delete_management(view_name, "VIEW")
print('Temporary View Deleted')

[JakeSkinner]

Hi @ITAdmin ,

I would recommend using the Append tool in ArcGIS Pro.  With the latest version of Pro, there is a Matching Field for Update option:

This will update existing features based on a unique field.  If the feature does not exist, it will Append the new feature.

If there is not a unique field, you could execute the Truncate or Delete Features tool first, and then execute the Append.

[ITAdmin]

Hi @JakeSkinner , 

Thank you for your response. But, I am trying to automate the process using code. Do you suggest adding that function to the code? 

Thank you. 

[JakeSkinner]

Yes, exactly.  I previously created this script for AGOL feature services, however the Append function at pro 3.x releases is very fast.

[ITAdmin]

Hi @JakeSkinner JakeSkinner,

Thank you for your response. I can see that it is working but with append, the problem is if I got 50 rows in the old dataset and the new dataset got 57. When I use append the total attributes I am getting is 107. But I just want to add only new 7 rows. I tried in append with field maps but not sure exactly which merge rule will work. Would be good if you can please share any suggestions on that ? If possible. 

Thank you for your time 🙂

[JakeSkinner · Accepted solution]

@ITAdmin you could use Truncate Table or Delete Rows before you apply the append.  I can't recall if Truncate Table will work on a hosted feature service, but Delete Rows will.

[ITAdmin]

@JakeSkinner 

Thank you very much for your time. It resolved my queries. 

Just FYI Truncate table or delete rows both are working for the feature layer. 

Thank you!