python - 无法使用 'where' 和变量删除 SQLite 行

Question

遇到问题，如有任何帮助，我们将不胜感激。

我想删除一行，它是从一个变量转移到我的函数中的。那是代码:

con = sql.connect('db.sqlite')
cur = con.cursor()
query = 'delete from media_tmp where media_id="%s"' % media_id.strip()
print(query)
cur.execute(query)
if con:
    con.close()

print(query) 给我以下内容:

delete from media_tmp where media_id="737589711821419460_184456611"

当我直接在 sqlite 中执行它时，它完美地工作并删除了应得的行。但由 cur.execute(query) 执行根本不起作用。

函数完成良好，没有错误。

Answer 1

您可能忘记提交更改:

con.commit()

sqlite3 命令行工具会自动提交更改，sqlite3 库不会。

你真的应该使用 SQL 参数而不是字符串插值:

query = 'delete from media_tmp where media_id=?'
cur.execute(query, (media_id.strip(),))

引用sqlite3 documentation :

Usually your SQL operations will need to use values from Python variables. You shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack (see http://xkcd.com/327/ for humorous example of what can go wrong).

Instead, use the DB-API’s parameter substitution. Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method. (Other database modules may use a different placeholder, such as %s or :1.)