如何从一个表中选择id并插入/更新到另一个表

Question

我正在用python开发一个自动交易系统，我已经建立了一个数据库，在每次生成新订单时插入一行

我遇到的问题是，如果订单成功，我想将OrderID添加到我的Position表中

以下是可能发生的情况

如果没有具有相同OrderID, AccountID, strategyID, AssetID的打开引用，则插入新行，其中PositionID=NUM, OpenReference=OrderID, CloseReference=NULL, Status=2

如果存在不存在status(3)的位置，请检查它是否匹配OpenReference参数（OrderID, AccountID, strategyID, AssetID），如果它确实将OrderID更新到列CloseReference并更新status=3

订单表设置

CREATE TABLE `__order` (
  `OrderID` int NOT NULL AUTO_INCREMENT,
  `AccountID` int DEFAULT NULL,
  `StrategyID` int DEFAULT NULL,
  `AssetID` int DEFAULT NULL,
  `TimeSubmitted` datetime DEFAULT NULL,
  `Action` mediumtext,
  `Type` mediumtext,
  `Price` float DEFAULT NULL,
  `Quantity` int DEFAULT NULL,
  `Outstanding` int DEFAULT NULL,
  `TimeCompleted` datetime DEFAULT NULL,
  `Commission` float DEFAULT NULL,
  `Status` mediumtext,
  PRIMARY KEY (`OrderID`),
  KEY `AssetID_FORK_idx` (`AssetID`),
  KEY `AccountID_FORK_idx` (`AccountID`),
  KEY `StratID_FORK_idx` (`StrategyID`),
  CONSTRAINT `AccountID_FORK` FOREIGN KEY (`AccountID`) REFERENCES `__account` (`AccountID`),
  CONSTRAINT `AssetID_FORK` FOREIGN KEY (`AssetID`) REFERENCES `__asset` (`AssetID`),
  CONSTRAINT `StratID_FORK` FOREIGN KEY (`StrategyID`) REFERENCES `__strategy` (`StrategyID`)
) ENGINE=InnoDB AUTO_INCREMENT=577 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

位置表设置

CREATE TABLE `__position` (
  `PositionID` int NOT NULL AUTO_INCREMENT,
  `OpenReference` int DEFAULT NULL,
  `CloseReference` int DEFAULT NULL,
  `Status` int DEFAULT NULL,
  PRIMARY KEY (`PositionID`),
  KEY `BuyReference_order_FK_idx` (`OpenReference`),
  KEY `SellReference_order_FK_idx` (`CloseReference`),
  KEY `Status_order_FK_idx` (`Status`),
  CONSTRAINT `BuyReference_order_FK` FOREIGN KEY (`OpenReference`) REFERENCES `__order` (`OrderID`),
  CONSTRAINT `SellReference_order_FK` FOREIGN KEY (`CloseReference`) REFERENCES `__order` (`OrderID`),
  CONSTRAINT `Status_order_FK` FOREIGN KEY (`Status`) REFERENCES `__status` (`StatusID`)
)

我的Python代码

def insert_position(self, openRef=None, status=None):
    return self.execute(
        sql="""
            INSERT INTO __position
                (OpenReference, Status) 
            VALUES 
                (%s, %s);""",
        params=(openRef, status,))

def update_position(self, positionID, closeRef=None, status=None):
    return self.execute(
        sql="""
            UPDATE __position
                SET CloseReference = %s,
                    Status = %s
            WHERE PositionID = %s;""", params=(closeRef, status, positionID,))

Answer 1

从SELECT查询开始，检查是否有具有给定订单ID的职位

如果有，根据需要用status != 3更新所有的。如果没有，请插入新行

def insert_or_update_position(self, order_id):
    self.execute(sql = "SELECT 1 FROM __position WHERE OpenReference = %s LIMIT 1", (order_id,))
    row = self.cursor.fetchone()
    if row:
        self.execute(sql="""
            UPDATE __position
            SET CloseReference = %s, Status = 3
            WHERE OpenReference = %s AND Status != 3""", (order_id, order_id))
    else:
        return self.execute(sql="""
            INSERT INTO __position (OpenReference, Status)
            VALUES (%s, 2)""", (order_id,))