"Mike Bayer" <[email protected]> writes:
> 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).
Thank you Mike, as always you put me on the right track! This is a sample of
what I ended up with for my real case:
ta_t = table_a.alias('ta')
tc_t = table_c.alias('tc')
used_q = (sa.select([ta_t.c.id.label('a_id'),
tc_t.c.content_id.label('cc_id'),
sa.func.count().label('used_count')])
.select_from(ta_t.join(tc_t))
.group_by(ta_t.c.id, tc_t.c.content_id)).cte('used_contents')
istmt = (insert(target_t)
.from_select(['a_id', 'cc_id', 'usage_count'],
sa.select([sa.text('a_id'),
sa.text('cc_id'),
sa.text('used_count')])
.select_from(used_q)))
upsert = istmt.on_conflict_do_update(
index_elements=['a_id', 'cc_id'],
set_=dict(usage_count=dnc.c.usage_count + istmt.excluded.usage_count))
Thanks again,
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/8736f2cf9g.fsf%40metapensiero.it.
