date:20230412

Re: [sqlalchemy] Query object modification

2023-04-12 Thread Mike Bayer



On Wed, Apr 12, 2023, at 5:21 PM, Luis Del Rio IV wrote:
> I am currently using the following sqlalchemy code,
> 
> _query = super().get_query(model, info, sort, **args)
> query = _query.group_by(
> func.date_format(DataModel.timestamp, "%Y-%m-%d %H")
> )
> return query
> 
> I am trying to aggregate the the max value of a field from DataModel.value 
> utilizing the group by clause.
> 
> In simple sql, you would do the following.
> 
> SELECT  max(data.value)  AS data_value
> from data
> GROUP BY date_format(data.timestamp, "%Y-%m-%d %H")
> 
> What would the proper way to express this? The various methods I have tried 
> somehow overwrite the original query and do not map to our attributes.

using legacy query style:

q1 = session.query(func.max(Data.value)).group_by(
func.date_format(Data.timestamp, "%Y-%m-%d %H")
)


using 2.0 style select(), replace "session.query" with "select":

s1 = select(func.max(Data.value)).group_by(
func.date_format(Data.timestamp, "%Y-%m-%d %H")
)

POC script is at the bottom of this email.

> 
> 
> I have some additional context on the issue
>  1. We are using the graphene-sqlalchemy package.
>  2. When you do a GraphQL web api query, the package creates a 
> sqlalchemy.orm.query.Query object.
>  3. We want to modify this standard query that the package creates so that we 
> can do the group_by action to help us get the maximum time series value for 
> every hour because this is not possible using GraphQL.
>  4. Talking to the graphene-sqlalchemy team they told us the best place to do 
> the modification to the standardized query is in the get_query method (line 
> 67) in this file: 
> https://github.com/graphql-python/graphene-sqlalchemy/blob/master/graphene_sqlalchemy/fields.py
>  5. The standardized query we need to modify translates to this SQL 
> statement: 
> SELECT sy_data.oid_id, sy_data.rrd_timestamp, sy_data.cabinet_id, sy_data
> .customer_id, sy_data.value, sy_data.active_flag, sy_data.timestamp 
> FROM sy_data 
> WHERE sy_data.rrd_timestamp >= %(rrd_timestamp_1)s AND sy_data.rrd_timestamp 
> <= %(rrd_timestamp_2)s AND (sy_data.oid_id = %(oid_id_1)s OR sy_data.oid_id = 
> %(o
> id_id_2)s) ORDER BY sy_data.oid_id ASC, sy_data.rrd_timestamp ASC
> 
> Therefore we need to find a way to insert a func.max for the values in the 
> first part of the SELECT statement, (before the FROM). It is easy for us to 
> apend the group_by like this.

if you have a Query which renders the above SQL, you can add group_by() to it 
in place.   but if these queries are being translated into GraphQL, and GraphQL 
does not have any concept of GROUP BY, then it wont work, what you want to do 
would not be possible unless a GraphQL query exists that does what you need.

> 
> query.group_by(
> func.date_format(DataModel.timestamp, "%Y-%m-%d %H")
> )
> 
> The big issue for us is to figure out how to insert the func.max

see func at 
https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.func


from sqlalchemy import Column
from sqlalchemy import DateTime
from sqlalchemy import func
from sqlalchemy import Integer
from sqlalchemy import select
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import Session

Base = declarative_base()


class Data(Base):
__tablename__ = "data"

id = Column(Integer, primary_key=True)
value = Column(Integer)
timestamp = Column(DateTime)


s = Session()

q1 = s.query(func.max(Data.value)).group_by(
func.date_format(Data.timestamp, "%Y-%m-%d %H")
)

print(q1)

s1 = select(func.max(Data.value)).group_by(
func.date_format(Data.timestamp, "%Y-%m-%d %H")
)

print(s1)

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/328c16af-189a-4050-a67f-06c78a9dec99%40app.fastmail.com.

[sqlalchemy] Query object modification

2023-04-12 Thread Luis Del Rio IV

 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.

I have some additional context on the issue

   1. We are using the graphene-sqlalchemy package.
   2. When you do a GraphQL web api query, the package creates a 
sqlalchemy.orm.query.Query 
   object.
   3. We want to modify this standard query that the package creates so 
   that we can do the group_by action to help us get the maximum time series 
   value for every hour because this is not possible using GraphQL.
   4. Talking to the graphene-sqlalchemy team they told us the best place 
   to do the modification to the standardized query is in the get_query method 
   (line 67) in this file: https://github.com/graphql-
   python/graphene-sqlalchemy/blob/master/graphene_sqlalchemy/fields.py 
   

   5. The standardized query we need to modify translates to this SQL 
   statement: 

