[sqlalchemy] Re: how can i generate " IS NOT True " instead of " != True " ( orm ) ?
> To be fair, it is correct in terms of doing what you asked, though if
> you want it to include NULLs I agree it doesn't do what you want...
You're absolutely correct. Poorly worded on my part. I meant to
convey that it's not the correct statement for me to call ; it is
indeed the correct sql for that statement. "!=" and "is not" are
completely different comparisons.
I just spent 4 hours digging through sqlalchemy docs and code to try
and figure out how to get this to work on columns. is & isnot should
really be column operators. there's also no "UNKNOWN" value/keyword
in sqlalchemy -- which is a concept that is in postgresql and might
have helped me in my wild chase.
Anyways, Thanks a ton!
filter(tablename.is_deleted.op("IS NOT")(True)) works perfectly ,
crisis averted!
Your coalesce idea is a good one. I feel a little bit better using
the .op() method though - this query is already kind of complex and
slow. adding a handful of coalesce statements instead of using a
native comparison is not a road i want to explore right now.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: how can i generate " IS NOT True " instead of " != True " ( orm ) ?
Jonathan Vanasco writes:
> in postgresql i have a boolean field that allows Null values.
>
> i'd like to query for the items that are not 'true'
>
> filter( tablename.is_deleted != True )
>
> creates this sql:
> is_deleted != True
>
> however this is incorrect and doesn't match the resultset i want. it
To be fair, it is correct in terms of doing what you asked, though if
you want it to include NULLs I agree it doesn't do what you want...
> needs to read :
> is_deleted IS NOT True
There are "is" and "isnot" operators in sqlalchemy.sql.operators, but I'm
not entirely sure how to trigger from within an expression (and they don't
seem to be column operators, but I'm probably missing something simple).
However, you should be able to use the generic "op" method, as in:
filter(tablename.is_deleted.op("IS NOT")(True))
Alternatively, you could explicitly manage the handling of NULL
entries:
from sqlalchemy.sql.functions import coalesce
filter(coalesce(tablename.is_deleted, False) != True)
This way you avoid the treatment of NULLs in the comparison entirely. This
method also extrapolates to other cases more readily since it works with
non-boolean fields as well.
-- David
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
