英文:

how to filter on a SQLAlchemy MutableList column

问题

我正在尝试在一个被定义为MutableList的列上进行筛选，但我不确定如何做，或者是否可能？

到目前为止，我尝试过以下方法：

```python
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()

没有返回结果。所以，我也尝试了--

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

但是，也没有结果。最后，我尝试了--

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

然而，这引发了一个ArgumentError --

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

底层数据库是MariaDB，我正在使用Flask SQLAlchemy。

<details>
<summary>英文:</summary>

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

Here&#39;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.

</details>


# 答案1
**得分**: 0

SQLAlchemy将`MutableList`列映射到`VARCHAR`或`BINARY`列类型。

Python的Pickle模块用于从列表中的任何数据生成二进制有效载荷。

Pickle二进制数据的内容对SQL不透明。因此，SQL无法搜索或读取它，因为Pickle是特定于Python，只有Python程序可以读取Pickle数据。

要使SQL可搜索的列表，您需要在SQLAlchemy模型中使用SQL本机关系映射器。您需要构建另一个表，其中包含每个列表实体的一行。

<details>
<summary>英文:</summary>

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.

</details>



# 答案2
**得分**: 0

你可以在这种情况下使用 `== any_`：

    MyClass.name_list == any_(*search_val_list)

<details>
<summary>英文:</summary>

You can use `== any_` in such cases:

    MyClass.name_list == any_(*search_val_list)


</details>