Re: [sqlalchemy] Re: Can I make bulk update through association proxy?

[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

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

Re: [sqlalchemy] Re: Can I make bulk update through association proxy?

[Mike Bayer]

On 02/27/2016 07:26 AM, Piotr Dobrogost wrote:

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.


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


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

Re: [sqlalchemy] Re: Can I make bulk update through association proxy?

[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

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

Re: [sqlalchemy] Re: Can I make bulk update through association proxy?

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

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

Re: [sqlalchemy] Re: Can I make bulk update through association proxy?

[Simon King]

On Fri, Feb 26, 2016 at 8:13 AM, Piotr Dobrogost <
p...@2016.groups.google.dobrogost.net> wrote:

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

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

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

Re: [sqlalchemy] Re: Can I make bulk update through association proxy?

[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

>

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

Re: [sqlalchemy] Re: Can I make bulk update through association proxy?

[Simon King]

On Thu, Feb 25, 2016 at 12:01 PM, Piotr Dobrogost <
p...@2016.groups.google.dobrogost.net> wrote:

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

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

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

Re: [sqlalchemy] Re: Can I make bulk update through association proxy?

[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

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

Re: [sqlalchemy] Re: Can I make bulk update through association proxy?

[Simon King]

On Thu, Feb 25, 2016 at 9:03 AM, Piotr Dobrogost <
p...@2016.groups.google.dobrogost.net> wrote:

> On Wednesday, February 24, 2016 at 2:41:43 PM UTC+1, Piotr Dobrogost wrote:
>>
>> 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(...)
>>
>
> Any hints?
>
>
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

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

[sqlalchemy] Re: Can I make bulk update through association proxy?

[Piotr Dobrogost]

On Wednesday, February 24, 2016 at 2:41:43 PM UTC+1, Piotr Dobrogost wrote:
>
> 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(...)
>

Any hints? 

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