我正在编写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的行？
发布评论