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 sqlalchemy+...@googlegroups.com.
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/7475d865-673f-410f-8fa8-115715d445cfn%40googlegroups.com.
