How to use user defined python function inside a sqlalchemy filter?

[Gyanaranjan Nayak]

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 ?

[Simon King]

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

> --
> 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.

[Yaakov Bressler]

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

[Yaakov Bressler]

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()

[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

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/271604e6-8365-434a-8b5a-d37095c88549n%40googlegroups.com.

[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?