Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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? Thank you!

share|improve this question

3 Answers 3

up vote 6 down vote accepted

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.

share|improve this answer
    
THANK YOU, THANK YOU, THANK YOU, THANK YOU! –  dubch87 Apr 13 '13 at 19:41

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.

share|improve this answer
1  
I do like this form better than the one the OP requested when building a query based on many values of a single attribute. –  nmpeterson Apr 14 '13 at 2:44

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 + "\'"
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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