topic Re: Where or Select clause in Python Questions

Where or Select clause

JoeBorgione — Sat, 11 Dec 2021 12:38:35 GMT

Seems like I've had this problem before, but I can't find the solution searching in my past posts.

I need to step through a list of alpha site-ids, and make a selection to a table view based on the given value of the list element. Something like this:

my_list = ['BC_00.1', 'BC_00.2', 'BC_00.3']

for i in my_list:
  select = 'SITE_ID = {}'.format(i) ### <--- problem child
  arcpy.SelectLayerByAttribute_management(tableview,"NEW_SELECTION",select)‍‍‍‍‍‍‍‍‍‍

but my select variable is set up wrong in line 4 so I get barked at that line 5 has a syntax error. It's question of where single and double quotes go in line 4, and for the life of me I just can't seem to get it. (Could it be the last 10 days in the Mexican Riviera? Forgetting code snippets is the new Montezuma's Revenge. I once forgot my zip code after a trip to Mexico; that was great vacation...)

Re: Where or Select clause

DarrenWiens2 — Sat, 11 Dec 2021 12:38:38 GMT

It's single quotes around strings. Best guess:

sql = "STATE_NAME = '{}'".format('California')
print(sql)

STATE_NAME = 'California'‍‍‍‍

Bookmark: SQL reference for query expressions used in ArcGIS—ArcGIS Pro | ArcGIS Desktop

Re: Where or Select clause

JoeBorgione — Sat, 11 Dec 2021 12:38:40 GMT

Darren- in your example you have a quoted value for the format() method. My problem is I'm handing a text element of a list off to format() and somehow I need to get that value quoted:

>>>sig_list[0]
u'BC_00.61'

>>> print sig_list[0]
BC_00.61

>>> for i in sig_list:
...     select = "SITE_ID = '{}'".format(i)
...     arcpy.SelectLayerByAttribute_management(tv,"NEW_SELECTION",select)
...     
Runtime error  Traceback (most recent call last):   File "<string>", line 3, in <module>   File "c:\program files (x86)\arcgis\desktop10.5\arcpy\arcpy\management.py", line 7744, in SelectLayerByAttribute     raise e ExecuteError: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute). 
 
>>> select
"SITE_ID = 'BC_00.61'"
# need to drop the bounding double quotes, but keep the inner single quotes.... 
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Still hacking and whacking on it....

edited moments later to add:

>>> for i in sig_list:
...     i = "'{}'".format(i)
...     select = "SITE_ID = "+i
...     arcpy.SelectLayerByAttribute_management(tv,"NEW_SELECTION",select)
...     
Runtime error  Traceback (most recent call last):   File "<string>", line 4, in <module>   File "c:\program files (x86)\arcgis\desktop10.5\arcpy\arcpy\management.py", line 7744, in SelectLayerByAttribute     raise e ExecuteError: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute).  
>>> select
"SITE_ID = 'BC_00.61'"
>>> print select
SITE_ID = 'BC_00.61'

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

So this tells me that the print statement strips the double quotes, but how do I pass my select variable sans double quotes to arcpy.SelectLayerByAttribute_management() and keep it happy?

Re: Where or Select clause

DarrenWiens2 — Tue, 11 Sep 2018 21:18:41 GMT

I guess triple quotes are the recommended method. Try:

select = """SITE_ID = '{}'""".format(i)

From: Specifying a query in Python—ArcPy Get Started | ArcGIS Desktop

Re: Where or Select clause

JoeBorgione — Tue, 11 Sep 2018 21:24:37 GMT

Dang it.... You saw my reply just before I tried deleting it. It all boiled down to a dumb mistake on my part. I was spelling the attribute name wrong in the arcpy.SelectLayerByAttribute_management() all along. I've had some stoopid mistakes but this one just might top the list....