当参数有时可以为 NULL 时，如何参数化 SQL 查询? [英] How to parameterize SQL query when the parameter can sometimes be NULL?

问题描述

使用 pyodbc 我可以像这样参数化查询；

value = "testval"查询 = \"""选择 *从测试表WHERE 列 = ?;"""游标.执行(查询，值)

但问题是如果value是None，查询应该是这样的；

value = 无查询 = \"""选择 *从测试表WHERE 列为空；"""游标.执行(查询)

那么当 value 可以是 None 或字符串时，查询应该是什么样的；

value = get_value() # 可以返回一个字符串或 None查询 = \"""选择 *从测试表WHERE 列 ???????????"""游标.执行(查询，值)

解决方案

解决方案是使用ISO/ANSI标准NULL-safe比较:

WHERE Column is NOT DISTINCT FROM ?

并不是所有的数据库都支持这个，所以你也可以使用:

WHERE 列 = ?或(列为空且?为空)

如果您不愿意将参数传递两次，可以将其包含在FROM 子句中:

<预><代码>...从 ...交叉连接(SELECT ? as compColumn) 参数WHERE (Column = params.compColumn0 或 (Column IS NULL and params.compColumn IS NULL)

With pyodbc I can parametrize the query like this;

value = "testval"

query = \
    """
    SELECT *
    FROM TestTable
    WHERE Column = ?;
    """

cursor.execute(query, value)

But the problem is that if the value is None, the query should look like this;

value = None

query = \
    """
    SELECT *
    FROM TestTable
    WHERE Column IS NULL;
    """

cursor.execute(query)

So how should the query look like when the value can either be None or a string;

value = get_value()  # can return a string or None

query = \
    """
    SELECT *
    FROM TestTable
    WHERE Column ???????????
    """

cursor.execute(query, value)

解决方案

The solution is to use the ISO/ANSI standard NULL-safe comparison:

WHERE Column IS NOT DISTINCT FROM ?

Not all databases support this, so you can also use:

WHERE Column = ? OR (Column IS NULL AND ? IS NULL)

If you are reluctant to pass the parameter twice, you can include it in the FROM clause:

. . .
FROM . . . CROSS JOIN
     (SELECT ? as compColumn) params
WHERE (Column = params.compColumn0 or (Column IS NULL and params.compColumn IS NULL)

这篇关于当参数有时可以为 NULL 时，如何参数化 SQL 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！