Sqlalchemy中的UniqueConstraint阻止更新

Question

在我的sqlalchemy模型定义中，我对image_uid和priority这两列设置了唯一约束，具体如下：

class GalleryImage(base.Base):
     __tablename__ = 'gallery_image'

    gallery_uid = sqlalchemy.Column(sqlalchemy.Unicode(32), sqlalchemy.ForeignKey('gallery.uid'), primary_key=True)
    image_uid = sqlalchemy.Column(sqlalchemy.Unicode(32), sqlalchemy.ForeignKey('image.uid'), primary_key=True)
    priority = sqlalchemy.Column(sqlalchemy.Integer, nullable=False)

    image = sqlalchemy.orm.relationship("Image", backref='galleries')

    __table_args__ = (
        sqlalchemy.UniqueConstraint('gallery_uid', 'priority', name='image_priority_unique'),
    )

一切运行得很好，除了当画廊有几张图片时，我尝试重新排序它们的情况。

举个例子。

之前：

uid: 1, priority: 1
uid: 2, priority: 2
uid: 3, priority: 3

之后：

uid: 1, priority: 3
uid: 2, priority: 2
uid: 3, priority: 1

从代码上看，操作是这样做的：

priority = len(gallery.images)

for item in gallery.images:
    item.priority = priority
    priority -= 1

    Session.add(item)

我的sql.log使用了事务，所以我理解这应该是一次性完成的，并且优先级应该被交换：

[2015-11-19 13:25:43] BEGIN (implicit)
[2015-11-19 13:25:43] SELECT image.uid AS image_uid, image.user_uid AS image_user_uid, image.upload_session_id AS image_upload_session_id, image.title AS image_title, image.description AS image_description, image.created_at AS image_created_at, image.updated_at AS image_updated_at FROM image WHERE image.uid = %(uid_1)s
[2015-11-19 13:25:43] {'uid_1': u'5'}
[2015-11-19 13:25:43] {}
[2015-11-19 13:25:43] SAVEPOINT sa_savepoint_1
[2015-11-19 13:25:43] {}
[2015-11-19 13:25:43] SELECT gallery.uid AS gallery_uid, gallery.user_uid AS gallery_user_uid, gallery.title AS gallery_title, gallery.gallery_type AS gallery_gallery_type, gallery.description AS gallery_description, gallery.ad_tag AS gallery_ad_tag, gallery.ad_path AS gallery_ad_path, gallery.ad_zone AS gallery_ad_zone, gallery.created_at AS gallery_created_at, gallery.updated_at AS gallery_updated_at FROM gallery WHERE gallery.uid = %(uid_1)s
[2015-11-19 13:25:43] {'uid_1': u'7'}
[2015-11-19 13:25:43] SELECT image.uid AS image_uid, image.user_uid AS image_user_uid, image.upload_session_id AS image_upload_session_id, image.title AS image_title, image.description AS image_description, image.created_at AS image_created_at, image.updated_at AS image_updated_at FROM image WHERE image.uid = %(uid_1)s
[2015-11-19 13:25:43] {'uid_1': u'5'}
[2015-11-19 13:25:43] SELECT gallery_image.gallery_uid AS gallery_image_gallery_uid, gallery_image.image_uid AS gallery_image_image_uid, gallery_image.priority AS gallery_image_priority FROM gallery_image WHERE %(param_1)s = gallery_image.gallery_uid ORDER BY gallery_image.priority
[2015-11-19 13:25:43] {'param_1': u'7'}
[2015-11-19 13:26:08] UPDATE gallery_image SET priority=%(priority)s WHERE gallery_image.gallery_uid = %(gallery_image_gallery_uid)s AND gallery_image.image_uid = %(gallery_image_image_uid)s
[2015-11-19 13:26:08] {'priority': 3, 'gallery_image_gallery_uid': u'7', 'gallery_image_image_uid': u'3'}
[2015-11-19 13:26:08] ROLLBACK TO SAVEPOINT sa_savepoint_1
[2015-11-19 13:26:08] {}
[2015-11-19 13:26:08] ROLLBACK

请给点建议。

相关文章：

• 暂无相关问题

Answer 1

经过几个小时的尝试不同的方法，我发现其实问题不在于sqlalchemy或者ORM，而是UniqueConstraint的定义有问题。

它应该像这样（针对postgres数据库）：

sqlalchemy.UniqueConstraint('gallery_uid', 'priority', name='image_gallery_unique', deferrable=True, initially="DEFERRED")

就像这个文档所说的：

http://www.postgresql.org/docs/9.1/static/sql-set-constraints.html

还有这个：

http://docs.sqlalchemy.org/en/rel_1_0/core/constraints.html#sqlalchemy.schema.UniqueConstraint

这些都解释得很清楚。