Where clause in da Cursor

1164
7
Jump to solution
12-04-2018 07:23 AM
JoeBorgione
MVP Emeritus

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

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

Accepted Solutions
MitchHolley1
MVP Regular Contributor

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)

View solution in original post

7 Replies
MitchHolley1
MVP Regular Contributor

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)
JoshuaBixby
MVP Esteemed Contributor

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

0 Kudos
JoeBorgione
MVP Emeritus

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.

That should just about do it....
0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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
MVP Emeritus

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

That should just about do it....
MitchHolley1
MVP Regular Contributor

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!

0 Kudos
JoeBorgione
MVP Emeritus

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

That should just about do it....