Where or Select clause

982
4
Jump to solution
09-10-2018 03:38 PM
JoeBorgione
MVP Emeritus

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

That should just about do it....
0 Kudos
1 Solution

Accepted Solutions
DarrenWiens2
MVP Honored Contributor

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 

View solution in original post

4 Replies
DarrenWiens2
MVP Honored Contributor

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 

JoeBorgione
MVP Emeritus

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?

That should just about do it....
0 Kudos
DarrenWiens2
MVP Honored Contributor

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 

0 Kudos
JoeBorgione
MVP Emeritus

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

That should just about do it....