SQLAlchemy: Using a CTE from a (sub)query w/ FROM clause specified as literal text
<p>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:</p>

<pre><code>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)')])
</code></pre>

<p>As it is, this query will not include the CTE preamble when rendered:</p>

<pre><code>&gt;&gt;&gt; print q
SELECT * from (select * from foo_cte)
</code></pre>

<p>If, however, I add all possible CTEs to the select list:</p>

<pre><code>q = q.select_from(cte)
</code></pre>

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

<pre><code>&gt;&gt;&gt; print q
WITH foo_cte AS
(SELECT * from foo)
 SELECT * from (select * from foo_cte)
FROM foo_cte
</code></pre>

<hr>

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