SQLAlchemy ORM - Deferring constraint checking

Question

I have a unique constraint on one of my tables that I would like to defer, which is something Postgresql supports from what I understand, but I can't seem to find where I can tell my operation to do so in SQLAlchemy when using the ORM (in general, not just specifically this case). I am using the bulk_update_mappings() function, and the constraint is the 2nd under __table_args__. Is this something I need to use SQLAlchemy Core or create my own SQL statement to achieve?

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()

Answer 1

Given that db is your session instance, run

db.execute('SET CONSTRAINTS ALL DEFERRED')

before your bulk operation in order to defer all deferrable constraints in the current transaction. Note that not all constraints are deferrable, even if they're declared as such. You could opt to defer the unique constraint only, if you know its name, such as unique_order:

def reorder(self, new_order, db):
    order = [{'id':i, 'question_order': index} for index, i in enumerate(new_order)]
    db.execute('SET CONSTRAINTS unique_order DEFERRED')
    db.bulk_update_mappings(Question, order)
    db.commit()