On Thu, Jan 9, 2014 at 12:20 AM, Michael Bayer <mike...@zzzcomputing.com>wrote:
>
> On Jan 8, 2014, at 7:54 AM, limodou <limo...@gmail.com> wrote:
>
> I think there are two things here:
>>
>
> 1. Should None be converted to NULL when deal with "condition & None" or
> "and_(condition, None)"
> 2. How to combine multiple condition into one condition with and_
>
> And I think the second question should be resolved by application itself,
> we just need to obey some good guide, that's ok.
>
> But for the first question, the old style I think None's behavior just
> like true(), but in 0.9.x, is not. So this makes the uncompatible process.
> Here is a test for 0.9.1:
>
> >>> print and_('id=3', None)
> id=3 AND NULL
> >>> print and_('id=3', '')
> id=3
> >>> print and_('id=3', true())
> id=3
>
> So empty string is the same as true(), and why empty string can be treated
> as true() but None is treated as NULL? Commonly, python will treat None,
> empty string are false boolean value, but here sqlalchemy does do like
> that obviously.
>
>
> Here is a sample script using the code you gave. Your code is equally
> broken in both 0.8 and 0.9, as if the list of conditions is empty, the same
> SELECT is produced on both versions both of which are invalid with “WHERE
> NULL”:
>
> from sqlalchemy.sql import select, column
>
> def my_select(conditions):
> cond = None
> for c in conditions:
> cond = c & cond
>
> return select([column('x')]).where(cond)
>
> print my_select([])
>
> 0.8.4:
>
> SELECT x
> WHERE NULL
>
> 0.9.1:
>
> SELECT x
> WHERE NULL
>
>
But I'm not talking about empty condition, but condition & None. In
application, I can test if the condition is None and don't execute sql at
all.
> Therefore, your script cannot work in either 0.8 or 0.9, unless you fix it
> as follows, in which case it works the same in both versions:
>
> def my_select(conditions):
> cond = conditions[0]
> for c in conditions[1:]:
> cond = c & cond
> stmt = select([column(‘x’)])
> if cond is not None:
> stmt = stmt.where(cond)
> return stmt
>
> or you assume that “conditions” is non-empty, in which case, as I
> mentioned earlier, do this:
>
> def my_select(conditions):
> cond = conditions[0]
> for c in conditions[1:]:
> cond = c & cond
>
> return select([column('x')]).where(cond)
>
> or preferably, just say and_(*conditions).
>
This thing is about how to deal with condition combination, if there is
None value, above code is still not right. So the correct code maybe need
add some test like "if c is None:".
And my point is not mainly about how to write correct condition combine,
but the which the right way to convert None.
>
> as for interpreting None as NULL, None has always been treated as NULL in
> a SQL expression context - it is treated as NULL when used as a WHERE
> condition by itself and it is treated as NULL when used in a comparison.
> 0.8 is inconsistent that it is not treated as NULL when it happens to be
> part of an AND:
>
>
Yes, I saw the code about 0.8.X and 0.9.1, the None convertion are the
same. But difference between them is in AND process. So this "inconsistent"
that you mean it's a bug in 0.8?
> from sqlalchemy.sql import select, column, literal
>
> c = column('x')
>
> print select([c]).where(c == 5) # 0.8 / 0.9: SELECT x WHERE x = :x_1
>
> print select([c]).where(c == None) # 0.8 / 0.9: SELECT x WHERE x IS NULL
>
> print select([c]).where("5”) # 0.8 / 0.9: SELECT x WHERE 5
>
> print select([c]).where(None) # 0.8 / 0.9: SELECT x WHERE NULL
>
> print select([c]).where((c == 5) & "5”) # 0.8 / 0.9: SELECT x WHERE x =
> :x_1 AND 5
>
> print select([c]).where((c == 5) & None) # 0.8: SELECT x WHERE x = :x_1
> # 0.9: SELECT x WHERE x = :x_1 AND NULL
>
> The only thing that might be more appropriate than coercing where(None)
> and where(x & None) into NULL would be raising an error - because in fact
> where(<x>) and where(expr & <x>) already throws an exception if <x> is not
> a SQL expression, string, or None/True/False (on both):
>
I think raise exception maybe better, so that it'll let user to know what
wrong with the condition. Otherwise some code like condition & None can run
in 0.8.X very well, but in 0.9 it'll only return nothing without any error
thrown at all. It will break the old code.
>
> print select([c]).where(5) # 0.8 / 0.9 - raises exception
>
> print select([c]).where(c & 5) # 0.8 / 0.9 - raises exception
>
> None also doesn’t act like true() in 0.8:
>
> print select([c]).where(true()) # 0.8: SELECT x WHERE true
>
> print select([c]).where(None) # 0.8: SELECT x WHERE NULL
>
>
> so overall, this change is mentioned in the “Migration Guide” exactly
> because it is in fact a behavioral change. You can argue it should be
> listed under “Core Behavioral Changes” instead of “Behavioral Improvements”
> and I wouldn’t have much issue with that, it is just listed under
> “Improvements” because it doesn’t change the behavior of code that’s
> written correctly in the first place.
>
>
>
Or the doc add the inconsistant about "condition & None" maybe the better.
Thank you very much.
--
I like python!
UliPad <<The Python Editor>>: http://code.google.com/p/ulipad/
UliWeb <<simple web framework>>: https://github.com/limodou/uliweb
My Blog: http://my.oschina.net/limodou
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
