[Python] How do I programmatically build a WITH 'ALIAS' AS (SELECT ...) CTE in sqlglot?

I'm trying to build a Common Table Expression (CTE) like this using the sqlglot Python library:

WITH A AS (
SELECT * FROM my_table
)
SELECT * FROM A


When I attempt to build this with the following code:

from sqlglot import exp, parse_one

cte_select = (
exp.select("*")
.from_("my_table")
)

cte_a = exp.Alias(
this=exp.Subquery(this=cte_select),
alias=exp.to_identifier("A")
)

query = exp.With(
expressions=[cte_a],
this=exp.select("*").from_("A")
)

print(query.sql(pretty=True))


I expect the output to be:

WITH A AS (
SELECT *
FROM my_table
)
SELECT *
FROM A


But I sometimes end up with this instead:

WITH (
SELECT *
FROM my_table
) AS A


This doesn't parse correctly as standard SQL, and tools like sqlglot throw a "CTE must have alias" error.

Questions

• What is the correct way to build CTEs with sqlglot’s AST API so that they’re rendered in the standard WITH alias AS (SELECT ...) form?
• Are there common pitfalls (e.g., misuse of .subquery("A")) that result in broken CTE syntax?
• Can you provide a minimal, working example that outputs valid CTE SQL using sqlglot?

Thanks for your help!

Continue reading...