[sqlalchemy] Re: how can i generate IS NOT True instead of != True ( orm ) ?
Jonathan Vanasco jonat...@findmeon.com 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.
[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.
