Can I use a function in a SQLAlchemy filter?

Question

I have a function that checks an object for some properties and returns boolean values ​​depending on the result. It is too complicated to write it to a filter, but it works and returns the correct value.

Now I want to use sqlalchemy to return all objects for which this function returns True. I tried:

DBSession.query(MyObject).filter(self.check_attributes(MyObject) == True).all() 

and

 DBSession.query(MyObject).filter(self.check_attributes(MyObject)).all() 

Both could not select the desired objects. What am I doing wrong?

Answer 1

As I said in my comment, hybrid_method / hybrid_property is the right template for your use case. It may seem complicated at first, but it is actually quite simple. The first version of the function works just like a Python method or property, and the second part acts like a class method. SQLAlchemy filters work on a class for generating SQL.

This is just a useless example, but instead of therabouts your example may have a complex calculation.

If you do not need to pass any parameters , I suggest using hybrid_property .

 class MyObject(Model): name = Column(String) num = Column(Integer) @hybrid_method def therabouts(self, n): return self.num > n - 5 and self.num <= n + 5 @therabouts.expression def therabouts(cls, n): return and_(cls.num > n - 5, cls.num <= n + 5) @hybrid_property def is_al(self): return self.name.lower().startswith('al') @is_al.expression def is_al(cls): return cls.name.ilike('al%') # When used as a class method @thereabouts.expression is called near20 = session.query(MyObject).filter(MyObject.therabouts(20)).first() # When used as an instance, @hybrid_method is called near20.therabouts(20) # True near20.therabouts(22) # Maybe True near20.therabouts(50) # False # filter example (class) all_als = session.query(MyObject).filter(MyObject.is_al).all() for al in all_als: print al.name # output Alan, Alanzo, Albert... # instance example (self) bob = MyObject(name='Robert') print bob.is_al # False 

Answer 2

If “use a function” means “write filters inside a function”, you can certainly - you just need to pass the correct argument (query) and use its return (filtered query) in the right place

Take an example from the SQLAlchemy tutorial:

 wendy = session.query(User).filter_by(name='wendy').one() 

You can easily move the filter to a function:

 def my_filter(query): return query.filter_by(name='wendy') wendy = my_filter(session.query(User)).one() 

---

However, if the function "that checks the object for some properties and returns boolean values ​​depending on the result", you mean a function that takes a database record as an argument, I don’t think it can be done (without undermining the whole purpose of using SQL) .

- EDIT - . As doog abides points out, the hybrid extension, while not working with database records, does something that is equivalent for many practical purposes.

---

Of course, some people will insist on writing something like:

 all_users = session.query(User).all() wendy= filter( lambda u:u.name=='wendy', all_users ) 

But, for the sake of common sense, your fellow programmers and users, do not do this.