I am using MySQL 8.0 and SQLalchemy.
I have a model as follows:
class Json(TypeDecorator):
impl = String(1000)
def process_bind_param(self, value, dialect):
return json.dumps(value)
def process_result_value(self, value, dialect):
if not value:
return None
return json.loads(value)
class BookCommerce(Base):
bcDetails = Column(Json)
This is rendered as varchar(1000) in the database, and the decorator means it all works well for accessing dict values that I save or retrieve from that field.
My problem is that I now want to search the field looking for values (including value fragments expressed as %my_string%). A query like this yields nothing:
BookCommerce.query.filter(BookCommerce.bcDetails.like(fragname))).all()
since SQLA (I think) renders the varchar into a JSON document before executing the query. Strangely, the SQL resulting from this query does actually work in a MySQL command window.
I've tried the accepted solution in this question, but it refers to the JSON type in MySQL and therefore croaks on the syntax (in Python and MySQL command line).
Any suggestions for how to handle this properly? I don't want to move away from this Json-decorated solution in general, I simply want a free-form search to locate matching records.
SQL queries obtained from echo=True on create_engine:
2021-03-25 19:47:11,638 INFO sqlalchemy.engine.Engine SELECT bookcommerce.`bcDetails` AS `bookcommerce_bcDetails`
FROM bookcommerce
WHERE bookcommerce.`bcDetails` LIKE %(bcDetails_1)s
2021-03-25 19:47:11,656 INFO sqlalchemy.engine.Engine [cached since 46.19s ago] {'bcDetails_1': '"%test%"'}
2021-03-25 19:47:11,663 DEBUG sqlalchemy.engine.Engine Col ('bookcommerce_bcDetails')
2021-03-25 19:47:11,667 INFO sqlalchemy.engine.Engine ROLLBACK
