Re: [sqlalchemy] Modifying Query Object

[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

[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

[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

[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

[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

[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

[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

[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] Re: Dealing with readonly column

[Mike Bayer]

On Fri, Apr 14, 2023, at 3:02 PM, Lele Gaifax wrote:
> "Mike Bayer"  writes:
>
>> On Fri, Apr 14, 2023, at 8:03 AM, Lele Gaifax wrote:
>>> I now have
>>>
>>>   CREATE TABLE something (id SERIAL, name TEXT)
>>>
>>>   CREATE FUNCTION counter(something)
>>>   RETURNS INTEGER AS $$
>>> SELECT count(*) FROM something_else se
>>> WHERE se.something_id = $1.id
>>>   $$ STABLE SQL
>>>
>>> and thus existing queries such as
>>>
>>>   SELECT s.name, s.counter
>>>   FROM something AS s
>>>
>>> work as before.
>>
>> how does "SELECT s.counter" work if the table does not have an actual 
>> "counter" column?  
>
> As explained here [1], "the field notation and functional notation are
> equivalent", that is "SELECT a.foo FROM t AS a" and "SELECT foo(a) FROM
> t AS a" means the same, when "foo" is a function accepting the "implicit
> composite type corresponding to a row in the table t".

oh geez it's that silly PostgreSQL syntax.not a fan.   you can use that but 
you'd be on your own

>
>>
>> Using Computed in this way is equivalent to using FetchedValue.Why is 
>> that a problem? 
>
> No, it's not a problem, but the following
>
>   stmt = something.update().values(counter=42).where(something.c.id=1)
>   connection.execute(stmt)
>  
> will raise an error at execution time, as will, assuming
> "instance_of_something" is an instance of the ORM class mapped to the
> table "something"

So what then is a "read only column" if not one that raises an error if someone 
tries to write to it explicitly?

>   instance_of_something.counter = 42
>   session.flush()
>
> I was just imagining something that could raise an error earlier.

well you have the ORM validates and the before_cursor_execute approaches, but I 
would think if this is simple developer level programming guards, the PG 
exception is perfect


>
> Unfortunately in this case PG does not help: the closest thing is a
> "GENERATED" column [2], but (up to version 15 at least) it "is a special
> column that is always computed from other columns", it cannot be an
> arbitrary subquery.

trigger, then.  im sure this is a thing PG can do

>
>> Without using server-side constructs, in a practical sense, simply
>> omitting the column from the Table or using mapper.exclude_properties
>> is in my experience sufficient.
>
> Uhm, how could I then select that value, to be returned by an API that
> queries the "something" table?

OK so the "readonly" you are looking for includes one where your application 
actually needs to load it, then fine, dont exclude it from your mappings.

-- 
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/eb9fc1e0-8494-4ec8-aad5-4cab3ae10bf5%40app.fastmail.com.

Re: [sqlalchemy] Using joins+max with sql server

[Mike Bayer]

this line of code:

query = session.query(CodigoProduto.CdChamada, Produto.IdProduto, 
Produto.NmProduto, ProdutoEmpresa.VlPrecoCusto, ProdutoEmpresa.VlPrecoSugerido, 
EstoqueEmpresa.DtReferencia, EstoqueEmpresa.QtEstoque)


should look like this:

query = session.query(CodigoProduto.CdChamada, Produto.IdProduto, 
Produto.NmProduto, ProdutoEmpresa.VlPrecoCusto, ProdutoEmpresa.VlPrecoSugerido, 
estoqueAtual.DtReferencia, estoqueAtual.QtEstoque)




On Fri, Apr 14, 2023, at 3:21 PM, Elias Coutinho wrote:
> *I am suffering!
> 
> It showed the same message.*
> 
> 
> # Subquery para buscar o maior registro de estoqueempresa para cada produto
> estoqueAtual = session.query(
> EstoqueEmpresa.IdProduto,
> EstoqueEmpresa.QtEstoque,
> func.max(EstoqueEmpresa.DtReferencia).label('MaxDtReferencia')
> ).group_by(EstoqueEmpresa.IdProduto, EstoqueEmpresa.QtEstoque).subquery()
> 
> #print(estoqueAtual)
> 
> # Realiza a consulta com SQLAlchemy
> ##query = session.query(CodigoProduto.CdChamada, Produto.IdProduto, 
> Produto.NmProduto, ProdutoEmpresa.VlPrecoCusto, 
> ProdutoEmpresa.VlPrecoSugerido, estoqueAtual.c.QtEstoque)\
> ##   .join(ProdutoEmpresa, Produto.IdProduto == 
> ProdutoEmpresa.IdProduto)\
> ##   .join(CodigoProduto, Produto.IdProduto == 
> CodigoProduto.IdProduto)\
> ##   .join(estoqueAtual, and_(Produto.IdProduto == 
> estoqueAtual.c.IdProduto))\
> ##   .join(EstoqueEmpresa, and_(EstoqueEmpresa.IdProduto == 
> Produto.IdProduto))\
> ##   .filter(ProdutoEmpresa.StAtivoVenda == 'S')\
> ##   .filter(ProdutoEmpresa.CdEmpresa == 4)\
> ##   .order_by(Produto.NmProduto)
> 
> query = session.query(CodigoProduto.CdChamada, Produto.IdProduto, 
> Produto.NmProduto, ProdutoEmpresa.VlPrecoCusto, 
> ProdutoEmpresa.VlPrecoSugerido, EstoqueEmpresa.DtReferencia, 
> EstoqueEmpresa.QtEstoque)\
>.join(ProdutoEmpresa, Produto.IdProduto == 
> ProdutoEmpresa.IdProduto)\
>.join(CodigoProduto, Produto.IdProduto == 
> CodigoProduto.IdProduto)\
>.join(estoqueAtual, and_(Produto.IdProduto == 
> estoqueAtual.c.IdProduto, EstoqueEmpresa.DtReferencia == 
> estoqueAtual.c.MaxDtReferencia))\
>.join(EstoqueEmpresa, and_(EstoqueEmpresa.IdProduto == 
> Produto.IdProduto, EstoqueEmpresa.DtReferencia == 
> estoqueAtual.c.MaxDtReferencia))\
>.filter(ProdutoEmpresa.StAtivoVenda == 'S')\
>.filter(ProdutoEmpresa.CdEmpresa == 4)\
>.order_by(Produto.NmProduto)
> 
> Em sex., 14 de abr. de 2023 às 15:21, Mike Bayer 
>  escreveu:
>> __
>> the initial issue is that you want DtReferencia from the subquery on the 
>> outside:
>> 
>> session.query(..., estoqueAtual.c.DtReferencia, ...)
>> 
>> and not "EstoqueEmpresa.DtReferencia", that's not available in the FROM 
>> list, it's inside a subquery.
>> 
>> also I dont think you'd want to "group by" the same column that you are 
>> feeing into max().that would defeat the purpose of using an aggregate.
>> 
>> On Fri, Apr 14, 2023, at 1:30 PM, Elias Coutinho wrote:
>>> Good afternoon.
>>> I am having trouble transforming a SQL Server query to SQL Alchemy.
>>> 
>>> *The SQL Server query is this*
>>> **
>>> SELECT CP.CdChamada, P.NmProduto, PE.VlPrecoCusto, PE.VlPrecoSugerido, 
>>> EE.QtEstoque, EE.DtReferencia
>>> FROM Produto P
>>>  INNER JOIN Produto_Empresa PE ON  P.IdProduto = PE.IdProduto
>>>  INNER JOIN CodigoProduto CP ON  P.IdProduto = CP.IdProduto
>>>  INNER JOIN (SELECT IdProduto, CdEmpresa, MAX(DtReferencia) AS 
>>> MaxDtReferencia
>>>  FROM EstoqueEmpresa
>>>  GROUP BY IdProduto, CdEmpresa) AS EE2 ON EE2.IdProduto = 
>>> P.IdProduto AND EE2.CdEmpresa = PE.CdEmpresa
>>>  INNER JOIN EstoqueEmpresa EE ON EE.IdProduto = EE2.IdProduto AND 
>>> EE.DtReferencia = EE2.MaxDtReferencia AND EE.CdEmpresa = EE2.CdEmpresa
>>> WHERE PE.StAtivoVenda = 'S' AND
>>>   PE.CdEmpresa = 4 AND
>>>   CP.IdTipoCodigoProduto = '00A002'
>>> ORDER BY CP.CdChamada**
>>> 
>>> *My code is as follows:*
>>> *My Model*
>>> class EstoqueEmpresa(Base):
>>> __tablename__ = 'EstoqueEmpresa'
>>> 
>>> IdProduto = Column(CHAR(10, 'SQL_Latin1_General_CP850_CI_AI'), 
>>> primary_key=True, nullable=False)
>>> CdEmpresa = Column(Integer, primary_key=True, nullable=False)
>>> DtReferencia = Column(DateTime, primary_key=True, nullable=False, 
>>> index=True)
>>> VersaoRegistro = Column(TIMESTAMP, nullable=False)
>>> QtCompra = Column(Float(53))
>>> VlCompra = Column(Float(53))
>>> QtVenda = Column(Float(53))
>>> VlVenda = Column(Float(53))
>>> VlCustoMercadoriaVendida = Column(Float(53))
>>> QtEntrada = Column(Float(53))
>>> VlEntrada = Column(Float(53))
>>> QtSaida = Column(Float(53))
>>> VlSaida = Column(Float(53))
>>> VlSaidaAcerto = Column(Float(53))
>>> QtSaidaAcerto = Column(Float(53))
>>> QtEstoque = Column(Float(53))

