Search code examples
pythonormsqlalchemy

SQLAlchemy map custom query results to a specific class

I have a next ORM model class:

class SearchResults(Base):
    __abstract__ = True
    author_id = Column(String)
    article_id = Column(String)

I would like to return only two columns:

  • author_id
  • article_id

This class presents an result of a search query it has no corresponding table.

ORM is failing with an error:

 sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped entity expected - got '<class 'app.db.models.SearchResults'>'

When class is used in the SQLAlchemy query like this:

search_results = db.query(SearchResults).select_from(models.Article).join(models.Author).all()

And when columns are specified I am getting a Tuple instead of a class in the results:

search_results = db.query(models.Acticle.id, models.Author.author_id).select_from(models.Article).join(models.Author).all()

Question: is it possible to map non-table results to a class?

Solution

  • Please take a look at the Mapping a Class against Arbitrary Selects or Mapping a Class against Multiple Tables sections of the documentation.

    For the second example you can get tables for Article and Author by using:

    author_table = models.Author.__table__
article_table = modesl.Article.__table__