Explicitly set value of column to NULL when there is a default set on the Column in SqlAlchemy

Question

I have a table defined like:

class Thing(Base):
    __tablename__ = "things"

    my_field = Column(Text, nullable=True, default=lambda c: "default")

(In my actual implementation the default function is more complicated, but I thought it pertinent to say I'm using a function to generate the default value.)

I would like to be able to explicitly set the value of my_field to NULL in some cases. I have tried:

thing = Thing(my_field=None)

But upon the Session.add(thing); Session.commit() the default value is generated by the function and writes over my explicit None. Is there a way to use the default in general but occasionally explicitly set Null for the column?

Answer 1

This is due to a quirk in the SQLAlchemy ORM. Even if you explicitly set the value to None on a column with a default, it is treated as if no value was set. You need to use the null SQL construct in order to work around this issue. See Forcing NULL on a column with a default for more detail.

from sqlalchemy import null

thing = Thing(my_field=null())