nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Find more posts tagged with

Accepted answers

All comments

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

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.

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

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?

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?

jskinner

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

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)

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.