Re: [sqlalchemy] Proper labels in joins when there are multiple relationships with the same table.
On Fri, Mar 29, 2019 at 4:59 PM 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 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 sqlal...@googlegroups.com.
>> > To post to this group, send email to sqlal...@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.
> V
Re: [sqlalchemy] Proper labels in joins when there are multiple relationships with the same table.
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 > 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 sqlal...@googlegroups.com .
> > To post to this group, send email to sqlal...@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.
Re: [sqlalchemy] Proper labels in joins when there are multiple relationships with the same table.
On Fri, Mar 29, 2019 at 2:55 PM 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 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.
[sqlalchemy] Proper labels in joins when there are multiple relationships with the same table.
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? -- 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.
