Using SearchCursor for NULL values

[ChrisBrannin]

I am trying to use the search cursor to find all null values within a table but am having a hard time with the where-clause statement. How do i search for null values using searchcursor?

I have a small example of what I am trying to do:

import arcpy

in_workspace = r"C:/data
"
fields = ["A", "B", "C", "D", "E", "F", "G"]
where = fields = "None"

for dirpath, dirnames, filenames in arcpy.da.Walk(in_workspace, datatype="featureclass", type="Polyline"):
    for filename in filenames:
        with arcpy.da.SearchCursor(filename, fields, where) as cursor:
            for row in cursor:
                print filename, "has null values"

I get - RuntimeError: cannot open 'filename'

Thanks for any help and time/consideration.

[curtvprice]

I am trying to use the search cursor to find all null values within a table but am having a hard time with the where-clause statement. How do i search for null values using searchcursor?

Your where expression is poorly formed, and you missed a step in the arcpy.da.walk.

I would suggest avoiding the cursor altogether:

fields = ["A", "B", "C", "D", "E", "F", "G"]
where = ['"{0}" IS NULL'.format(f) for f in fields]
where = " OR ".join(where)
# where = "A" IS NULL OR "B" IS NULL OR ...

for dirpath, dirnames, filenames in arcpy.da.Walk(in_workspace, datatype="featureclass", type="Polyline"):
    for filename in filenames:
        path = os.path.join(dirpath, filename) # see example 2 in the help for arcpy.da.walk
        lyr = arcpy.MakeFeatureLayer_management(path, "lyr", where)
        rows = int(arcpy.GetCount_management(lyr).getOutput(0))
        if rows > 0: print filename, " has null values"

If you want to look at all fields no matter what they are named, you can try this inside the loop:

        fields = [f.name for f in arcpy.Describe(path).Fields]
        where = " OR ".join(['"{0}" IS NULL'.format(f) for f in fields])
        lyr = arcpy.MakeFeatureLayer_management(path, "lyr", where)

[JakeSkinner]

Hi Chris,

When querying NULL values, you will want to use the syntax "Field IS NULL".  You will also want to iterate through your fields list and set the query up for each field name.  Here is an example:

import arcpy
from arcpy import env
env.workspace = r"C:\temp\python\test.gdb"

fields = ["OBJECTID", "Name", "Address"]

for dirpath, dirnames, filenames in arcpy.da.Walk(env.workspace):
    for filename in filenames:
        for field in fields:
            where = field + " IS NULL"
            try:
                with arcpy.da.SearchCursor(filename, fields, where) as cursor:
                    for row in cursor:
                        print("OBJECTID {0}").format(row[0]) + " in " + filename + " has a NULL value in field " + field
            except RuntimeError:
                pass

del row, cursor

[ChrisBrannin]

Your where expression is poorly formed, and you missed a step in the arcpy.da.walk.

I would suggest avoiding the cursor altogether:

fields = ["A", "B", "C", "D", "E", "F", "G"]
where = ['"{0}" IS NULL'.format(f) for f in fields]
where = " OR ".join(where)
# where = "A" IS NULL OR "B" IS NULL OR ...

for dirpath, dirnames, filenames in arcpy.da.Walk(in_workspace, datatype="featureclass", type="Polyline"):
    for filename in filenames:
        path = os.path.join(dirpath, filename) # see example 2 in the help for arcpy.da.walk
        lyr = arcpy.MakeFeatureLayer_management(path, "lyr", where)
        rows = int(arcpy.GetCount_management(lyr).getOutput(0))
        if rows > 0: print filename, " has null values"

If you want to look at all fields no matter what they are named, you can try this inside the loop:

        fields = [f.name for f in arcpy.Describe(path).Fields]
        where = " OR ".join(['"{0}" IS NULL'.format(f) for f in fields])
        lyr = arcpy.MakeFeatureLayer_management(path, "lyr", where)

Thank you for taking a look and with the helpful script. I am running into an issue where it prints 'has null values' when there are no null values. Such that, a date column prints null values when they are not null, same with LAT/LON fields. It seems that is there is more than 1 field in the variable [fields] then it says that the last field in the list has null values. Any thoughts on this?

[ChrisBrannin]

Hi Chris,

When querying NULL values, you will want to use the syntax "Field IS NULL".  You will also want to iterate through your fields list and set the query up for each field name.  Here is an example:

import arcpy
from arcpy import env
env.workspace = r"C:\temp\python\test.gdb"

fields = ["OBJECTID", "Name", "Address"]

for dirpath, dirnames, filenames in arcpy.da.Walk(env.workspace):
    for filename in filenames:
        for field in fields:
            where = field + " IS NULL"
            try:
                with arcpy.da.SearchCursor(filename, fields, where) as cursor:
                    for row in cursor:
                        print("OBJECTID {0}").format(row[0]) + " in " + filename + " has a NULL value in field " + field
            except RuntimeError:
                pass

del row, cursor

Thank you for the suggestion and helpful script. When I run this on a larger folder (100+) it crashes IDLE. But ran nicely on only 5 or so folders. Thoughts?

[JillianPenney]

This is another simple way to find rows with Null values. You may want to make it more sophisticated by specifying the fields you want returned rather then using (*). Or add some of the functionality from the previous suggestions, depending on how much detail you need returned.

# Import the arcpy module
import arcpy

# Location of the feature class I want to update
gdb = "C:\\Data\\NothingSpecial.gdb\\"
fcs = ["calc","table1","etc"]

for item in fcs:
    fc = gdb + item

    # Create a cursor using arcpy data access module
    sc = arcpy.da.SearchCursor(fc, "*")
    print "Null values in feature class: "+fc.rsplit("\\",1)[1]
    print "==================================================="

    for row in sc:
        if None in row:
            print ("Null value in row {0}".format(row[0]))

[MathewCoyle]

Thank you for the suggestion and helpful script. When I run this on a larger folder (100+) it crashes IDLE. But ran nicely on only 5 or so folders. Thoughts?

This is very likely due to running out of memory during the process.

[ChrisBrannin]

@Jill: Thank you for the snippet. I will try this out.
@mzcoyle: I closed everything else out and ran it again, with no crashes. I found another issue though, good call on the memory. Thanks