the Values construct doesn't have CTE direct support right now so you need to make a subquery first, then CTE from that
from sqlalchemy import Column
from sqlalchemy import column
from sqlalchemy import Integer
from sqlalchemy import select
from sqlalchemy import String
from sqlalchemy import values
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = "my_table"
id = Column(Integer, primary_key=True)
name = Column(String)
v1 = select(
values(
column("name", Integer), column("color", String), name="my_values"
).data([("Lancelot", "blue"), ("Galahad", "blue. no, yellow")])
).cte()
stmt = select(A, v1.c.color).join_from(A, v1, A.name ==
v1.c.name)
print(stmt)