Re: Idiot's query: proper way to handle NULL in SELECT query?

On 09/05/10 05:20, Frank Miles wrote:
> On Sat, May 08, 2010 at 05:33:35PM -0700, Jan Urbański wrote:
>> [ psycopg2 won't do that for you ]
> 
> Thanks for your suggestions, Jan.
> 
> What I'd like my app to be able to do is to make queries regardless of
> whether the data have specific, non-NULL values {in python, the values
> will be something other than None}; or be NULL {python:None}.  ISTM that
> Mogrify needs to convert the "column_name = %s" into "column_name IS
> NULL" ; or "column_name <> %s" into "column_name IS NOT NULL" if the
> value is specified as None.

I'm afraid no driver does that for you, and psycopg2 won't do it too.
The purpose of mogrification is not second guessing what the user
wanted, it's interpolating Python objects into the query string using
some kinds of casting rules (such as None -> NULL).

> Otherwise the app - which in general must
> put together a protracted series of queries in order to answer the user's
> questions - must go through each of the values where the value of the
> column must/not be NULL.  I don't see why psycopg shouldn't do this
> (but then, see subject line).

Again, no driver will mangle your query like that, as it would seriously
violate the POLA. To answer your question in the subject: the proper way
to handle NULLs is to properly write your queries so that they handle
NULLs. psycopg2 is just a database connector, it never *writes* the
queries, it's always the user that does that.

Another layer of abstraction (read: an ORM) might be handling this for
you, and for instance SQUAlchemy transforms filter conditions like
"filter(Object.field == None)" into "object.field IS NULL" literal SQL
strings. But the job of a database connector is executing your queries
*exactly* as you wrote them, not rewriting the query in a way that
without implementing a full SQL parser and probably throwing in some
mind reading capabilities is impossible.

> COALESCE doesn't work.  While I suppose (not having tried it) that one
> could construct a query like:
>    SELECT {variable-list} FROM table WHERE COALESCE(column_name,X) = X;
> this presupposes that you have a value (X) which you can guarantee will
> never exist in the table.  Seems potentially dangerous and likely
> less efficient.

Yes, if that is what you are trying to do, then COALESCE might not be
what you want. How about

SELECT c1, c2 FROM table WHERE c1 IS NULL OR c1 = %s

if you use None as the parameter you will get rows where c1 is NULL (and
only those, because NULL = <whatever> is never true). If you use
something else than None, you will get rows where c1 is equal to that
something. That's just an example, the bottom line is: psycopg2 executes
quereis exactly as you ask it to, so it's up to you to give it correct
queries.

Alternatively you can take a look at higher abstraction layers that you
can use on top of psycopg2 that might do what you want: the Django ORM,
SQLAlchemy and others.

Cheers,
Jan
_______________________________________________
Psycopg mailing list
[email protected]
http://lists.initd.org/mailman/listinfo/psycopg