Re: [sqlalchemy] ".contains" query with VARBINARY Column
as replied on the issue I'd rather improve the behavior.
On Wed, Jul 13, 2022, at 12:29 PM, Peter Harrison wrote:
> 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
>>>
>>> .
>>
>>
>>
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>>
>> http://www.sqlalchemy.org/
>>
>> To
Re: [sqlalchemy] ".contains" query with VARBINARY Column
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
>
> .
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
>
Re: [sqlalchemy] ".contains" query with VARBINARY Column
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
>
> .
--
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/5ad5b40e-9831-4868--7fe52e000c6f%40www.fastmail.com.
[sqlalchemy] ".contains" query with VARBINARY Column
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.
