Python SQL Select 可能包含 NULL 值

发布于 2024-07-25 15:20:28 字数 325 浏览 2 评论 0原文

我是 python 新手，在尝试执行的 SQL 查询中遇到了问题。

我正在创建一个 SQL SELECT 语句，其中填充了数组中的值，如下所示：

ret = conn.execute('SELECT * FROM TestTable WHERE a = ? b = ? c = ?', *values)

当值数组中有实际值时，这可以正常工作。但是，在某些情况下，值中的单个条目可能会设置为“无”。然后查询失败，因为“= NULL”测试不起作用，因为该测试应该是 IS NULL。

有一个简单的方法可以解决这个问题吗？

原文

I'm new to python and have hit a problem with an SQL query I'm trying to perform.

I am creating an SQL SELECT statement that is populated with values from an array as follows:

ret = conn.execute('SELECT * FROM TestTable WHERE a = ? b = ? c = ?', *values)

This works ok where I have real values in the values array. However in some cases an individual entry in values may be set to None. The query then fails because the "= NULL" test does not work since the test should be IS NULL.

Is there an easy way around this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

月野兔 2024-08-01 15:20:28

如果您使用的是 SQL Server，那么只要您将 ANSI_NULLS 设置为关闭，会话 '= null' 比较就会起作用。

设置 ANSI_NULLS

回复收藏 0 原文

话少心凉 2024-08-01 15:20:28

使用：“Select * from testtable where (a = ? or a is null) and (b=? or b is null) ”

这将选择 a 与提供的值完全匹配的情况，并将在列中包含空值 - 如果这就是你想要的。

回复收藏 0 原文

ぺ禁宫浮华殁 2024-08-01 15:20:28

您始终可以使用三元运算符将“=”切换为“IS”：

("=","IS")[var is None]

如果 var 为 None，则返回“IS”，否则返回“=”。

虽然在一行中完成此操作并不是很优雅，但只是为了演示：

query = "SELECT * FROM testTable WHERE a %s %s" % ( ("=","IS")[a is None], str(a) )

You can always use the ternary operator to switch '=' for 'IS':

("=","IS")[var is None]

Would return "IS" if var is None and "=" otherwise.

It's not very elegant to do this in one line though, but just for demonstrating:

query = "SELECT * FROM testTable WHERE a %s %s" % ( ("=","IS")[a is None], str(a) )

回复收藏 0 原文

梦明 2024-08-01 15:20:28

如果您喜欢冒险，也可以查看 SQLAlchemy。它提供了许多其他功能，其中包括一个 SQL 构造工具包，可以自动将与 None 的比较转换为 IS NULL 操作。

回复收藏 0 原文

○闲身 2024-08-01 15:20:28

首先，我强烈警告您不要使用 SELECT * FROM tbl WHERE (col = :x OR col IS NULL) ，因为这很可能会使您的查询失去索引资格（使用查询分析器）。 SET ANSI_NULLS 也是您的特定数据库类型可能不支持的事情之一。

一个更好的解决方案（或者至少是一个更通用的解决方案）是，如果您的 SQL 方言支持 Coalesce，请像这样编写查询：

SELECT * FROM tbl WHERE col = COALESCE(:x, col)

因为 col = col 将始终计算为true，为 :x 传递 NULL 不会损坏您的查询，并且应该允许更有效的查询计划。这还有一个优点，即它在存储过程中工作，您可能无法自由地动态构建查询字符串。

First and foremost, I would strongly caution you against using SELECT * FROM tbl WHERE (col = :x OR col IS NULL) as this will most likely disqualify your query from indexing (use a query profiler). SET ANSI_NULLS is also one of those things that may not be supported in your particular database type.

A better solution (or at least a more universal solution) is, if your SQL dialect supports Coalesce, to write your query like this:

SELECT * FROM tbl WHERE col = COALESCE(:x, col)

