Python中两次SQL INSERT导致tblOrder_Line数据分两行插入的解决求助

执行以下Python代码后，tblOrder_Line表会生成两行数据：一行仅填充costWhenPurchased字段，其余字段为NULL；另一行填充orderNo、itemID、quantity字段。期望将所有字段值插入到同一行中。

原代码：

def placeOrder_2():
    purchase_amount = int(input("Enter the amount different products you would like to purchase: "))

    for x in range(purchase_amount):
        p_id = input("Enter the ID of the product you would like to purchase: ")
        p_quantity = int(input("Enter the quantity you would like to purchase"))

        sqlstring = """
        INSERT INTO tblOrder_Line (costWhenPurchased)
        SELECT unitPrice FROM tblInventory
        WHERE itemID = ? VALUES(?)
                     """
        values = (p_id,)
        runsql(sqlstring, values)
        placeOrder_3(p_id, p_quantity)


def placeOrder_3(p_id, p_quantity):
    sqlstring = """
    INSERT INTO tblOrder_Line(orderNo, itemID, quantity) VALUES (?,?,?)
                """
    values = (p_id, p_quantity)
    runsql(sqlstring, values)
    placeOrder_4(p_id, p_quantity)

问题根源是两次独立的INSERT操作，每次操作都会新增一行数据。需要将逻辑合并为一次INSERT，同时从tblInventory获取unitPrice作为costWhenPurchased的值，和其他字段一起插入。

修改后的代码：

def placeOrder_2():
    purchase_amount = int(input("Enter the amount different products you would like to purchase: "))
    # 替换为实际的当前订单号获取逻辑，比如从订单主表中读取刚生成的订单号
    current_order_no = "ORD001"

    for x in range(purchase_amount):
        p_id = input("Enter the ID of the product you would like to purchase: ")
        p_quantity = int(input("Enter the quantity you would like to purchase: "))

        # 一次性插入所有字段，通过SELECT获取unitPrice
        sqlstring = """
        INSERT INTO tblOrder_Line (orderNo, itemID, quantity, costWhenPurchased)
        SELECT ?, ?, ?, unitPrice 
        FROM tblInventory
        WHERE itemID = ?
        """
        values = (current_order_no, p_id, p_quantity, p_id)
        runsql(sqlstring, values)
        placeOrder_4(p_id, p_quantity)

# 原placeOrder_3函数可删除，逻辑已合并到placeOrder_2中

关键说明：

• 合并两次INSERT为一次，确保所有字段插入到同一行
• SQL通过SELECT语句同时获取unitPrice和传入其他参数，占位符数量与参数列表匹配
• current_order_no需要替换为实际业务中的订单号来源（比如订单主表生成的编号）

内容的提问来源于stack exchange，提问作者bitmap