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

Question

I have a system in which requests and CTEs can be provided by the user as a text 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)')])

However, this request will not include the CTE preamble for rendering:

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

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

q = q.select_from(cte)

... then they have extra and extra FROM clauses added by SQLAlchemy for rendering, which makes the syntax invalid:

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

---

Is this possible in both directions - printing the CTE preamble without adding it to the generated FROM clause?

Answer 1

q

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

sqlalchemy sql :

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