topic Re: Writing IS NULL/IS NOT NULL SQL statements in python/acrpy in Python Questions

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

DoratheaBlock — Thu, 06 Jun 2019 17:29:25 GMT

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.

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

JoshuaBixby — Fri, 10 Dec 2021 20:12:27 GMT

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.

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

JoshuaBixby — Thu, 06 Jun 2019 18:02:25 GMT

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

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

JoeBorgione — Fri, 10 Dec 2021 20:12:30 GMT

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

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

RandyBurton — Fri, 10 Dec 2021 20:12:33 GMT

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.

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

DoratheaBlock — Fri, 07 Jun 2019 22:46:47 GMT

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

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

DoratheaBlock — Fri, 07 Jun 2019 23:17:49 GMT

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?

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

JoeBorgione — Sat, 08 Jun 2019 02:18:45 GMT

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

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

RandyBurton — Fri, 10 Dec 2021 20:12:35 GMT

Assuming you are trying to determine

if any value/row in any of the fields are NULL

you can try something like:

import arcpy

inFeature = r'C:\path\to\file.gdb\SCRIPT_PARAMETER_TEST' # path to feature
fields = ['Prov', 'Rd_symbol', 'Name_type' ]

# create where_clause
wc = []
for f in fields:
    wc.append('{} IS NULL'.format(f)) # search for null fields
where_clause = ' OR '.join(wc) # join to make OR statement
print where_clause # print where clause for test
# Prints: 'Prov IS NULL OR Rd_symbol IS NULL OR Name_type IS NULL'

rows = [row for row in arcpy.da.SearchCursor(inFeature, fields, where_clause)]
if len(rows): # if len(rows) > 0
    print('{} has null values'.format(inFeature))
else:
    print('{} does not have null values'.format(inFeature)) # but may also be empty feature
del rows‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

You could also use Select Layer By Attribute and Get Count in place of the search cursor (lines 14+).

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

JoshuaBixby — Mon, 10 Jun 2019 13:45:35 GMT

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

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

JoshuaBixby — Fri, 10 Dec 2021 20:12:38 GMT

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