update an attribute if record found in a query

[MatthewWalker1]

I am trying to update an attribute in one table, if that record is found in a MakeQuery table (I made a table view of all records not yet processed, and once processed, I want to turn the 'Processed' flag to 'Y').

rows = arcpy.UpdateCursor("Assets") for row in rows:  if row.Processed == "N" and row.Asset_ID == "Asset_Unprocessed.Asset_ID":   row.Processed = "Y"   rows.updateRow(row) del row del rows

I am trying to update in the Assets table the 'Processed' flag to 'Y', IF the Assets key is found in the 'Assets_Unprocessed' table.  This does not throw an error, it just does not find and update my test data properly.  The issue is the comparison to the Assets_Unprocessed table (the rest of the code works), and I've tried several different ways to type that table (full path, brackets, etc.) and can't seem to get that part to work.

[JakeSkinner]

Hi Matthew,

Are you joining the 'Asset_Unprocessed' table to the 'Assets' table?

[Zeke]

You don't show where you're looping through Asset_Unprocessed. Unless the actual value of Asset_ID is the string "Asset_Unprocessed.Asset_ID", no matches will be found. This isn't an error in the coding; it's doing exactly what it's told to do.

If what you want is to update Assets if there is a match in the ID fields of the two tables, one solution would be to first run a SearchCursor on Assets_Unprocessed and populate a list with all that table's ids. Then run an UpdateCursor on Assets and check if the id value is in the list. Or, if the tables are joined as Jake asks, instead, change your code to check row.Assets_Unprocessed.Asset_ID, or whatever field name the join gave it. However, this wouldn't catch instances where Assets_Unprocessed.Asset_ID wasn't in the row for the matching id, if there are any. Yet another way would be to run the UpdateCursor in a nested loop in a SearchCursor loop, but I think I read somewhere that ESRI discourages nested cursors.

Something like (untested):

au = "Assets_Unprocessed"
lstIds = []

# with... syntax automaticalyy deletes auRows, row
with arcpy.SearchCursor(au) as auRows:
    for row in auRows:
        lstIds.append(auRows.Asset_ID)

with arcpy.UpdateCursor("Assets") as aRows:
    for row in aRows:
        if row.Processed == "N" and row.Asset_ID in lstIds:
            row.Processed = "Y"
            arows.update(row)

[MatthewWalker1]

Greg,

Thanks for the input, I see how that could resolve my issue.  I tried running that SearchCursor section of code you supplied, but an error was returned:

Runtime error <type 'exceptions.AttributeError'>: 'Cursor' object has no attribute '__exit__'

I am unfamiliar with the with...as statement so I'm not sure what is missing or needed here.  I understand the process of building a list of Asset_ID's from Assets_Unprocessed then searching for records in Assets with those Asset_ID's, but it seems I'm having difficulty in first building that list with the SearchCursor and the use of the with...as statement.

[Zeke]

Do you have any Nulls or empty fields in the Asset_ID field? Maybe add an if statement checking for them before appending to the list. What version of Arc are you running?

You may want to modify the SearchCursor arguments to include just the Asset_ID field as well, and make sure someone else isn't accessing the data and putting a lock on it. Or maybe lstIds.append(row.getValue("Asset_ID")). I use arcpy.da, which is a slightly different syntax.

The with...as syntax just provides for automatic garbage collection, so you don't have to manually delete rows or row. Maybe the version in your setup doesn't support with for a SearchCursor, so you could try going back to your original syntax. for that. In that case, consider wrapping it in a try/except block. This is actually what I'd try first. The with...as expects an __exit__ attribute, so if it's not there you'll get this error.

au = "Assets_Unprocessed"
fld = "Asset_ID" 
lstIds = []  

auRows = arcpy.SearchCursor(au) 
for row in auRows:     
    lstIds.append(auRows.Asset_ID)

del auRows
del row 

try:     
    aRows = arcpy.UpdateCursor("Assets") 
    for row in aRows:         
        if row.Processed == "N" and row.Asset_ID in lstIds:             
            row.Processed = "Y"             
            aRows.update(row)
except Exception as e:
     print e.message
     #do whatever

del aRows
del row

[JakeSkinner]

Greg,

Thanks for the input, I see how that could resolve my issue.  I tried running that SearchCursor section of code you supplied, but an error was returned:

Runtime error <type 'exceptions.AttributeError'>: 'Cursor' object has no attribute '__exit__'

I am unfamiliar with the with...as statement so I'm not sure what is missing or needed here.  I understand the process of building a list of Asset_ID's from Assets_Unprocessed then searching for records in Assets with those Asset_ID's, but it seems I'm having difficulty in first building that list with the SearchCursor and the use of the with...as statement.

Can you upload a small sample of each dataset in a zipped File Geodatabase?

[MatthewWalker1]

I have attached a file geodb containing the Assets table and Assets_Unprocessed that I am trying to query and update.  The Asset table is queried in a MakeQueryTable for records that have not been processed, and dumped into the table Assets_Unprocessed, to be used by other utilities.  Once these records have been processed, I need to flag them as such by changing the 'N' to 'Y' in the Processed attribute within the Assets table.

The latest reply mentioned using a SearchCursor without the with...as statement, but I cannot get that to work either (error returns 'Cursor' object has no attribute 'Asset_ID' when I run the SearchCursor code:

au = "Assets_Unprocessed"
lstIds = []
fld = "Asset_ID"

auRows = arcpy.SearchCursor(au)
for row in auRows:
 lstIds.append(auRows.Asset_ID)

[MatthewWalker1]

and FYI, I am running Arc 10.0 SP5 and running my python code within an ArcMap arcpy session.

[JakeSkinner · Accepted solution]

Hi Matthew,

Try the following:

table = 'assets_unprocessed' assetIDList = []  rows = arcpy.SearchCursor(table) for row in rows:     assetIDList.append(row.Asset_ID)  del row, rows  table2 = 'assets'  for ID in assetIDList:     rows = arcpy.UpdateCursor(table2)     for row in rows:         if row.Asset_ID == ID:             row.Processed = 'Y'             rows.updateRow(row)  del row, rows print 'finished'

[Zeke]

Not sure what to tell you. Worked for me. Are you importing arcpy?

Jake, your method works, but is looping through the table n times in the inner loop the best way to go?

The latest reply mentioned using a SearchCursor without the with...as statement, but I cannot get that to work either (error returns 'Cursor' object has no attribute 'Asset_ID' when I run the SearchCursor code: