Should load_only work with hybrid properties?

[Gmoney]

I'm trying to use load_only and was able to get a really basic example to work.  My problem is that once I try to load one of my hybrid properties, it fails.  

This code works for any number of basic Column properties but fails once I add the hybrid property:

columns = (Task.msg_id, Task.hybrid_test)

q = (db.session.query(Task).options(load_only(*columns).lazyload('*')))

AttributeError: Neither 'hybrid_property' object nor 'ExprComparator' object associated with Task.hybrid_test has an attribute 'property'

Just wondering if I should be able to get this to work if I keep digging or post a more thorough code snippet, or is it not possible using this approach?

[Gmoney]

I should at least clarify that I have @hybrid_test.expression in place for that property and it works when I just do the base 'get me everything' query.

[Mike Bayer]

well hybrids don't apply to a load_only operation because they are not included in the query that emits when the ORM SELECTs for an object.    that is, @hybrid_property.expression gives you a SQL expression, but that's never included when you say session.query(Task).   It's only if you said, session.query(Task).filter(Task.hybrid_test == 'x').

There's a construct called column_property() that *is* included in the columns when you say query(Task).  This is actually the kind of property that's used for all the regular columns but it also accomodates arbitrary SQL expressions.   to that extent there's some overlap between column_property and hybrids which is why they are both listed at https://docs.sqlalchemy.org/en/13/orm/mapped_sql_expr.html.

let me know if that makes sense.

--

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/2661a10c-4741-4e5b-a1e7-2db578c5712b%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Gmoney]

I think it's starting to make sense.  I did misunderstand exactly when the .expression SQL was used - I had thought even queries for ORM models would use it, but I see it does work as you describe.

My goal is to return a subset of fields and make that query building operation be dynamic and "simple" for downstream services.  My trouble seems to be that as I move away from the baseline full "query(MyORMClass)", the relationship configured joins no longer automatically come along, so I have to re-specify them in the query.

Thanks for the response, I will consider the column_property alternative - it might be better for our situation.

As far as limiting fields returned then though - I think these are my options, if I understand them right:

query(ORM_Class)

-Returns list of ORM Model instances

-The only option that auto-includes all joins defined in ORM Model relationships.

-Does not calculate hybrid_property in SQL

-Referencing hybrid_property on the returned ORM Model evaluates hybrid_property in python.

load_only(ORM_Class.column_attr,... etc)

-Returns list of ORM Models (sparse, lazily populated)

-Will *NOT* add necessary joins from relationship definitions in Model.  Must join manually in query.

-Can't ask for hybrid_property in query

-Referencing hybrid_property on the returned ORM Model still evaluates hybrid_property in python.

query(ORM_Class.column_attr, ORM_Class.hybrid_property)

-Returns Tuple

-Will *NOT* add necessary joins from relationship definitions in Model.  Must join manually in query.

-Uses the .expression form of the hybrid_property to SELECT/calculate it in SQL

with_entities(ORM_Class.column_attr,... etc)

-Returns Tuple

-Will *NOT* add necessary joins from relationship definitions in Model.  Must join manually in query.

-Uses the .expression form of the hybrid_property to SELECT/calculate it in SQL

On Monday, June 3, 2019 at 2:21:41 PM UTC-4, Mike Bayer wrote:

well hybrids don't apply to a load_only operation because they are not included in the query that emits when the ORM SELECTs for an object.    that is, @hybrid_property.expression gives you a SQL expression, but that's never included when you say session.query(Task).   It's only if you said, session.query(Task).filter(Task.hybrid_test == 'x').

There's a construct called column_property() that *is* included in the columns when you say query(Task).  This is actually the kind of property that's used for all the regular columns but it also accomodates arbitrary SQL expressions.   to that extent there's some overlap between column_property and hybrids which is why they are both listed at https://docs.sqlalchemy.org/en/13/orm/mapped_sql_expr.html.

let me know if that makes sense.

On Mon, Jun 3, 2019, at 2:17 PM, Gmoney wrote:

I should at least clarify that I have @hybrid_test.expression in place for that property and it works when I just do the base 'get me everything' query.

On Monday, June 3, 2019 at 2:13:17 PM UTC-4, Gmoney wrote:

I'm trying to use load_only and was able to get a really basic example to work.  My problem is that once I try to load one of my hybrid properties, it fails.  

This code works for any number of basic Column properties but fails once I add the hybrid property:

columns = (Task.msg_id, Task.hybrid_test)

q = (db.session.query(Task).options(load_only(*columns).lazyload('*')))

AttributeError: Neither 'hybrid_property' object nor 'ExprComparator' object associated with Task.hybrid_test has an attribute 'property'

Just wondering if I should be able to get this to work if I keep digging or post a more thorough code snippet, or is it not possible using this approach?

--

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 sqlal...@googlegroups.com.

[Gmoney]

I thought I figured out another clever way, but it's not working working as I thought it might.  Thought maybe using ".from_self" I could do the base full automatic ORM Model query to get everything, then do the .from_self to wrap it with an outer query and only return the columns I need.  

        q = (db.session.query(Task).from_self(*columns_i_want))

Unfortunately though, even though query(Task) alone can figure out the relationships/joins, once it's in this statement with the from_self version, you don't get them anymore.

I was so excited - I thought that was definitely going to work.  Any chance I'm just missing a step that could make that method work?  I see the '.add_entity' option but that didn't seem to help and kind of defeats the purpose of my simplicity goal anyway.

[Mike Bayer]

I think the thing you need to understand about the "hybrid" is that it isn't "loaded" with the object, that is, it has no place in __dict__ to be stored.  The function that you create is invoked every time you access it on your object or class.  If you have an object instance of Task, as you've observed, the in-python version of it will run every time when you say some_task.my_hybrid.   That's why it's called a "hybrid".

So technically, it doesn't make sense to say quey(Task).load_only(Task.some_hybrid), becuase there is no place on Task to put that value.

However, it *does* make sense that you might want to load_only the columns on Task that your hybrid uses in order to create its value.  If your hybrid was like, "return self.x + self.y" you would want to load_only(Task.x, Task.y).

So from *there*, there could be a way that these column attributes could be extracted from your hybrid automatically, so that if you said load_only(Task.some_hybrid), it would actually mean load_only(Task.x Task.y).   I could even come up with a recipe to do that in the meantime.   But I'm not sure this would make a good feature or not because it adds more layers of magic to a concept that is already proving to be confusing.

let me know if that makes sense.

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/02f8addf-a7ef-4fad-b972-b582ec902f30%40googlegroups.com.

[Gmoney]

I think I get the idea.  That's why the properties with hybrid_property.expression defined are able to work as SQL SELECT fields in the cases where a tuple is returned rather than an ORM model... because you don't have to have a place in _dict__ to put it, it just goes into the tuple.

My main goal was figuring out how to easily limit which columns are returned, without re-specifying joins that are already defined in the relationships on the Model, so that probably muddied my question.  I think the answer to that is that either I query the whole ORM object (and get the joins for free, as defined in the Model) or if I must limit the columns returned, then I just have to re-specify the joins in each query.

Thank you for the feedback and all the support in general.