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 [email protected].
> 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/5ad5b40e-9831-4868-aaaa-7fe52e000c6f%40www.fastmail.com.
