Insert (or Update) Cursor setting value to None not honored when field has a default value

Question

To set a field to null, the InsertCursor or UpdateCursor is setting the value to None.

Here is a sample for the UpdateCursor:

with arcpy.da.UpdateCursor(test_fc, ['SERVICEINDICATOR'], """"OBJECTID" = 1""") as uCur:
for row in uCur:
    row[0] = None
    uCur.updateRow(row)

The field allows nulls but has a default value. When setting the value to null, it always sets to the default.

In other words, if the default is 'N', setting the field to None results in the row having 'N'.

How can the cursors respect the None/Null request?

This is an Oracle SDE database (so the answer to arcpy.UpdateCursor setting value to 0 instead of Null does not apply) and ArcMap 10.1

Answer 1

This behavior is according to design.

Keep in mind that there are at least two places where defaults can be applied:

• In ArcObjects through the field definition
• In the RDBMS at the column definition

DA UpdateCursor is interacting with the former, so when you pass in a None, ArcObjects sees this, notices that the field has a default, and assigns the default value to the column. If the default-laden field is not on the column list, ArcObjects does not look at the value, so a NULL results (unless the database has a default, or a NOT NULL column is not referenced in the insert, at which point an error would be raised).

The only way to immediately get a NULL added to a row where the field has a default is to use an insert trigger, so that each time the third value of the legal domain {'Y','N','Q'} appears, a NULL is populated where the Q would have been. If some delay is permitted between insert and availability, then there are a large number of options for doing batch updates.

Answer 2

This behavior with ArcPy DA cursors was caused by BUG-000091314: When using the data access module and an update curs... Esri finally addressed this defect in Pro 3.1 through the introduction of a new explicit parameter in the ArcPy DA InsertCursor - ArcGIS Pro | Documentation and UpdateCursor - ArcGIS Pro | Documentation.

Modifying the OP's code using the new parameter to force nullable fields to accept NULL:

with arcpy.da.UpdateCursor(test_fc, ['SERVICEINDICATOR'], """"OBJECTID" = 1""", explicit=True) as uCur:
    for row in uCur:
        row[0] = None
        uCur.updateRow(row)