Writing ArcPy Update Cursor "Where Clause" that Refers to its own Rows?

Question

I'm using Arc 10.2.2.

I have 2 files:

1. "Input_Table.exe" (only 3 columns: "ID", "Lat", and "Lon")
2. "Target_Shapefile.shp" (many columns including the same "ID", "Lat", and "Lon")

Any rows in the Target_Shapefile with an ID that's present in the Input_Table need to be updated with those appropriate values.

I'm aware of the Update Cursor and that using its 3rd parameter, the "Where Clause", will reduce the number of retrieved rows to be updated, thus saving time.

But how do I refer to a row in the same shapefile that's being updated? (Target_Shapefile)

More specifically, is the following syntax for my "Where Clause" valid?

    Where = "Where ['ID'] in Input_ID_list"

My Complete Code:

# Create a list of all IDs to be updated in the Target_Shapefile.
rows = arcpy.da.SearchCursor(Input_Table)
for row in rows:
    Input_ID = row.getValue('ID')
    Input_ID_list = Input_ID_list + Input_ID

# Set "Where Clause"
Where = "Where ['ID'] in Input_ID_list"

# Create the Update Cursor...
with arcpy.da.UpdateCursor(Target_Shapefile,['ID', 'Lat', 'Lon'] , Where) as Tcursor:
        for row in Tcursor:
                do some things...

Answer 1

Store the relationship between IDs and lat/lons in a python dictionary, generated with a search cursor. Then update your shapefile with an update cursor, reading lat and lons from your dictionary. No SQL where clause is needed.

#empty dictionary
latLonDi = {}
#iterate input table
with arcpy.da.SearchCursor (Input_Table, ["ID", "Lat", "Lon"]) as curs:
    for iden, lat, lon in curs:
        #update dictionary
        latLonDi [iden] = (lat, lon)

#update
with arcpy.da.UpdateCursor(Target_Shapefile, ['ID', 'Lat', 'Lon']) as curs:
    for iden, lat, lon in curs:
        #get value from dictionary if value is in dictionary
        try: lat, lon = latLonDi [iden]
        except KeyError: continue #not in dictionary - skip row

        #create new row with lat and lon from input table
        row = (iden, lat, lon)
        #update row
        curs.updateRow (row)

In some cases an SQL where clause might be faster if your shapefile table is small. Iterate through the shapefile attribute table and store the IDs in a python set. Then create your sql where clause from the set.

#store all shapefile ids in set
ids = set ()
with arcpy.da.UpdateCursor (Target_Shapefile, "ID") as curs:
    for iden, in curs:
        ids.add (iden)

The sql format should look like ID IN (1, 2, 3). Specifics of your SQL depend on the table format and the id field type. See esri's help on the subject.

For string field types:

dataSource = arcpy.Describe (Target_Shapefile).path
delimIdFld = arcpy.AddFieldDelimiters (dataSource, "ID")
idStr = "', '".join (ids)
#sql
sql = "{} IN ('{}')".format (delimIdFld, idStr) # ID IN ('1', '2', '3')

For numeric field types:

dataSource = arcpy.Describe (Target_Shapefile).path
delimIdFld = arcpy.AddFieldDelimiters (dataSource, "ID")
sql = "{} IN {}".format (delimIdFld, tuple (ids)) # ID IN (1, 2, 3)

Finally, apply your sql and continue with the above code:

#search cursor with sql applied
with arcpy.da.SearchCursor (Input_Table, ["ID", "Lat", "Lon"], sql) as curs:
    ...

Answer 2

Using the where clause can be very beneficial, but calling it a loop over and over can be very slow. This is usually called 'row by agonizing row' and should be avoided if possible.

Let's assume the number of rows in Input_Table isn't huge; a million records or so should be fine. If we load up all the values of interest (ID, with a tuple of Lat and Long) into a dictionary, we just just run through all the records in the Target_Shapefile and look up in the dictionary for the values associated with that ID. If it's in the loaded dictionary, we'll update Target_Shapefile.

The following is untested, but should give you a general idea of this approach:

# load all the input values into a dictionary
loc_dict = {}

with arcpy.da.SearchCursor(Input_Table) as rows:
    for row in rows:
        Input_ID = row.getValue('ID')
        Input_Lat = row.getValue('Lat')
        Input_Lon = row.getValue('Lon')
        loc_dict[Input_ID] = (Input_Lat, Input_Lon)

# now let's update the Target_Table
with arcpy.da.UpdateCursor(Target_Shapefile, ('ID', 'Lat', 'Long')) as Tcursor:
    for row in Tcursor:
        cur_Target_ID = row.getValue('ID')
        # only update if the ID is found in the dictionary
        if cur_Target_ID in loc_dict:
            row[1] = loc_dict[cur_Target_ID][0]
            row[2] = loc_dict[cur_Target_ID][1]
            rows.updateRow(row)

A couple of notes:

• The Python here is a bit chatty, but I wanted to leave some of your code as is to see what I'm trying here. Pythonistas would combine some of these to make it cleaner.
• When working with cursors, try to always use with as it cleans up after itself nicely.
• This can chew up memory, but should be pretty fast if you can store all the input values into memory. If not, we'll need to try a different approach.

Answer 3

I think you are making this harder than it is. If you collect all of the ID's from the Input Table format them as a string delineated for your whereclause (based on a target of shapefile) your whereclause should be something like "Input_ID in Target_ID_list" (Target ID List should be formatted correctly while be captured/created with delineations and containing makers like parenthesis or brackets)

---

Not sure try your query in the desktop query tool. Probably needs delimiters between values and an encasing symbol parenthesis/brackets, curlies. Have not worked with Shapefiles in forever but look into the help in the Query tool of desktop. It should make it clearer. You only need a string variable, no list or array needed and keep it out of the quotes.