Use updatcursor to replace values in multiple fields

Question

I have spent a couple days digging through forums here, ESRI help and elsewhere and each variation of the codes I have tried has not worked. As best as I can tell this is due to my attempt to loop through multiple fields or something to do with formatting when the fields parameter is collected using the field as text option.

#Simple search and replace script
import arcpy

#define where clause to format for correct database type
def buildWhereClause(table, field, value):
    """Constructs a SQL WHERE clause to select rows having the specified value
    within a given field and table."""

    # Add DBMS-specific field delimiters
    fieldDelimited = arcpy.AddFieldDelimiters(table, field)

    # Determine field type
    fieldType = arcpy.ListFields(table, field)[0].type

    # Add single-quotes for string field values
    if str(fieldType) == 'String':
        value = "'%s'" % value

    # Format WHERE clause
    whereClause = "%s = %s" % (fieldDelimited, value)
    return whereClause

# Retrieve input parameters: the feature class, the search term, the replace
# term, and the fields affected by the search and replace.
fc = arcpy.GetParameterAsText(0)
oldValue = arcpy.GetParameterAsText(1)
newValue = arcpy.GetParameterAsText(2)
fields = arcpy.GetParameterAsText(3)

# Create the SQL expression for the update cursor. Here this is
#  done on a separate line for readability.
whereClause = buildWhereClause(fc, fields, oldValue)

# Create the update cursor
rows = arcpy.da.UpdateCursor(fc, fields, whereClause)

# Perform the update and move to the next row as long as there are
#  rows left
for row in rows:
    row[0] = newValue
    rows.updateRow(row)

# Delete the cursors to remove any data locks
del row, rows

What I need it to do is obtain all the fields in which the user wants to replace the oldValue with the newValue by reading in the multiple fields the user has checked the box for. Then loop through each field and in any instance where oldValue occurs re-write it to = newValue until all the fields have been updated.

Added:

Here is the traceback info thrown when this is run as a tool inside Model Builder

Traceback (most recent call last):
   File "C:\arcgisserver\ETL_Scripts\Scripts\RewriteValues.py", line 44, in <module>
   whereClause = buildWhereClause(fc, fields, oldValue)

   File "C:\arcgisserver\ETL_Scripts\Scripts\RewriteValues.py", line 25, in buildWhereClause
   fieldType = arcpy.ListFields(table, field)[0].type
IndexError: list index out of range

Answer 1

This might suffice for what you need. I have not run this code myself, so there may be a typo or two in there. The main difference between what I believe you were going for, and what I've added here is that I do not select rows with the value in it first, I just look at every row in the feature class. It may not be quick on massive feature classes. This will also fail if a non string field is selected as part of fields, but so would your code.

    fc = arcpy.GetParameterAsText(0)
    oldValue = arcpy.GetParameterAsText(1)
    newValue = arcpy.GetParameterAsText(2)
    fields = arcpy.GetParameterAsText(3).split(';')

    cursor = arcpy.da.UpdateCursor(fc, fields)

    for row in cursor:
        for field in fields:
            current = row.getValue(field)
            if current == oldValue:
                row.setValue(field, newValue)
                cursor.updateRow(row)

And just because I've noticed a lot of people don't know this, there is a find and replace feature built into ArcMap, so you may be re-inventing the wheel here, depending on your use case. In an ArcMap attribute table window, hit Ctrl + F to bring up the find & replace window. It's the same window you'd expect to find in something like excel. I think you'll need to be in an edit session for replace to work, though.

Answer 2

The fields value needs to be converted to a python list if it includes more than one field. You are testing for the field type in the whereclause method, but not in the value assignment method. The newValue has to be converted from String to the appropriate numeric type if the field is numeric and to a real date if the field is a date.

The untested code below should point you in the direction you need to go:

import arcpy
import time

#define where clause to format for correct database type
def buildWhereClause(table, field, value):
    """Constructs a SQL WHERE clause to select rows having the specified value
    within a given field and table."""

    # Add DBMS-specific field delimiters
    fieldDelimited = arcpy.AddFieldDelimiters(table, field)

    # Determine field type
    fieldType = arcpy.ListFields(table, field)[0].type

    # Add single-quotes for string field values
    if str(fieldType) == 'String':
        value = "'%s'" % value

    # Format WHERE clause
    whereClause = "%s = %s" % (fieldDelimited, value)
    return whereClause

# Retrieve input parameters: the feature class, the search term, the replace
# term, and the fields affected by the search and replace.
fc = arcpy.GetParameterAsText(0)
oldValue = arcpy.GetParameterAsText(1)
newValue = arcpy.GetParameterAsText(2)
fields = arcpy.GetParameterAsText(3)

fields = [field.strip() for field in fields.split(',')]

fieldType = arcpy.ListFields(fc, fields[0])[0].type

# Test for Numeric, Date or String fieldtypes
if str(fieldType) in ['SmallInteger','Integer','Single','Double']:
    try:
        newValue = float(newValue)
    except:
        print 'The new value must be a number!'
        return
elif str(fieldType) == 'Date':
    try:
        newValue = datetime.strptime(newValue, '%m/%d/%Y')
    except:
        print 'The new value must be a date in MM/DD/YYYY format!'
        return
elif str(fieldType) <> 'String':
    print 'The input field must be a String, Numeric or Date Field!'
    return

# Create the SQL expression for the update cursor. Here this is
#  done on a separate line for readability.
whereClause = buildWhereClause(fc, fields, oldValue)

# Create the update cursor
rows = arcpy.da.UpdateCursor(fc, fields, whereClause)

# Perform the update and move to the next row as long as there are
#  rows left
for row in rows:
    row[0] = newValue
    rows.updateRow(row)

# Delete the cursors to remove any data locks
del row, rows