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

• Vince: I'd have to think that was designed behavior. Setting a default value tells the software you won't accept a NULL, and to use that instead. How could it possibly know you mean "except in this case"?
• Richard Morgan: @Vince Interesting thought! Oddly, on the InsertCursor, if the field is not specified, the value is null, which is where I would think it will fill in the default instead.
• Vince: If you had set the default value in Oracle, certainly, but it takes a sophisticated insert to realize it needs to add unspecified columns.
• Vince: You could create a trigger to convert "Q" to a NULL, then set "Q" when you mean NULL.

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)