Re: [sqlalchemy] Using joins+max with sql server

[Elias Coutinho]

*I am suffering!It showed the same message.*


# Subquery para buscar o maior registro de estoqueempresa para cada produto
estoqueAtual = session.query(
EstoqueEmpresa.IdProduto,
EstoqueEmpresa.QtEstoque,
func.max(EstoqueEmpresa.DtReferencia).label('MaxDtReferencia')
).group_by(EstoqueEmpresa.IdProduto, EstoqueEmpresa.QtEstoque).subquery
()

#print(estoqueAtual)

# Realiza a consulta com SQLAlchemy
##query = session.query(CodigoProduto.CdChamada, Produto.IdProduto,
Produto.NmProduto, ProdutoEmpresa.VlPrecoCusto,
ProdutoEmpresa.VlPrecoSugerido, estoqueAtual.c.QtEstoque)\
##   .join(ProdutoEmpresa, Produto.IdProduto ==
ProdutoEmpresa.IdProduto)\
##   .join(CodigoProduto, Produto.IdProduto ==
CodigoProduto.IdProduto)\
##   .join(estoqueAtual, and_(Produto.IdProduto ==
estoqueAtual.c.IdProduto))\
##   .join(EstoqueEmpresa, and_(EstoqueEmpresa.IdProduto ==
Produto.IdProduto))\
##   .filter(ProdutoEmpresa.StAtivoVenda == 'S')\
##   .filter(ProdutoEmpresa.CdEmpresa == 4)\
##   .order_by(Produto.NmProduto)

