For the life of me I cannot get a where clause to work in a cursor (search or update): fields = [ 'SampleDate' , 'SampleID' , 'OBJECTID' ] where = 'SampleDate is null' # do where clauses really work in a cursor? not for me. with arcpy . da . SearchCursor ( fc , fields , where ) as cursor : for row in cursor : s_date = str ( row [ 0 ] ) . split ( ' ' ) [ 0 ] mnth = s_date . split ( '-' ) [ 1 ] day = s_date . split ( '-' ) [ 2 ] yr = s_date . split ( '-' ) [ 0 ] timestamp ( ) newdate = '{}{}{}' . format ( yr , mnth , day ) pre = 'FP' exp = "'{} {} {}'" . format ( pre , newdate , new_time ) print '{} {} {}' . format ( row [ 1 ] , exp , row [ 2 ] ) When I include the where clause as shown I get no returns with my print. I've capitalized IS NULL, same result; I have used the same 'where' in an ArcMap interactive selection just fine. I gotta be missing something slight....

Have you tried adding a field delimiter to the field you're querying? fields = [ 'SampleDate' , 'SampleID' , 'OBJECTID' ] qry_field = arcpy . AddFieldDelimiters ( source , 'SampleDate' ) where = '{} IS NULL' . format ( qry_field )

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Where clause in da Cursor

JoeBorgione

For the life of me I cannot get a where clause to work in a cursor (search or update):

fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'SampleDate'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'SampleID'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'OBJECTID'</SPAN><SPAN class="punctuation token">]</SPAN>
where <SPAN class="operator token">=</SPAN> <SPAN class="string token">'SampleDate is null'</SPAN> <SPAN class="comment token"># do where clauses really work in a cursor?&nbsp; not for me.</SPAN>
<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>fc<SPAN class="punctuation token">,</SPAN>fields<SPAN class="punctuation token">,</SPAN>where<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; s_date <SPAN class="operator token">=</SPAN> str<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>split<SPAN class="punctuation token">(</SPAN><SPAN class="string token">' '</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; mnth <SPAN class="operator token">=</SPAN> s_date<SPAN class="punctuation token">.</SPAN>split<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'-'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; day <SPAN class="operator token">=</SPAN> s_date<SPAN class="punctuation token">.</SPAN>split<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'-'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; yr <SPAN class="operator token">=</SPAN> s_date<SPAN class="punctuation token">.</SPAN>split<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'-'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; timestamp<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; newdate <SPAN class="operator token">=</SPAN> <SPAN class="string token">'{}{}{}'</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>yr<SPAN class="punctuation token">,</SPAN>mnth<SPAN class="punctuation token">,</SPAN>day<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; pre <SPAN class="operator token">=</SPAN> <SPAN class="string token">'FP'</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; exp <SPAN class="operator token">=</SPAN> <SPAN class="string token">"'{} {} {}'"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>pre<SPAN class="punctuation token">,</SPAN>newdate<SPAN class="punctuation token">,</SPAN>new_time<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="string token">'{}&nbsp; {}&nbsp; {}'</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN>exp<SPAN class="punctuation token">,</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN><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>‍</SPAN><SPAN>‍</SPAN></SPAN>

When I include the where clause as shown I get no returns with my print. I've capitalized IS NULL, same result; I have used the same 'where' in an ArcMap interactive selection just fine. I gotta be missing something slight....

Find more posts tagged with

Accepted answers

MitchHolley1

Have you tried adding a field delimiter to the field you're querying?

fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'SampleDate'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'SampleID'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'OBJECTID'</SPAN><SPAN class="punctuation token">]</SPAN>
qry_field <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>AddFieldDelimiters<SPAN class="punctuation token">(</SPAN>source<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'SampleDate'</SPAN><SPAN class="punctuation token">)</SPAN>
where <SPAN class="operator token">=</SPAN> <SPAN class="string token">'{} IS NULL'</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>qry_field<SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

All comments

JoeBorgione

Mine is a couple miles down Little Cottonwood Cyn from Snowbird in a drainage called Maybird Gulch.

MitchHolley1

Hahaha! I just realized we have almost the same picture. No, it's actually at Breckenridge atop Peak 10. Hoping to make the trek back out there soon!

JoeBorgione

Mitch Holley‌; that works well. Thank you. (BTW, you avatar: is that at the top of the Little Cloud lift at Snowbird?)

bixb0012

I use WHERE clauses with cursors fairly regularly, so I know they work. What if you try "WHERE 1=1" and "WHERE 1=2". The first should retrieve all the records while the second should retrieve no records.

JoeBorgione

Sorry; enterprise geodatabase, 10.5.1, sql server backend, arcgis desktop 10.5.1; using python 2.7 for this.

This particular code is just working against a feature class, but when I run it against a feature layer (my s.o.p.) I get the same results; seems like when I run the same sort of thing on a fgdb (same version) I get the same mis-behavior. If I understand the point of a where clause, it acts like a filter so only those records that meet the where clause criteria are scanned with the cursor. If I drop the where clause from the cursor from the above snippet, the print statement returns all the records, as it should.

bixb0012

What version of ArcMap and what time of back-end data store, i.e., shapefile, file geodatabase, enterprise geodatabase, etc....

MitchHolley1

Have you tried adding a field delimiter to the field you're querying?

fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'SampleDate'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'SampleID'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'OBJECTID'</SPAN><SPAN class="punctuation token">]</SPAN>
qry_field <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>AddFieldDelimiters<SPAN class="punctuation token">(</SPAN>source<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'SampleDate'</SPAN><SPAN class="punctuation token">)</SPAN>
where <SPAN class="operator token">=</SPAN> <SPAN class="string token">'{} IS NULL'</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>qry_field<SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.