subject:"\[sqlalchemy\] Modifying Query Object"

Re: [sqlalchemy] Modifying Query Object

2023-04-16 Thread Mike Bayer

if you add a column to a Query that was returning an object, like Data, you 
will get back tuples of (Data, extra_col).  this is probably what the tool you 
are using is complaining about.

On Fri, Apr 14, 2023, at 10:51 PM, Mike Bayer wrote:
> 
> 
> On Tue, Apr 11, 2023, at 9:17 PM, Luis Del Rio IV wrote:
>> The sql query itself returns several rows, as it should. But when trying to 
>> combine the max using sqlalchemy the rows return as the following.
>> 
>> Received incompatible instance \"(> 0x7f2c6cfd6670>, '2021-04-10 18', Decimal('7294.0'))\".",
> 
> that's not a SQLAlchemy error.   I would again suggest you get help from the 
> maintainers of the library you're using.
> 
> 
> 
>> 
>> Here I am able to get the max for that row group, but am unable to pass it 
>> into the DataModel object
>> 
>> On Tuesday, April 11, 2023 at 4:59:15 PM UTC-7 S Mahabl wrote:
>>> Do you get many rows?
>>> 
>>> SELECT  date_format(data.timestamp, "%Y-%m-%d %H"), max(data.value)  AS 
>>> data_value
>>> from data
>>> GROUP BY date_format(data.timestamp, "%Y-%m-%d %H")
>>> On Tue, Apr 11, 2023 at 4:24 PM Luis Del Rio IV  wrote:
 Hello,
 
 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.
 
 
 --
 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/a73c1830-8c64-437a-8ea7-a171767e2223n%40googlegroups.com
  
 .
>> 
>> 
>> --
>> 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/3eeda16b-9c36-4ca5-91b5-cba325343fban%40googlegroups.com
>>  
>> .
> 
> 
> 
> -- 
> 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/ee62e370-b0c1-4656-95e2-4ecbcf4791e7%40app.fastmail.com
>  
> .

-- 
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

Re: [sqlalchemy] Modifying Query Object

2023-04-14 Thread Mike Bayer



On Tue, Apr 11, 2023, at 9:17 PM, Luis Del Rio IV wrote:
> The sql query itself returns several rows, as it should. But when trying to 
> combine the max using sqlalchemy the rows return as the following.
> 
> Received incompatible instance \"( 0x7f2c6cfd6670>, '2021-04-10 18', Decimal('7294.0'))\".",

that's not a SQLAlchemy error.   I would again suggest you get help from the 
maintainers of the library you're using.



> 
> Here I am able to get the max for that row group, but am unable to pass it 
> into the DataModel object
> 
> On Tuesday, April 11, 2023 at 4:59:15 PM UTC-7 S Mahabl wrote:
>> Do you get many rows?
>> 
>> SELECT  date_format(data.timestamp, "%Y-%m-%d %H"), max(data.value)  AS 
>> data_value
>> from data
>> GROUP BY date_format(data.timestamp, "%Y-%m-%d %H")
>> On Tue, Apr 11, 2023 at 4:24 PM Luis Del Rio IV  wrote:
>>> Hello,
>>> 
>>> 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.
>>> 
>>> 
>>> -- 
>>> 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/a73c1830-8c64-437a-8ea7-a171767e2223n%40googlegroups.com
>>>  
>>> .
> 
> 
> -- 
> 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/3eeda16b-9c36-4ca5-91b5-cba325343fban%40googlegroups.com
>  
> .

-- 
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/ee62e370-b0c1-4656-95e2-4ecbcf4791e7%40app.fastmail.com.

Re: [sqlalchemy] Modifying Query Object

2023-04-14 Thread Peter Harrison

I have some additional context on the issue Luis mentioned.

   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.

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

Getting a solution to this will help the graphene-sqlalchemy team create 
better documentation.

On Tuesday, April 11, 2023 at 4:59:15 PM UTC-7 S Mahabl wrote:

> Do you get many rows?
>
> SELECT  date_format(data.timestamp, "%Y-%m-%d %H"), max(data.value)  AS 
> data_value
> from data
> GROUP BY date_format(data.timestamp, "%Y-%m-%d %H")
> On Tue, Apr 11, 2023 at 4:24 PM Luis Del Rio IV  
> wrote:
>
>> Hello,
>>
>> 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.
>>
>> -- 
>> 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/a73c1830-8c64-437a-8ea7-a171767e2223n%40googlegroups.com
>>  
>> 
>> .
>>
>

-- 
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/9766a396-ba23-44fc-b0d7-4c72bea2f779n%40googlegroups.com.

