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+unsubscr...@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/ef40b1ad-a7ab-4c47-8357-d3aede4dbcbd%40www.fastmail.com.
