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&utm_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/5ad5b40e-9831-4868-aaaa-7fe52e000c6f%40www.fastmail.com.