how to filter on a SQLAlchemy MutableList column

March 3, 2023

I am trying to filter on a column defined as a MutableList, but I’m not sure how to do it, or if it is even possible?

Here’s what I tried so far:

from sqlalchemy import Column, String, Integer, PickleType
from sqlalchemy.ext.mutable import MutableList

class MyClass(db.Model):
    id = Column(Integer, primary_key=True, autoincrement=True, nullable=False, index=True)
    name = Column(String(100), nullable=False)
    name_list = Column(MutableList.as_mutable(PickleType), default=[])

search_val = ['test']

new = MyClass(
  name="foo",
  name_list=search_val
)
db.session.add(new)
db.session.commit()

q1 = MyClass.query.filter(MyClass.name_list.in_(search_val)).first()

Returns no results. So, I also tried —

q2 = MyClass.query.filter(MyClass.name_list == search_val).first()

But, also no results. And, finally —

search_val_2 = 'test'
q3 = MyClass.query.filter(MyClass.name_list.in_(search_val_2)).first()

However, that causes an ArgumentError —

sqlalchemy.exc.ArgumentError: IN expression list, SELECT construct, or bound parameter object expected, got 'test'.

The underlying database is MariaDB, and I’m using Flask SQLAlchemy.

>Solution :

SQLAlchemy maps MutableList column to VARCHAR or BINARY column types.

Python Pickle module is used to produce a binary payload from whatever data you have in your list.

Any content of the pickled binary data is not transparent to SQL. Thus, SQL do not have ability to search or read it, because Pickle is specific to Python and only Python programs can read pickled data.

To have SQL searchable lists you need to use SQL native relationship mappers in your SQLAlchemy models. You would need to construct another table which contains one row per a list entty.