On Wed, Apr 12, 2023, at 5:21 PM, Luis Del Rio IV wrote:
> I am currently using the following sqlalchemy code,
>
> _query = super().get_query(model, info, sort, **args)
> query = _query.group_by(
> func.date_format(DataModel.timestamp, "%Y-%m-%d %H")
> )
> return query
>
> I am trying to aggregate the the max value of a field from DataModel.value
> utilizing the group by clause.
>
> In simple sql, you would do the following.
>
> SELECT max(data.value) AS data_value
> from data
> GROUP BY date_format(data.timestamp, "%Y-%m-%d %H")
>
> What would the proper way to express this? The various methods I have tried
> somehow overwrite the original query and do not map to our attributes.
using legacy query style:
q1 = session.query(func.max(Data.value)).group_by(
func.date_format(Data.timestamp, "%Y-%m-%d %H")
)
using 2.0 style select(), replace "session.query" with "select":
s1 = select(func.max(Data.value)).group_by(
func.date_format(Data.timestamp, "%Y-%m-%d %H")
)
POC script is at the bottom of this email.
>
>
> I have some additional context on the issue
> 1. We are using the graphene-sqlalchemy package.
> 2. When you do a GraphQL web api query, the package creates a
> sqlalchemy.orm.query.Query object.
> 3. We want to modify this standard query that the package creates so that we
> can do the group_by action to help us get the maximum time series value for
> every hour because this is not possible using GraphQL.
> 4. Talking to the graphene-sqlalchemy team they told us the best place to do
> the modification to the standardized query is in the get_query method (line
> 67) in this file:
> https://github.com/graphql-python/graphene-sqlalchemy/blob/master/graphene_sqlalchemy/fields.py
> 5. The standardized query we need to modify translates to this SQL
> statement:
> SELECT sy_data.oid_id, sy_data.rrd_timestamp, sy_data.cabinet_id, sy_data
> .customer_id, sy_data.value, sy_data.active_flag, sy_data.timestamp
> FROM sy_data
> WHERE sy_data.rrd_timestamp >= %(rrd_timestamp_1)s AND sy_data.rrd_timestamp
> <= %(rrd_timestamp_2)s AND (sy_data.oid_id = %(oid_id_1)s OR sy_data.oid_id =
> %(o
> id_id_2)s) ORDER BY sy_data.oid_id ASC, sy_data.rrd_timestamp ASC
>
> Therefore we need to find a way to insert a func.max for the values in the
> first part of the SELECT statement, (before the FROM). It is easy for us to
> apend the group_by like this.
if you have a Query which renders the above SQL, you can add group_by() to it
in place. but if these queries are being translated into GraphQL, and GraphQL
does not have any concept of GROUP BY, then it wont work, what you want to do
would not be possible unless a GraphQL query exists that does what you need.
>
> query.group_by(
> func.date_format(DataModel.timestamp, "%Y-%m-%d %H")
> )
>
> The big issue for us is to figure out how to insert the func.max
see func at
https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.func
from sqlalchemy import Column
from sqlalchemy import DateTime
from sqlalchemy import func
from sqlalchemy import Integer
from sqlalchemy import select
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import Session
Base = declarative_base()
class Data(Base):
__tablename__ = "data"
id = Column(Integer, primary_key=True)
value = Column(Integer)
timestamp = Column(DateTime)
s = Session()
q1 = s.query(func.max(Data.value)).group_by(
func.date_format(Data.timestamp, "%Y-%m-%d %H")
)
print(q1)
s1 = select(func.max(Data.value)).group_by(
func.date_format(Data.timestamp, "%Y-%m-%d %H")
)
print(s1)
--
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/328c16af-189a-4050-a67f-06c78a9dec99%40app.fastmail.com.
