Python 和 sqlite3:删除多行 [英] Python and sqlite3: deleting multiple rows

问题描述

我需要从一个sqlite3表中删除多行，使用如下SQL语句:

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)

我的问题是用 Python 编写代码，ID 列在列表中.以下不起作用，产生语法错误:

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?

当然，我不希望必须遍历列表，逐行删除行，因为那样效率很低.

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

附注.管理员已经指出这个问题与 这个.但是，这个问题是关于 DML 语句(删除)，而那个是关于 DATA 语句(选择)的.差异可能看起来很表面，但实际上很关键，因为 SQLITE3 允许 executemany 命令与 DML 语句一起使用，但不能与数据语句一起使用.因此，这两个问题的答案是截然不同的.

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.

推荐答案

如果您需要自动将多个元素解包到 SQL 语句中，则不能将其作为列表传递 - 您必须实际考虑语句中的位置参数(即添加与要解压缩的元素一样多的 ? ).类似的东西:

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.

这篇关于Python 和 sqlite3:删除多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！