I see, thank you for the assistance.
Doing stuff like this would be a LOT easier if there was an equivalent to
"SELECT *"
On Sunday, July 10, 2022 at 10:54:19 AM UTC-6 Mike Bayer wrote:
> well your final select() is only against Entry, you don't have the daily
> expected calories part in the list of columns you are expecting. Also,
> your Entry class has no attribute called daily_calories_less_than_expected
> on it directly.
>
> there's two ways to get the data you want, one is to query for the
> additional column, see below
>
> query = (
> select(
> Entry,
>
> query_calorie_sum_less_than_expected.c.daily_calorie_sum_less_than_expected,
> )
> .outerjoin(
> query_calorie_sum_less_than_expected,
> Entry.user_id == query_calorie_sum_less_than_expected.c.user_id,
> )
> .where(Entry.user_id == User.id)
> )
>
>
> e = create_engine("sqlite://", echo=True)
>
> Base.metadata.create_all(e)
> s = Session(e)
> with s.no_autoflush:
> s.add_all(
> [
> User(
> username="u1",
> entries=[Entry(calorie_count=500)],
> daily_expected_calories=[
> DailyExpectedCalories(
> date=datetime.date.today(), expected_Calories=2000
> )
> ],
> )
> ]
> )
> s.commit()
>
> for entry, dclte in s.execute(query):
> print(f"{entry} {dclte}")
>
>
> the other is to make an attribute on Entry directly, there's actually a
> few ways to do that also :) general docs are at
> https://docs.sqlalchemy.org/en/14/orm/mapped_sql_expr.html . Here it is
> using query_expression() so that you can keep using the same query you
> have, omitted the test data this time:
>
> # mapping
>
> from sqlalchemy.orm import query_expression
>
> class Entry(Base):
> __tablename__ = "Entries"
> id = Column(Integer, primary_key=True)
> user_id = Column(Integer, ForeignKey("Users.id"))
> timestamp = Column(DateTime(timezone=True))
> time_zone_utc_offset = Column(Integer)
> calorie_count = Column(Integer)
> meal = Column(String)
> daily_calorie_sum_less_than_expected = query_expression()
>
>
>
> # building up the query:
>
> from sqlalchemy.orm import with_expression
>
> query = (
> select(
> Entry,
> )
> .options(
> with_expression(
> Entry.daily_calorie_sum_less_than_expected,
>
> query_calorie_sum_less_than_expected.c.daily_calorie_sum_less_than_expected,
> )
> )
> .outerjoin(
> query_calorie_sum_less_than_expected,
> Entry.user_id == query_calorie_sum_less_than_expected.c.user_id,
> )
> .where(Entry.user_id == User.id)
> )
>
>
> # iterating:
>
> for entry in s.scalars(query):
> print(f"{entry} {entry.daily_calorie_sum_less_than_expected}")
>
>
>
>
>
>
>
>
> On Sun, Jul 10, 2022, at 12:20 PM, Montana Burr wrote:
>
> Thank you, Mike, and apologies for not providing a stack trace or MCVE of
> my own - I assumed the code I did provide was sufficient.
>
> So, take your program and add
>
> for entry in s.execute(query):
> print(entry.daily_calories_less_than_expected)
>
> to the bottom. This represents what I'm trying to do - access an
> apparently nonexistent column (apologies if I didn't make that clear
> earlier).
>
> I just ran your program with that change and was able to reproduce the
> original error, so I'm assuming that either both of our queries are wrong,
> or the code in the "for" loop I just mentioned is wrong.
>
>
>
> On Sunday, July 10, 2022 at 9:27:23 AM UTC-6 Mike Bayer wrote:
>
>
> I've converted your fragments into a full MCVE and it runs fine, no error
> is generated. would need to see a stack trace. Try out the program below
> also.
>
> import datetime
>
> from sqlalchemy import Column
> from sqlalchemy import create_engine
> from sqlalchemy import Date
> from sqlalchemy import DateTime
> from sqlalchemy import ForeignKey
> from sqlalchemy import func
> from sqlalchemy import Integer
> from sqlalchemy import select
> from sqlalchemy import String
> from sqlalchemy.orm import declarative_base
> from sqlalchemy.orm import relationship
> from sqlalchemy.orm import Session
>
> Base = declarative_base()
>
>
> class User(Base):
> __tablename__ = "Users"
> id = Column(Integer, primary_key=True)
> username = Column(String)
> password_hash = Column(String)
> entries = relationship("Entry")
> daily_expected_calories = relationship("DailyExpectedCalories")
>
>
> class Entry(Base):
> __tablename__ = "Entries"
> id = Column(Integer, primary_key=True)
> user_id = Column(Integer, ForeignKey("Users.id"))
> timestamp = Column(DateTime(timezone=True))
> time_zone_utc_offset = Column(Integer)
> calorie_count = Column(Integer)
> meal = Column(String)
>
>
> class DailyExpectedCalories(Base):
> __tablename__ = "DailyExpectedCalories"
> date = Column(Date, primary_key=True)
> user_id = Column(Integer, ForeignKey("Users.id"), primary_key=True)
> expected_Calories = Column(Integer)
>
>
> query_calorie_sum_less_than_expected = (
> select(
> Entry.user_id,
> (
> func.sum(Entry.calorie_count)
> < DailyExpectedCalories.expected_Calories
> ).label("daily_calorie_sum_less_than_expected"),
> )
> .join(
> DailyExpectedCalories, Entry.user_id ==
> DailyExpectedCalories.user_id
> )
> .subquery()
> )
>
> query = (
> select(Entry)
> .outerjoin(
> query_calorie_sum_less_than_expected,
> Entry.user_id == query_calorie_sum_less_than_expected.c.user_id,
> )
> .where(Entry.user_id == User.id)
> )
>
>
> e = create_engine("sqlite://", echo=True)
>
> Base.metadata.create_all(e)
> s = Session(e)
> with s.no_autoflush:
> s.add_all(
> [
> User(
> username="u1",
> entries=[Entry()],
> daily_expected_calories=[
> DailyExpectedCalories(
> date=datetime.date.today(), expected_Calories=2000
> )
> ],
> )
> ]
> )
> s.commit()
>
> for entry in s.scalars(query):
> print(entry)
>
>
> On Sat, Jul 9, 2022, at 4:12 PM, Montana Burr wrote:
>
> Hi folks!
>
> I have a rather complicated SQL query to perform. I kind of know how I
> would do it in SQL and am looking to port it to SQLAlchemy.
>
> I have these ORM classes:
>
> class User(Base):
> __tablename__ = "Users"
> id = Column(Integer, primary_key=True)
> username = Column(String)
> password_hash = Column(String)
> entries = relationship("Entry")
> daily_expected_calories = relationship("DailyExpectedCalories")
>
> class Entry(Base):
> __tablename__ = "Entries"
> id = Column(Integer, primary_key=True)
> user_id = Column(Integer, ForeignKey("Users.id"))
> timestamp = Column(DateTime(timezone=True))
> time_zone_utc_offset = Column(Integer)
> calorie_count = Column(Integer)
> meal = Column(String)
>
>
> class DailyExpectedCalories(Base):
> __tablename__ = "DailyExpectedCalories"
> date = Column(Date, primary_key=True)
> user_id = Column(Integer, ForeignKey("Users.id"), primary_key=True)
> expected_Calories = Column(Integer)
>
> and I am looking to build a query that gives me objects of type Entry and
> a Boolean field indicating whether the user has consumed fewer calories
> than expected during the day of the Entry.
>
> For example, if I a 1111-Calorie food in the morning, a 2222-Calorie food
> in the afternoon, and a 3333-Calorie food in the afternoon, but I only
> expected to eat 3000 Calories, the results of the query might look
> something like this:
>
> 07/09/2022 9:00 AM 1111 False
> 07/09/2022 1:00 PM 2222 False
> 07/09/2022 7:00 PM 3333 False
>
> I've come up with these queries:
>
> query_calorie_sum_less_than_expected = select(Entry.user_id, (
> func.sum(Entry.calorie_count) < DailyExpectedCalories.
> expected_Calories
> ).label("daily_calorie_sum_less_than_expected")).join(
> DailyExpectedCalories,
> Entry.user_id == DailyExpectedCalories.user_id).subquery()
> query = select(Entry).outerjoin(
> query_calorie_sum_less_than_expected,
> Entry.user_id == query_calorie_sum_less_than_expected
> .c.user_id).where(
> Entry.user_id == user.id)
>
> but when I do
>
> results = engine.execute(query)
>
> and then do something like
>
> for row in results:
> results.daily_calorie_sum_less_than_expected
>
> SQLAlchemy complains that the aforementioned column does not exist.
>
> So how would I do the kind of query I'm looking to do?
>
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/4b724e77-da6c-4581-aecb-f9196e53ab80n%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/4b724e77-da6c-4581-aecb-f9196e53ab80n%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
>
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/7475d865-673f-410f-8fa8-115715d445cfn%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/7475d865-673f-410f-8fa8-115715d445cfn%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
>
>
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/a2ff7075-01e5-4ca4-9569-953ed06e669cn%40googlegroups.com.
