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 " + nulwith 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.
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.
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?
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)
A better place for this question would be Python or Geodatabase
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)
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)?
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...
Joe Borgione, I am tied up with family the next couple days, feel free to chime in.
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.