Build a SQL expression to select the rows to update

Question

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 "", 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

Andrew_Needham · Answer

Thanks, ill have a look at doing that.

DavidSolari · Answer

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 · Answer

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) Share.zip