Creating Where Clause from user input via Python list with ArcPy

Question

I am trying to perform a query based on values in a Python list. The where clause will then be used in an Update Cursor, so that the rows NOT equal to the values in the list will be deleted.

This is what the list will look like after it is passed into the script from the user (using a multivalue parameter in the script tool), and the semicolon delimited string has been split. This is just an example of many choices the user can make.

userSelectionList = ["40","77","95]
selectionQRY = "RTE_1_NBR <> 'userSelectionList'" # <-- I know this doesn't work

Hard coded, it would look like:

selectionQRY = "RTE_1_NBR <> '40' AND RTE_1_NBR <> '77' AND RTE_1_NBR <> '95'"

But the input and number of selection depends on the user's selection.

It should finally be something like this:

userSelectionList = ["40","77","95]
selectionQRY = "RTE_1_NBR <> 'userSelectionList'"
with arcpy.da.UpdateCursor(userI,["RTE_1_NBR"],selectionQRY) as userUC:
 for row in userUC:
 userUC.deleteRow()
del userUC

Is it possible to make the where clause from a user input/dynamic Python list?

Answer 1

You can use the join method:

selectionQRY = "RTE_1_NBR <> '" + "' AND RTE_1_NBR <> '".join(userSelectionList) + "'"

A string will be created, in three parts. The middle part is the key, where code of the form string.join(list) will take all objects in the list, convert them to strings, and then append them all together, with the specified string interjected between each of them. So, ",".join(["a","b","c"]) would produce "a,b,c".

In your particular example, the code "' AND RTE_1_NBR <> '".join(userSelectionList) would produce the string 40' AND RTE_1_NBR <> '77' AND RTE_1_NBR <> '95 for userSelectionList = ["40","77","95"]. Obviously, the opening RTE_1_NBR <> ' and closing ' are missing from the query, so those need to be added separately, at the beginning and end, respectively.

Answer 2

You could modify a function given in one of my previous answers which builds a where clause from a list of values and automatically adds the correct field delimiters and wraps string values in single-quotes. Just change IN to NOT IN to do the negation.

The resulting where clause would look something like "RTE_1_NBR" NOT IN('40','77','95'), depending on the underlying DBMS and field type.

Answer 3

How about using a for loop to build the query.

selectionQRY = "\"RTE_1_NBR\" <> \'" + userSelectionList[0] + "\'"
for selection in userSelectionList[1:]:
 selectionQRY += " AND \"RTE_1_NBR\" <> \'" + selection + "\'"