Bulk update session synchronization using RETURNING

[Steven Winfield]

Hi,

Is there a way to coax orm.query.Query.update() into using the RETURNING clause (e.g. in Postgres) in order to synchronize the session after the update? I couldn't find anything in the docs, nor in orm/persistence.py.

I'm imagining something like:

session.query(Foo).filter(Foo.id == 1).update({Foo.baz: Foo.baz + 1}, synchronize_session='returning')

...which would emit:

UPDATE foo SET baz = baz + 1 WHERE id = 1 RETURNING id, baz

or, perhaps more generally:

... RETURNING id AS foo_id, bar AS foo_bar, baz AS foo_baz, ...

...and the result set could be used to modify any in-session Foo objects, avoiding the extra db roundtrip of the "fetch" method and wouldn't be implementation-limited as the "evaluate" method is.

If such a thing doesn't already exist then I can probably already hack something together by using:

 .update(..., update_args={"returning": (col1, col2, ...)}) 

(at least, I think that's the correct syntax - I can't find any docs for the "returning" kwarg, only the returning() method)

Cheers,

Steve.

[Mike Bayer]

On Fri, Aug 25, 2017 at 6:39 AM, Steven Winfield
<steven....@cantab.net> wrote:
> Hi,
>
> Is there a way to coax orm.query.Query.update() into using the RETURNING
> clause (e.g. in Postgres) in order to synchronize the session after the
> update? I couldn't find anything in the docs, nor in orm/persistence.py.
>
> I'm imagining something like:
>
> session.query(Foo).filter(Foo.id == 1).update({Foo.baz: Foo.baz + 1},
> synchronize_session='returning')
>
> ...which would emit:
>
> UPDATE foo SET baz = baz + 1 WHERE id = 1 RETURNING id, baz
>
> or, perhaps more generally:
> ... RETURNING id AS foo_id, bar AS foo_bar, baz AS foo_baz, ...
>
> ...and the result set could be used to modify any in-session Foo objects,
> avoiding the extra db roundtrip of the "fetch" method and wouldn't be
> implementation-limited as the "evaluate" method is.

not a bad idea at all but no, there's no trivial path to that, a new
BulkUpdate strategy would need to be created in
sqlalchemy/orm/persistence.py.

>
> If such a thing doesn't already exist then I can probably already hack
> something together by using:
> .update(..., update_args={"returning": (col1, col2, ...)})

adding the RETURNING is not the hard part it's getting those values
back, matching them back up to the objects, and populating that is
most of the effort.

>
> (at least, I think that's the correct syntax - I can't find any docs for the
> "returning" kwarg, only the returning() method)
>
>
> Cheers,
> Steve.
>

> --
> 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+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.