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

[Mike Bayer]

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()), '%'))

)

--

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

[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

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/5ad5b40e-9831-4868-aaaa-7fe52e000c6f%40www.fastmail.com.

[Mike Bayer]

as replied on the issue I'd rather improve the behavior.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAN61F1zHP%3DxscwjdyxdXZY0JOgJNu_AhSJfd1t%3DhQh%3D%3D0EhCUw%40mail.gmail.com.