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

On Sun, May 09, 2010 at 02:48:05AM -0700, Jan Urbański wrote:
> 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.

Good to know, though (for me) disappointing.

> 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.

What is "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.

I don't see where any ambiguity (or "mind reading") is involved.  If one
program can do it, that says it can be done.  But if it is unrealistic to
expect psycopg to have this capability, fine - that answers my question.

> > 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.

Along this vein, I suppose one could construct the query (for '=') like:

  SELECT c2 FROM table WHERE (c1 IS NULL AND %s IS NULL) OR (c1 = %s);

and repeat the parameter in that list.  Hmmn.  Not too bad, though
somewhat clunky if the query depends on a significant number of
parameters.

> 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.

My program already requires that the users install about 10
dependencies on its Windows version.  I'm really hesitant to add
even one more layer - especially as some of these layers have
version dependencies.

I was hoping that I was missing some higher-level capability within
psycopg, but if it's not there, and there is no realistic hope that it
will be within its capabilities, I will continue to do what I have in
the past: essentially rewriting the query depending on whether the
value[s] were NULL/None or not.

Thanks for helping me understand the limitations of psycopg.

	-Frank
_______________________________________________
Psycopg mailing list
[email protected]
http://lists.initd.org/mailman/listinfo/psycopg