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