query = session.query(CodigoProduto.CdChamada, Produto.IdProduto, Produto.
NmProduto, ProdutoEmpresa.VlPrecoCusto, ProdutoEmpresa.VlPrecoSugerido,
EstoqueEmpresa.DtReferencia, EstoqueEmpresa.QtEstoque)\
   .join(ProdutoEmpresa, Produto.IdProduto == ProdutoEmpresa.
IdProduto)\
   .join(CodigoProduto, Produto.IdProduto == CodigoProduto.
IdProduto)\
   .join(estoqueAtual, and_(Produto.IdProduto ==
estoqueAtual.c.IdProduto,
EstoqueEmpresa.DtReferencia == estoqueAtual.c.MaxDtReferencia))\
   .join(EstoqueEmpresa, and_(EstoqueEmpresa.IdProduto ==
Produto.IdProduto, EstoqueEmpresa.DtReferencia == estoqueAtual.c
.MaxDtReferencia))\
   .filter(ProdutoEmpresa.StAtivoVenda == 'S')\
   .filter(ProdutoEmpresa.CdEmpresa == 4)\
   .order_by(Produto.NmProduto)

Em sex., 14 de abr. de 2023 às 15:21, Mike Bayer <
mike_not_on_goo...@zzzcomputing.com> escreveu:

> the initial issue is that you want DtReferencia from the subquery on the
> outside:
>
> session.query(..., estoqueAtual.c.DtReferencia, ...)
>
> and not "EstoqueEmpresa.DtReferencia", that's not available in the FROM
> list, it's inside a subquery.
>
> also I dont think you'd want to "group by" the same column that you are
> feeing into max().that would defeat the purpose of using an aggregate.
>
> On Fri, Apr 14, 2023, at 1:30 PM, Elias Coutinho wrote:
>
> Good afternoon.
> I am having trouble transforming a SQL Server query to SQL Alchemy.
>
> *The SQL Server query is this*
>
> SELECT CP.CdChamada, P.NmProduto, PE.VlPrecoCusto, PE.VlPrecoSugerido,
> EE.QtEstoque, EE.DtReferencia
> FROM Produto P
>  INNER JOIN Produto_Empresa PE ON  P.IdProduto = PE.IdProduto
>  INNER JOIN CodigoProduto CP ON  P.IdProduto = CP.IdProduto
>  INNER JOIN (SELECT IdProduto, CdEmpresa, MAX(DtReferencia) AS
> MaxDtReferencia
>  FROM EstoqueEmpresa
>  GROUP BY IdProduto, CdEmpresa) AS EE2 ON EE2.IdProduto =
> P.IdProduto AND EE2.CdEmpresa = PE.CdEmpresa
>  INNER JOIN EstoqueEmpresa EE ON EE.IdProduto = EE2.IdProduto AND
> EE.DtReferencia = EE2.MaxDtReferencia AND EE.CdEmpresa = EE2.CdEmpresa
> WHERE PE.StAtivoVenda = 'S' AND
>   PE.CdEmpresa = 4 AND
>   CP.IdTipoCodigoProduto = '00A002'
> ORDER BY CP.CdChamada
>
> *My code is as follows:*
> *My Model*
> class EstoqueEmpresa(Base):
> __tablename__ = 'EstoqueEmpresa'
>
> IdProduto = Column(CHAR(10, 'SQL_Latin1_General_CP850_CI_AI'),
> primary_key=True, nullable=False)
> CdEmpresa = Column(Integer, primary_key=True, nullable=False)
> DtReferencia = Column(DateTime, primary_key=True, nullable=False,
> index=True)
> VersaoRegistro = Column(TIMESTAMP, nullable=False)
> QtCompra = Column(Float(53))
> VlCompra = Column(Float(53))
> QtVenda = Column(Float(53))
> VlVenda = Column(Float(53))
> VlCustoMercadoriaVendida = Column(Float(53))
> QtEntrada = Column(Float(53))
> VlEntrada = Column(Float(53))
> QtSaida = Column(Float(53))
> VlSaida = Column(Float(53))
> VlSaidaAcerto = Column(Float(53))
> QtSaidaAcerto = Column(Float(53))
> QtEstoque = Column(Float(53))
> VlEstoque = Column(Float(53))
> VlUltimoCustoMedio = Column(Float(53))
> DtInicialAlinhamentoEstoque = Column(DateTime)
> QtCompraNaoAtualizaCustoMedio = Column(Float(53))
> VlCompraNaoAtualizaCustoMedio = Column(Float(53))
> QtEntradaNaoAtualizaCustoMedio = Column(Float(53))
> VlEntradaNaoAtualizaCustoMedio = Column(Float(53))
>
> *My code*
> from sqlalchemy import create_engine, text, Column, update, insert, select
> from sqlalchemy.orm.exc import NoResultFound
> from sqlalchemy.orm import Session
> from sqlalchemy.orm import sessionmaker
> from sqlalchemy import func, and_
>
> import unidecode
> import pandas as pd
> from datetime import datetime
> import re
> import 

