fossil tinsel
I'm building an involved query in sqlalchemy, and I want to load the data for insertion into a CTE first so that I can build a boolean clause that validates the data against other rules tables to gate the insertion.
But I've got a minimal reproduction case that demonstrates the issue I'm hitting:
import sqlalchemy as sa
print(sa.select(
sa.values(
sa.column("name", sa.Text),
sa.column("color", sa.Text),
).data([
("Lancelot", "blue"),
("Galahad", "red, no, blue!")
])
))
"""
SELECT name, color
FROM (VALUES (:param_1, :param_2), (:param_3, :param_4))
"""
This is invalid SQL since the VALUES clause as generated doesn't know which column is "name" and which is "color".
I can get this to work with select(text("*")).select_from(values(...).data(...)) in that it will generate valid SQL for the SELECT, but on that Select object I can't use myselect.c.name, since now the python construct doesn't know the labels, and in fact list(myselect.c) is [].
The eventual SQL I would hope to generate, as a CTE, would look like:
WITH myselect(name, color) AS (
SELECT *
FROM (VALUES (:param_1, :param_2), (:param_3, :param_4))
)
But I'm not sure how to build that with sqlalchemy.
The python code I was starting with was recommended by Mike Bayer in this thread: https://groups.google.com/g/sqlalchemy/c/UhfF70QjN-U
slow quartz
