Thanks! It looks like 1.4 is required for this, correct? Any way to do this under 1.3?
> On Feb 20, 2022, at 8:17 PM, Mike Bayer <mike...@zzzcomputing.com> wrote: > > 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. >> 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. -- 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/17721A18-526A-42FC-AD2D-9FD5BA202890%40americanefficient.com.