How to select id's from one table and insert/update to another table

Question

I am working on an automated trading system in python, I have set up a database which inserts a row every time a new order is generated.

What I am having trouble with is if the order is successful I would like to add the OrderID to my Position Table.

Here are the scenarios that could happen

if there is no open reference with the same OrderID, AccountID, strategyID, AssetID then the insert new row where PositionID=NUM, OpenReference=OrderID, CloseReference=NULL, Status=2

if there are positions that are not of status(3) existing check to see if it matches the OpenReference parameters(OrderID, AccountID, strategyID, AssetID) if it does update the OrderID to column CloseReference and update status=3.

Order Table Setup

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;

Position Table Setup

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

My Python Code

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

Do a `SELECT` query to see if there's a row that meets the condition. If there is, execute an `UPDATE` query, otherwise execute the `INSERT` query. — Barmar, Sep 26 '20 at 05:28
What is `NUM`, and why isn't it a parameter to `insert_position`? Or is `PositionID` an auto-increment ID? — Barmar, Sep 26 '20 at 16:39
`OpenRef` and `CloseRef` are foreign keys to `Order.OrderID`? — Barmar, Sep 26 '20 at 16:41
Isn't `OrderID` a primary key? Can it have the same OrderID but different AccountID, strategyID, AssetID? — Barmar, Sep 26 '20 at 16:52
The __position table is related based on the foreign keys OpenRefernce and ClosedReference. The OrderID is a primary key to the __order Table. AccountID, strategyID, AssetID must match the OpenReference values. if it doesn't match must be related to another __position row. — JGProgrammer, Sep 27 '20 at 14:07

score 0 · Answer 1 · answered Sep 26 '20 at 17:03

Start with a SELECT query to check if there are any positions with the given order ID.

If there are, update all the ones with status != 3 as required. If not, insert the new row.

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

How to select id's from one table and insert/update to another table

1 Answers1