On Tue, Nov 5, 2019, at 4:08 AM, Lele Gaifax wrote: > Hi all, > > I'm almost sure I'm missing the obvious, struggling to understand how to use a > CTE with a PG "upsert" statement: I need to modify an existing (and working as > expected) update statement based on a CTE to an upsert, because now the target > information is kept in a different table.
looking at the supported cases in https://github.com/sqlalchemy/sqlalchemy/blob/master/test/sql/test_cte.py I think you want the CTE to come out via INSERT..FROM SELECT. I don't think it does anything if you put the CTE inside the VALUES clause. The PG examples use insert().from_select(...things with the CTE). If there's specific SQL on PG's website you're looking to emulate we can see if this should be adjusted. > > As said, while my current CTE+UPDATE works, I could not figure out what I'm > doing wrong with the new "upsert": invariably I obtain a statement where the > CTE part is missing, and thus a "TableNotFound" exception where the INSERT ... > ON CONFLICT ... references the CTE relation. > > After initial trials and failures, I distilled the following snippet, where > even a plain INSERT does not come out as expected: the first statement, an > UPDATE based on the CTE, is complete, while the latter two, a plain INSERT > based on the CTE and the "upsert" variant, do not: > > from sqlalchemy import Table, Column, String, Integer, MetaData, > create_engine, select > from sqlalchemy.dialects.postgresql import insert > > > metadata = MetaData() > e = create_engine("postgresql://scott:tiger@localhost/test") > > > orders = Table('orders', metadata, > Column('region', String, primary_key=True), > Column('amount', Integer)) > > > some_sales = (select([orders.c.region, orders.c.amount]) > .where(orders.c.region.in_(['a', 'b'])) > .cte("some_sales")) > > stmt = (orders.update() > .values(amount=some_sales.c.amount*2) > .where(orders.c.region == some_sales.c.region)) > > print(stmt.compile(e)) > print('='*10) > > stmt = (orders.insert() > .values(region=some_sales.c.region, > amount=some_sales.c.amount*2)) > > print(stmt.compile(e)) > print('='*10) > > istmt = (insert(orders) > .values(region=some_sales.c.region, > amount=some_sales.c.amount*2)) > upsert = istmt.on_conflict_do_update( > index_elements=[orders.c.region], > set_=dict(amount=some_sales.c.amount*2)) > > print(upsert.compile(e)) > print('='*10) > > This emits: > > WITH some_sales AS > (SELECT orders.region AS region, orders.amount AS amount > FROM orders > WHERE orders.region IN (%(region_1)s, %(region_2)s)) > UPDATE orders SET amount=(some_sales.amount * %(amount_1)s) FROM some_sales > WHERE orders.region = some_sales.region > ========== > INSERT INTO orders (region, amount) VALUES (some_sales.region, > (some_sales.amount * %(amount_1)s)) > ========== > INSERT INTO orders (region, amount) VALUES (some_sales.region, > (some_sales.amount * %(amount_1)s)) ON CONFLICT (region) DO UPDATE SET amount > = (some_sales.amount * %(amount_2)s) > ========== > > Can anybody shed some light on what I'm doing wrong? > > Thanks in advance, > ciao, lele. > -- > nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri > real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia. > [email protected] | -- Fortunato Depero, 1929. > > -- > 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/877e4ed71x.fsf%40metapensiero.it. > -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/c26c562b-88b2-4f9b-b4a5-a9521e75fc5e%40www.fastmail.com.