Since col = col will always evaluate to true, passing in NULL for :x won't damage your query, and should allow for a more efficient query plan. This also has the advantage that it works from within a stored procedure, where you may not have the liberty of dynamically building a query string.

回复收藏 0 原文

微暖i 2024-08-01 15:20:28

目前，以下内容已在 python sqlite3 上进行了测试，但它应该适用于其他数据库类型，因为它非常通用。该方法与@MoshiBin 答案相同，但有一些补充：

这是使用cursor.execute()常规语法的形式，因此使用此形式时不支持空变量：

import sqlite3
conn = sqlite3.connect('mydbfile.db')
c = conn.cursor()
c.execute('SELECT * FROM TestTable WHERE colname = ?', (a, ))

为了支持空变量，您可以替换第四行to：

request = 'SELECT * FROM TestTable WHERE colname %s' % ('= ' + str(a) if a else 'IS NULL')
c.execute(request)

此外，如果变量是文本类型，还需要包含引号：

request = "SELECT * FROM TestTable WHERE colname %s" % ("= '" + a + "'" if a else 'IS NULL')

最后，如果变量本身可以包含单引号，则还需要通过加倍来转义：

request = "SELECT * FROM TestTable WHERE colname %s" % ("= '" + a.replace("'", "''") + "'" if a else 'IS NULL')

编辑：< br>
最近我发现了另外两种方法也可以在这种情况下使用，并使用常规的cursor.execute()语法，但是我现在还没有测试这个：（

c.execute('SELECT * FROM TestTable WHERE colname = :1 OR (:1 IS NULL AND colname IS NULL)', {'1': a})

感谢@BillKarwin 答案）

或者使用 CASE 表达式：

c.execute('SELECT * FROM TestTable WHERE CASE :1 WHEN NOT NULL THEN colname = :1 ELSE colname IS NULL END', {'1': a})

The following was tested on python sqlite3 by now, however it should work in other DB types since it quite general. The approach is the same to @MoshiBin answer with some additions:

Here is the form using cursor.execute() regular syntax, so the null variables is not supported while using this form:

import sqlite3
conn = sqlite3.connect('mydbfile.db')
c = conn.cursor()
c.execute('SELECT * FROM TestTable WHERE colname = ?', (a, ))

In order to support null variables you may replace the 4th line to:

request = 'SELECT * FROM TestTable WHERE colname %s' % ('= ' + str(a) if a else 'IS NULL')
c.execute(request)

Besides that if the variable is in text type, you also need to include a quotes:

request = "SELECT * FROM TestTable WHERE colname %s" % ("= '" + a + "'" if a else 'IS NULL')

Finaly if a variable can contain a single quotes itself, you also need to escape it by doubling:

request = "SELECT * FROM TestTable WHERE colname %s" % ("= '" + a.replace("'", "''") + "'" if a else 'IS NULL')

Edit:
Lately I have found two other approaches that also can be used in this case and uses regular cursor.execute() syntax, however I did't test this ones by now:

c.execute('SELECT * FROM TestTable WHERE colname = :1 OR (:1 IS NULL AND colname IS NULL)', {'1': a})

(Thx to @BillKarwin answer)

or, using the CASE expression:

c.execute('SELECT * FROM TestTable WHERE CASE :1 WHEN NOT NULL THEN colname = :1 ELSE colname IS NULL END', {'1': a})

回复收藏 0 原文

美羊羊 2024-08-01 15:20:28

这对我来说很有效。

with mysqlcon.cursor() as cursor:
    sql = "SELECT * \
            FROM `books` \
            WHERE `date` is NULL"
    cursor.execute(sql)
    books = cursor.fetchall()

    return books

It worked on my end.

with mysqlcon.cursor() as cursor:
    sql = "SELECT * \
            FROM `books` \
            WHERE `date` is NULL"
    cursor.execute(sql)
    books = cursor.fetchall()

    return books

回复收藏 0 原文

~没有更多了~