topic Re: SelectLayerByAttribute_analysis HELP!! in Python Questions

SelectLayerByAttribute_analysis HELP!!

DonaldFiore — Fri, 10 Jan 2014 14:21:54 GMT

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??

Re: SelectLayerByAttribute_analysis HELP!!

MichaelVolz — Fri, 10 Jan 2014 14:49:33 GMT

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.

Re: SelectLayerByAttribute_analysis HELP!!

T__WayneWhitley — Sun, 12 Dec 2021 00:17:32 GMT

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)

Re: SelectLayerByAttribute_analysis HELP!!

DonaldFiore — Fri, 10 Jan 2014 16:58:58 GMT

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.

Re: SelectLayerByAttribute_analysis HELP!!

T__WayneWhitley — Fri, 10 Jan 2014 17:20:59 GMT

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\'  ' >>>

Re: SelectLayerByAttribute_analysis HELP!!

MichaelVolz — Fri, 10 Jan 2014 17:54:35 GMT

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.

Re: SelectLayerByAttribute_analysis HELP!!

DonaldFiore — Fri, 10 Jan 2014 17:56:57 GMT

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!

Re: SelectLayerByAttribute_analysis HELP!!

T__WayneWhitley — Fri, 10 Jan 2014 18:07:20 GMT

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.

Re: SelectLayerByAttribute_analysis HELP!!

Anonymous User — Sun, 12 Dec 2021 00:17:34 GMT

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') '''