SQLAlchemy – can you add custom methods to the query object?

by

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)

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

Otherwise you would pass the argument directly to the sessionmaker:

sessionmaker(bind=engine, query_cls=MyQuery)

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)

More Related Contents:

  1. Difference between filter and filter_by in SQLAlchemy
  2. Sqlite / SQLAlchemy: how to enforce Foreign Keys?
  3. SQLAlchemy IN clause
  4. Flask SQLAlchemy query, specify column names
  5. Executing multiple statements with Postgresql via SQLAlchemy does not persist changes
  6. How can I bind a list to a parameter in a custom query in SQLAlchemy?
  7. How to disable SQLAlchemy caching?
  8. memory-efficient built-in SqlAlchemy iterator/generator?
  9. sqlalchemy unique across multiple columns
  10. How to create a new database using SQLAlchemy?
  11. How can I use psycopg2.extras in sqlalchemy?
  12. Multi-threaded use of SQLAlchemy
  13. How to get column names from SQLAlchemy result (declarative syntax)
  14. How to discover table properties from SQLAlchemy mapped object
  15. Dynamic Class Creation in SQLAlchemy
  16. SQLAlchemy equivalent to SQL “LIKE” statement
  17. How to get rows which match a list of 3-tuples conditions with SQLAlchemy
  18. How do I INSERT INTO t1 (SELECT * FROM t2) in SQLAlchemy?
  19. Creating partial unique index with sqlalchemy on Postgres
  20. SQLAlchemy Joinedload filter column
  21. creating a database outside the application context
  22. SQLAlchemy func.count on boolean column
  23. How to close a SQLAlchemy session?
  24. sqlalchemy existing database query
  25. Flask-SQLAlchemy Constructor
  26. convert selected datetime to date in sqlalchemy
  27. Is possible to mapping view with class using mapper in SqlAlchemy?
  28. How to prevent UUID primary key for new SQLAlchemy objects being created with the same value
  29. SQLALchemy dynamic filter_by
  30. SQLAlchemy ordering by count on a many to many relationship

Leave a Comment