Build a SQL expression to select the rows to update

[Andrew_Needham]

Hi All,

I'm having issues building a valid SQL expression I keep getting an invalid syntax, no matter what I try.

I'm using Pro version 3.0.3

I have a filed called "OID_" in a point Feature class 

the error is to do with the sql_expression

  File "<string>", line 1
    OID_ >= 1001 AND OID_ <= 2000
                 ^
SyntaxError: invalid syntax

What am I missing here?

Below is a snippet  of my code:

field_to_search = param2 #"OID_"
field_to_update = param3 #"test"
field_with_values = param4 #"T_500"

# Define a list of search and copy ranges
search_ranges = [(i, i+999) for i in range(1001, 998002, 1000)]
copy_ranges = [(i+1, i+1000) for i in range(1001, 998001, 1000)]
# Create a search cursor to loop through the feature class
with arcpy.da.UpdateCursor(fc, [field_to_search, field_to_update, field_with_values]) as cursor:
# Loop through each range of rows
for search_range, copy_range in zip(search_ranges, copy_ranges):
# Build a SQL expression to select the rows to update
sql_expression = 'OID_ >= {} AND OID_ <= {}'.format(search_range[0], search_range[1])

# Select the rows to update
selected_rows = [row for row in cursor if eval(sql_expression)]

# Loop through the selected rows
for i, row in enumerate(selected_rows, start=1):
# Calculate the index for the row to copy from
copy_index = row[0] + 1
if copy_index >= copy_range[0] and copy_index <= copy_range[1]:
# Select the row to copy from
cursor_copy = arcpy.da.SearchCursor(fc, field_with_values, "OID_ = {}".format(copy_index))
for row_copy in cursor_copy:
# Update the field with the value from the other field
row[1] = row_copy[0]
cursor.updateRow(row)
break
del cursor_copy
# Reset the cursor to the first row before starting the next range
cursor.reset()

FYI this is the other versions I have tried:

sql_expression = "{} >= {} AND {} <= {}".format(arcpy.AddFieldDelimiters(fc, field_to_search), int(search_range[0]), int(search_range[1]), arcpy.AddFieldDelimiters(fc, field_to_search))

sql_expression = f"{field_to_search} >= {int(search_range[0])} AND {field_to_search} <= {int(search_range[1])}"

sql_expression = '{} >= {} AND {} <= {}'.format(field_to_search, str(search_range[0]), field_to_search, str(search_range[1]))

sql_expression = "'{}' >= {} AND '{}' <= {}".format(field_to_search, search_range[0], field_to_search, search_range[1])

sql_expression = "'{}' >= {} AND '{}' <= {}".format(field_to_search, search_range[0], field_to_search, search_range[1])

sql_expression = "{} >= {} AND {} <= {}".format(arcpy.AddFieldDelimiters(fc, field_to_search), search_range[0], arcpy.AddFieldDelimiters(fc, field_to_search), search_range[1]) # Select the rows to update selected_rows = [row for row in cursor if eval(sql_expression)]

Thanks

[DavidSolari]

Please post your code with the code sample formatting and proper indentation so we can see how things should work on our end, thanks!

[Andrew_Needham]

Thanks, I was wondering how to add the code that keeps the formatting.

I have attached the data and model builder script. I have reduced the amount of data that it would normally have, to be able to upload at an attachment.

Cheers

import arcpy
def ScriptTool(param0, param1, param2, param3, param4):
    # Update Left and Right rows
    print("Update Left and Right rows, this will take a while...")
    
    # Set the workspace where the feature class is located
    arcpy.env.workspace = param0 #arcpy.env.workspace = r""
    fc = param1 #fc = "T13_trial_pt_Export"   
    # Set the input feature class and the fields to update
    field_to_search = param2 # "OID_"
    field_to_update = param3 # "test"
    field_with_values = param4 # "T_500"
    
    # Define a list of search and copy ranges
    search_ranges = [(i, i+999) for i in range(1001, 998002, 1000)]
    copy_ranges = [(i+1, i+1000) for i in range(1001, 998001, 1000)]
    # Create a search cursor to loop through the feature class
    with arcpy.da.UpdateCursor(fc, [field_to_search, field_to_update, field_with_values]) as cursor:
        # Loop through each range of rows
        for search_range, copy_range in zip(search_ranges, copy_ranges):
            # Build a SQL expression to select the rows to update
            sql_expression = 'OID_ >= {} AND OID_ <= {}'.format(search_range[0], search_range[1])
            # Select the rows to update
            selected_rows = [row for row in cursor if eval(sql_expression)]
            # Loop through the selected rows
            for i, row in enumerate(selected_rows, start=1):
                # Calculate the index for the row to copy from
                copy_index = row[0] + 1
                if copy_index >= copy_range[0] and copy_index <= copy_range[1]:
                    # Select the row to copy from
                    cursor_copy = arcpy.da.SearchCursor(fc, field_with_values, "OID_ = {}".format(copy_index))
                    for row_copy in cursor_copy:
                        # Update the field with the value from the other field
                        row[1] = row_copy[0]
                        cursor.updateRow(row)
                        break
                    del cursor_copy
            # Reset the cursor to the first row before starting the next range
            cursor.reset()
            
    print("Done!")
    arcpy.AddMessage("Script Complete!")
    
    return fc
# This is used to execute code if the file was run but not imported
if __name__ == '__main__':
    # Tool parameter accessed with GetParameter or GetParameterAsText
    param0 = arcpy.GetParameterAsText(0)
    param1 = arcpy.GetParameterAsText(1)
    param2 = arcpy.GetParameterAsText(2)
    param3 = arcpy.GetParameterAsText(3)
    param4 = arcpy.GetParameterAsText(4)
    fc = ScriptTool(param0, param1, param2, param3, param4)
    arcpy.SetParameterAsText(5, fc)

[DavidSolari]

Ah, much better! I haven't looked too closely at the exact logic of the script, but there's a couple of big issues:

• You're attempting to "eval" a SQL expression on line 24, which is generating invalid Python. If you want to apply a filter to a cursor, you should create the SQL string and then pass it in as the third parameter to the cursor constructor. This'll let you use SQL syntax features and it filters the data in the database which saves you some needless Python work. Alternatively you can rewrite the conditional expression to use Python syntax, something like:

[row for row in cursor if row[0] >= search_range[0] and row[0] <= search_range[1]]​

• That list comprehension on line 24 is burning through the entire cursor object so it won't update the correct row.

Overall I think your best bet is to take a step back and rearrange the order you're running your statements in. One trick that might apply here is to use a dictionary comprehension to quickly turn a dataset into a lookup table. Something like:

lookup = {x[0]: x[1] for x in arcpy.da.SearchCursor(lookup_table, (lookup_key_field, value_field))}
with arcpy.da.UpdateCursor(update_table, (update_key_field, data_field)) as cursor:
    for key, current_value in cursor:
        new_value = lookup.get(key, current_value)
        if new_value != current_value:  # Avoid needless updates, saves costly db hits
            cursor.updateRow((key, new_value))

That way you do one pass through the lookup table and make matches in memory, saving a ton of database activity. Good luck!

[Andrew_Needham]

Thanks, ill have a look at doing that.