sqlalchemy之in子查询问题

背景

表模型如下：

Post表：

class Post(db.Model):
    __tablename__ = 'posts'
    id = db.Column(db.Integer, primary_key=True)
    body = db.Column(db.Text)

Solutions表：

class Solutions(db.Model):
    __tablename__ = 'solutions'
    id = db.Column(db.Integer, primary_key=True)
    label = db.Column(db.String(1000))
    weight = db.Column(db.Integer, default=0)
    timestamp = db.Column(db.DateTime(), default=datetime.now)
    title = db.Column(db.String(1000))
    solutionId = db.Column(db.String(1000))
    description = db.Column(db.Text)

Solutions表通过SolutionId与Post表中的id相联系。

目前需求是通过模糊查询Post表，获取涉及的id，再找到对应的Solutions表中的项目，sql语句如下：

1	select * from solutions where solutionId in(select id from posts where body like '%问题%' ) order by weight desc

sqlalchemy方法

先进行Post表查询：
1
ids_raw = Post.query.with_entities(Post.id).filter(Post.body.like(u'%问题%')).all()
使用模型（Post）进行查询，默认返回的是整个模型，如果仅需要返回某一列，那么就需要使用.with_entities()方法。该方法会替换sql语句中的select部分。
https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.query.Query.with_entities

对查询出来的ids_raw进行处理

使用了with_entities(Post.id)之后，返回的是一个列表，每个值的类型为<class 'sqlalchemy.util._collections.result'>

>>> type(ids_raw)
<type 'list'>
>>> type(ids_raw[1])
<class 'sqlalchemy.util._collections.result'>

直接使用的话，sqlalchemy无法识别，最后回返回空列表，需要把转换为普通的List类型或者元组类型。

先看下sqlalchemy.util._collections.result类型输出的具体内容：

>>> dir(ids_raw[1])
['__add__', '__class__', '__contains__', '__delattr__', '__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__getitem__', '__getnewargs__', '__getslice__', '__gt__', '__hash_
_', '__init__', '__iter__', '__le__', '__len__', '__lt__', '__module__', '__mul__', '__ne__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__rmul__', '__setattr__', '__sizeof__',
 '__slots__', '__str__', '__subclasshook__', '_asdict', '_fields', '_real_fields', 'count', 'id', 'index', 'keys']
>>> ids_raw[1].id
19L

可以看出每个元素都有一个属性名为自身的值，这才是我们需要的值

获取列表

>>> ids = []
>>> for id in ids_raw:
...     ids.append(id.id)
...
>>> ids
[18L, 19L, 21L, 31L, 32L, 33L, 35L, 42L, 44L, 88L]

实现in子查询

>>> res =Solutions.query.filter(Solutions.solutionId.in_(ids)).all()
>>> res
[<app.models.Solutions object at 0x04FDB650>, <app.models.Solutions object at 0x04FDB210>, <app.models.Solutions object at 0x04FDB1F0>, <app.models.Solutions object at 0x04FDB6D0>, <app.mo
dels.Solutions object at 0x04FDB050>, <app.models.Solutions object at 0x04FDB0D0>, <app.models.Solutions object at 0x04FDB070>, <app.models.Solutions object at 0x04FDB290>, <app.models.Sol
utions object at 0x04FDB2D0>]

在sqlalchemy中，in子句是通过.in_()实现的。

https://docs.sqlalchemy.org/en/13/orm/internals.html?highlight=in_#sqlalchemy.orm.properties.ColumnProperty.Comparator.in_

拓展

with_entities()是用于将替换数据库查询是select的具体内容，但其实返回的也是一个模型，而非是具体数值。
所以其实如果不用的话，效果也一样的。只不过返回的为原生模型，数据量更多一点而已。替换成如下语句，后续对ids_raw进行处理，提取所有的id生成python原生的列表或元组类型即可。
1
ids_raw = Post.query.filter(Post.body.like(u'%问题%')).all()

上面的第二第三步可以通过map()来简化为一步：

>>> res =Solutions.query.filter(Solutions.solutionId.in_(map(lambda x:x.id, ids_raw))).all()
>>> res
[<app.models.Solutions object at 0x04FDB650>, <app.models.Solutions object at 0x04FDB210>, <app.models.Solutions object at 0x04FDB1F0>, <app.models.Solutions object at 0x04FDB6D0>, <app.mo
dels.Solutions object at 0x04FDB050>, <app.models.Solutions object at 0x04FDB0D0>, <app.models.Solutions object at 0x04FDB070>, <app.models.Solutions object at 0x04FDB290>, <app.models.Sol
utions object at 0x04FDB2D0>]

尤其要注意利用模型或者db.session的输出类型为sqlalchemy.util._collections.result，这种类型虽然直接输出看起来和python的基础类型一旦，但是不可直接用在sqlalchemy的查询里当参数，会导致输出为空。