Mike, Would this select query be able to get our aggregated data?
query = select(func.max(DataModel.value)).select_from(_query .subquery()).group_by( func.date_format(DataModel.timestamp, "%Y-%m-%d %H") ) We tried this route but are now getting this error "errors": [ { "message": "'Select' object has no attribute 'statement'", "locations": [ { "line": 2, "column": 3 } ], On Thursday, April 13, 2023 at 10:13:44 AM UTC-7 Peter Harrison wrote: > Thanks Mike, > > Ideally we'd prefer to find a solution via Graphene-SQLAlchemy. > Unfortunately we don't have the luxury of creating our own query when > interacting with Graphene-SQLAlchemy. > > So the key question for us is, can you modify an existing > sqlalchemy.orm.query.Query object to insert a "func.max(Data.value)" object > into the select? This is what Graphene-SQLAlchemy is giving us. > If this is possible, then the group_by part is easy. We have tested that > frequently, the hard part is the modifying the original "select" object. > > 1. We have tried add_columns, but that adds in incompatible object type in > the GraphQL results making it an unusable option. > 2. We thought that modifying the select would be possible using data with > "statement.froms" but can't figure out how to update the MetaData object in > it > > If modifying the "select" after its creation is not possible, we need to > start considering using a separate reporting table with hourly data. > > On Wednesday, April 12, 2023 at 3:12:46 PM UTC-7 Mike Bayer wrote: > >> >> >> 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 >> >> <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/d9d912d7-07c8-48c1-9e1f-4209ab37fff5n%40googlegroups.com.