Writing IS NULL/IS NOT NULL SQL statements in python/acrpy

[DoratheaBlock]

I am trying to write a search cursor that looks through the fields in a Feature Class and if there is a NULL value in any of the fields prints the statement "NULL values present". I am having trouble getting my where_clause to work. 

Here is what I have:

import arcpy

inFeature = 'SCRIPT_PARAMETERS_TEST'

inField = 'Prov'  
inField1 = 'Rd_Symbol'
inField2 = 'Name_Type'

nul = 'NULL'
where_clause = inField + " IS " + nul + " or " + inField1 + " IS " + nul + " or " + inField2 + " IS " + nul

with arcpy.da.SearchCursor(inFeature, (inField, inField1, inField2), where_clause) as cursor:
print("NULL values present")

The code runs however, there are two problems. First it prints NULL values present always, even when I change the fields to fields that are fully populated. Second I feel like there is probably a cleaner way to write the where clause... I have looked into things like:

where_clause = 'field_name' is None

where_clause =  'field_name' is 'NULL'

where_clause =  'field_name' IS Null'

but have not had success either getting a syntax error message. Or for 'field_name' is None there was no error message but when I checked the statement this is what I get:

>>>> print(where_clause)

FALSE

If you have any suggestions or if there is a resource anyone recommends to help better understand writing SQL statements with python/arcpy that would be much appreciated. 

[JoshuaBixby]

Looking over the end of your code:

with arcpy.da.SearchCursor(inFeature, (inField, inField1, inField2), where_clause) as cursor:
 print("NULL values present")‍‍

Assuming the indentation error is just a copy/paste artifact, where/how are you looping over the cursor?  You are creating the cursor and then just printing a single line, and that line will always print if the cursor is instantiated, regardless of how many records are in the cursor.

[DoratheaBlock]

It does seem to be an issue with not looping over the cursor because when I do loop over the cursor the it returns nothing, like I am expecting.

with arcpy.da.SearchCursor(inFeature, (inField, inField1, inField2), where_clause2) as cursor:

    for row in cursor:

        count+=1

        print("ANTHOER NULL VALUE")

print(count)

0

>>>

when I do not loop over the cursor:

count = 0

with arcpy.da.SearchCursor(inFeature, (inField, inField1, inField2), where_clause2) as cursor:

        count+=1 # indentation is 2x Tab

        print("ANTHOER NULL VALUE")

print(count)

ANTHOER NULL VALUE

1

>>>

>>> count = 0

with arcpy.da.SearchCursor(inFeature, (inField, inField1, inField2), where_clause2) as cursor:

    count+=1 ## indentation is 1x Tab

    print("ANOTHER NULL VALUE")

print(count)

ANOTHER NULL VALUE

1

>>>

I was under the impression that you didn't have to loop over a cursor for it to work but maybe I was misinformed... Do you always loop over a cursor?

[JoshuaBixby]

As Joe already mentioned, you have to loop over a cursor to do anything with it. 

Revisiting the first statement of your original post:

I am trying to write a search cursor that looks through the fields in a Feature Class and if there is a NULL value in any of the fields prints the statement "NULL values present".

Another approach would be to do it all in Python:

import arcpy

fc = # path to feature class
flds = # list of fields to check for NULL

with arcpy.da.SearchCursor(fc, ["OID@"] + flds) as cur:
    for row in cur:
        if None in row[1:]:
            print(row)
            ‍‍‍‍‍‍‍‍‍‍

[JoshuaBixby]

A better place for this question would be Python‌ or Geodatabase‌

[JoeBorgione]

 First it prints NULL values present always, even when I change the fields to fields that are fully populated. 

Could that be a result of using 'OR'?  Basically you are asking 'if one or more of these 3 fields is null'.

I've struggled with where clauses myself: in this thread using field delimeters was suggested and helped immensely.

In terms of a 'cleaner' where clause, I prefer using the format() function over the use of '+':

import arcpy

inFeature = 'SCRIPT_PARAMETERS_TEST'

inField = 'Prov'  
inField1 = 'Rd_Symbol'
inField2 = 'Name_Type'

 

#nul = 'NULL'
#where_clause = inField + " IS " + nul + " or " + inField1 + " IS " + nul + " or " + inField2 + " IS " + nul

where_clause = '{} IS NULL or {} IS NULL or {} IS NULL'.format(inField,inField1,inField2)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[DoratheaBlock]

 By 'Basically you are asking 'if one or more of these 3 fields is null' do you mean that I am testing if the field exists or not (that would not be what I am attempting to do) or if  any value/row in any of the fields are NULL (what I am trying to do)?

[JoeBorgione]

The latter. You know the fields exist;  you are checking the values. What I am suggesting is sometimes we need to be careful for what we ask for.   With the OR  condition all you need is one of those three fields to be the <Null> value (None in pythonese) and your Where statement evaluates to true.   I think you and Joshua Bixby‌ have a good thing going. I'll watch from sidelines...

[JoshuaBixby]

Joe Borgione‌, I am tied up with family the next couple days, feel free to chime in.

[RandyBurton]

The quoting of your where clause is incorrect.  Try:

# if field_name is the name of the field:

where_clause = "field_name is NULL"

# if field_name is a variable containing a field name:

field_name = 'myFieldName' # the name of the field

where_clause = "{} is NULL".format(field_name)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

That said,  I'm not sure if you want to indicate if a feature contains any field where there is a null, or if you want to find rows in the feature that has a field with a null value.  See this thread for some additional ideas: Calculate number of NULL values in a feature dataset.