Aliasing a constant within a recursive CTE

[Russ]

I'm trying to use the new CTE support in SQLAlchemy in a way that will
allow me to reference the recursion level as a field in the query
result. This is easy in a straight SQL CTE by aliasing a constant in
the non-recursive part, and then referencing the alias in the
recursive part. The limited example below (tested in PostgreSQL)
demonstrates this with a single field, and yields 0-10 inclusive:

WITH RECURSIVE cte AS (
SELECT 0 as x
UNION ALL
SELECT cte.x + 1 FROM cte WHERE cte.x < 10
)
SELECT * from cte;

I can't figure out how to replicate this in SQLAlchemy, though.
Specifically, I'm stumped on how to represent the "0 as x" part in
SQLAlchemy. How do you do it? I've tried variations of this:

select("0").alias(name="x")

as column specs, but with no luck so far.

[Ryan Kelly]

select(literal(0).alias("x")) should do it, see the documentation at
http://docs.sqlalchemy.org/en/rel_0_7/core/expression_api.html#sqlalchemy.sql.expression.literal

>
> as column specs, but with no luck so far.
>

-Ryan Kelly

[Russ]

> select(literal(0).alias("x")) should do it, see the documentation at ...

Thanks... literal() gave me a location on which to attach a label I
can reference. I'm closer, but still can't get this to work.

Here's my closest so far (iwth SQLAlchemy 0.7.8):

import sqlalchemy as sa
#set up the non-recursive part of the query (sort of?)...
cte = sa.select(
[sa.literal(0).label("x"), ]
).cte(name = "cte", recursive = True)
#bring in the recursive part (sort of?)...
cte = cte.union_all(
sa.select([cte.c.x + 1, ]).\
where(cte.c.x < 10)
)
#select from the resulting CTE...
statement = sa.select([cte.c.x, ])
print statement

which yields...

WITH RECURSIVE cte(x) AS
(SELECT :param_1 AS x),
cte(x) AS
(SELECT :param_1 AS x UNION ALL SELECT cte.x + :x_1 AS anon_1
FROM cte
WHERE cte.x < :x_2)
SELECT cte.x
FROM cte

which isn't right at all, and is confusing to me. Especially where
the UNION ALL ended up and that the CTE wrapping brackets are totally
wrong.

I'll keep at it. It is hard to assemble this piece by piece and check
as I go since adding the .cte seems to prevent printing of progress
until the very end.

[Michael Bayer]

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.

> --
> 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.
>

[Russell Warren]

On Mon, Jul 9, 2012 at 9:53 PM, Michael Bayer <mik...@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

[Russell Warren]

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

[Michael Bayer]

On Jul 10, 2012, at 2:04 AM, Russell Warren wrote:

>
> 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.

Referring to PG's docs:

http://www.postgresql.org/docs/8.4/static/queries-with.html

when I wrote the CTE functionality, I designed it to suit the "included_parts" example, where you can see there is an alias to included_parts. This alias fits naturally into SQLAlchemy's system of aligning object identity with lexical identity, that is, to say "x == 5", you need an "x" object that is unique in the query based on it's Python identity.

But you're right, I didn't study the previous example in that doc, which refers to "t" without any alias. This usage is not nearly as natural, because "t" refers to the UNION ALL as a whole, including the two select statements, one of which was called "t" as well. So referring to both the inner and outer "t" at the same time doesn't work with the SQLA's usual approach.

So some modifications to CTE are made in r079123b04dc6 (0.8) / ra742d1526e86 (0.7) such that CTEs are now rendered based on name-based logic only. A given CTE name will only be rendered as a full statement once, and precedence rules now ensure that the "outermost" CTE is the one that's rendered in all cases. Two entirely non-related CTEs with the same name now generates a CompileError.

With this change, your original intuition:

import sqlalchemy as sa
#set up the non-recursive part of the query (sort of?)...

cte = sa.select(
[sa.literal(0).label("x"), ]

).cte(name = "cte", recursive = True)
#bring in the recursive part (sort of?)...
cte = cte.union_all(
sa.select([cte.c.x + 1, ]).\
where(cte.c.x < 10)
)
#select from the resulting CTE...

statement = sa.select([cte.c.x, ])
print statement

works as you expect:

WITH RECURSIVE cte(x) AS
(SELECT :param_1 AS x UNION ALL SELECT cte.x + :x_1 AS anon_1
FROM cte
WHERE cte.x < :x_2)
SELECT cte.x
FROM cte

the original output you were getting isn't all that confusing (any more than the whole CTE thing is in the first place) - it just rendered cte(x) twice, one for each version used in the query.

[Russell Warren]

On Tue, Jul 10, 2012 at 11:31 AM, Michael Bayer <mik...@zzzcomputing.com> wrote:

So some modifications to CTE are made in r079123b04dc6 (0.8) / ra742d1526e86 (0.7)  such that CTEs are now rendered based on name-based logic only.  A given CTE name will only be rendered as a full statement once, and precedence rules now ensure that the "outermost" CTE is the one that's rendered in all cases.   Two entirely non-related CTEs with the same name now generates a CompileError.

Awesome.  Thanks for the changes, Mike.  I confirmed it works as advertised for 0.7.9 (but you knew that).

Until 0.7.9 is released I'll just use the "cte_working" alias trick.  Your fix makes it unnecessary, but it doesn't seem to do any harm and will still work later so I'm set.

Thanks again.  And yeah, I agree that CTE thing/syntax is more than a bit confusing in general!  Incredibly useful, though.

Russ