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

[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] Query object modification

[Peter Harrison]

Thanks Mike,

Ideally we'd prefer to find a solution via Graphene-SQLAlchemy. 
Unfortunately we don't have the luxury of creating our own query when 
interacting with Graphene-SQLAlchemy.

So the key question for us is, can you modify an existing 
sqlalchemy.orm.query.Query object to insert a "func.max(Data.value)" object 
into the select? This is what Graphene-SQLAlchemy is giving us.
If this is possible, then the group_by part is easy. We have tested that 
frequently, the hard part is the modifying the original "select" object.

1. We have tried add_columns, but that adds in incompatible object type in 
the GraphQL results making it an unusable option.
2. We thought that modifying the select would be  possible using data with 
"statement.froms" but can't figure out how to update the MetaData object in 
it

If modifying the "select" after its creation is not possible, we need to 
start considering using a separate reporting table with hourly data.

On Wednesday, April 12, 2023 at 3:12:46 PM UTC-7 Mike Bayer wrote:

>
>
> On Wed, Apr 12, 2023, at 5:21 PM, Luis Del Rio IV wrote:
>
> I am currently using the following sqlalchemy code,
>
> _query = super().get_query(model, info, sort, **args)
> query = _query.group_by(
> func.date_format(DataModel.timestamp, "%Y-%m-%d %H")
> )
> return query
>
> I am trying to aggregate the the max value of a field from DataModel.value 
> utilizing the group by clause.
>
> In simple sql, you would do the following.
>
> SELECT  max(data.value)  AS data_value
> from data
> GROUP BY date_format(data.timestamp, "%Y-%m-%d %H")
>
> What would the proper way to express this? The various methods I have 
> tried somehow overwrite the original query and do not map to our attributes.
>
>
> using legacy query style:
>
> q1 = session.query(func.max(Data.value)).group_by(
> func.date_format(Data.timestamp, "%Y-%m-%d %H")
> )
>
>
> using 2.0 style select(), replace "session.query" with "select":
>
> s1 = select(func.max(Data.value)).group_by(
> func.date_format(Data.timestamp, "%Y-%m-%d %H")
> )
>
> POC script is at the bottom of this email.
>
>
>
> I have some additional context on the issue
>
>1. We are using the graphene-sqlalchemy package.
>2. When you do a GraphQL web api query, the package creates a 
> sqlalchemy.orm.query.Query 
>object.
>3. We want to modify this standard query that the package creates so 
>that we can do the group_by action to help us get the maximum time series 
>value for every hour because this is not possible using GraphQL.
>4. Talking to the graphene-sqlalchemy team they told us the best place 
>to do the modification to the standardized query is in the get_query 
> method 
>(line 67) in this file: https://github.com/graphql-
>python/graphene-sqlalchemy/blob/master/graphene_sqlalchemy/fields.py 
>
> 
>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 

Re: [sqlalchemy] ".contains" query with VARBINARY Column

[Peter Harrison]

Thanks Mike,

The examples on the documentation page only show how to work with strings.
Could this be updated?
https://docs.sqlalchemy.org/en/14/core/sqlelement.html

Specifically:

   1. ColumnOperators.endswith()
   2. ColumnOperators.contains()
   3. ColumnOperators.like()
   4. ColumnOperators.startswith()

I created this issue with suggested text
https://github.com/sqlalchemy/sqlalchemy/issues/8253

Peter


On Wed, Jul 13, 2022 at 6:09 AM Mike Bayer  wrote:

