Mapping a table function to a model using SQLAlchamy

Question

I have a CTE which resides in a table function which requires a parameter being passed into it. The data I need is then called with something like

SELECT * FROM myThingFunction('e543149c-6589-49c6-b962-bf2503c0e278')

What I would like to do if possible is map a SQLAlchamy model so that I can apply filters, limits etc to the record set returned, for example

qry = session.query(Thing).limit(100)

What I am struggling with is how do I handle the parameter. I know that I am treating the function like a table which feels a bit wrong as the function is more of a composite set of relations rather than a table mapping to just one type of domain object but I need to get this data into Python somehow.

Answer 1

Have you seen the recipe for mapping arbitrary selects? You can write a factory method which returns a class representing the query for a given parameter:

def myThingFunction(param):
  tmpSelect = select(..)
  class tmpCls(Base):
    __table__ = tmpSelect
  return tmpCls

But there is a note below this recipe which states that creating a mapping is unnecessary. I haven't tried it, but in principle,

session.query(func.myThingFunction("bar")).all()

might work, too. (func.foo creates a GenericFunction on the fly, see the documentation for FunctionElement and below.)