[sqlalchemy] Re: Dealing with readonly column

[Lele Gaifax]

"Mike Bayer"  writes:

> On Fri, Apr 14, 2023, at 8:03 AM, Lele Gaifax wrote:
>> I now have
>>
>>   CREATE TABLE something (id SERIAL, name TEXT)
>>
>>   CREATE FUNCTION counter(something)
>>   RETURNS INTEGER AS $$
>> SELECT count(*) FROM something_else se
>> WHERE se.something_id = $1.id
>>   $$ STABLE SQL
>>
>> and thus existing queries such as
>>
>>   SELECT s.name, s.counter
>>   FROM something AS s
>>
>> work as before.
>
> how does "SELECT s.counter" work if the table does not have an actual 
> "counter" column?  

As explained here [1], "the field notation and functional notation are
equivalent", that is "SELECT a.foo FROM t AS a" and "SELECT foo(a) FROM
t AS a" means the same, when "foo" is a function accepting the "implicit
composite type corresponding to a row in the table t".

Consider:

  foo=# \d artists
Table "public.artists"
   Column |  Type   | Collation | Nullable | Default 
  +-+---+--+-
   id | integer |   | not null | 
   name   | text|   |  | 
  Indexes:
  "artists_pkey" PRIMARY KEY, btree (id)

  foo=# select a.name, name(a) from artists as a;
   name  | name  
  ---+---
   peter gabriel | peter gabriel
  (1 row)

[1] https://www.postgresql.org/docs/current/rowtypes.html#ROWTYPES-USAGE


> In general, whenever a column has a server side default of this
> nature, you want to use FetchedValue or some subclass of this in the
> column definition, so the ORM as well as Core knows that something in
> the server will be generating a value for this column:
> https://docs.sqlalchemy.org/en/20/core/defaults.html#triggered-columns
> /
> https://docs.sqlalchemy.org/en/20/orm/persistence_techniques.html#orm-server-defaults
>
>>
>>   from sqlalchemy import Computed
>>  
>>   something = Table("something", metadata,
>> Column("id", Integer, primary_key=True),
>> Column("name", Text),
>> Column("counter", Integer, Computed("counter")))
>>
>> but accordingly to the documentation [2] "SA behavior ... is currently
>> that the value [assigned to the column] will be ignored", so that does
>> not bring me any advantage.
>
> Using Computed in this way is equivalent to using FetchedValue.Why is 
> that a problem? 

No, it's not a problem, but the following

  stmt = something.update().values(counter=42).where(something.c.id=1)
  connection.execute(stmt)
  
will raise an error at execution time, as will, assuming
"instance_of_something" is an instance of the ORM class mapped to the
table "something"

  instance_of_something.counter = 42
  session.flush()

I was just imagining something that could raise an error earlier.

> true "readonly" at the Core level, where any attempt to use
> connection.execute() in such a way that data is being sent to this
> column, you'd need to use event hooks such as before_cursor_execute()
> https://docs.sqlalchemy.org/en/20/core/events.html#sqlalchemy.events.ConnectionEvents.before_cursor_execute
> and then do heuristics to figure out what the statement is, and if any
> of the parameters point to this column. I would not go this route
> since it's overkill and not that reliable for said overkill.

Right, not worth the effort indeed.

> If you want to absolutely disallow client side changes to this column,
> since you are setting up PG defaults anyway, using a trigger or a true
> COMPUTED column would be the easiest route.

Unfortunately in this case PG does not help: the closest thing is a
"GENERATED" column [2], but (up to version 15 at least) it "is a special
column that is always computed from other columns", it cannot be an
arbitrary subquery.

[2] https://www.postgresql.org/docs/15/ddl-generated-columns.html

> Without using server-side constructs, in a practical sense, simply
> omitting the column from the Table or using mapper.exclude_properties
> is in my experience sufficient.

