well you can do `select('*')` if you want but if you want the rows interpreted
as Entry objects and not individual column objects you need to tell that to the
program.
select * at the moment works more easily with a core execution, like this:
query = (
select('*').select_from(Entry)
.outerjoin(...)
for row in s.connection().execute(query):
print(f"{row}")
output
SELECT *
FROM "Entries" LEFT OUTER JOIN (SELECT "Entries".user_id AS user_id,
sum("Entries".calorie_count) < "DailyExpectedCalories"."expected_Calories" AS
daily_calorie_sum_less_than_expected
FROM "Entries" JOIN "DailyExpectedCalories" ON "Entries".user_id =
"DailyExpectedCalories".user_id) AS anon_1 ON "Entries".user_id =
anon_1.user_id, "Users"
WHERE "Entries".user_id = "Users".id
2022-07-10 13:46:32,563 INFO sqlalchemy.engine.Engine [generated in 0.00020s] ()
(1, 1, None, None, 500, None, 1, 1, 1, 'u1', None)
I can try to see if session.execute(query) where the Q is ORM enabled can still
work with "select *"
On Sun, Jul 10, 2022, at 1:30 PM, Montana Burr wrote:
>
> 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
>
> <https://groups.google.com/d/msgid/sqlalchemy/a2ff7075-01e5-4ca4-9569-953ed06e669cn%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/27af1858-f129-46b6-98b2-dd653d619df0%40www.fastmail.com.
