every case is now listed out for improved error reporting in:
https://github.com/sqlalchemy/sqlalchemy/issues/4433
On Wed, Jan 9, 2019 at 12:21 AM Mike Bayer <mike...@zzzcomputing.com> wrote:
>
> 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.
