current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 2 down vote favorite

I'm selecting all records and then removing certain values from this selection in a Python script. I've succeeded using a for loop in a SearchCursor to remove records from the selection but it is very slow. Is there anyway to remove the records all at once using "IN" like how this would work in the field calculator

SELECT * FROM FeatureClass WHERE Field IN ('a', 'f', 'l')

This is what I have so far:

gRoutes =  ('R10T', 'R10TBP', 'R10TWP', 'R20T', 'R20TBP', 'R20TWP', 'R32T', 'R32TBP', 'R32TWP', 'R45T', 'R45TBY', 'R45TBP', 'R45TWP', 'R64T', 'R64TBP', 'R64TWP', 'R96T', 'R96TBP', 'R96TWP', 'R10TBY', 'R20TBY', 'R32TBY', 'R64TBY', 'R96TBY')

# field name
QF1 = "Product_Code"

WC1 = '"' + QF1 + '" IN ' + "'" + gRoutes + "'"

arcpy.SelectLayerByAttribute_management("layerFC","REMOVE_FROM_SELECTION", WC1)

This is what the WC1 expression prints as:

'"Product_Code" IN \'[\'R10T\', \'R10TBP\', \'R10TWP\', \'R20T\', \'R20TBP\', \'R20TWP\', \'R32T\', \'R32TBP\', \'R32TWP\', \'R45T\', \'R45TBY\', \'R45TBP\', \'R45TWP\', \'R64T\', \'R64TBP\', \'R64TWP\', \'R96T\', \'R96TBP\', \'R96TWP\', \'R10TBY\', \'R20TBY\', \'R32TBY\', \'R64TBY\', \'R96TBY\']\''

And this is the error I'm getting:

Start Time: Thu Oct 20 14:59:48 2016
ERROR 000358: Invalid expression
Failed to execute (SelectLayerByAttribute).
Failed at Thu Oct 20 14:59:48 2016 (Elapsed Time: 0.02 seconds)
share|improve this question
2  
It looks like the gRoutes is a becoming a Python list. You don't want this. It should be a string so add Double quotes outside the parenthesis where you are defining gRoutes. – klewis yesterday
    
Thanks klewis. Still getting "Error 000358 Invalid Expression". Same result when printing WC1. Seems as if it is still being converted to python list. – ShaunO yesterday
    
Previous Q&As with the same error may be worth reviewing. – PolyGeo♦ yesterday
up vote 2 down vote accepted

Try this (SQL in python is kind of annoying sometimes):

gRoutes =  '(\'R10T\', \'R10TBP\', \'R10TWP\', \'R20T\', \'R20TBP\', \'R20TWP\', \'R32T\', \'R32TBP\', \'R32TWP\', \'R45T\', \'R45TBY\', \'R45TBP\', \'R45TWP\', \'R64T\', \'R64TBP\', \'R64TWP\', \'R96T\', \'R96TBP\', \'R96TWP\', \'R10TBY\', \'R20TBY\', \'R32TBY\', \'R64TBY\', \'R96TBY\')'

# field name
QF1 = '\"Product_Code\"'

WC1 = QF1 + ' IN ' + gRoutes

arcpy.SelectLayerByAttribute_management("layerFC","REMOVE_FROM_SELECTION", WC1)
share|improve this answer
    
That did it! Thanks. Any idea why the slashes are required? WC1 still prints out the same but the values were removed and a million times faster. – ShaunO yesterday
1  
The backslashes escape the single quotes so they're not read by Python (in the case of gRoutes not read as separate list elements). When you're using reserved characters but want them to appear, you have to escape them. And SQL is pretty picky about how you quote stuff (but you know that already). – Jvhowube yesterday
    
Cool. Good to know. Thanks again! – ShaunO yesterday
up vote 3 down vote

This is why Python allows both quotes and apostrophes. Best practice would use them and a .format() to improve readability:

gRoutes = "'R10T', 'R10TBP', 'R10TWP', 'R20T', 'R20TBP', 'R20TWP', 'R32T', 'R32TBP', 'R32TWP', 'R45T', 'R45TBY', 'R45TBP', 'R45TWP', 'R64T', 'R64TBP', 'R64TWP', 'R96T', 'R96TBP', 'R96TWP', 'R10TBY', 'R20TBY', 'R32TBY', 'R64TBY', 'R96TBY'"
WC1 = '"Product_Code" IN ({:s})'.format(gRoutes)

You can even compile the members as a Python list or array, then expand at runtime (clipping the opening and closing parens/braces):

gRoutes = ( 'R10T', 'R10TBP', 'R10TWP', 'R20T', 'R20TBP', 'R20TWP', 'R32T', 'R32TBP', 'R32TWP', 'R45T', 'R45TBY', 'R45TBP', 'R45TWP', 'R64T', 'R64TBP', 'R64TWP', 'R96T', 'R96TBP', 'R96TWP', 'R10TBY', 'R20TBY', 'R32TBY', 'R64TBY', 'R96TBY' )
WC1 = '"Product_Code" IN ({:s})'.format(str(gRoutes)[1:-1])

Be sure to confirm that the IN list doesn't exceed the RDBMS's maximum.

share|improve this answer
up vote 0 down vote

Prepping IN strings for SQL can be a pain. Simplest way to process your gRoutes list would be something like 

gRoutes = "\', \'".join(gRoutes)
gRoutes = "\'{}\'".format(gRoutes)

so

WC1 = "{} IN ({})".format(QF1, gRoutes)

or z could include your bracket characters, "(\'{}\')".format(y) and WC1 could skip them.

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.