topic Where clause in da Cursor in Python Questions

Where clause in da Cursor

JoeBorgione — Sat, 11 Dec 2021 21:52:20 GMT

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

Re: Where clause in da Cursor

MitchHolley1 — Sat, 11 Dec 2021 21:52:23 GMT

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

Re: Where clause in da Cursor

JoshuaBixby — Tue, 04 Dec 2018 15:50:18 GMT

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

Re: Where clause in da Cursor

JoeBorgione — Tue, 04 Dec 2018 16:02:22 GMT

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.

Re: Where clause in da Cursor

JoshuaBixby — Tue, 04 Dec 2018 16:39:19 GMT

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.

Re: Where clause in da Cursor

JoeBorgione — Tue, 04 Dec 2018 17:25:48 GMT

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

Re: Where clause in da Cursor

MitchHolley1 — Tue, 04 Dec 2018 18:00:46 GMT

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!

Re: Where clause in da Cursor

JoeBorgione — Tue, 04 Dec 2018 18:06:30 GMT

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