如何在 psycopg 中使用 SELECT 查询查找空值? [英] How can I find null values with SELECT query in psycopg?

问题描述

我在 python 中使用 psycopg2 库，INSERT 查询在我用 None 插入空值时效果很好，但是当我想用 None 插入 SELECT 空值时不返回任何.

I am using psycopg2 library in python and the INSERT query works good when I insert null Value with None, but when I want to do SELECT null values, with None doesn't return any.

cur.execute("SELECT id FROM registro WHERE id_movil = (%s);", (None,))

此查询不返回任何行，我不知道错误在哪里.

This query doesnt return any rows, i dont know where is the error.

有人知道如何使用 SELECT 查询来查找数据库中的空值吗?

Anyone know how to make SELECT query's to find null values in a DB?

推荐答案

首先要做的是找出它把你的命令变成了什么查询:

First thing to do is find out what query it's turning your command into:

print(cur.mogrify("SELECT id FROM registro WHERE id_movil = (%s);", (None,)))

如果这给你的不是 IS NULL 代码检查，它不会从表中得到 NULL.

If that gives you anything other than an IS NULL codition check, it won't get NULLs from the table.

具体来说，如果您看到短语 = NULL，您就会知道它没有被正确翻译⁽¹⁾，您必须执行以下操作:

Specifically, if you see the phrase = NULL, you'll know it's not being translated correctly⁽¹⁾ and you'll have to do something like:

if var is None:
    cur.execute("SELECT id FROM registro WHERE id_movil IS NULL;")
else:
    cur.execute("SELECT id FROM registro WHERE id_movil = (%s);", (var,))

或者，如果您知道您一直在寻找 NULL 值(正如您使用的常量 None 似乎表明的那样)，只需使用:

Or, if you know you're always looking for NULL values (as your use of the constant None seems to indicate), just use:

cur.execute("SELECT id FROM registro WHERE id_movil IS NULL;")

<小时>

⁽¹⁾ 当参数为 None%s 被替换为 NULLcode>，并且返回并将 = 更改为 IS(或将 <> 更改为 IS NOT).

---

⁽¹⁾ It's quite possible that only the %s is replaced with a NULL when the argument is None, and it's not quite clever enough to go back and change = into IS (or <> into IS NOT).

这可以解释为什么会这样:

This would explain why this works:

cur.execute("INSERT into mytbl value (%s);", (None,))

(因为替换 only %s 给出了你想要的命令)，但是任何带有 = NULL 的东西都不会按预期运行，除非你已经被它烧了足够多的时间以知道会发生什么:-)

(because substitution of only the %s gives the command you want), but anything with = NULL will not act as expected, unless you've been burnt by it enough times to know what to expect :-).

这篇关于如何在 psycopg 中使用 SELECT 查询查找空值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！