Sqlalchemy: Using a Cte from a (Sub)Query W/ from Clause Specified as Literal Text
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...
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?
1 Answer
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
Sophia analyzes international trade, startup ecosystems, retail transformation, and supply chain logistics for modern digital publications.
