I suppose adding the "for" loop may be unnecessary.
Simply change print(entry) in the s.scalars loop to
print(entry.daily_calories_less_than_expected) and you'll (hopefully)
receive a similar error: "AttributeError: 'Entry' object has no attribute
'daily_calories_less_than_expected'"
On Sunday, July 10, 2022 at 10:20:12 AM UTC-6 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/f254c853-ab33-4104-93b2-c8b16458784an%40googlegroups.com.
