Re: [sqlalchemy] How to use user defined python function inside a sqlalchemy filter?

[Yaakov Bressler]

That's a really interesting distinction Simon, and thank you for pointing 
all this out. It makes sense that anything passed to the DB would need to 
SQL compatible.

What are your thoughts about creating functions on the DB side and 
executing through the ORM? Too much work for something like this?* (Ex: 
Postgres offers PLP 
) – perhaps 
integrating advanced python functions can become a future feature for 
SQLAlchemy?*

On Friday, May 7, 2021 at 7:09:46 AM UTC-4 Simon King wrote:

> The point of hybrid attributes is to allow you to construct a property
> that can be evaluated against an instance (in which case it is "normal
> python"), or against a class (in which case it needs to return an SQL
> expression). *Sometimes* the same python code can work in both
> contexts, but only if it is a very simple expression.
>
> Here's your example:
>
> @hybrid_property
> def is_id_valid(self):
> # some logic here
> if self.id % 3 == 0:
> return True
> else:
> return False
>
> If you had already retrieved an instance of GeneralBeqReq from the
> database, you could access its "is_id_valid property" and it would
> work as expected, because "self.id" would be a normal python integer.
> But if you try to access it from the class (ie.
> GeneralBeqReq.is_id_valid), it won't do what you expect. "self" will
> actually be the class, GeneralBeqReq. "self.id" will be a special
> SQLAlchemy "InstrumentedAttribute" object. It happens that
> InstrumentedAttributes do support the % operator, returning an SQL
> expression object, so "self.id % 3 == 0" will actually return an SQL
> expression. But using it inside an "if" statement doesn't make any
> sense - you're checking the boolean value of the SQL expression
> object, not the result of evaluating it against any particular row. So
> GeneralBeqReq.is_id_valid is just going to return a constant (either
> True or False, not sure which).
>
> You'd need to add an alternative definition of the is_id_valid
> property which returns an SQL expression:
>
>
> https://docs.sqlalchemy.org/en/14/orm/extensions/hybrid.html#defining-expression-behavior-distinct-from-attribute-behavior
>
> If you can't express your validation function as an SQL expression,
> you can't pass it to Query. You'd have to postprocess the query
> results in python instead, like this:
>
> a = session.query(GeneralBeqReq)\
> .filter(GeneralBeqReq.c.BeqReqStatus == 1)\
> .all()
> a = [obj for obj in a if obj.is_id_valid]
>
> Simon
>
> On Wed, May 5, 2021 at 4:28 PM Yaakov Bressler  
> wrote:
> >
> > Query would be modified to the following:
> >
> > a = session.query(GeneralBeqReq)\
> > .filter(
> > GeneralBeqReq.c.BeqReqStatus == 1, # this doesn't look right, but whatevz
> > GeneralBeqReq.is_id_valid() == True, # a bit redundant, but explicit is 
> better than implicit
> > )\
> > .all()
> >
> > On Wednesday, May 5, 2021 at 11:26:01 AM UTC-4 Yaakov Bressler wrote:
> >>
> >> Would it be wrong to assume that the desired function could be added as 
> a hybrid attribute, then queried through the class obj?
> >>
> >> Example:
> >>
> >> from sqlalchemy.ext.hybrid import hybrid_property
> >>
> >> class GeneralBeqReq(Base):
> >> ...
> >> @hybrid_property
> >> def is_id_valid(self):
> >> # some logic here
> >> if self.id % 3 == 0:
> >> return True
> >> else:
> >> return False
> >>
> >> On Wednesday, April 28, 2021 at 5:01:32 AM UTC-4 Simon King wrote:
> >>>
> >>> Parameters that you pass to the Query.filter function are eventually
> >>> going to be rendered into an SQL statement, so your is_id_valid
> >>> function probably needs to return something built from SQLAlchemy's
> >>> SQL expression language:
> >>> https://docs.sqlalchemy.org/en/14/core/tutorial.html
> >>>
> >>> If you can explain the sort of validation that is_id_valid needs to
> >>> do, we might be able to help more.
> >>>
> >>> Simon
> >>>
> >>>
> >>> On Wed, Apr 28, 2021 at 6:33 AM Gyanaranjan Nayak  
> wrote:
> >>> >
> >>> > I have a function with name is_id_valid(id) which returns either 
> True or False.
> >>> >
> >>> > I want to pass this function inside a sqlalchemy query inside the 
> filter condition.
> >>> > My query example is :
> >>> >
> >>> > a = session.query(GeneralBeqReq).filter(GeneralBeqReq.c.BeqReqStatus 
> == 1,
> >>> > is_id_valid (GeneralBeqReq.c.id) == True).all()
> >>> >
> >>> > When I run the above query It is throwing the following error.
> >>> >
> >>> > AttributeError: Neither 'Column' object nor 'Comparator' object has 
> an attribute 'strip'
> >>> >
> >>> >
> >>> > Can you please guide me how to use this function inside my query ?
> >>> >
> >>> >
> >>> > --
> >>> > SQLAlchemy -
> >>> > The Python SQL Toolkit and Object Relational Mapper
> >>> >
> >>> > http://www.sqlalchemy.org/
> >>> >
> >>> > To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> >>> > ---
> >>> > You received 

