null() and notnull()

[jo]

Hi all,

I would like to know if there's a notnull() function in sqlalchemy
similar to null()
to avoid things like not_(null()) ?

thank you

j

[Michael Bayer]

well usually null() and not_(null()) aren't needed as explicit
constructs. comparisons like somecol == None and somecol != None
will generate the appropriate NULL/NOT NULL expression.

[jo]

Well, MIchael, in my case a notnull() function could be very interesting because
I'm using it in a function, and would like pass values as parameters in such way:


def search( self, **kw ):
by_where_clause = []
for k,v in kw.items():
if k == 'myfield1':
if v == 'nn': # I use 'nn' to generate a NOT NULL
because we don't have a notnull() function
by_where_clause.append( self.c.field1 <> None)
else:
by_where_clause.append( self.c.field1 == v)

elif k == 'myfield2':
if v == 'nn': # I use 'nn' to generate a NOT NULL
because we don't have a notnull() function
by_where_clause.append( self.c.field2 <> None)
else:
by_where_clause.append( self.c.field2 == v)

elif k == 'myfield3':
if v == 'nn': # I use 'nn' to generate a NOT NULL
because we don't have a notnull() function
by_where_clause.append( self.c.field3 <> None)
else:
by_where_clause.append( self.c.field3 == v)
...




Mytb.search(myfield=None) -- generates WHERE myfield IS NULL
Mytb.search(myfield=null()) -- generates WHERE myfield IS NULL
Mytb.search(myfield='nn') -- generates WHERE myfield IS NOT NULL



if we have a notnull() function these thing could be easier:

def search( self, **kw ):
by_where_clause = {}
for k,v in kw.items():
by_where_clause[ k ] = v


Mytb.search(myfield=None) -- generates WHERE myfield IS NULL
Mytb.search(myfield=null()) -- generates WHERE myfield IS NULL
Mytb.search(myfield=notnull()) -- generates WHERE myfield IS NOT NULL

[a...@svilendobrev.com]

> def search( self, **kw ):
> by_where_clause = {}
> for k,v in kw.items():
> by_where_clause[ k ] = v

i guess u want to do query.filter_by(**by_where_clause) after that?

it's just a syntax sugar over .filter(). so

by_where_clause = []
for k,v in kw.items():

col = getattr(self.c,k)

if v == 'nn': # I use 'nn' to generate a NOT NULL

by_where_clause.append( c <> None)
else:
by_where_clause.append( c == v)
query.filter(and_(*by_where_clause))
that's 3 lines more ..

svil

[Michael Bayer]

why dont you just stick with None instead of "nn" ? then you just
write:

if v is None:
clause.append(self.c.field != v)
else:
clause.append(self.c.field == v)

[jo]

a...@svilendobrev.com wrote:
>> def search( self, **kw ):
>> by_where_clause = {}
>> for k,v in kw.items():
>> by_where_clause[ k ] = v
>>
> i guess u want to do query.filter_by(**by_where_clause) after that?
>

This is the final result, and I'm not problems about it.
what I'm looking for is a function which returns the opposite of null()
to use as a function parameter

null() translates to 'IS NULL'
my need is a functions which translates to 'IS NOT NULL', something like
not_null() / notnull()


j

[jo]

Michael Bayer wrote:
> why dont you just stick with None instead of "nn" ? then you just
> write:
>
> if v is None:
> clause.append(self.c.field != v)
> else:
> clause.append(self.c.field == v)

It could be an idea but not intuitive and unnatural

because None = IS NOT NULL (very ugly) :-(
and null() = IS NULL

j

[Michael Bayer]

On Mar 19, 2009, at 12:34 PM, jo wrote:

>
> Michael Bayer wrote:
>> why dont you just stick with None instead of "nn" ? then you just
>> write:
>>
>> if v is None:
>> clause.append(self.c.field != v)
>> else:
>> clause.append(self.c.field == v)
> It could be an idea but not intuitive and unnatural
>
> because None = IS NOT NULL (very ugly) :-(
> and null() = IS NULL

well the logic you're doing above seems unnatural all by itself, that
if v is NULL you want a NOT NULL. that would seem just as
"unnaturual" using "null()" as "None".

just make yourself a notnull = not_(null()) and problem solved.

[jo]

Michael Bayer ha scritto:

the hint you suggested me doesn't work because SQLAlchemy translate it
to " = (NOT NULL) " which is invalid sql...

print Tabella.search(tabella_id=not_(null())).compile()

SELECT tabella.id, ...
FROM tabella
WHERE tabella.id = (NOT NULL)

ERROR: operator does not exist: integer = boolean
LINE 3: WHERE tabella.id = (NOT NULL);
^
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.

> >
>

[Michael Bayer]

On Mar 20, 2009, at 4:40 AM, jo wrote:

>
>
>
>
> Michael Bayer ha scritto:
>> On Mar 19, 2009, at 12:34 PM, jo wrote:
>>
>>
>>> Michael Bayer wrote:
>>>
>>>> why dont you just stick with None instead of "nn" ? then you just
>>>> write:
>>>>
>>>> if v is None:
>>>> clause.append(self.c.field != v)
>>>> else:
>>>> clause.append(self.c.field == v)
>>>>
>>> It could be an idea but not intuitive and unnatural
>>>
>>> because None = IS NOT NULL (very ugly) :-(
>>> and null() = IS NULL
>>>
>>
>> well the logic you're doing above seems unnatural all by itself, that
>> if v is NULL you want a NOT NULL. that would seem just as
>> "unnaturual" using "null()" as "None".
>>
>> just make yourself a notnull = not_(null()) and problem solved.
>>
>>
> the hint you suggested me doesn't work because SQLAlchemy translate it
> to " = (NOT NULL) " which is invalid sql...
>

sqlalchemy produces IS NOT NULL using the != operator in conjunction
with None or null(). I don't see a reason to add another way of doing
the same thing.