Can I make bulk update through association proxy?

[Piotr Dobrogost]

Hi!

Let's say I have a model Text with attribute "values" which is association proxy.

I can update single object like this:

text = session.query(Text).one()

text.values.update(...)

How can I update multiple objects with the same value without manually looping over result of a query?

texts = session.query(Text).all()

texts???values???.update(...)

Regards,

Piotr Dobrogost

[Piotr Dobrogost]

Any hints? 

Regards,

Piotr

 

[Simon King]

I can't think of a way you could do this with objects you've already loaded into memory. Perhaps you could use Query.update to issue the appropriate SQL directly to the database?

http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html#sqlalchemy.orm.query.Query.update

Simon

[Piotr Dobrogost]

On Thursday, February 25, 2016 at 11:10:36 AM UTC+1, Simon King wrote

I can't think of a way you could do this with objects you've already loaded into memory. Perhaps you could use Query.update to issue the appropriate SQL directly to the database?

http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html#sqlalchemy.orm.query.Query.update

Thanks for your reply.

My reasoning is that if it's possible for one object (and it is) it should be possible for multiple objects as well.

It seems to use Query.update I would need to filter/select related objects (those accessible through association proxy) directly and this is inconvenient as I would like to treat them as part of the primary objects and be able to filter/select them for update "through" primary objects by means of association proxy.

Regards,

Piotr

[Simon King]

Maybe I'm not understanding your question properly. The return value from query.all() is a plain python list. You're asking for it to return a different kind of object, that wraps the underlying list and allows you to specify arbitrary operations that should be applied to each object in that list? I guess I could imagine a complicated class that might support something like that, but I don't think it exists at the moment, and it would seem like a lot of work just to avoid a simple loop...

Simon

[Piotr Dobrogost]

On Thursday, February 25, 2016 at 2:33:13 PM UTC+1, Simon King wrote:

Maybe I'm not understanding your question properly. The return value from query.all() is a plain python list. You're asking for it to return a different kind of object, that wraps the underlying list and allows you to specify arbitrary operations that should be applied to each object in that list? I guess I could imagine a complicated class that might support something like that, but I don't think it exists at the moment, and it would seem like a lot of work just to avoid a simple loop...

Well, after calling .all() you indeed get plain python list and you can't do anything but iterate over it; it's "too late". In this case it appears one should not call .all() and instead call something on the query itself i.e. session.query(Text).???.update(...). I would like to know if and what to insert so that SA would generate UPDATE for objects _related_ (those accessible via association proxy) to these selected objects.

Regards,

Piotr

[Simon King]

OK, so you're looking for something that will immediately send UPDATE statements to the database, like Query.update() does at the moment, but without manually specifying the join conditions. This is very different from your "text.values.update(...)" example, which is an entirely in-python operation adding and removing objects from the SQLAlchemy session. Under the hood, text.values.update() is doing the looping that you are trying to avoid.

I'm not aware of any way to do this.

Sorry,

Simon

[Jonathan Vanasco]

On Friday, February 26, 2016 at 4:54:01 AM UTC-5, Simon King wrote:

I'm not aware of any way to do this.

I'm not either, and I'm scared of the SQL that would be generated and the wire traffic/memory if there were.  That would be subselects within subqueryloads within... this also seems a bit more about "updates to associations" vs "association_proxy".  

Just to step back for a second, calling `update()` on the relation would require every object to be loaded from the database and into the identity map.  This is for 2 reasons:  1) the ORM works on objects, not the db directly; 2) sqlalchemy would need to keep both objects in the   database and in-memory in sync (what if the 10% of the objects are loaded from memory and the rest are in the DB?).  Think of how that feature would work on an object where a relation has 1MM rows in the association - the computer could grind to a halt.

For this general task, I `flush` the session, use the `update` command on the target class -- filtering the WHERE based on the parent object and  join conditions --  then I `expire_all` (because that update may have affected in-memory object relations).  There is a small hit on reloading all the data, but I've found the `update` to run considerably faster and make it worth-it.

[Piotr Dobrogost]

On Friday, February 26, 2016 at 7:45:46 PM UTC+1, Jonathan Vanasco wrote:

I'm not either, and I'm scared of the SQL that would be generated and the wire traffic/memory if there were.  That would be subselects within subqueryloads within... this also seems a bit more about "updates to associations" vs "association_proxy".  

True. I admit this would be rather complicated but as I'm new to SA and everybody has SA in high regard I hoped it could be capable of doing such a thing.

Thanks for taking time to reply and for all information you provided which is very helpful.

Regards,

Piotr

[Mike Bayer]

If I can just wrap this up, do i understand correctly that this would
the functionality of "joined eager loading", except applied to something
like an UPDATE..FROM ?

I think you can get pretty close to that just using plain UPDATE..FROM
and spelling out the joins you want. Recently we even got UPDATE..FROM
a JOIN b working.




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

[Piotr Dobrogost]

On Saturday, February 27, 2016 at 10:26:53 PM UTC+1, Mike Bayer wrote:

If I can just wrap this up, do i understand correctly that this would
the functionality of "joined eager loading", except applied to something
like an UPDATE..FROM ?

I think so although I can't say I'm 100% sure as I'm not fluent in neither SA nor in SQL.

Jonathan Vanasco wrote earlier: 

>For this general task, I `flush` the session, use the `update` command on the target class -- filtering the >WHERE based on the parent object and  join conditions --  then I `expire_all` (because that update may have >affected in-memory object relations).  There is a small hit on reloading all the data, but I've found the `update` >to run considerably faster and make it worth-it.

This explains how to do this starting from the "opposite end". I mean the situation is that there's some parent_class <-relationship-> target_class and I want to update objects of target_class having initially objects of parent_class without explicitly selecting objects of target_class first as in Jonathan's solution.

Regards,

Piotr