Re: [sqlalchemy] Re: Bulk Lazy Loader for relationships

[mike bayer]

On 05/04/2017 12:07 AM, David Chanin wrote:
Interesting - so it will be possible in 1.2 to do more custom 
relationship loading in a "post load" hook?


it will, however this hook is still local to the objects that are local 
to the results of that Query.




Thanks for the feedback! That definitely makes sense - I didn't fully 
understand all the logic in _emit_lazyload() initially so I wasn't sure 
what was OK to remove and what wasn't. I made a PR with those changes 
here: https://github.com/operator/sqlalchemy_bulk_lazy_loader/pull/1.


One thing I still don't fully understand is the passive variable. What 
does passive mean, and is it set per-model or per-relationship?


"passive" is an integer value that originates from various functions in 
the ORM, usually from functions in orm/attributes.py and 
orm/unitofwork.py.  It is a value that is specific to a particular 
attribute on a class (column or relationship) and it is used in the 
context of a particular read operation on an attribute.


This flag started out as a True/False.  When True, it meant, "give me 
this information but please don't emit SQL if you don't have it". 
Later, it became an enumeration of integers that referred to a wider 
range of possible scenarios and behaviors. Finally, it moved from an 
enumeration of sequential integers to a bitflag; still an integer value, 
but one that can be individually broken into specific true/false values. 
   This lets us move from logic like "if flag == VALUE_A or flag == 
VALUE_B or flag == VALUE_C" into "if flag & HAS_THING"; it's a lot more 
direct.


The elements refer to such behaviors as "should I return the DB-loaded 
value for this attribute, or whatever the user has just placed here?", 
"if I have to emit SQL, should I turn off autoflush?", "if this object 
is not actually "persistent" in the Session, can I still emit SQL using 
the primary key value it happens to have?" (this is a special use case 
added by popular request), "if I go to fetch this value, and it has no 
value set at all, should I populate the attribute with a default 
value/collection?".


Also in base.py you will see "pre-packaged" sets of bitflags that 
correspond to known use cases, which correspond roughly to the 
enumerated values these originally derived from.





Thanks a lot Mike!

On Wednesday, May 3, 2017 at 10:14:03 PM UTC+8, Mike Bayer wrote:


Related note, in 1.2 I'm adding a new mechanism for loading things
which
is a "post load" hook, that is, a bunch of objects are loaded in a
query, and then afterwards, more loaders can run on a batch of
completed
objects.   The effect looks similar to how "subqueryload" works right
now, except "subqueryload" fires off within the initial population step
of the objects.  The two kinds of loaders using this are the "select
IN"
loading, which is like subqueryloading but more or less better in most
ways (also seems you're doing "select IN" here), and "select IN"
loading
of subclass attributes on a joined inheritance mapping.

The hook you have here would be a third kind of hook, a "lazyload that
works across the Session" hook.   Definitely something new.   I'm not
sure all of the logic that's been copied into _emit_lazyload() really
applies though; the top logic is all related to the specific object
that
has triggered the load, like if its pending or not, if it had any query
option set up, the state.load_path, etc.   You can't assume any of that
stuff applies to all the other states if you are going across the whole
result.It's probably better, since this is a very different kind of
loader, to make it just load for all the states in the same way without
looking at any of their options or things like that.



On 05/03/2017 08:52 AM, David Chanin wrote:
 > Ack, thanks Simon! That is definitely a bug :). I just pushed a fix.
 >
 > Thanks for the feedback!
 > David
 >
 > On Wednesday, May 3, 2017 at 5:47:54 PM UTC+8, David Chanin wrote:
 >
 > Hi Everyone,
 >
 > We just open-sourced a custom lazy loader for SQLAlchemy that
does
 > bulk lazy loading of relations - essentially a lazy
subqueryload.
 > The idea is that whenever a relation is lazy-loaded on a
model, the
 > loader will look for all similar models in the session that
haven't
 > had that relation populated yet and will issue a single SQL
query to
 > populate them all in bulk. I'm really curious to hear any
feedback
 > you may have on this idea / implementation, pitfalls that we're
 > overlooking, or ideas for improvement. The repo is at
 > https://github.com/operator/sqlalchemy_bulk_lazy_loader

 > 

Re: [sqlalchemy] Re: Bulk Lazy Loader for relationships

[David Chanin]

Interesting - so it will be possible in 1.2 to do more custom relationship 
loading in a "post load" hook?

Thanks for the feedback! That definitely makes sense - I didn't fully 
understand all the logic in _emit_lazyload() initially so I wasn't sure 
what was OK to remove and what wasn't. I made a PR with those changes here: 
https://github.com/operator/sqlalchemy_bulk_lazy_loader/pull/1.

