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
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).
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:
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):
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.
>
> --
> 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.
signature.asc
Description: Message signed with OpenPGP using GPGMail
