On Jun 27, 2010, at 1:11 PM, Ergo wrote: > Hello, > > I'm looking for a way to execute a recursive query and get ORM > instances with it. > > http://www.postgresql.org/docs/8.4/interactive/queries-with.html > > I know we don't have any direct support for recursive syntax in SA, > but is there a way to execure arbitrary query that would return all > the data to build ORM objects and use those objects later ?
You can feed any textual statement into Query() to return ORM objects using from_statement(): http://www.sqlalchemy.org/docs/ormtutorial.html#using-literal-sql If you're much more ambitious than that, and you'd like to try generating a WITH clause into a select() construct, this is possible as well, although the thorny part would be the SQL expressions inside the WITH. Below is a proof of concept which sneaks additional properties onto the select() construct which affect its string compilation, and access those attributes via the Query. You can probably get it to work for simple column expressions inside of a WITH. The part where it would break down is when Query starts generating subqueries of itself, and it makes copies of the select() - a lot more thought and testing would be required for that. Subquery generation is usually avoidable if you aren't using eager loads or joined inheritance. from sqlalchemy.ext.compiler import compiles from sqlalchemy.sql.expression import Select import re @compiles(Select) def compile(element, compiler, **kw): s = compiler.visit_select(element, **kw) if hasattr(element, '_apply_special_comment'): s = re.sub(r'^SELECT ', '-- SPECIAL COMMENT\nSELECT ', s) return s from sqlalchemy.orm import sessionmaker from sqlalchemy.orm.query import Query, _generative class MySpecialQuery(Query): _apply_special_comment = False @_generative() def with_special_comment(self): self._apply_special_comment = True def _compile_context(self, *arg, **kw): context = Query._compile_context(self, *arg, **kw) if self._apply_special_comment: context.statement._apply_special_comment = True return context from sqlalchemy.sql import column Session = sessionmaker(query_cls=MySpecialQuery) session = Session() print session.query(column("foo")).statement print session.query(column("foo")).with_special_comment().statement > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
