On Fri, Mar 29, 2019 at 4:59 PM <[email protected]> wrote:
>
> 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.
OK then you would not use eager loading, you want joins and columns:
from sqlalchemy.orm import aliased
customer = aliased(User)
reporter = aliased(User)
q = session.query(
Account.x.label("x_name"),
Account.y.label("y_name"),
customer.p.label("p_name"),
reporter.q.label("r_name")).select_from(Account).join(customer,
Account.customer).join(reporter, Account.reporter)
pd.read_sql(q.statement, query.session.bind)
An example of using aliases with ORM joins is in the ORM tutorial at:
https://docs.sqlalchemy.org/en/latest/orm/tutorial.html#using-aliases
Querying for individual columns using custom label names is also in
the tutorial, at:
https://docs.sqlalchemy.org/en/latest/orm/tutorial.html#querying
hope this helps.
>
> 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]> 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].
>> > 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.
>
> --
> 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.
--
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.
