使用问号在sqlite3中进行参数化查询 [英] Parameterized queries in sqlite3 using question marks

问题描述

我正在将sqlite3模块与Python配合使用，到目前为止，已经有了这段代码.在数据库中，我存储了每天的天气状况，现在我需要我的代码以用更新的数据替换某些行.该代码应该在寻找datetime值等于new_data[0]的行.

I am using sqlite3 module with Python and have this code so far. In the database I have stored daily weather conditions, and now I need my code to replace some rows with updated data. The code is supposed to be looking for the row with datetime value equal to new_data[0].

我对查询进行参数化的方式是错误的，但无法找出正确，最优雅的解决方法！

The way I parameterized the query is wrong, but cannot figure out the correct and most elegant way of going about it!

new_data = ['12 Mar 2014', 'sunny', 20, 12]

conn = sqlite3.connect(database_file)
c = conn.cursor()
c.execute("UPDATE weather SET datetime = ?, condition = ?, high = ?, low = ? WHERE datetime = %s" new_data, %new_data[0])

推荐答案

您正在混用带字符串操作的参数化查询.首先，这是非常不安全的，其次，您在语法上产生了问题(查询字符串后缺少逗号).尝试以下方法:

You are mixin up a parameterized query with string operations. First, that's highly insecure and second, you have created a problem with your syntax (you missed a comma after your query string). Try this instead:

new_data = ('12 Mar 2014', 'sunny', 20, 12, '12 Mar 2014',)

conn = sqlite3.connect(database_file)
c = conn.cursor()
c.execute("UPDATE weather SET datetime = ?, condition = ?, high = ?, low = ? WHERE datetime = ?", new_data)

更多详细信息可以在这里找到: https://docs.python.org/2/library/sqlite3.html

More details can be found here: https://docs.python.org/2/library/sqlite3.html

这篇关于使用问号在sqlite3中进行参数化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！