python - 使用字典列表更新 SQL 表

Question

如何在SQL查询中正确使用字典？我应该预定义 SQL 变量以防止 SQL 注入(inject)吗？例如。我在尝试使用字典更新表时在 Python 中遇到错误，但查询在具有预定义变量和值的纯 SQL 中运行完美。

_mysql_connector.MySQLInterfaceError:日期时间值不正确:第 1 行的“colF”列为“1”

我的口述

     {'ID': 1, 'colA': 'valA1', 'colB': 'valB1', 'colC': 'valC1', 
        'colD': 'valD1', 'colE': 'valE1', 'colF': datetime.datetime(2017, 12, 23, 0, 0), 
'colG': datetime.datetime(2018, 
        7, 11, 0, 0), 'colH': datetime.datetime(2018, 9, 1, 0, 0)}

SQL语句

UPDATE table1
    SET 
    colA = CASE \
    WHEN %(valA1)s IS NOT NULL THEN %(valA1)s 
    END,\
    colB = CASE \
    WHEN %(valB1)s IS NOT NULL THEN %(valB1)s 
    END,\
    colC = CASE \
    WHEN %(valC1)s IS NOT NULL THEN %(valC1)s 
    END,\
    colD = CASE \
    WHEN %(valD1)s IS NOT NULL THEN %(valD1)s 
    END,\
    colE = CASE \
    WHEN %(valE1)s IS NOT NULL THEN %(valE1)s 
    END,\
    colF = CASE\
    WHEN  %(valF)s IS NOT NULL THEN %(valF)s
    END,\
    colG = CASE\
    WHEN  %(valF1)s IS NOT NULL THEN %(valF1)s 
    END,\
    colH = CASE\
    WHEN  %(valH1)s IS NOT NULL THEN %(valH1)s
    END\
    WHERE %(ID)s = Id """

当我格式化查询字符串时

 colF1 = CASE  
    WHEN  2018-07-11 00:00:00 IS NOT NULL THEN 2018-07-11 00:00:00 
    END,
    colH1 = CASE
    WHEN  2018-09-01 00:00:00 IS NOT NULL THEN 2018-09-01 00:00:00
    END 
    WHERE  Id = 1  

还有一个问题当值不为空时。我想语法是错误的。

Answer 1

您尝试的参数化查询出现了几个问题:

1. 如 MySQLdb 中所述文档、列或表标识符不能用于参数占位符。此类占位符仅用于文字值。此外，考虑多行的三重引号字符串以避免 \ 换行符:

   sql = """UPDATE table1
            SET 
              colA = CASE 
                        WHEN colA IS NOT NULL 
                        THEN %(valA1)s 
                     END,
            ...
         """
   
   cur.execute(sql, mydict)
   con.commit()
   

2. 要使用命名占位符方法，字典键必须与占位符名称相匹配。目前，您需要反转字典中的大部分键/值顺序，而您只对 ID 正确执行了这些操作。但是，如上面 #1 中所述，删除所有列标识符:

   {'ID': 1, 'valA1': 'data value', 'valB1': 'data value', 'valC1': 'data value', ... }
   

3. 大多数数据库(包括 MySQL)中的日期时间必须采用字符串形式 YYYY-MM-DD HH:MM:SS。 MySQL 引擎无法执行 Python 的 datetime() 对象。但是，某些 DBI-API 可能能够转换此对象类型。

   {'valF': '2017-12-23 00:00:00', 
    'valG': '2018-07-11 00:00:00', 
    'valH': '2018-09-01 00:00:00'}