> you're sending a Python bytestring as the expression for which there's no
> explicit support for operators like concat, contains, etc.
>
> the solution is to build the SQL composition directly using func.concat ,
> or just building up the LIKE expression in Python, so that there's no
> ambiguity what's being asked for.
>
> hostname = 'CHJWNNEK'
> statement = select(MacIp.hostname).where(
> MacIp.hostname.like(func.concat(func.concat('%', hostname.encode()),
> '%'))
> )
>
>
>
> On Wed, Jul 13, 2022, at 2:29 AM, Peter Harrison wrote:
>
> Hello,
>
> I'm having an issue with a "contains" query on a VARBINARY column. It
> appears the statement compiles incorrectly, or I am not using SQLAlchemy
> correctly.
>
> I know the MySQL CLI query that works correctly as you will see below, but
> I don't know how to get it. The CLI query is only one character different
> from the one that SQLAlchemy creates.
>
> I've spent a few days googling this with no luck.
>
> Any assistance with my syntax would be greatly appreciated.
>
> *PIP packages*
>
> *PyMySQL*==1.0.2
> SQL*Alchemy*==1.4.39
>
> *Code Snippet*
>
> hostname = 'CHJWNNEK'
> statement = select(MacIp.hostname).where(
> MacIp.hostname.contains(hostname.encode()
> )
>
> *Issue*
>
> The SQLAlchemy example compiles to this when adding this argument to the
> compile function *"compile_kwargs={'literal_binds': True}"*:
>
> SELECT smap_macip.hostname
> FROM smap_macip
> WHERE (smap_macip.hostname LIKE concat('%%' + 'CHJWNNEK', '%%'))
>
> This gives no results, however it works when I do the query from the CLI
> like this. ('+' replaced with ',')
>
> SELECT smap_macip.hostname
> FROM smap_macip
> WHERE (smap_macip.hostname LIKE concat(*'%%', 'CHJWNNEK', '%%'*))
>
>
> *Column Contents*
>
> select hostname from smap_macip;
> ++
> | hostname   |
> ++
> | TVUPQBAZJX |
> | *CHJWNNEKYE* |
> | LODFHBAWVT |
> | QMQRDNJJPV |
> | ICHGULIMUU |
> | AMXHISKNVT |
> ++
>
> *Table Definition*
>
> class MacIp(BASE):
> """Database table definition."""
>
> __tablename__ = 'smap_macip'
> __table_args__ = (
> UniqueConstraint('idx_device', 'ip_', 'idx_mac'),
> {'mysql_engine': 'InnoDB'}
> )
>
> idx_macip = Column(
> BIGINT(20, unsigned=True), primary_key=True, unique=True)
> idx_device = Column(
> ForeignKey('smap_device.idx_device'),
> nullable=False, index=True, default=1, server_default=text('1'))
> idx_mac = Column(
> ForeignKey('smap_mac.idx_mac'),
> nullable=False, index=True, default=1, server_default=text('1'))
> ip_ = Column(VARBINARY(256), nullable=True, default=Null)
> hostname = Column(VARBINARY(256), nullable=True, default=Null)
> type = Column(BIGINT(unsigned=True), nullable=True, default=Null)
> enabled = Column(BIT(1), default=1)
> ts_modified = Column(
> DateTime, nullable=False,
> default=datetime.datetime.utcnow, onupdate=datetime.datetime.now)
> ts_created = Column(
> DateTime, nullable=False, default=datetime.datetime.utcnow)
>
>
>
> Peter
>
>
> --
> 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/CAN61F1wyz99HCon%2BaQkgoXv%2B3YMwV97XWo6SL1oYGb2KhNc%2Bcg%40mail.gmail.com
> <https://groups.google.com/d/msgid/sqlalchemy/CAN61F1wyz99HCon%2BaQkgoXv%2B3YMwV97XWo6SL1oYGb2KhNc%2Bcg%40mail.gmail.com?utm_medium=email_source=footer>
> .
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To pos

[sqlalchemy] ".contains" query with VARBINARY Column

[Peter Harrison]

Hello,

I'm having an issue with a "contains" query on a VARBINARY column. It
appears the statement compiles incorrectly, or I am not using SQLAlchemy
correctly.

I know the MySQL CLI query that works correctly as you will see below, but
I don't know how to get it. The CLI query is only one character different
from the one that SQLAlchemy creates.

I've spent a few days googling this with no luck.

Any assistance with my syntax would be greatly appreciated.

*PIP packages*

PyMySQL==1.0.2
SQLAlchemy==1.4.39

*Code Snippet*

hostname = 'CHJWNNEK'
statement = select(MacIp.hostname).where(
MacIp.hostname.contains(hostname.encode()
)

*Issue*

The SQLAlchemy example compiles to this when adding this argument to the
compile function *"compile_kwargs={'literal_binds': True}"*:

SELECT smap_macip.hostname
FROM smap_macip
WHERE (smap_macip.hostname LIKE concat('%%' + 'CHJWNNEK', '%%'))

This gives no results, however it works when I do the query from the CLI
like this. ('+' replaced with ',')

SELECT smap_macip.hostname
FROM smap_macip
WHERE (smap_macip.hostname LIKE concat(*'%%', 'CHJWNNEK', '%%'*))

*Column Contents*

select hostname from smap_macip;
++
| hostname   |
++
| TVUPQBAZJX |
| *CHJWNNEKYE* |
| LODFHBAWVT |
| QMQRDNJJPV |
| ICHGULIMUU |
| AMXHISKNVT |
++

*Table Definition*

class MacIp(BASE):
"""Database table definition."""

__tablename__ = 'smap_macip'
__table_args__ = (
UniqueConstraint('idx_device', 'ip_', 'idx_mac'),
{'mysql_engine': 'InnoDB'}
)

idx_macip = Column(
BIGINT(20, unsigned=True), primary_key=True, unique=True)
idx_device = Column(
ForeignKey('smap_device.idx_device'),
nullable=False, index=True, default=1, server_default=text('1'))
idx_mac = Column(
ForeignKey('smap_mac.idx_mac'),
nullable=False, index=True, default=1, server_default=text('1'))
ip_ = Column(VARBINARY(256), nullable=True, default=Null)
hostname = Column(VARBINARY(256), nullable=True, default=Null)
type = Column(BIGINT(unsigned=True), nullable=True, default=Null)
enabled = Column(BIT(1), default=1)
ts_modified = Column(
DateTime, nullable=False,
default=datetime.datetime.utcnow, onupdate=datetime.datetime.now)
ts_created = Column(
DateTime, nullable=False, default=datetime.datetime.utcnow)



Peter

-- 
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/CAN61F1wyz99HCon%2BaQkgoXv%2B3YMwV97XWo6SL1oYGb2KhNc%2Bcg%40mail.gmail.com.

Re: [sqlalchemy] Integrating graphene-sqlalchemy-filter into graphene-sqlalchemy

[Peter Harrison]

Thanks Mike,

I contacted one of the maintainers of graphene-sqlalchemy and he suggested
this list as a source of interested parties who use the package with
sqlalchemy.

That repo has become less active in recent months, and I'm sure there are
users out there like me who want it rejuvenated.

sqlalchemy and graphene-sqlalchemy have been life savers for me, and I'd
like to contribute financially to the combined health of both.

If you are a user of the graphene-sqlalchemy-filter package and are
interested, please contact me off list.

Thanks for your understanding.

Peter


On Wed, Mar 30, 2022 at 12:04 PM Mike Bayer 
wrote:

> we dont maintain graphene-sqlalchemy-filter on this list, you would need
> to reach out to the developers of that library.
>
> On Wed, Mar 30, 2022, at 2:30 PM, Peter Harrison wrote:
>
> Hello everyone,
>
> My organization uses both graphene-sqlalchemy and
> graphene-sqlalchemy-filter for some subsystems. Unfortunately development
> in graphene-sqlalchemy-filter has stalled and does not support the latest
> version of graphene which supports nifty batching.
>
> The graphene-sqlalchemy-filter feature is very useful.
>
> We are  interested in partially sponsoring work on graphene-sqlalchemy to
> integrate filters like graphene-sqlalchemy-filter. Identical filter syntax
> would be ideal for seamless integration.
>
> Please let me know the best way to proceed, both on and off list, as
> appropriate.
>
>
> --
> 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/8a2fc6b8-b7e3-443b-a49f-53e50c749307n%40googlegroups.com
> <https://groups.google.com/d/msgid/sqlalchemy/8a2fc6b8-b7e3-443b-a49f-53e50c749307n%40googlegroups.com?utm_medium=email_source=footer>
> .
>
>
> --
> 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/04a90bda-4461-471b-bf91-6cf8d4e92bba%40www.fastmail.com
> <https://groups.google.com/d/msgid/sqlalchemy/04a90bda-4461-471b-bf91-6cf8d4e92bba%40www.fastmail.com?utm_medium=email_source=footer>
> .
>

-- 
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/CAN61F1yHMpHozNSjs6AH%3DPecUAmGR9z%2B1OUHRtVfj3E0gr8LSw%40mail.gmail.com.

[sqlalchemy] Integrating graphene-sqlalchemy-filter into graphene-sqlalchemy

[Peter Harrison]

Hello everyone,

My organization uses both graphene-sqlalchemy and 
graphene-sqlalchemy-filter for some subsystems. Unfortunately development 
in graphene-sqlalchemy-filter has stalled and does not support the latest 
version of graphene which supports nifty batching.

The graphene-sqlalchemy-filter feature is very useful.

We are  interested in partially sponsoring work on graphene-sqlalchemy to 
integrate filters like graphene-sqlalchemy-filter. Identical filter syntax 
would be ideal for seamless integration.

Please let me know the best way to proceed, both on and off list, as 
appropriate.

-- 
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/8a2fc6b8-b7e3-443b-a49f-53e50c749307n%40googlegroups.com.