2

I have a Python script that hits a REST API and grabs attribute data for rooms in buildings from an IBM Tririga DB. I have an Enterprise geodatabase rooms feature class populated with these same attributes. I want to update the feature data if there are changes in the Tririga DB. It works, but at the moment, it's changing all the data for the record, whether it matches or not. For example Rows[1,2,3] match the DB, but Row[4] does not. All four values are overwritten anyway. This is fine, but I'd like to make the script more efficient/elegant. I only want to change the specific row indexes where there are differences.

I'm assigning the feature class and DB attributes to a dictionary {fc_attr:db_attr} and comparing the differences between the keys and values. I'm wondering if there is a way to identify the specific row or rows where there are differences and change only those?

Here the relevant section of my script. I'd like to change the line row[6], row[7], row[8], row[9] = tri_attribute to rows_that_need_to_be_changed[x]= tri_attribute:

with arcpy.da.UpdateCursor(rooms, '*') as cursor:

##sde feature class attributes

for row in cursor:

    fcSpaceID = row[2]
    fcSiteName = row[5]
    fcRoomNum = row[6]
    fcSpaceClass = row[8]
    fcArea = row[9]
    fcEditUser = row[13] ## last_edited_user field
    fcEditDate = row[14] ## last_edited_date field


    attributes_dict = {fcRoomNum:triRoomNum,
                          fcSpaceClass:triSpaceClass,
                          fcArea:triArea,
                          }

    fc_attribute = attributes_dict.keys()
    tri_attribute = attributes_dict.values()

    ##update feature class with data from Tririga
    if fcSpaceID == triSpaceID:

        if fc_attribute != tri_attribute:

            row[13] = "GIS Admin"
            row[14] = datetime.now()
            row[6], row[7], row[8], row[9] = tri_attribute


            cursor.updateRow(row)

    else:
        continue
1
  • Your indentation is off on the for loop, your dictionary use pattern is unconventional to the point of obscurity, your inner if test will always return True, and your stated goal doesn't align with the existing code. The usual way to do this is to export the data table with {key,values...} into the other database, and use a query joined with key to drive a conditional update. You can emulate this with Python, but then you need a proper dictionary of dictionaries from the source, and a lookup inside the loop (could use a lot of RAM) or nested loops with a where_clause to limit update.
    – Vince
    Dec 14, 2019 at 15:10

1 Answer 1

1

You have a lot of missing context in your code, so I'm assuming you have a query loop on the Triga DB which populates the triSpaceID, triRoomNum, triSpaceClass, and triArea variables.

The quickest fix to your existing code is to toss the attributes_dict manipulation and the if fcSpaceID == triSpaceID: block and use the where_clause parameter to arcpy.da.UpdateCursor:

cursorCols = ['RoomNum','SpaceClass','Area','EditUser','EditDate']
tstamp = datetime.now()
for triSpaceID,triRoomNum,triSpaceClass,triArea in notRelevantToAnswer():
    with arcpy.da.UpdateCursor(rooms, cursorCols, 
                    where_clause = "SpaceID ='{:s}'".format(triSpaceID)) as cursor:
        for row in cursor:
            if (row[:3] != [triRoomNum,triSpaceClass,triArea]):
                row[0] = triRoomNum 
                row[1] = triSpaceClass 
                row[2] = triArea
                row[3] = "GIS Admin"
                row[4] = tstamp
                cursor.updateRow(row)

Your old code had an evil N-squared loop in the Update. If the runtime wasn't oppressive, then the feature count was low enough that you could do this instead:

# Cache Triga DB rows
trigaData = {}
for triSpaceID,triRoomNum,triSpaceClass,triArea in notRelevantToAnswer():
    trigaData[triSpaceID] = [triRoomNum,triSpaceClass,triArea]

# Singleton Update cursor
cursorCols = ['SpaceID','RoomNum','SpaceClass','Area','EditUser','EditDate']
tstamp = datetime.now()
with arcpy.da.UpdateCursor(rooms, cursorCols) as cursor:
    for row in cursor:
        if (row[0] not in trigaData):
            print("Warning: Enterprise row with key '{:s}' has no Triga data".format(row[0]))
            continue

        trigaRow = trigaData[row[0]]
        if (row[1:4] != trigaData):
            row[1] = trigaRow[0] 
            row[2] = trigaRow[1] 
            row[3] = trigaRow[2]
            row[4] = "GIS Admin"
            row[5] = tstamp
            cursor.updateRow(row)

Either of these would meet the your requirements, though you might have to tweak for column names and datatypes. Both should be significantly faster. You could even consume the dictionary values upon use (via del trigaData[row[0]]), and be able to detect rows in Triga not in the database (by scanning the dictionary contents after the cursor completes).

2
  • Thanks. I'm following your example here, but I'm trying to figure out how to get the script to skip over records that don't need changing. It's updating the cursor row regardless. Something like: if (row[:20] != [triRoomNum,triSpaceClass,triArea,triK2Compliant]): update else: continue Dec 17, 2019 at 23:40
  • The code already does this, as is. A continue is pointless when it's the last line in the block, as is an empty else. You could easily have a type error in the array comparison (which is why array equivalence, though permitted, is fraught for failure). But comparing a 20-element array with a 4-element array will not ever reach a type comparison failure (length would be an early thing tested, right after not None).
    – Vince
    Dec 18, 2019 at 12:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.