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.

zwer · Accepted Answer · 2018-09-24 12:25:43Z

6

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.

answered Sep 24, 2018 at 12:25

zwer

25.9k3 gold badges53 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

whiteFang · Accepted Answer · 2018-09-24 12:30:47Z

-2

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

answered Sep 24, 2018 at 12:30

whiteFang

575 bronze badges

1 Comment

roganjosh Over a year ago

No, you should not do this. String formatting is a SQL injection risk.

Collectives™ on Stack Overflow

Python and sqlite3: deleting multiple rows [duplicate]

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related