WITH RECURSIVE and ORM
Ergo
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 ?
Michael Bayer
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 sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>
Mariano Mara
I built a little extension to support WITH RECURSIVE with SA's SQL
Expression Language. You can find all about it here [1].
It might not address your problem directly but you might use it as
inspiration.
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PgsqlRecursive