在设置了default和server_default的情况下，当row包含null时，session.merge为什么抛出IntegrityError

Question

当使用sqlalchemy和服务器默认值将字段设置为不可为空时，我正在尝试使用session.merge(object)更新记录。当它试图更新现有记录时，会抛出一个错误，表明违反了not null约束。我可以通过从表初始化中的记录中删除null值来解决这个问题，但这似乎很奇怪。这是意料之中的，还是我不了解服务器默认设置或session.merge的工作原理？

Python版本: 3.6.8

sqlalchemy版本: 1.3.4

示例代码：

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base 

Base = declarative_base()       

class TestTable(Base):

    __tablename__ = 'test_table'

    id = sa.Column(sa.Integer, primary_key=True)
    field = sa.Column(sa.String, default='Unknown', server_default=sa.text("'Unknown'"), nullable=False)
    field2 = sa.Column(sa.Integer)

engine = sa.create_engine('postgresql://postgres@localhost/test')

Base.metadata.create_all(bind=engine)

session = sa.orm.sessionmaker(engine)() 

record = {'id': 1, 'field': None, 'field2': 3}
session.merge(TestTable(**record))

session.commit()

session.merge(TestTable(**record))

session.commit()

抛出错误：

IntegrityError: (psycopg2.errors.NotNullViolation) null value in column "field" violates not-null constraint
DETAIL:  Failing row contains (1, null, 3).

[SQL: UPDATE test_table SET field=%(field)s WHERE test_table.id = %(test_table_id)s]
[parameters: {'field': None, 'test_table_id': 1}]
(Background on this error at: http://sqlalche.me/e/gkpj)

Answer 1

在第一次合并时，该行不存在，因此创建它：

record = {'id': 1, 'field': None, 'field2': 3}
session.merge(TestTable(**record))

...emits：

INSERT INTO test_table (id, field, field2) VALUES (%(id)s, %(field)s, %(field2)s)
{'id': 1, 'field': 'Unknown', 'field2': 3}

根据the documentation，在insert时将default参数应用于Column，这就是为什么在随insert语句一起发送的参数集中，None已被'Unknown'替换的原因。文档状态：

表示该列的默认值的标量、Python或ColumnElement表达式，如果在insert的VALUES子句中未指定该列，则在插入时将调用该表达式。

...so在这种情况下，"not specified“似乎应该解释为"not None"，因为您已经在record dict中明确地”指定“了值。与此相关的是，当您在没有为任何属性提供任何值的情况下实例化一个实例时，这些属性已经隐式地持有None的值：

print(TestTable().field is None)  # True

如果您从列定义中删除了default=... kwarg，即使您为TestTable构造函数提供了field=None，删除了默认值，该参数的值也不会发送到数据库：

INSERT INTO test_table (id, field2) VALUES (%(id)s, %(field2)s)
{'id': 1, 'field2': 3}

在这种情况下，服务器端默认值将触发。因此，在刷新insert语句之前，SQLAlchemy将交换客户端默认为指定值的列的任何None值。

在第二次合并时，行已经存在于数据库中，因此合并实例的值被填充到表示数据库中已经存在的行的实例上。在合并过程中，该实例上的field列的值从'Unknown'更改为None。值的这种更改意味着该字段的新值将作为更新查询的一部分发送到数据库。请注意，客户端和服务器端默认值都不适用于update语句。

UPDATE test_table SET field=%(field)s WHERE test_table.id = %(test_table_id)s
{'field': None, 'test_table_id': 1}

当我们尝试将NOT NULL列显式设置为NULL时，IntegrityError被引发。

Answer 2

我不太熟悉sqlalchemy，但在我看来，问题在于您误解了表定义中默认值的功能。指定的默认不是用代替null。如果列不在值列表中，则在insert中替换默认值；如果使用关键字default，则在insert/update中替换默认值。例如，在psql中尝试以下命令(抱歉，我无法转换为sqlalchemy)：

 create table txx (id serial, col1 text, col2 text default 'xxx'); 
 insert into txx(col1) values ('abcd');
 insert into txx(col1,col2) values ('defg',null), ('hijk', DEFAULT);
 select *  from txx;

 update txx set col2 = DEFAULT where col2 is null;
 select *  from txx;

另一方面，总是应用NOT NULL约束。此外，非空约束和默认值是完全相互独立的。希望这能有所帮助。