CTE w/VALUES in SELECT?

804 views
Skip to first unread message

Philip Semanchuk

unread,
Feb 17, 2022, 1:00:28 PM2/17/22
to sqlal...@googlegroups.com
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

unread,
Feb 20, 2022, 8:18:18 PM2/20/22
to noreply-spamdigest via sqlalchemy
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


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.


Philip Semanchuk

unread,
Feb 21, 2022, 9:06:41 AM2/21/22
to sqlal...@googlegroups.com
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

unread,
Feb 21, 2022, 9:50:35 AM2/21/22
to noreply-spamdigest via sqlalchemy
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  

Philip Semanchuk

unread,
Feb 21, 2022, 11:59:41 AM2/21/22
to sqlal...@googlegroups.com


> 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.

Reply all
Reply to author
Forward
0 new messages