Setting column as None does not explicitly add the NULL to insert statement
Issue #3794
duplicate
The following code illustrates this problem:
import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
engine = sa.create_engine(
'postgres://postgres@localhost/test'
)
engine.echo = True
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()
class Question(Base):
__tablename__ = 'question'
id = sa.Column(sa.Integer, primary_key=True)
weight = sa.Column(sa.Integer, default=1)
Base.metadata.create_all(bind=session.bind)
q = Question(weight=None)
session.add(q)
session.commit()
assert q.weight is None
The problem also exists by changing the default=1 to server_default='1'. Also in this case the explicit weight=NULL is not added to INSERT statement.
Comments (5)
-
repo owner
-
repo owner
You know I knew we handled this and I thought the "render_nulls" was where we did this, but that led me to forget we have another setting too that is at the column level which IMO this is the best we can do:
import sqlalchemy as sa from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.declarative import declarative_base engine = sa.create_engine( 'postgres://postgres@localhost/test' ) engine.echo = True Base = declarative_base() Session = sessionmaker(bind=engine) session = Session() class Question(Base): __tablename__ = 'question' id = sa.Column(sa.Integer, primary_key=True) weight = sa.Column(sa.Integer().evaluates_none(), default=1) Base.metadata.create_all(bind=session.bind) q = Question(weight=None) session.add(q) session.commit() assert q.weight is None -
repo owner
- changed status to duplicate
Duplicate of
#3250. -
repo owner
-
reporter
Thanks for the quick response and explaining this thoroughly.
evaluates_none()solves this for me. - Log in to comment
Here's the reason we don't do that:
Changing this in place would break the (IMO, probably very large) number of applications that don't want NULL in there but have situations where None is being explicitly set. Some will have a NOT NULL constraint that will fail, others won't and will just have silent failure of their default generator.
Turning off the attribute in place:
leads to these failures, I haven't evaluated which of these are "this is just what we expect" vs. "this is a thing that can no longer work":
you will note there's a flag in there called "render_nulls". This is a flag I added recently to help the use case of "bulk inserts", where a large set of inserts need to have the same parameters in order for them to be batched. This flag isn't used by the normal flush() process right now, however. At best there would be some path to enabling this for a flush in some case.
There is a workaround for this, which is to set your value to "sa.null()" instead. This s an explicit NULL and you'll see NULL rendered in the INSERT (it isn't passed as a parameter however).