How to toggle sometimes one and sometimes more than one item in Sqlite3?

Question

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

The above method toggles the boolean value in column status of a given item_id. However, item_id can sometimes be plural, i.e. it may contain more than one value, which I have no control of.

How should I rewrite the sqlite3 doc-string to toggle sometimes one and sometimes more than one item_id? Other than changing item_id: str to item_id: list, how do I write the SQLite commands to apply the CASE statement to a list of item ids? Thank you in advance.

Below method will do what I want. However, it is not a pure SQLITE approach. I would like to know the SQL commands to achieve the below.

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

`item_id` is `str`; if multiple values are expected, how are they delineated (normally I would expect `item_id` instead to be an iterable data structure)? What have you tried so far? Can you show those attempts as a [mre]? — esqew, Aug 17 '23 at 15:24

score 2 · Accepted Answer · edited Aug 17 '23 at 16:14

2

(Answering assuming item_id uses a comma (,) to delineate between distinct item_ids for which this query should be run.)

Create a List[Tuple] of item_ids, then use cur.executemany():

def toggle_status_of_item(self, item_id: str):
    item_ids = list(map(lambda x: (x,), item_id.split(",")))
    sql = """UPDATE table
             SET status = CASE status
                          WHEN 0 THEN 1 
                          ELSE 0 END
             WHERE item_id = ?"""
    self.cur.executemany(sql, item_ids)
    self.con.commit()

edited Aug 17 '23 at 16:14

Sun Bear

7,594
11
56
102

answered Aug 17 '23 at 15:29

esqew

42,425
27
92
132

@python_user No, since each member of `item_ids` has an individual query executed when using `cur.executemany()`. – esqew Aug 17 '23 at 15:33
@python_user You could refactor slightly to use `NOT... IN...` and stick with `cur.execute()`, but I believe when using `sqlite3` you'd need to [dynamically add `?` parameters to `sql` for each member of `item_ids`](https://stackoverflow.com/questions/4788724/sqlite-bind-list-of-values-to-where-col-in-prm). Possible, but adds a bit of code complexity that would likely only be desirable at larger scales. – esqew Aug 17 '23 at 15:36

score 0 · Answer 2 · answered Aug 17 '23 at 16:07

Thanks to @esqew, I learned that:

item_ids has to be a list of tuples where each tuple contains a str element, e.g. item_ids = [("A",), ("B",), ("C",)].
I can use sqlite's .executemany() to replicate what the python for-loop would do.

Working solution:

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

and this method can be executed as:

item_ids = [("A",), ("B",), ("C",)]
class_instance_name.toggle_status_of_items(item_ids)

How to toggle sometimes one and sometimes more than one item in Sqlite3?

2 Answers2