CTE w/VALUES in SELECT?

[Philip Semanchuk]

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

[Mike Bayer]

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)

-- 

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/E6CA442E-CB28-431B-9056-61144A9838D2%40americanefficient.com.

[Philip Semanchuk]

Thanks! It looks like 1.4 is required for this, correct? Any way to do this under 1.3?

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/52b46002-1700-4fc9-b2be-fa2976edf5ef%40www.fastmail.com.

[Mike Bayer]

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  

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/17721A18-526A-42FC-AD2D-9FD5BA202890%40americanefficient.com.

[Philip Semanchuk]

> On Feb 21, 2022, at 9:50 AM, Mike Bayer <mik...@zzzcomputing.com> wrote:
>
> 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

Thanks. In 1.3 sqlalchemy.sql.expression.ValuesBase exists, but it inherits from UpdateBase and is tied (AFAICT) to inserts and updates.

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/6f7cf736-bf18-4ba2-baaf-5bb2cad4c9c1%40www.fastmail.com.