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 <[email protected]> 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 [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 >> >> <https://groups.google.com/d/msgid/sqlalchemy/5ad5b40e-9831-4868-aaaa-7fe52e000c6f%40www.fastmail.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/CAN61F1zHP%3DxscwjdyxdXZY0JOgJNu_AhSJfd1t%3DhQh%3D%3D0EhCUw%40mail.gmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/CAN61F1zHP%3DxscwjdyxdXZY0JOgJNu_AhSJfd1t%3DhQh%3D%3D0EhCUw%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/327a043f-0f95-4be9-9a9e-3e99fd5a8f09%40www.fastmail.com.
