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