On Tue, Jan 8, 2019 at 4:16 PM Gmoney <grgg...@gmail.com> wrote:
>
> Unable to limit the columns returned from a query using .load_only or
> .with_entities. I feel like I'm missing a key concept here, and could use
> some guidance.
>
> One column of interest is a relationship - which I'm guessing is my problem.
> class Task(Base):
> __tablename__ = 'TASK'
> id = Column('ID', VARCHAR2(250), primary_key=True)
> subject = Column('SUBJECT', VARCHAR2(250))
> comments =relationship('Comment', back_populates='task', lazy='joined')
>
> class Comment(Base):
> __tablename__ = 'COMMENT'
> id = Column('ID', Integer, primary_key=True)
> msg_id = Column('MSG_ID', VARCHAR2(250), ForeignKey(Task.id))
> comment_given_by = Column('COMMENT_GIVEN_BY', VARCHAR2(250))
> task = relationship('Task', back_populates='comments', lazy='joined')
>
> Running the query without any attempt to limit columns works fine (ie. the
> Classes/models seem OK)
> query = session.query(Task)
>
> But now I tried .with_entities.
> If you ONLY request actual Columns, and not relationships, it lists the
> columns and tables in the select, but does not add the ON clause (cartesian
> join)
> If I request the relationship, it generates invalid SQL:
> query = session.query(Task).with_entities(Task.id, Task.comments)
> # SELECT
> # "TASK"."ID",
> # "TASK"."ID" = "COMMENT"."MSG_ID" AS comments
> # FROM
> # "TASK",
> # "COMMENT"
>
>
> So trying with .load_only:
> No luck many ways, all failing... every random thing I try results in a
> different exception - I feel like I'm getting farther and just guessing now
The documentation for what you are trying to do is at:
https://docs.sqlalchemy.org/en/latest/orm/loading_columns.html#deferred-loading-with-multiple-entities
When there are multiple entities, the Query never jumps between two
entities without you specifying how you get there. in this case,
the way you navigate between them is via Task.comments, so that
navigation must be stated:
session.query(Task).options(load_only(Task.id),
defaultload(Task.comments).load_only(Comment.comment_given_by))
You in fact did pretty much the same query at the end of your attempts
and I think you probably relied upon print(query) to determine if it
was "working" or not, because your query with subqueryload() works,
you just would not be able to see this by print(query) alone because
subqueryload() emits a second query only when the objects are loaded.
>
> query = session.query(Task, Comment).options(load_only(Task.id,
> Comment.comment_given_by))
> ---->sqlalchemy.exc.ArgumentError: Wildcard loader can only be used with
> exactly one entity. Use Load(ent) to specify specific entities.
load_only means, defer('*') + undefer(Task.id) +
undefer(Comment.comment_given_by). But the wildcard doesn't know
how to do Task, Comment together, you would need to say
Load(Task).load_only(Task.id),
Load(Comment).load_only(Comment.comment_given_by), but again this is
not what you want because you are asking for a cartesian product in
the above query. Error message is not great here because it comes
from the defer('*') that you can't see happening here, so that should
be improved.
> query = session.query(Task).options(load_only('id', 'comments'))
> ----> Exception: can't locate strategy for <class
> 'sqlalchemy.orm.relationships.RelationshipProperty'> (('deferred', False),
> ('instrument', True))
load_only() does not apply to relationships like "comments', only to
columns. again not a great error message here, that could be
improved.
>
> query = session.query(Task).options(load_only(Task.id,
> Comment.comment_given_by))
> ----> sqlalchemy.exc.ArgumentError: Can't find property 'comment_given_by' on
> any entity specified in this Query. Note the full path from root
> (Mapper|Task|TASK) to target entity must be specified.
Task has nothing to do with Comment unless you state this in terms of
Task.comments, which is what "full path" means
>
> query = session.query(Task, Comment).options(load_only(Task.id,
> Comment.comment_given_by))
> ----> sqlalchemy.exc.ArgumentError: Wildcard loader can only be used with
> exactly one entity. Use Load(ent) to specify specific entities.
you already tried this one above
>
> query = session.query(Task).options(load_only(Task.id, Task.comments))
> ----> Exception: can't locate strategy for <class
> 'sqlalchemy.orm.relationships.RelationshipProperty'> (('deferred', False),
> ('instrument', True))
this is the same thing as the other one you did, string attribute
names or Object.attrname, same thing
>
> query = session.query(Task).options(load_only(Task.comments),
> subqueryload("comments").load_only('comment_given_by'))
> ----> No exception but only generates: "SELECT "TASK"."ID" FROM "TASK"
I'm not sure you tested this correctly, this one actually works - the
subqueryload("comments") cancels out the load_only(Task.comments) that
would normally raise an error. Subqueryload is emitted as a second
query, so if you just said print(query), you would not have seen it.
Here is what it emits if I run on a model like this:
s.query(A).options(load_only(A.bs), subqueryload(A.bs).load_only(B.data)).all()
output:
SELECT a.id AS a_id
FROM a
SELECT b.id AS b_id, b.data AS b_data, anon_1.a_id AS anon_1_a_id
FROM (SELECT a.id AS a_id
FROM a) AS anon_1 JOIN b ON anon_1.a_id = b.a_id ORDER BY anon_1.a_id
>
> --
> 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.
