Python and sqlite3: deleting multiple rows [duplicate]

Question

I need to delete multiple rows from a sqlite3 table, using a SQL statement such as:

DELETE FROM table WHERE id IN (23, 19, 35, 16, 12, 78)

My problem is coding this in Python, with the ids in a list. The following doesn't work, producing a syntax error:

cursor.execute('DELETE FROM table WHERE id IN ?', (id_list,))

I tried converting the list to a tuple, with identical results. Any ideas what the correct syntax for this should be, please?

EDIT: Of course, I do not wish to have to traverse the list deleting the rows one by one as that would be very inefficient.

PS. Administrators have pointed out the similarity of this question with this. However, this question is about a DML statement (delete), whereas that one was about a DATA statament (select). The difference may appear superficial, but is in fact critical in that SQLITE3 allows the executemany command to be used with DML statements, but not with data statements. Therefore, the answers to the two questions are very different.

Answer 1

If you need to automatically unpack multiple elements into an SQL statement you cannot just pass it as a list - you have to actually account for the positional arguments in your statement (i.e. add as many ? as there are elements to unpack). Something like:

id_list = (23, 19, 35, 16, 12, 78)
query = "DELETE FROM table WHERE id IN ({})".format(", ".join("?" * len(id_list)))
# DELETE FROM table WHERE id IN (?, ?, ?, ?, ?, ?)
cursor.execute(query, id_list)

Keep in mind that this might not be necesarily more efficient than issuing multiple statements via:

cursor.executemany("DELETE FROM table WHERE id = ?", id_list)

It all depends on query buffering, your table relations etc.