Python And Sqlite3: Deleting Multiple Rows

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

Solution 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)))
# DELETEFROMtableWHERE 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.

Solution 2:

cursor.execute("DELETE FROM rows WHERE ids IN (%s);", ', '.join(idsList));