我有一张表上的独特限制需要延迟,Postgresql支持这个功能,但是我无法在SQLAlchemy ORM中找到设置选项(不仅仅是针对这种情况)。我使用了
bulk_update_mappings()
函数,该限制出现在__table_args__
的第二个参数下。是否需要使用SQLAlchemy Core或创建自己的SQL语句来实现此功能?class Question(Base):
QuestionType = enum.Enum('QuestionType', 'mcq')
__tablename__ = 'questions'
id = Column(Integer, primary_key=True)
type = Column(Enum(_QuestionType), nullable=False)
description = Column(String, nullable=False)
question_order = Column(Integer, nullable=False)
question_set_id = Column(Integer, ForeignKey('question_sets.id', ondelete='cascade'), nullable=False)
question_set = relationship('QuestionSet', back_populates='questions')
__table_args__ = (
UniqueConstraint('question_set_id', 'description'),
UniqueConstraint('question_set_id', 'question_order', deferrable=True)
)
__mapper_args__ = {
'polymorphic_identity': 'question',
'polymorphic_on': type,
}
#from another class
def reorder(self, new_order, db):
order = [{'id':i, 'question_order': index} for index, i in enumerate(new_order)]
db.bulk_update_mappings(Question, order)
db.commit()
db.execute('SET CONSTRAINTS ALL DEFERRED')
以推迟当前事务中的所有可推迟约束:https://www.postgresql.org/docs/current/static/sql-set-constraints.html。如果您知道唯一约束的名称,可以选择仅推迟唯一约束。 - Ilja Everilädb.execute('SET CONSTRAINTS unique_order DEFERRED;')
。 - nav