Re: [sqlalchemy] Modifying Query Object

2023-04-14 Thread Peter Harrison

I have some additional context on the issue Luis mentioned.

   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.

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

Getting a solution to this will help the graphene-sqlalchemy team create
better documentation.

Peter


On Tue, Apr 11, 2023 at 4:59 PM S Mahabl  wrote:

> Do you get many rows?
>
> SELECT  date_format(data.timestamp, "%Y-%m-%d %H"), max(data.value)  AS
> data_value
> from data
> GROUP BY date_format(data.timestamp, "%Y-%m-%d %H")
>
> On Tue, Apr 11, 2023 at 4:24 PM Luis Del Rio IV  wrote:
>
>> Hello,
>>
>> 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.
>>
>> --
>> 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/a73c1830-8c64-437a-8ea7-a171767e2223n%40googlegroups.com
>> 
>> .
>>
> --
> 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/CAOV%2B3C2PU2ndh9Uf-ZGtDj_ao-3rhQATE9MjYAppSSnwKT6%2Beg%40mail.gmail.com
> 
> .
>

-- 
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/CAN61F1xnZNX2HfwB0jMY6iOnzNLHQ__t9k7ARPJZpowVFVeSUg%40mail.gmail.com.

Re: [sqlalchemy] Modifying Query Object

2023-04-14 Thread Luis Del Rio IV

 The sql query itself returns several rows, as it should. But when trying
to combine the max using sqlalchemy the rows return as the following.

Received incompatible instance \"(, '2021-04-10 18', Decimal('7294.0'))\".",

Here I am able to get the max for that row group, but am unable to pass it
into the DataModel object


Luis Del Rio IV
DevOps Engineer
[image: Colovore]
1101 Space Park Drive
Santa Clara, CA 95054

mobile: 408. <%204085315362>*375.4195*
24/7: 408.330.9290 <%20408%203309290>


On Tue, Apr 11, 2023 at 4:59 PM S Mahabl  wrote:

> Do you get many rows?
>
> SELECT  date_format(data.timestamp, "%Y-%m-%d %H"), max(data.value)  AS
> data_value
> from data
> GROUP BY date_format(data.timestamp, "%Y-%m-%d %H")
>
> On Tue, Apr 11, 2023 at 4:24 PM Luis Del Rio IV  wrote:
>
>> Hello,
>>
>> 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.
>>
>> --
>> 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/a73c1830-8c64-437a-8ea7-a171767e2223n%40googlegroups.com
>> 
>> .
>>
> --
> 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 a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/j5fIV6NmAns/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAOV%2B3C2PU2ndh9Uf-ZGtDj_ao-3rhQATE9MjYAppSSnwKT6%2Beg%40mail.gmail.com
> 
> .
>

-- 
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/CABKymD8cF3JQoxD_-qH6Y4kg-tp9GOGM2kO3bqrm_qY6Ogz%3DcQ%40mail.gmail.com.

Re: [sqlalchemy] Modifying Query Object

2023-04-14 Thread Luis Del Rio IV

 The sql query itself returns several rows, as it should. But when trying 
to combine the max using sqlalchemy the rows return as the following.

Received incompatible instance "(server.db.models.Data object at , 
'2021-04-10 18', Decimal('7294.0'))".", 

Here I am able to get the max for that row group, but am unable to pass it 
into the DataModel object


On Tuesday, April 11, 2023 at 4:59:15 PM UTC-7 S Mahabl wrote:

> Do you get many rows?
>
> SELECT  date_format(data.timestamp, "%Y-%m-%d %H"), max(data.value)  AS 
> data_value
> from data
> GROUP BY date_format(data.timestamp, "%Y-%m-%d %H")
> On Tue, Apr 11, 2023 at 4:24 PM Luis Del Rio IV  
> wrote:
>
>> Hello,
>>
>> 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.
>>
>> -- 
>> 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/a73c1830-8c64-437a-8ea7-a171767e2223n%40googlegroups.com
>>  
>> 
>> .
>>
>

-- 
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/dc2df88c-b30d-4cb2-a017-385edc46e1cfn%40googlegroups.com.

Re: [sqlalchemy] Modifying Query Object

2023-04-14 Thread Luis Del Rio IV

The sql query itself returns several rows, as it should. But when trying to 
combine the max using sqlalchemy the rows return as the following.

Received incompatible instance \"(, '2021-04-10 18', Decimal('7294.0'))\".", 

Here I am able to get the max for that row group, but am unable to pass it 
into the DataModel object
On Tuesday, April 11, 2023 at 4:59:15 PM UTC-7 S Mahabl wrote:

