CODEWITHSUNDEEP
pythonnullpsycopg2
Telmo Aldalur
Telmo Aldalur

Reputation: 63

How can I find null values with SELECT query in psycopg?

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.

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

Upvotes: 6

Views: 7183

Answers (4)

hwjp
hwjp

Reputation: 16081

An alternative which I'm currently experimenting with is a postgres setting called transform_null_equals (https://www.postgresql.org/docs/13/runtime-config-compatible.html#GUC-TRANSFORM-NULL-EQUALS)

you can set it in postgresql.conf or however you do your config (i'm using docker-compose for local dev and RDS for prod, so that means a parameter group).

transform_null_equals = 1

The effect is that postgres then just magically transforms any = NULL queries into IS NULL without you having to think about it! 🎉

Upvotes: 1

Rad
Rad

Reputation: 8379

Try this

cur.execute("SELECT id FROM registro WHERE (id_movil = (%s) or id_movil is null);", (movil,))

Upvotes: 2

paxdiablo
paxdiablo

Reputation: 882058

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,)))

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

Specifically, if you see the phrase = NULL, you'll know it's not being translated correctly(1) 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,))

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;")

(1) 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,))

(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 :-).

Upvotes: 8

chucksmash
chucksmash

Reputation: 6007

There's a different syntax for checking whether something is NULL-valued:

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

The same applies for getting everything that is not NULL:

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

EDIT:

You can combine WHERE clauses using AND just like you normally would:

cur.execute(
    "SELECT id FROM registro WHERE id_movil IS NULL AND name = (%s);",
    ('Bob',)
)

Upvotes: 0

Related Questions