Uhm, how could I then select that value, to be returned by an API that
queries the "something" table?

Anyway, as said, it was just to be sure I didn't miss some SA magic (it
wouldn't be the first time you've managed to surprise me, and I bet
you'll keep doing that :-). My coworkers will surely be able to cope
with an error coming back from the database at flush time!

Thanks, lele.
-- 
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
l...@metapensiero.it  | -- Fortunato Depero, 1929.

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

Re: [sqlalchemy] Using joins+max with sql server

[Mike Bayer]

the initial issue is that you want DtReferencia from the subquery on the 
outside:

session.query(..., estoqueAtual.c.DtReferencia, ...)

and not "EstoqueEmpresa.DtReferencia", that's not available in the FROM list, 
it's inside a subquery.

also I dont think you'd want to "group by" the same column that you are feeing 
into max().that would defeat the purpose of using an aggregate.

On Fri, Apr 14, 2023, at 1:30 PM, Elias Coutinho wrote:
> Good afternoon.
> I am having trouble transforming a SQL Server query to SQL Alchemy.
> 
> *The SQL Server query is this*
> **
> SELECT CP.CdChamada, P.NmProduto, PE.VlPrecoCusto, PE.VlPrecoSugerido, 
> EE.QtEstoque, EE.DtReferencia
> FROM Produto P
>  INNER JOIN Produto_Empresa PE ON  P.IdProduto = PE.IdProduto
>  INNER JOIN CodigoProduto CP ON  P.IdProduto = CP.IdProduto
>  INNER JOIN (SELECT IdProduto, CdEmpresa, MAX(DtReferencia) AS MaxDtReferencia
>  FROM EstoqueEmpresa
>  GROUP BY IdProduto, CdEmpresa) AS EE2 ON EE2.IdProduto = 
> P.IdProduto AND EE2.CdEmpresa = PE.CdEmpresa
>  INNER JOIN EstoqueEmpresa EE ON EE.IdProduto = EE2.IdProduto AND 
> EE.DtReferencia = EE2.MaxDtReferencia AND EE.CdEmpresa = EE2.CdEmpresa
> WHERE PE.StAtivoVenda = 'S' AND 
>   PE.CdEmpresa = 4 AND 
>   CP.IdTipoCodigoProduto = '00A002'
> ORDER BY CP.CdChamada**
> 
> *My code is as follows:*
> *My Model*
> class EstoqueEmpresa(Base):
> __tablename__ = 'EstoqueEmpresa'
> 
> IdProduto = Column(CHAR(10, 'SQL_Latin1_General_CP850_CI_AI'), 
> primary_key=True, nullable=False)
> CdEmpresa = Column(Integer, primary_key=True, nullable=False)
> DtReferencia = Column(DateTime, primary_key=True, nullable=False, 
> index=True)
> VersaoRegistro = Column(TIMESTAMP, nullable=False)
> QtCompra = Column(Float(53))
> VlCompra = Column(Float(53))
> QtVenda = Column(Float(53))
> VlVenda = Column(Float(53))
> VlCustoMercadoriaVendida = Column(Float(53))
> QtEntrada = Column(Float(53))
> VlEntrada = Column(Float(53))
> QtSaida = Column(Float(53))
> VlSaida = Column(Float(53))
> VlSaidaAcerto = Column(Float(53))
> QtSaidaAcerto = Column(Float(53))
> QtEstoque = Column(Float(53))
> VlEstoque = Column(Float(53))
> VlUltimoCustoMedio = Column(Float(53))
> DtInicialAlinhamentoEstoque = Column(DateTime)
> QtCompraNaoAtualizaCustoMedio = Column(Float(53))
> VlCompraNaoAtualizaCustoMedio = Column(Float(53))
> QtEntradaNaoAtualizaCustoMedio = Column(Float(53))
> VlEntradaNaoAtualizaCustoMedio = Column(Float(53))
> **
> *My code*
> from sqlalchemy import create_engine, text, Column, update, insert, select
> from sqlalchemy.orm.exc import NoResultFound
> from sqlalchemy.orm import Session
> from sqlalchemy.orm import sessionmaker
> from sqlalchemy import func, and_
> 
> import unidecode
> import pandas as pd
> from datetime import datetime
> import re
> import itertools
> 
> from itertools import islice
> 
> # Importo somente as tabelas que vou usar
> from models import CodigoProduto, ProdutoEmpresa, EstoqueEmpresa, Produto
> 
> #-
> # Configuração da conexão com o banco de dados
> USER = 'sa'
> PASSWORD = 'Abc*123'
> HOST = 'SERVER-02\MSSQLSERVERB'
> DATABASE = 'ALTERDATA_TESTE'
> 
> engine = 
> create_engine(f'mssql+pyodbc://{USER}:{PASSWORD}@{HOST}/{DATABASE}?driver=ODBC+Driver+17+for+SQL+Server')
> 
> # Create a Session object
> Session = sessionmaker(bind=engine)
> session = Session()
> 
> 
> #-
> # Subquery para buscar o maior registro de estoqueempresa para cada produto
> estoqueAtual = session.query(
> EstoqueEmpresa.IdProduto,
> EstoqueEmpresa.QtEstoque,
> func.max(EstoqueEmpresa.DtReferencia).label('MaxDtReferencia')
> ).group_by(EstoqueEmpresa.IdProduto, EstoqueEmpresa.QtEstoque, 
> EstoqueEmpresa.DtReferencia).subquery()
> 
> #print(estoqueAtual)
> 
> # Realiza a consulta com SQLAlchemy
> ##query = session.query(CodigoProduto.CdChamada, Produto.IdProduto, 
> Produto.NmProduto, ProdutoEmpresa.VlPrecoCusto, 
> ProdutoEmpresa.VlPrecoSugerido, estoqueAtual.c.QtEstoque)\
> ##   .join(ProdutoEmpresa, Produto.IdProduto == 
> ProdutoEmpresa.IdProduto)\
> ##   .join(CodigoProduto, Produto.IdProduto == 
> CodigoProduto.IdProduto)\
> ##   .join(estoqueAtual, and_(Produto.IdProduto == 
> estoqueAtual.c.IdProduto))\
> ##   .join(EstoqueEmpresa, and_(EstoqueEmpresa.IdProduto == 
> Produto.IdProduto))\
> ##   .filter(ProdutoEmpresa.StAtivoVenda == 'S')\
> ##   .filter(ProdutoEmpresa.CdEmpresa == 4)\
> ##   .order_by(Produto.NmProduto)
> 
> query = session.query(CodigoProduto.CdChamada, Produto.IdProduto, 
> Produto.NmProduto, 

[sqlalchemy] Using joins+max with sql server

[Elias Coutinho]

Good afternoon.
I am having trouble transforming a SQL Server query to SQL Alchemy.

*The SQL Server query is this*

SELECT CP.CdChamada, P.NmProduto, PE.VlPrecoCusto, PE.VlPrecoSugerido, 
EE.QtEstoque, EE.DtReferencia
FROM Produto P
 INNER JOIN Produto_Empresa PE ON  P.IdProduto = PE.IdProduto
 INNER JOIN CodigoProduto CP ON  P.IdProduto = CP.IdProduto
 INNER JOIN (SELECT IdProduto, CdEmpresa, MAX(DtReferencia) AS 
MaxDtReferencia
 FROM EstoqueEmpresa
 GROUP BY IdProduto, CdEmpresa) AS EE2 ON EE2.IdProduto = 
P.IdProduto AND EE2.CdEmpresa = PE.CdEmpresa
 INNER JOIN EstoqueEmpresa EE ON EE.IdProduto = EE2.IdProduto AND 
EE.DtReferencia = EE2.MaxDtReferencia AND EE.CdEmpresa = EE2.CdEmpresa
WHERE PE.StAtivoVenda = 'S' AND 
  PE.CdEmpresa = 4 AND 
  CP.IdTipoCodigoProduto = '00A002'
ORDER BY CP.CdChamada

*My code is as follows:*
*My Model*
class EstoqueEmpresa(Base):
__tablename__ = 'EstoqueEmpresa'

IdProduto = Column(CHAR(10, 'SQL_Latin1_General_CP850_CI_AI'), 
primary_key=True, nullable=False)
CdEmpresa = Column(Integer, primary_key=True, nullable=False)
DtReferencia = Column(DateTime, primary_key=True, nullable=False, index=
True)
VersaoRegistro = Column(TIMESTAMP, nullable=False)
QtCompra = Column(Float(53))
VlCompra = Column(Float(53))
QtVenda = Column(Float(53))
VlVenda = Column(Float(53))
VlCustoMercadoriaVendida = Column(Float(53))
QtEntrada = Column(Float(53))
VlEntrada = Column(Float(53))
QtSaida = Column(Float(53))
VlSaida = Column(Float(53))
VlSaidaAcerto = Column(Float(53))
QtSaidaAcerto = Column(Float(53))
QtEstoque = Column(Float(53))
VlEstoque = Column(Float(53))
VlUltimoCustoMedio = Column(Float(53))
DtInicialAlinhamentoEstoque = Column(DateTime)
QtCompraNaoAtualizaCustoMedio = Column(Float(53))
VlCompraNaoAtualizaCustoMedio = Column(Float(53))
QtEntradaNaoAtualizaCustoMedio = Column(Float(53))
VlEntradaNaoAtualizaCustoMedio = Column(Float(53))

*My code*
from sqlalchemy import create_engine, text, Column, update, insert, select
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.orm import Session
from sqlalchemy.orm import sessionmaker
from sqlalchemy import func, and_

import unidecode
import pandas as pd
from datetime import datetime
import re
import itertools

from itertools import islice

# Importo somente as tabelas que vou usar
from models import CodigoProduto, ProdutoEmpresa, EstoqueEmpresa, Produto

#-
# Configuração da conexão com o banco de dados
USER = 'sa'
PASSWORD = 'Abc*123'
HOST = 'SERVER-02\MSSQLSERVERB'
DATABASE = 'ALTERDATA_TESTE'

engine = create_engine(f'mssql+pyodbc://{USER}:{PASSWORD}@{HOST}/{DATABASE}
?driver=ODBC+Driver+17+for+SQL+Server')

# Create a Session object
Session = sessionmaker(bind=engine)
session = Session()


#-
# Subquery para buscar o maior registro de estoqueempresa para cada produto
estoqueAtual = session.query(
EstoqueEmpresa.IdProduto,
EstoqueEmpresa.QtEstoque,
func.max(EstoqueEmpresa.DtReferencia).label('MaxDtReferencia')
).group_by(EstoqueEmpresa.IdProduto, EstoqueEmpresa.QtEstoque, 
EstoqueEmpresa.DtReferencia).subquery()

#print(estoqueAtual)

# Realiza a consulta com SQLAlchemy
##query = session.query(CodigoProduto.CdChamada, Produto.IdProduto, 
Produto.NmProduto, ProdutoEmpresa.VlPrecoCusto, 
ProdutoEmpresa.VlPrecoSugerido, estoqueAtual.c.QtEstoque)\
##   .join(ProdutoEmpresa, Produto.IdProduto == 
ProdutoEmpresa.IdProduto)\
##   .join(CodigoProduto, Produto.IdProduto == 
CodigoProduto.IdProduto)\
##   .join(estoqueAtual, and_(Produto.IdProduto == 
estoqueAtual.c.IdProduto))\
##   .join(EstoqueEmpresa, and_(EstoqueEmpresa.IdProduto == 
Produto.IdProduto))\
##   .filter(ProdutoEmpresa.StAtivoVenda == 'S')\
##   .filter(ProdutoEmpresa.CdEmpresa == 4)\
##   .order_by(Produto.NmProduto)

