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)
  • 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 '16 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 '16 at 22:43
  • Previous Q&As with the same error may be worth reviewing. – PolyGeo Oct 20 '16 at 23:02
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)
  • 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 '16 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 '16 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.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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