Re: [sqlalchemy] How to use user defined python function inside a sqlalchemy filter?

[Simon King]

The point of hybrid attributes is to allow you to construct a property
that can be evaluated against an instance (in which case it is "normal
python"), or against a class (in which case it needs to return an SQL
expression). *Sometimes* the same python code can work in both
contexts, but only if it is a very simple expression.

Here's your example:

@hybrid_property
def is_id_valid(self):
# some logic here
if self.id % 3 == 0:
return True
else:
return False

If you had already retrieved an instance of GeneralBeqReq from the
database, you could access its "is_id_valid property" and it would
work as expected, because "self.id" would be a normal python integer.
But if you try to access it from the class (ie.
GeneralBeqReq.is_id_valid), it won't do what you expect. "self" will
actually be the class, GeneralBeqReq. "self.id" will be a special
SQLAlchemy "InstrumentedAttribute" object. It happens that
InstrumentedAttributes do support the % operator, returning an SQL
expression object, so "self.id % 3 == 0" will actually return an SQL
expression. But using it inside an "if" statement doesn't make any
sense - you're checking the boolean value of the SQL expression
object, not the result of evaluating it against any particular row. So
GeneralBeqReq.is_id_valid is just going to return a constant (either
True or False, not sure which).

You'd need to add an alternative definition of the is_id_valid
property which returns an SQL expression:

https://docs.sqlalchemy.org/en/14/orm/extensions/hybrid.html#defining-expression-behavior-distinct-from-attribute-behavior

If you can't express your validation function as an SQL expression,
you can't pass it to Query. You'd have to postprocess the query
results in python instead, like this:

a = session.query(GeneralBeqReq)\
.filter(GeneralBeqReq.c.BeqReqStatus == 1)\
.all()
a = [obj for obj in a if obj.is_id_valid]

Simon

On Wed, May 5, 2021 at 4:28 PM Yaakov Bressler  wrote:
>
> Query would be modified to the following:
>
> a = session.query(GeneralBeqReq)\
> .filter(
> GeneralBeqReq.c.BeqReqStatus == 1, # this doesn't look right, but 
> whatevz
> GeneralBeqReq.is_id_valid() == True, # a bit redundant, but explicit 
> is better than implicit
> )\
> .all()
>
> On Wednesday, May 5, 2021 at 11:26:01 AM UTC-4 Yaakov Bressler wrote:
>>
>> Would it be wrong to assume that the desired function could be added as a 
>> hybrid attribute, then queried through the class obj?
>>
>> Example:
>>
>> from sqlalchemy.ext.hybrid import hybrid_property
>>
>> class GeneralBeqReq(Base):
>> ...
>> @hybrid_property
>> def is_id_valid(self):
>> # some logic here
>> if self.id % 3 == 0:
>> return True
>> else:
>> return False
>>
>> On Wednesday, April 28, 2021 at 5:01:32 AM UTC-4 Simon King wrote:
>>>
>>> Parameters that you pass to the Query.filter function are eventually
>>> going to be rendered into an SQL statement, so your is_id_valid
>>> function probably needs to return something built from SQLAlchemy's
>>> SQL expression language:
>>> https://docs.sqlalchemy.org/en/14/core/tutorial.html
>>>
>>> If you can explain the sort of validation that is_id_valid needs to
>>> do, we might be able to help more.
>>>
>>> Simon
>>>
>>>
>>> On Wed, Apr 28, 2021 at 6:33 AM Gyanaranjan Nayak  wrote:
>>> >
>>> > I have a function with name is_id_valid(id) which returns either True or 
>>> > False.
>>> >
>>> > I want to pass this function inside a sqlalchemy query inside the filter 
>>> > condition.
>>> > My query example is :
>>> >
>>> > a = session.query(GeneralBeqReq).filter(GeneralBeqReq.c.BeqReqStatus == 1,
>>> > is_id_valid (GeneralBeqReq.c.id) == True).all()
>>> >
>>> > When I run the above query It is throwing the following error.
>>> >
>>> > AttributeError: Neither 'Column' object nor 'Comparator' object has an 
>>> > attribute 'strip'
>>> >
>>> >
>>> > Can you please guide me how to use this function inside my query ?
>>> >
>>> >
>>> > --
>>> > SQLAlchemy -
>>> > The Python SQL Toolkit and Object Relational Mapper
>>> >
>>> > http://www.sqlalchemy.org/
>>> >
>>> > To post example code, please provide an MCVE: Minimal, Complete, and 
>>> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
>>> > description.
>>> > ---
>>> > 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+...@googlegroups.com.
>>> > To view this discussion on the web visit 
>>> > https://groups.google.com/d/msgid/sqlalchemy/f388cfb8-0b0d-4ab3-8e26-84c4eb91b4a9n%40googlegroups.com.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
>