query = session.query(CodigoProduto.CdChamada, Produto.IdProduto, Produto.
NmProduto, ProdutoEmpresa.VlPrecoCusto, ProdutoEmpresa.VlPrecoSugerido, 
EstoqueEmpresa.DtReferencia, estoqueAtual.c.QtEstoque)\
   .join(ProdutoEmpresa, Produto.IdProduto == ProdutoEmpresa.
IdProduto)\
   .join(CodigoProduto, Produto.IdProduto == CodigoProduto.
IdProduto)\
   .join(estoqueAtual, and_(Produto.IdProduto == 
estoqueAtual.c.IdProduto, 
EstoqueEmpresa.DtReferencia == estoqueAtual.c.MaxDtReferencia))\
   .join(EstoqueEmpresa, and_(EstoqueEmpresa.IdProduto == 
Produto.IdProduto, EstoqueEmpresa.DtReferencia == estoqueAtual.c
.MaxDtReferencia))\
   .filter(ProdutoEmpresa.StAtivoVenda == 'S')\
   

Re: [sqlalchemy] Dealing with readonly column

[Mike Bayer]

On Fri, Apr 14, 2023, at 8:03 AM, Lele Gaifax wrote:
> Hi,
>
> I wonder if there is a way to declare a particular column of a table as
> "readonly", either for the purpose of documenting the model, or to get
> early error should someone try to update it.

