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

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

  • 2
    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"? – Vince Mar 8 '16 at 19:17
  • @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. – Richard Morgan Mar 8 '16 at 19:24
  • 1
    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 Mar 8 '16 at 19:32
  • 2
    You could create a trigger to convert "Q" to a NULL, then set "Q" when you mean NULL. – Vince Mar 8 '16 at 19:34

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.