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.
