Select By Query from Items in Python List

MitchHolley1

I'd like to create a SQL query statement which can select by multiple items in a Python string.  

Can this be done?

lyr <SPAN class="operator token">=</SPAN> r<SPAN class="string token">''</SPAN>
my_list <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'12'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'13'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'14'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'15'</SPAN><SPAN class="punctuation token">]</SPAN>

<SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> my_list<SPAN class="punctuation token">:</SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
&nbsp;&nbsp;&nbsp; qry <SPAN class="operator token">=</SPAN> <SPAN class="string token">"OBJECTID"</SPAN> <SPAN class="operator token">+</SPAN> i

arcpy<SPAN class="punctuation token">.</SPAN>SelectLayerByAttribute_management<SPAN class="punctuation token">(</SPAN>lyr<SPAN class="punctuation token">,</SPAN> qry<SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

The problem I'm having is formatting the list properly so it won't trip up the SQL statement.  Should I create a new list with the items separated by a "+" character?

BradCollins1

Have you tried using the SQL "IN" Operator?

SQL IN Operator

http://www.w3schools.com/sql/sql_in.asp 

<SPAN class="comment token"># Path to the Layer</SPAN>
lyr <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"<NAME_OF_LAYER>"</SPAN>

<SPAN class="comment token"># Integer list of OBJECTID(s)</SPAN>
oid <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="number token">12</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">13</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">14</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">15</SPAN><SPAN class="punctuation token">]</SPAN>

<SPAN class="comment token"># SQL Query</SPAN>
sql <SPAN class="operator token">=</SPAN> <SPAN class="string token">"{} IN ({})"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"OBJECTID"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">", "</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>str<SPAN class="punctuation token">(</SPAN>n<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">for</SPAN> n <SPAN class="keyword token">in</SPAN> oid<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="comment token"># Tool Execution</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>management<SPAN class="punctuation token">.</SPAN>SelectLayerByAttribute<SPAN class="punctuation token">(</SPAN>lyr<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"NEW_SELECTION"</SPAN><SPAN class="punctuation token">,</SPAN> sql<SPAN class="punctuation token">)</SPAN>‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

The above format and list comprehension would yield the following string:

'OBJECTID IN (12, 13, 14, 15)'

The trick to this is that you need to create a string that represents valid SQL Syntax. This can be done is quite a few ways. I've listed a few quick options below.

<SPAN class="comment token"># A few possible SQL Queries</SPAN>
sql <SPAN class="operator token">=</SPAN> <SPAN class="string token">" OR "</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="string token">"OBJECTID = {}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>n<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">for</SPAN> n <SPAN class="keyword token">in</SPAN> oid<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="comment token"># => OBJECTID = 12 OR OBJECTID = 13 OR OBJECTID = 14 OR OBJECTID = 15</SPAN>

sql <SPAN class="operator token">=</SPAN> <SPAN class="string token">"{0} >= {1} AND {0} <= {2}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"OBJECTID"</SPAN><SPAN class="punctuation token">,</SPAN> min<SPAN class="punctuation token">(</SPAN>oid<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> max<SPAN class="punctuation token">(</SPAN>oid<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="comment token"># => OBJECTID >= 12 AND OBJECTID <= 15</SPAN>

sql <SPAN class="operator token">=</SPAN> <SPAN class="string token">"{} BETWEEN {} AND {}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"OBJECTID"</SPAN><SPAN class="punctuation token">,</SPAN> min<SPAN class="punctuation token">(</SPAN>oid<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> max<SPAN class="punctuation token">(</SPAN>oid<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="comment token"># => OBJECTID BETWEEN 12 AND 15</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>