I thought 1.3 had "values", but if not, then you'd need to roll a recipe of some kind, the original recipe is at https://github.com/sqlalchemy/sqlalchemy/wiki/PGValues
On Mon, Feb 21, 2022, at 9:06 AM, Philip Semanchuk wrote: > 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 > >> <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 > > <mailto:sqlalchemy%2bunsubscr...@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 > <mailto:sqlalchemy%2bunsubscr...@googlegroups.com>. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/17721A18-526A-42FC-AD2D-9FD5BA202890%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/6f7cf736-bf18-4ba2-baaf-5bb2cad4c9c1%40www.fastmail.com.