尝试使用SQLAlchemy实现回滚，同时一次在父子表中插入数据

Question

当使用SQL Alchemy试图实现具有两个表的回滚功能时

class OrderDetailModel(BaseModel):
prod_id: int
price_id: int
amount: int
class OrderModel(BaseModel):
user_id: int
order_detail: List[OrderDetailModel] = []
def create_order(self, db: Session):
db.begin()
order_details: OrderDetails = []
od: OrderDetails = OrderDetails()
for order in self.order_detail:
od.prod_id = order.prod_id
od.price_id = order.price_id
od.quantity = 1
od.amount = order.amount
order_details.append(od)
print('Order_details list size >>>>>>>>>>>>>>>>>>>>', len(order_details))
order = Order(unique_order_id=''.join(random.choices(string.ascii_lowercase, k=10)),
order_date=datetime.utcnow().date(),
checkout_date=datetime.utcnow().date(),
user_id=self.user_id,
order_status="FINISHED",
order_amount=100,
order_details=order_details)
db.bulk_insert_mappings(order, order_details)    #### if this is used it gives TypeError: 'OrderDetails' object is not iterable

# db.add(order) ### if we use this add, it inserts only one Order_Detail
db.commit()
return "success"

下面是两个表的脚本

class Order(Model):
__tablename__="marketplace_orders"
id = sa.Column(BIGINT, primary_key=True, autoincrement=True)
unique_order_id = sa.Column(sa.String(),  unique=True, nullable=False)
order_date= sa.Column(types.DateTime, nullable=False)
checkout_date=sa.Column(types.DateTime, nullable=False)
user_id = sa.Column(BIGINT, sa.ForeignKey("ums_users.id"), nullable=False)
order_status=sa.Column(sa.Enum(MarketPlaceOrderStatus), nullable=False)
order_amount = sa.Column(BIGINT)
order_details = relationship("OrderDetails", back_populates="order", lazy="dynamic")
class OrderDetails(Model):
__tablename__="order_details"
id = sa.Column(BIGINT, primary_key=True, autoincrement=True)
order_id=sa.Column(BIGINT, ForeignKey("marketplace_orders.id"), nullable=False)
prod_id = sa.Column(BIGINT, ForeignKey("products.id"), nullable=False)
price_id = sa.Column(BIGINT, ForeignKey("prices.id"), nullable=False)
quantity=sa.Column(sa.Integer, nullable=False)
amount=sa.Column(sa.Integer, nullable=False)
order = relationship("Order", back_populates="order_details")

我正在使用以下参数调用API

{
"user_id": 1,
"order_detail": [ { "prod_id": 1, "price_id": 3, "amount": 1200 }, { "prod_id": 1, "price_id": 3, "amount": 12233 } ]
}

如果我用db.bulk_insert_mappings(order, order_details)

TypeError: 'OrderDetails' object is not iterable

，当使用db.add(order)时，它只插入一个Order_Detail，而我的示例输入包含两个对象Order_Detail

Answer 1

最简单的方法是使用db.add(order)。您遇到的问题实际上与SQLAlchemy无关:您在for循环中重用了相同的OrderDetails对象。如果你切换

od: OrderDetails = OrderDetails()
for order in self.order_detail:
...

for order in self.order_detail:
od: OrderDetails = OrderDetails()
...

一切正常

此外，您不必创建一个空的OrderDetails对象，您可以将循环重写为

for order in self.order_detail:
od = OrderDetails(**order.dict(), amount=1)
order_details.append(od)

或者

order_details = [OrderDetails(**o.dict(), amount=1) for o in self.order_detail]