SQLAlchemy - 您可以向查询对象添加自定义方法吗? [英] SQLAlchemy - can you add custom methods to the query object?

问题描述

有没有办法为查询对象创建自定义方法，以便您可以执行此类操作?

Is there a way to create custom methods to the query object so you can do something like this?

User.query.all_active()

其中 all_active() 本质上是 .filter(User.is_active == True)

并且能够过滤掉它?

User.query.all_active().filter(User.age == 30)

推荐答案

您可以子类化基础 Query 类以添加您自己的方法:

You can subclass the base Query class to add your own methods:

from sqlalchemy.orm import Query

class MyQuery(Query):

  def all_active(self):
    return self.filter(User.is_active == True)

然后您告诉 SQLAlchemy 在创建会话时使用这个新的查询类 (此处的文档).从您的代码看来，您可能正在使用 Flask-SQLAlchemy，因此您可以按如下方式进行操作:

You then tell SQLAlchemy to use this new query class when you create the session (docs here). From your code it looks like you might be using Flask-SQLAlchemy, so you would do it as follows:

db = SQLAlchemy(session_options={'query_cls': MyQuery})

否则你会直接将参数传递给 sessionmaker:

Otherwise you would pass the argument directly to the sessionmaker:

sessionmaker(bind=engine, query_cls=MyQuery)

截至目前，这个新的查询对象并不是那么有趣，因为我们在方法中对 User 类进行了硬编码，因此它不适用于其他任何东西.更好的实现将使用查询的基础类来确定要应用的过滤器.这有点棘手，但也可以做到:

As of right now, this new query object isn't that interesting because we hardcoded the User class in the method, so it won't work for anything else. A better implementation would use the query's underlying class to determine which filter to apply. This is slightly tricky but can be done as well:

class MyOtherQuery(Query):

  def _get_models(self):
    """Returns the query's underlying model classes."""
    if hasattr(query, 'attr'):
      # we are dealing with a subquery
      return [query.attr.target_mapper]
    else:
      return [
        d['expr'].class_
        for d in query.column_descriptions
        if isinstance(d['expr'], Mapper)
      ]

  def all_active(self):
    model_class = self._get_models()[0]
    return self.filter(model_class.is_active == True)

最后，动态关系(如果有的话)不会使用这个新的查询类.为了让那些也使用它，您可以在创建关系时将其作为参数传递:

Finally, this new query class won't be used by dynamic relationships (if you have any). To let those also use it, you can pass it as argument when you create the relationship:

users = relationship(..., query_class=MyOtherQuery)

这篇关于SQLAlchemy - 您可以向查询对象添加自定义方法吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！