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) On Thu, Feb 17, 2022, at 1:00 PM, Philip Semanchuk wrote: > Hi, > I'm trying to use a VALUES statement in a CTE, and I can't figure out the > correct SQLAlchemy constructs to make this happen. I'd appreciate any help. > Here's the SQL I'd like to express in SQLAlchemy -- > > WITH knights(name, favorite_color) AS ( > VALUES > ('Lancelot', 'blue'), > ('Galahad', 'blue. no, yellow') > ), > SELECT my_table.*, favorite_color > FROM my_table > JOIN knights USING (name) > > Ideally, I would like to be able to express this as a Query that I can later > execute or pass to an insert().from_select(columns, my_query). > > The backend is Postgres 11, and our SQLAlchemy version is 1.3. > > Thanks > Philip > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com > <mailto:sqlalchemy%2bunsubscr...@googlegroups.com>. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/E6CA442E-CB28-431B-9056-61144A9838D2%40americanefficient.com. > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/52b46002-1700-4fc9-b2be-fa2976edf5ef%40www.fastmail.com.