在Python中参数化DELETE SQL语句时遇到问题

Question

我正在编写一个针对MySQL数据库的Python3脚本。使用mysql.connector功能。

我正在试着写一个删除命令。我可以让它与静态字符串一起工作，但不能让它与参数化语句一起工作。

这个可以用：

sql = "DELETE FROM mytable where report_dt < '" + str(report_date) + "'"
try:
    mycursor.execute(sql)
    mydb.commit()

这个失败了：

sql = "DELETE FROM mytable where report_dt < '%s'"
try:
    mycursor.execute(sql, report_date)
    mydb.commit()

report_date变量来自datetime.datetime.now()，格式如下：

str(report_date)返回：‘2020-06-09 10:59:11’

无错误消息…它实际上并没有删除它应该删除的行。

Answer 1

这两个应该都能做到。

sql = "DELETE FROM mytable WHERE report_dt < %(date)s;"
try:
    mycursor.execute(sql, {'date': str(report_date)})
    mydb.commit()
except:
    print(f"Error deleting report from {report_date}.")

或

sql = "DELETE FROM mytable WHERE report_dt < %s;"
try:
    mycursor.execute(sql, [str(report_date)])
    mydb.commit()
except:
    print(f"Error deleting report from {report_date}.")

Python涵盖了数据库库在Python中为了保持一致性而应该遵循的PEP 249。

在execute方法下：

参数可以作为序列或映射提供，并将绑定到操作中的变量。

通常，我总是尝试使用映射(例如dict)，以便对SQL中的内容有一些易于遵循的清晰度。尤其是当我需要在多个地方重复相同的内容时。

我可以为一个只有一个或两个参数的简短查询做一个序列(例如list)。

从注释中澄清，尝试参数化的问题有两个问题。首先，Cursor.execute()的"parameters“参数需要是序列或映射类型。其次，查询上的参数化操作将处理所有必需的引号，因此参数标记两边的单引号(例如，'%s' to only %s)导致了一条不太容易理解的错误消息。

缺少引号是因为参数化使查询安全，所以在SQL语句本身中编写的任何其他语句都不必执行任何操作。不幸的是，像"SQL语法错误“这样的简单消息并不能使这一点变得非常明显。在文档中我能找到的最好的东西就是Type Objects and Constructors。pyscopg2文档确实有专门的section about quoting。

Answer 2

看起来这个解决方案是上面几个答案的混合：

sql = "DELETE FROM mytable where report_dt < %s"
try:
    mycursor.execute(sql, [report_date])
    mydb.commit()

删除%s两边的单引号，并使用括号将report_date变量括起来。

当它是静态文本输入时，单引号是必需的，但显然在参数化期间，它的工作方式有所不同。

谢谢大家！