2

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)
Improve this question
3
  • 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
    Oct 20, 2016 at 22:25
  • Thanks klewis. Still getting "Error 000358 Invalid Expression". Same result when printing WC1. Seems as if it is still being converted to python list.
    – geoJshaun
    Oct 20, 2016 at 22:43
  • Previous Q&As with the same error may be worth reviewing.
    – PolyGeo ♦
    Oct 20, 2016 at 23:02

3 Answers 3

Reset to default
3

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)
Improve this answer
2
  • 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.
    – geoJshaun
    Oct 20, 2016 at 23:14
  • 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
    Oct 20, 2016 at 23:23
3

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.

Improve this answer
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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