This is definitely along the right track but it conflicts a little bit with
the recommended pandas api, for example:
query = session.query(Account)
return pd.read_sql(query.statement, query.session.bind)
I can still follow your method and add each row to a dataframe instead but
its not as efficient as the above and speed is a concern in this case. And
you are right I would like to avoid working with the stringified query. Is
there a solution in this case? Thanks much for the help.
On Friday, March 29, 2019 at 4:33:39 PM UTC-4, Mike Bayer wrote:
>
> On Fri, Mar 29, 2019 at 2:55 PM <[email protected] <javascript:>> wrote:
> >
> > I have a model, Account, with two foreign keys / relationships to
> another model, Users.
> >
> > class Account(object):
> > @declared_attr
> > def customer_id(cls):
> > return Column(ForeignKey(User.id))
> > @declared_attr
> > def customer(cls):
> > return relationship(User, lazy='joined',
> foreign_keys=cls.customer_id)
> >
> > @declared_attr
> > def reporter_id(cls):
> > return Column(ForeignKey(User.id))
> > @declared_attr
> > def reporter(cls):
> > return relationship(User, lazy='joined',
> foreign_keys=cls.reporter_id)
> >
> > session.query(Account) gives me the correct join logic for eager
> loading. The issue comes when I want to load this data into a pandas
> dataframe. The joined columns show up labeled as users_1_colname and
> users_2_colname which makes it unclear which came from reporter and which
> came from customer. I know in a one off query I can use aliases but how can
> I have a certain alias dedicated to a relationship? I don't want to
> manually generate the query and I don't want to change the column names in
> pandas. I want users_1 to always be labeled reporter and users_2 to always
> be labeled customer when I query Account. Is this possible?
>
> When using relationships there is no exposure of any kind of "labels"
> to the end user, unless you are taking the stringified version of an
> ORM query using str(). There's no need to do that, as Query can
> execute the results for you directly where it then returns them as ORM
> objects, without you ever having to deal with any labels:
>
> for acc in session.query(Account):
> row = {"customer": acc.customer.name, "reporter":
> acc.reporter.name} # or whatever fields you want, this assumes there
> is a "User.name" field
> my_dataframe.add_row(row) # or whatever pandas API is here
>
> the names you use with the results of an ORM query that uses eager
> loading are in terms of the attribute names you put on the objects.
>
> There's a lot of other ways to do this, which can be more automatic,
> but that's the simplest, feel free to share more details if this is
> not sufficient.
>
>
>
> >
> > --
> > 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 [email protected] <javascript:>.
> > To post to this group, send email to [email protected]
> <javascript:>.
> > 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
