3

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
  • 1
    So, what is the script doing? What errors does it throw? Do you run it outside of ArcMap, or in the Python window? – Baltok Oct 1 '14 at 18:20
  • 1
    Try printing out the fields parameter you're getting from arcpy.GetParameterAsText(3). I may be wrong, but if you select multiple fields, you'll get "field1;field2;field3" as a string. What Update Cursor is expecting is a python list of fields. Use fields = arcpy.GetParameterAsText(3).split(';') to get an actual list. However, that's going to reveal another problem - buildWhereClause will only accept one field name, not a list of field names. As Baltok mentioned though, we're going to need error messages. – MWrenn Oct 1 '14 at 18:47
  • I apologize I should have added that info I run this script as a tool inside Model Builder as part of an ETL process that will be migrated to server for automation once we have it working. I will add the traceback info but as @MWrenn mentioned it is a list issue – Scott Fierro GISP Oct 3 '14 at 12:48
5

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.

ArcMap Find & Replace Window

  • 2
    +1 for the Find and Replace. Never knew it was there until after I scripted my own version. I wasn't happy to find out I burnt time coding a duplicate! – Branco Oct 1 '14 at 21:01
  • I saw and actually tried several variations using the for field in fields option with no success. Utilizing what you gave me still throws an error with the list. It throws it for the line current = row.getValue(field) as AttributeError: 'list' object has no attribute 'getValue' – Scott Fierro GISP Oct 3 '14 at 13:08
  • @MWrenn You've mixed syntax for the old-style cursors and the da cursors. The da cursors don't have getValue/setValue methods. – nmpeterson Oct 3 '14 at 13:43
3

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
  • I see what you are saying, I only tested for it there as a 1 time check that whoever made the ETL didn't grab a wrong field. We are using set fields that will never change and this will become a fully automated process where the 11 string fields will be the checked parameter inputs that we are interested in. I did at one point run into an error that the arcobject wasn't being properly converted to a python object for the list fields but I think even if I build out the fieldType as you show I'd still have an issue looping through that list in the iterations part. – Scott Fierro GISP Oct 3 '14 at 13:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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