如何在多主键表中插入具有自动递增ID的行？

问题描述：

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

I am writing a turbogears2 application. I have a table like this:

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的行？

I want to insert a new row into this table, but I got a "Field 'order_id' doesn't have a default value" error. It seems that I have to set the id of order manually, because I got two primary-key. My question is, how can I insert a row that generate new ID automatically?

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

If I generate id manually, I got some problem. For example:

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

以这种方式添加新订单似乎没问题，但是，如果两个请求几乎同时运行这些代码，我们就会遇到问题。

Add a new order in this way seems ok, but however, we got some problem if two request run these code in almost same time.

如果存在请求a和b按以下顺序运行此代码：

If there is request a and b run this code in following order:

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的订单。我可以捕获异常，然后重试。但是我想知道，还有更好的方法吗？

Then the request a might failed, because there is already an order with same id in table. I can catch the exception and try again. But I am wondering, is there any better way to do?

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

Sometimes, the id is not simple integer, we might need order id like this:

2009090133的2009年9月1日第33订单的标准

2009090133 standards for 33rd order at 2009-09-01

在这种情况下，无法使用自动增量。因此，我别无选择，请手动为订单分配ID。因此，我的另一个问题是，有什么方法比捕获异常并重试插入具有ID的行还要好。

In these case, autoincrement is not usable. So I have no choice, manualy assign id for order. So my another question is, is there any better way than catch exception and retry to insert a row with id.

答

在列定义上使用默认值

id = Column(Integer, default = sqlexpression)

其中sqlexpression可以是sql表达式。这是文档。对于自动增量，您应该使用SQL表达式 coalesce（从order，0中选择max（order.id）+ 1 。为简便起见，您可以导入sqlalchemy.sql.text，以便id列看起来像

Where sqlexpression can be a sql expression. Here is the documentation. For autoincrement you should use the sql expression coalesce(select max(order.id) from order,0) + 1. For ease you could import sqlalchemy.sql.text so the id column could look something like

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

如何在多主键表中插入具有自动递增ID的行？

相关推荐