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

Question

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

我遇到的问题是，如果订单成功，我想将订单ID添加到我的头寸表中。

以下是可能发生的情况

如果没有具有相同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`)
)

我的蟒蛇代码

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,))