SelectLayerByAttribute_analysis HELP!!

[DonaldFiore]

Hey all, starting to get frustrated, if anyone can lend some clarity it'd be greatly appreciated.

arcpy.SelectLayerByAttribute_management('mwstates','NEW_SELECTION','"STATE_NAME"=\'Illinois\' or '"STATE_NAME"=\'Indiana\''


Code keeps returning only 'Illinois', (I want both 'Illinois' and 'Indiana') I've tried numerous variations. I think I have to be missing something obvious here. Thoughts??

[MichaelVolz]

How about something like:

arcpy.SelectLayerByAttribute_management('mwstates','NEW_SELECTION',"STATE_NAME in ('Illinois' , 'Indiana')")

This syntax works in a python script I have using Select_analysis against an SDE database.

[T__WayneWhitley]

Doesn't look like you pasted all of your query...

If this helps, you could 'escape' all of your quotation characters with the backwards slash, \' and \", like so:

>>> query = '\"STATE_NAME\"=\'Illinois\' or \'\"STATE_NAME\"=\'Indiana\''

>>> print query
"STATE_NAME"='Illinois' or '"STATE_NAME"='Indiana'

...or even use the built-in python format in various ways - here is one of them (and I changed the SQL syntax, using the IN operator):

>>> query = '{0} IN ({1}, {2})'.format('"STATE_NAME"', "'Indiana'", "'Illinois'")

>>> print query
"STATE_NAME" IN ('Indiana', 'Illinois')

...FYI, if it makes the code more readable as well, you could define a var (as I've done with 'query') and pass that to your Select by attributes:

arcpy.SelectLayerByAttribute_management('mwstates','NEW_SELECTION',query)

[DonaldFiore]

Thanks for the tips. I know the code is very sloppy, but I know I have "" '' formatting correct. Its the SQL expression that is not correct. I tried using the In format but still no dice. I can't seem to find anything online about the correct SQL expression to use in arcpy to obtain more than feature by attribute.

[T__WayneWhitley · Accepted solution]

What do you mean exactly?  There are references - you can start here:

Building a query expression
http://resources.arcgis.com/en/help/main/10.2/index.html#//00s50000002t000000

...and if you look in the 'tree' view panel to the left (if you have it visible in your browser), there's another subtopic, "SQL reference..."


If you want to use it, there's also an AddFieldDelimiters command, in case you want a universal query for sources out of different workspace types -- I think a personal gdb used brackets on the field, etc etc....

Also try forming the query with the select wizard in ArcMap to 'see' the correct sytax....other than that, make sure you are selecting what you think you're selecting i.e. that the attribute value is actually in there somewhere.


BTW:  (extra single-quote)
Like I said before, maybe you pasted in error, but your query posted is NOT correct:

>>> '"STATE_NAME"=\'Illinois\' or '"STATE_NAME"=\'Indiana\'' SyntaxError: unexpected character after line continuation character >>> 

You have and 'extra' single-quote before "STATE_NAME" - this works:

>>> '  "STATE_NAME"=\'Illinois\' or "STATE_NAME"=\'Indiana\'  ' '  "STATE_NAME"=\'Illinois\' or "STATE_NAME"=\'Indiana\'  ' >>>

[MichaelVolz]

Are you running this in a standalone python script outside of ArcMap or are you using the Field Calculator?

Maybe you can use the Field Calculator with python inside ArcMap to get the syntax correct.

[DonaldFiore]

Ok! Got It! Thanks for recommending running the wizard in arcmap, I forgot you could pull py snippets of script from the geoprocessing window.


Correct Script:
arcpy.SelectLayerByAttribute_management("mwstates","NEW_SELECTION","STATE_NAME = 'Illinois' OR STATE_NAME = 'Indiana'")

I was encasing the Field (State_Name) in double quotes, apparently only the expression itself needs to be encased.

Thanks!

[T__WayneWhitley]

Beautiful.  Welcome to the forums!

Now, the 'correct' etiquette, if you haven't already done so, is to award points to various valuable posts or the 'green checkmark' to that single post you deem the best answer (before you log out).  Those are the buttons to the right of the posts...fiddle with those.  You can even give yourself the answer if you want...lol.

[Anonymous User]

There is another way you can make this query easier on yourself.  I just found this gem the other day...Apparently you can form queries using a list like this:

query = ''' "STATE_NAME" IN ('Illinois', 'Indiana') '''