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 <mike...@zzzcomputing.com> 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 > <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 > <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 sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAN61F1zHP%3DxscwjdyxdXZY0JOgJNu_AhSJfd1t%3DhQh%3D%3D0EhCUw%40mail.gmail.com.