> Do you get many rows?
>
> SELECT  date_format(data.timestamp, "%Y-%m-%d %H"), max(data.value)  AS 
> data_value
> from data
> GROUP BY date_format(data.timestamp, "%Y-%m-%d %H")
> On Tue, Apr 11, 2023 at 4:24 PM Luis Del Rio IV  
> wrote:
>
>> Hello,
>>
>> 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.
>>
>> -- 
>> 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/a73c1830-8c64-437a-8ea7-a171767e2223n%40googlegroups.com
>>  
>> 
>> .
>>
>

-- 
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/94139311-fe93-4584-b07a-239ec3b85159n%40googlegroups.com.

Re: [sqlalchemy] Modifying Query Object

2023-04-14 Thread Peter Harrison

I have some additional context on the issue Luis mentioned.

   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.

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

Getting a solution to this will help the graphene-sqlalchemy team create
better documentation.

Peter


On Tue, Apr 11, 2023 at 4:59 PM S Mahabl  wrote:

> Do you get many rows?
>
> SELECT  date_format(data.timestamp, "%Y-%m-%d %H"), max(data.value)  AS
> data_value
> from data
> GROUP BY date_format(data.timestamp, "%Y-%m-%d %H")
>
> On Tue, Apr 11, 2023 at 4:24 PM Luis Del Rio IV  wrote:
>
>> Hello,
>>
>> 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.
>>
>> --
>> 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/a73c1830-8c64-437a-8ea7-a171767e2223n%40googlegroups.com
>> 
>> .
>>
> --
> 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/CAOV%2B3C2PU2ndh9Uf-ZGtDj_ao-3rhQATE9MjYAppSSnwKT6%2Beg%40mail.gmail.com
> 
> .
>

-- 
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/CAN61F1zZeAk69nVdc_VhnBgjdn%2B2-JzutG0xy6YFwrWdtNG%2BLg%40mail.gmail.com.

Re: [sqlalchemy] Modifying Query Object

2023-04-14 Thread Luis Del Rio IV

 The sql query itself returns several rows, as it should. But when trying 
to combine the max using sqlalchemy the rows return as the following.

Received incompatible instance \"(, '2021-04-10 18', Decimal('7294.0'))\".", 

Here I am able to get the max for that row group, but am unable to pass it 
into the DataModel object

On Tuesday, April 11, 2023 at 4:59:15 PM UTC-7 S Mahabl wrote:

> Do you get many rows?
>
> SELECT  date_format(data.timestamp, "%Y-%m-%d %H"), max(data.value)  AS 
> data_value
> from data
> GROUP BY date_format(data.timestamp, "%Y-%m-%d %H")
> On Tue, Apr 11, 2023 at 4:24 PM Luis Del Rio IV  
> wrote:
>
>> Hello,
>>
>> 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.
>>
>> -- 
>> 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/a73c1830-8c64-437a-8ea7-a171767e2223n%40googlegroups.com
>>  
>> 
>> .
>>
>

-- 
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/3eeda16b-9c36-4ca5-91b5-cba325343fban%40googlegroups.com.

Re: [sqlalchemy] Modifying Query Object

2023-04-11 Thread S Mahabl

Do you get many rows?

SELECT  date_format(data.timestamp, "%Y-%m-%d %H"), max(data.value)  AS
data_value
from data
GROUP BY date_format(data.timestamp, "%Y-%m-%d %H")

On Tue, Apr 11, 2023 at 4:24 PM Luis Del Rio IV  wrote:

> Hello,
>
> 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.
>
> --
> 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/a73c1830-8c64-437a-8ea7-a171767e2223n%40googlegroups.com
> 
> .
>

-- 
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/CAOV%2B3C2PU2ndh9Uf-ZGtDj_ao-3rhQATE9MjYAppSSnwKT6%2Beg%40mail.gmail.com.

[sqlalchemy] Modifying Query Object

2023-04-11 Thread Luis Del Rio IV

Hello,

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.

-- 
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/a73c1830-8c64-437a-8ea7-a171767e2223n%40googlegroups.com.

Re: [sqlalchemy] Modifying Query Object

Re: [sqlalchemy] Modifying Query Object

Re: [sqlalchemy] Modifying Query Object

Re: [sqlalchemy] Modifying Query Object

Re: [sqlalchemy] Modifying Query Object

Re: [sqlalchemy] Modifying Query Object

Re: [sqlalchemy] Modifying Query Object

Re: [sqlalchemy] Modifying Query Object

Re: [sqlalchemy] Modifying Query Object

Re: [sqlalchemy] Modifying Query Object

[sqlalchemy] Modifying Query Object

11 matches

Site Navigation

Mail list logo

Footer information