如何在多主键表中插入带自增 ID 的行？

Question

我正在写一个turbogears2的应用程序。我有一个这样的表：

class Order(DeclarativeBase):
    __tablename__ = 'order'

    # id of order
    id = Column(Integer, autoincrement=True, primary_key=True)

    # buyer's id
    buyer_id = Column(Integer, ForeignKey('user.user_id',
        onupdate="CASCADE", ondelete="CASCADE"), primary_key=True)

我想在这个表里插入一行新的数据，但出现了一个错误：“字段 'order_id' 没有默认值”。看起来我必须手动设置订单的ID，因为我有两个主键。我的问题是，怎么才能插入一行数据，让ID自动生成呢？

如果我手动生成ID，就会遇到一些问题。例如：

maxId = DBSession.query(func.max(Order)).one()[0]
newOrder = Order(id=maxId + 1, buyer_id=xxx)
DBSession.add(newOrder)

以这种方式添加新订单看起来没问题，但如果有两个请求几乎同时运行这些代码，就会出现问题。

假设请求a和b按以下顺序运行这段代码：

a.maxId = DBSession.query(func.max(Order)).one()[0]
b.maxId = DBSession.query(func.max(Order)).one()[0]
b.newOrder = Order(id=maxId + 1, buyer_id=xxx)
b.DBSession.add(newOrder)
a.newOrder = Order(id=maxId + 1, buyer_id=xxx)
a.DBSession.add(newOrder)

那么请求a可能会失败，因为表中已经有一个相同ID的订单。我可以捕捉到这个异常并重试。但我在想，有没有更好的方法呢？

有时候，ID并不是简单的整数，我们可能需要这样的订单ID：

2009090133 表示2009年9月1日的第33个订单

在这种情况下，自增功能就不能用了。所以我别无选择，只能手动为订单分配ID。那么我的另一个问题是，除了捕捉异常并重试插入带ID的行，还有没有更好的方法呢？

Answer 1

如果你想给每个买家的订单分配连续的编号，那么你需要在插入订单时对每个买家的交易进行排序。你可以通过对买家的数据行进行独占锁定来实现这一点：

sess.query(Buyer.id).with_lockmode('update').get(xxx)
order_id = sess.query(func.max(Order.id)+1).filter_by(buyer_id=xxx).scalar() or 1
sess.add(Order(id=order_id, buyer_id=xxx))

使用这种方法时，如果有两个交易同时尝试为同一个买家插入订单，其中一个会在第一行被阻塞，直到另一个交易完成或失败。

Answer 2

你应该在列定义中使用默认值

id = Column(Integer, default = sqlexpression)

这里的sqlexpression可以是一个SQL表达式。你可以查看这个文档。对于自增字段，你应该使用这个SQL表达式 coalesce(select max(order.id) from order,0) + 1。为了方便，你可以导入sqlalchemy.sql.text，这样id列看起来就像下面这样

id = Column(Integer, default = text("coalesce(select max(order.id) from order,0) + 1"))