Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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
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
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.