How to execute raw sql query and use model objects in Flask with SQLAlchemy

Question

I need to build a complex query and I prefer to use raw postgres. But the query returns a sqlalchemy.engine.result.ResultProxy object and when I iterate through that each of those objects is a sqlalchemy.engine.result.RowProxy object

search_term = "%{0}%".format(d)
items = db.engine.execute("SELECT inventory.id,inventory.location,inventory.amount,inventory.units, plant_material.variety FROM inventory LEFT JOIN plant_material ON inventory.plant_material_id = plant_material.id WHERE plant_material.variety ILIKE %s", (search_term))

This standard query returns my model object that I define in Marshmallow, <class 'app.models.Inventory'>:

Inventory.query.filter(Inventory.common_name.like(search_term)).limit(10)

How can I return the models.Inventory object and use raw sql?

If you are curious about my model definitions check out this gist

Answer 1

https://docs.sqlalchemy.org/en/14/orm/queryguide.html#selecting-orm-entities-and-attributes

from sqlalchemy.sql import text, select
db.session.execute(select(ModelClass).from_statement(text("SELECT ..."))).scalars().all()