"readonly" can mean a few different things

>
> Implementing a new "feature", where I have to rewrite the logic that
> keeps up-to-date a "counter" column, I thought of replacing that column
> with a "computed value" (a.k.a. "virtual column"), using a PostgreSQL
> SQL function that compute that counter on-the-fly [1]: this allowed me
> to avoid touching dozens of places where that column is read in the PHP
> code (what a relief!).
>
> To illustrate, where I had a table such as
>
>   CREATE TABLE something (id SERIAL, name TEXT, counter INTEGER)
>
> I now have
>
>   CREATE TABLE something (id SERIAL, name TEXT)
>
>   CREATE FUNCTION counter(something)
>   RETURNS INTEGER AS $$
> SELECT count(*) FROM something_else se
> WHERE se.something_id = $1.id
>   $$ STABLE SQL
>
> and thus existing queries such as
>
>   SELECT s.name, s.counter
>   FROM something AS s
>
> work as before.

how does "SELECT s.counter" work if the table does not have an actual "counter" 
column?  


>
> For now, I just left the SA Table definition (I'm using classic mappings)
> untouched, that is something like
>
>   from sqlalchemy import Column, Integer, MetaData, Table, Text
>
>   metadata = MetaData()
>
>   something = Table("something", metadata,
> Column("id", Integer, primary_key=True),
> Column("name", Text),
> Column("counter", Integer))
>
> but as said, I looked around to see if there is a way to inform SA that
> that "counter" column cannot be updated, just read. I tried to use the
> Computed() thing like

so here's where "readonly" means different things.  if you want to omit 
"counter" from your application entirely, you can remove it from the Table() 
definition.

Also with some irony I am actually working on a whole different feature right 
now where there's a new parameter, which I'm not going to make public yet, 
called "_omit_from_statements" for an individual column.From an ORM mapping 
perspective, it's essentially the same as if you added this column to the 
Mapper exclude_properties list: 
https://docs.sqlalchemy.org/en/20/orm/mapping_api.html#sqlalchemy.orm.Mapper.params.exclude_properties
 which will also make that column not present within the mapping or any of the 
statements used.

neither of these two things make the column "read only", they just make the 
column not really present in the application.

In general, whenever a column has a server side default of this nature, you 
want to use FetchedValue or some subclass of this in the column definition, so 
the ORM as well as Core knows that something in the server will be generating a 
value for this column: 
https://docs.sqlalchemy.org/en/20/core/defaults.html#triggered-columns / 
https://docs.sqlalchemy.org/en/20/orm/persistence_techniques.html#orm-server-defaults
 

>
>   from sqlalchemy import Computed
>  
>   something = Table("something", metadata,
> Column("id", Integer, primary_key=True),
> Column("name", Text),
> Column("counter", Integer, Computed("counter")))
>
> but accordingly to the documentation [2] "SA behavior ... is currently
> that the value [assigned to the column] will be ignored", so that does
> not bring me any advantage.

Using Computed in this way is equivalent to using FetchedValue.Why is that 
a problem? 


So far note all of these techniques have to do with "read only", but they are 
the usual approaches taken when there's a server generated column that you'd 
prefer the application not deal with explicitly.

>
> On the ORM side, I could "hide" the concrete column and expose it thru a
> readonly property, but I'd like to have a similar safety-belt also at
> the Core layer, because many services/APIs will be written at that
> level.
>
> Do you have any suggestion?

true "readonly" at the ORM level, meaning the column is present, it's mapped, 
and you want to disallow user code from saying myclass.col = "foo" or some 
error is raised, you can only do with attribute events or with a validator: 
https://docs.sqlalchemy.org/en/20/orm/mapped_attributes.html#sqlalchemy.orm.validates
 or other Python-side object technique.

true "readonly" at the Core level, where any attempt to use 
connection.execute() in such a way that data is being sent to this column, 
you'd need to use event hooks such as before_cursor_execute() 
https://docs.sqlalchemy.org/en/20/core/events.html#sqlalchemy.events.ConnectionEvents.before_cursor_execute
 and then do heuristics to figure out what the statement is, and if any of the 
parameters point to this column.I would not go this route since it's 
overkill and not that reliable for said overkill.

If you want to 

[sqlalchemy] Dealing with readonly column

[Lele Gaifax]

Hi,

I wonder if there is a way to declare a particular column of a table as
"readonly", either for the purpose of documenting the model, or to get
early error should someone try to update it.

Some context: I have to maintain an old application, based on
PostgreSQL, with several surrounding tools (mainly a PHP (bleach :-!)
frontend and set of "services" built with Python+SQLObject). At the same
time, I'm slowly developing a "future" replacement, rewriting the whole
stack, starting from the DB layer using SQLAlchemy.

Implementing a new "feature", where I have to rewrite the logic that
keeps up-to-date a "counter" column, I thought of replacing that column
with a "computed value" (a.k.a. "virtual column"), using a PostgreSQL
SQL function that compute that counter on-the-fly [1]: this allowed me
to avoid touching dozens of places where that column is read in the PHP
code (what a relief!).

To illustrate, where I had a table such as

  CREATE TABLE something (id SERIAL, name TEXT, counter INTEGER)

I now have

  CREATE TABLE something (id SERIAL, name TEXT)

  CREATE FUNCTION counter(something)
  RETURNS INTEGER AS $$
SELECT count(*) FROM something_else se
WHERE se.something_id = $1.id
  $$ STABLE SQL

and thus existing queries such as

  SELECT s.name, s.counter
  FROM something AS s

work as before.

For now, I just left the SA Table definition (I'm using classic mappings)
untouched, that is something like

  from sqlalchemy import Column, Integer, MetaData, Table, Text

  metadata = MetaData()

  something = Table("something", metadata,
Column("id", Integer, primary_key=True),
Column("name", Text),
Column("counter", Integer))

but as said, I looked around to see if there is a way to inform SA that
that "counter" column cannot be updated, just read. I tried to use the
Computed() thing like

  from sqlalchemy import Computed
  
  something = Table("something", metadata,
Column("id", Integer, primary_key=True),
Column("name", Text),
Column("counter", Integer, Computed("counter")))

but accordingly to the documentation [2] "SA behavior ... is currently
that the value [assigned to the column] will be ignored", so that does
not bring me any advantage.

On the ORM side, I could "hide" the concrete column and expose it thru a
readonly property, but I'd like to have a similar safety-belt also at
the Core layer, because many services/APIs will be written at that
level.

Do you have any suggestion?

Thanks in advance,
bye, lele.

[1] 
https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-COMPOSITE-FUNCTIONS
[2] https://docs.sqlalchemy.org/en/20/core/defaults.html#computed-ddl
-- 
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
l...@metapensiero.it  | -- Fortunato Depero, 1929.

-- 
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/87y1mullgf.fsf%40metapensiero.it.