SELECT sy_data.oid_id, sy_data.rrd_timestamp, sy_data.cabinet_id, sy_data
.customer_id, sy_data.value, sy_data.active_flag, sy_data.timestamp 
FROM sy_data 
WHERE sy_data.rrd_timestamp >= %(rrd_timestamp_1)s AND 
sy_data.rrd_timestamp <= %(rrd_timestamp_2)s AND (sy_data.oid_id = 
%(oid_id_1)s OR sy_data.oid_id = %(o
id_id_2)s) ORDER BY sy_data.oid_id ASC, sy_data.rrd_timestamp ASC

Therefore we need to find a way to insert a func.max for the values in the 
first part of the SELECT statement, (before the FROM). It is easy for us to 
apend the group_by like this.

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.

-- 
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/742e96bc-f6cc-43ca-8a2e-697b3803301cn%40googlegroups.com.

[sqlalchemy] Re: trying to create a custome dialect did not find proper step guide

2023-04-12 Thread Jonathan Vanasco

There are no guides, because this is a very advanced topic.

If you are unable to code this based on the examples I shared above, you 
will have to find someone who can.




On Friday, April 7, 2023 at 11:41:30 PM UTC-4 sayakchak...@gmail.com wrote:

> Thank you sir for your response ,can you please guide me about how to 
> create a customs dialect because in sqlalchemy i find a topic on 3rd party 
> dialect but i dont fine and proper guide for it if you can please can you 
> provide me guide.
>
>
> On Friday, April 7, 2023 at 11:12:14 PM UTC+5:30 Jonathan Vanasco wrote:
>
>> Most custom dialects are written by forking an existing dialect.
>>
>> Unfortunately, a dialect can not accomplish what you want to do.
>>
>> SqlAlchemy Dialects are used to generate SQL, which is then passed to the 
>> database via a DBAPI driver.
>>
>> For example, when someone uses PostgreSQL with SQLAlchemy, they use the 
>> SQLAlchemy postgresql dialect AND their choice of a DBAPI driver (which 
>> could be any one of psycopg2, psycopg 3, pg8000, asyncpg, psycopg2cffi, etc)
>>
>> There are a few independent projects that developed REST solutions by 
>> writing both a dialect AND a DBAPI driver into a single package.
>>
>> Examples include:
>>  https://github.com/laughingman7743/PyAthena/
>>  https://github.com/betodealmeida/gsheets-db-api
>>  https://github.com/aadel/sqlalchemy-solr
>>
>> You will need to implement a solution similar to one of the above 
>> projects.
>>
>> If you look at the table of known dialects - 
>> https://docs.sqlalchemy.org/en/20/dialects/index.html - you may find 
>> other projects that implemented dialect+dbapi drivers in a single package.
>>
>> On Friday, April 7, 2023 at 1:03:19 AM UTC-4 sayakchak...@gmail.com 
>> wrote:
>>
>>> create a custom SQLAlchemy  dialect, called PCRRESTapiDialect for SQL 
>>> Alchemy that can call a rest api and return result as a table.
>>> i am trying to achive this but i dont find  any solution
>>
>>

-- 
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/07f14651-4a69-44d3-a50a-3ed4185fc170n%40googlegroups.com.

Re: [sqlalchemy] chinook with sqlalchemy core

2023-04-12 Thread Soumaya Mauthoor

Or any sample database would e.g. northwind
https://docs.devexpress.com/XtraReports/403995/product-information/sample-northwind-database

Le mer. 12 avr. 2023 à 20:40, sumau  a écrit :

> Hello
>
> Is there a sqlalchemy core version of the chinook database
> https://github.com/lerocha/chinook-database? A python script which
> creates the different tables and populates them with the same data?
>
> Regards
> Soumaya
>
> --
> 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/8f85498c-da47-431e-90c7-d3af518cb85cn%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/CAN14jWSRQcTNx5ArQTgKzV3QX2kBpYhwS4qkG-QqTPYErqLQvA%40mail.gmail.com.

[sqlalchemy] chinook with sqlalchemy core

2023-04-12 Thread sumau

Hello

Is there a sqlalchemy core version of the chinook 
database https://github.com/lerocha/chinook-database? A python script which 
creates the different tables and populates them with the same data?

Regards
Soumaya

-- 
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/8f85498c-da47-431e-90c7-d3af518cb85cn%40googlegroups.com.

Re: [sqlalchemy] Query object modification

[sqlalchemy] Query object modification

[sqlalchemy] Re: trying to create a custome dialect did not find proper step guide

Re: [sqlalchemy] chinook with sqlalchemy core

[sqlalchemy] chinook with sqlalchemy core

5 matches

Site Navigation

Mail list logo

Footer information