SQLAlchemy: Using a CTE from a (sub)query w/ FROM clause specified as literal text

Question

I have a system where queries and CTEs can be provided by the user as textual configuration. One possible configuration is akin to the following:

import sqlalchemy as sa
cte = sa.select([sa.sql.text('* from foo')]).cte('foo_cte')
q = sa.select([sa.sql.text('* from (select * from foo_cte)')])

As it is, this query will not include the CTE preamble when rendered:

>>> print q
SELECT * from (select * from foo_cte)

If, however, I add all possible CTEs to the select list:

q = q.select_from(cte)

...then they have additional and extranous FROM clauses added by SQLAlchemy on render, making the syntax invalid:

>>> print q
WITH foo_cte AS
(SELECT * from foo)
 SELECT * from (select * from foo_cte)
FROM foo_cte

---

Is it possible to have it both ways -- printing the CTE preamble without also adding it to the generated FROM clause?

Answer 1

If you defined q as

q = sa.select(['*']).select_from(cte)

Then sqlalchemy is able to figure out the proper sql to emit:

import sqlalchemy as sa
cte = sa.select([sa.text('* from foo')]).cte('foo_cte')    
q = sa.select(['*']).select_from(cte)

print(q)
WITH foo_cte AS
(SELECT * FROM foo)
 SELECT *
FROM foo_cte