I now have a less klugey (no strings) implementation that I came to after
circling back to the the recursive CTE example in the docs, but is
structured in a way that I can wrap my head around a lot better:
import sqlalchemy as sa
#initialize a recursive CTE construct using the non-recursive term...
cte_init = sa.\
select([sa.literal(0).label("x"), ]).\
cte(recursive = True, name = "cte")
#Make an alias that can be thought of as the CTE's working table...
# - this is *mandatory* for SQLAlchemy to build the query correctly,
# even though the alias itself is not required in this particular
# resulting query (is it ever needed?)
cte_working = cte_init.alias("working")
cte_recurs = sa.\
select([cte_working.c.x + 1, ]).\
where(cte_working.c.x < 10)
cte = cte_init.union_all(cte_recurs)
statement = sa.select([cte.c.x, ])
print statement
which yields:
WITH RECURSIVE cte(x) AS
(SELECT :param_1 AS x UNION ALL SELECT working.x + :x_1 AS anon_1
FROM cte AS working
WHERE working.x < :x_2)
SELECT cte.x
FROM cte
>>> statement.compile().params
{u'x_2': 10, u'param_1': 0, u'x_1': 1}
which works perfectly in PostgreSQL:
>> print conn.execute(statement).fetchall()
[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
As per the comments in the code, the alias is absolutely required (it gets
mangled without the alias), but is seemingly pointless in the resulting
SQL. Of course, it wouldn't be that surprising if it were required for a
more complex CTE (or strict SQL compliance?), but I'm wondering if it is
just needed for the underlying query generator?
Anyway - with this more closely matching the example in the docs I wonder
what the heck my problem was originally.
That said, it might be useful to structure the documented CTE examples in a
way like I've done above (init, working, recursive, and final cte) as an
easier introduction to how to use CTEs in SQLAlchemy (at least for the
recursive ones). Hopefully it helps someone else.
Russ
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