One thing I still don't fully understand is the passive variable. What does 
passive mean, and is it set per-model or per-relationship?

Thanks a lot Mike!

On Wednesday, May 3, 2017 at 10:14:03 PM UTC+8, Mike Bayer wrote:
>
>
> Related note, in 1.2 I'm adding a new mechanism for loading things which 
> is a "post load" hook, that is, a bunch of objects are loaded in a 
> query, and then afterwards, more loaders can run on a batch of completed 
> objects.   The effect looks similar to how "subqueryload" works right 
> now, except "subqueryload" fires off within the initial population step 
> of the objects.  The two kinds of loaders using this are the "select IN" 
> loading, which is like subqueryloading but more or less better in most 
> ways (also seems you're doing "select IN" here), and "select IN" loading 
> of subclass attributes on a joined inheritance mapping. 
>
> The hook you have here would be a third kind of hook, a "lazyload that 
> works across the Session" hook.   Definitely something new.   I'm not 
> sure all of the logic that's been copied into _emit_lazyload() really 
> applies though; the top logic is all related to the specific object that 
> has triggered the load, like if its pending or not, if it had any query 
> option set up, the state.load_path, etc.   You can't assume any of that 
> stuff applies to all the other states if you are going across the whole 
> result.It's probably better, since this is a very different kind of 
> loader, to make it just load for all the states in the same way without 
> looking at any of their options or things like that. 
>
>
>
> On 05/03/2017 08:52 AM, David Chanin wrote: 
> > Ack, thanks Simon! That is definitely a bug :). I just pushed a fix. 
> > 
> > Thanks for the feedback! 
> > David 
> > 
> > On Wednesday, May 3, 2017 at 5:47:54 PM UTC+8, David Chanin wrote: 
> > 
> > Hi Everyone, 
> > 
> > We just open-sourced a custom lazy loader for SQLAlchemy that does 
> > bulk lazy loading of relations - essentially a lazy subqueryload. 
> > The idea is that whenever a relation is lazy-loaded on a model, the 
> > loader will look for all similar models in the session that haven't 
> > had that relation populated yet and will issue a single SQL query to 
> > populate them all in bulk. I'm really curious to hear any feedback 
> > you may have on this idea / implementation, pitfalls that we're 
> > overlooking, or ideas for improvement. The repo is at 
> > https://github.com/operator/sqlalchemy_bulk_lazy_loader 
> > . Hope 
> it's 
> > useful to others as well. 
> > 
> > Thanks so much! 
> > 
> > -- 
> > 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. 
>

-- 
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+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: Bulk Lazy Loader for relationships

[mike bayer]

Related note, in 1.2 I'm adding a new mechanism for loading things which 
is a "post load" hook, that is, a bunch of objects are loaded in a 
query, and then afterwards, more loaders can run on a batch of completed 
objects.   The effect looks similar to how "subqueryload" works right 
now, except "subqueryload" fires off within the initial population step 
of the objects.  The two kinds of loaders using this are the "select IN" 
loading, which is like subqueryloading but more or less better in most 
ways (also seems you're doing "select IN" here), and "select IN" loading 
of subclass attributes on a joined inheritance mapping.


The hook you have here would be a third kind of hook, a "lazyload that 
works across the Session" hook.   Definitely something new.   I'm not 
sure all of the logic that's been copied into _emit_lazyload() really 
applies though; the top logic is all related to the specific object that 
has triggered the load, like if its pending or not, if it had any query 
option set up, the state.load_path, etc.   You can't assume any of that 
stuff applies to all the other states if you are going across the whole 
result.It's probably better, since this is a very different kind of 
loader, to make it just load for all the states in the same way without 
looking at any of their options or things like that.




On 05/03/2017 08:52 AM, David Chanin wrote:

Ack, thanks Simon! That is definitely a bug :). I just pushed a fix.

Thanks for the feedback!
David

On Wednesday, May 3, 2017 at 5:47:54 PM UTC+8, David Chanin wrote:

Hi Everyone,

We just open-sourced a custom lazy loader for SQLAlchemy that does
bulk lazy loading of relations - essentially a lazy subqueryload.
The idea is that whenever a relation is lazy-loaded on a model, the
loader will look for all similar models in the session that haven't
had that relation populated yet and will issue a single SQL query to
populate them all in bulk. I'm really curious to hear any feedback
you may have on this idea / implementation, pitfalls that we're
overlooking, or ideas for improvement. The repo is at
https://github.com/operator/sqlalchemy_bulk_lazy_loader
. Hope it's
useful to others as well.

Thanks so much!

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