On Mon, Jul 9, 2012 at 9:53 PM, Michael Bayer <mike...@zzzcomputing.com>wrote:
> First off, there's a bug with CTE + union in 0.7.8 and earlier. Get
> 0.7.9 from the hg tip linked on the download page.
>
> Next, maybe try calling cte() *after* you've done
> select(...).union_all(otherselect()). Not sure if that will do it though.
>
I tried the tip version (which was '0.8.0b1') and it didn't help me (or I'm
daft). I also tried moving the cte() to after the union_all, as suggested,
but that didn't help either.
With a combo of the given advice, some keep-trying-everything perseverance,
and reverting to some string hacks, I've got it working with the code below:
import sqlalchemy as sa
cte_init = sa.select([sa.literal(0).label("x"), ])
cte_recurs = sa.select(
["cte.x + 1", ],
from_obj = "cte"
).where("cte.x < 10")
cte = cte_init.\
union_all(cte_recurs).\
cte(name = "cte", recursive = True)
statement = sa.select([cte, ])
print statement
which yields the following functional and correct SQL (in both 0.7.8 and
0.8.0b1):
WITH RECURSIVE cte(x) AS
(SELECT :param_1 AS x UNION ALL SELECT cte.x + 1
FROM cte
WHERE cte.x < 10)
SELECT cte.x
FROM cte
>>> statement.compile().params
{u'param_1': 0}
This was mainly an exercise in figuring out how to get a small
self-referencing (?) CTE working with SQLAlchemy. I need to extend this to
some significantly larger CTEs where I am hoping to be able to construct
the base select expressions (cte_init and cte_recurs) with all the help
sqlalchemy provides in this respect.
With what I've kluged together so far, it seems like my main limitation is
having to use the string expressions to cheat direct references to the
"cte" itself. I can work with that, but... it still seems wrong.
Especially since the recursive example in the docs [1] works properly with
aliasing, and does the .cte() on the initial select.
Now that I've got this working via hack, I'll circle back and try to do it
using the more complex recursive example in the docs again.
[1]
http://docs.sqlalchemy.org/en/rel_0_7/core/expression_api.html#sqlalchemy.sql.expression._SelectBase.cte
--
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.
