update.da.cursor question about fields

CW-GIS

Hi everyone,

I'm stuck on a situation that I feel I'm making more complicated than it needs to be.

Situation: 

I have a database (700K records) that updates daily, I convert it to a spatial dataframe. I have a hosted feature layer (points) that updates daily. Right now I truncate and append the entire thing, but having it empty for a while isn't ideal.

I want to basically this:

• For each row in the database, search for the same 'key' in the hosted.
• If return clause = 1
• If any of the fields have different values, then update that field (or just the entire row)
• else 
• If return clause = 0
• insert cursor to insert that row
• else next row

Where I am getting confused is, does the da.cursor just use field position to insert values? row[19] will update the 19th column. But what if someone inserts a new column in the database, would everything get screwed up? Can I instead just match the field names with a field map?

Tonyalmeida

Your right, mine does the same. Try this.

Changes made are:

1- Trimmed whitespace and converted all field values to strings before comparison

2 - Prevents redundant inserts by comparing existing unique IDs in the hosted layer

import arcpy

# Paths to the local database and the hosted feature layer
database_path = "database"
hosted_layer = "feature_layer"

# Fields to check and update
fields = ['unique_id', 'other_field', 'field1', 'field2', 'field3']  # Update this list as needed

# Get the initial count of features in the hosted layer
initial_feature_count = int(arcpy.GetCount_management(hosted_layer).getOutput(0))
print(f"Initial number of features: {initial_feature_count}")

# Create a dictionary to store records from the database for quick lookups
database_records = {}
with arcpy.da.SearchCursor(database_path, fields) as search_cursor:
    for row in search_cursor:
        unique_id = row[fields.index('unique_id')]
        database_records[unique_id] = row

update_count = 0
insert_count = 0

# Process rows in the hosted layer to update existing records
with arcpy.da.UpdateCursor(hosted_layer, fields) as update_cursor:
    for row in update_cursor:
        unique_id = row[fields.index('unique_id')]
        if unique_id in database_records:
            matching_row = database_records[unique_id]
            updated = False
            # Compare each field and update values if they differ
            for i, field in enumerate(fields):
                if str(row[i]).strip() != str(matching_row[i]).strip():
                    row[i] = matching_row[i]
                    updated = True
            if updated:
                update_cursor.updateRow(row)
                update_count += 1

# Insert new rows for records in the database that are not in the hosted layer
with arcpy.da.SearchCursor(hosted_layer, ['unique_id']) as hosted_cursor:
    hosted_ids = {row[0] for row in hosted_cursor}

for unique_id, record in database_records.items():
    if unique_id not in hosted_ids:
        with arcpy.da.InsertCursor(hosted_layer, fields) as insert_cursor:
            insert_cursor.insertRow(record)
            insert_count += 1

# Get the final count of features in the hosted layer
final_feature_count = int(arcpy.GetCount_management(hosted_layer).getOutput(0))
print(f"Final number of features: {final_feature_count}")
print(f"Number of features updated: {update_count}")
print(f"Number of features inserted: {insert_count}")

print("Update and insert operations completed.")