时间戳

首页 > 解决方案 > 使用字典列表更新 SQL 表

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

问题描述

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

_mysql_connector.MySQLInterfaceError: Incorrect datetime value: '1' for column 'colF' at row 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

还有一个问题when value is not null。语法是错误的，我想。

标签： pythonmysqlsql

解决方案

您尝试的参数化查询会出现几个问题：

  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'}

推荐阅读