如何在SQLite3中切换单个或多个item_id的status字段状态？

问题背景 #

现有方法可切换单个指定item_id对应的status字段布尔值：

def toggle_status_of_item(self, item_id: str):
    sql = """UPDATE table
             SET status = CASE status
                          WHEN 0 THEN 1 
                          ELSE 0 END
             WHERE item_id = ?"""
    self.cur.execute(sql, (item_id,))
    self.con.commit()

但item_id有时会传入多个值，目前通过循环逐个执行SQL的方式实现批量切换（如下），但希望使用纯SQLite语句完成批量操作，避免循环：

def toggle_status_of_item(self, item_ids: list):
    sql = """UPDATE table
            SET status = CASE status
                         WHEN 0 THEN 1 
                         ELSE 0 END
            WHERE item_id = ?"""
    for id in item_ids:
        self.cur.execute(sql, (id,))
    self.con.commit()

纯SQL解决方案 #

使用SQLite的IN操作符匹配多个item_id，同时动态生成对应数量的参数占位符，只需执行一次SQL即可完成批量状态切换：

def toggle_status_of_item(self, item_ids: list):
    # 根据列表长度生成对应数量的占位符，比如2个ID对应"?, ?"
    placeholders = ", ".join(["?"] * len(item_ids))
    sql = f"""UPDATE table
             SET status = CASE status
                          WHEN 0 THEN 1 
                          ELSE 0 END
             WHERE item_id IN ({placeholders})"""
    self.cur.execute(sql, item_ids)
    self.con.commit()

说明 #

• IN ({placeholders})会被替换为IN (?, ?, ...)的格式，完美适配传入的item_ids列表
• 单次执行SQL即可完成所有目标记录的状态翻转，相比循环执行效率更高，完全符合纯SQL批量操作的需求

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