flask_sqlalchemy filter或filter_by怎样使用in？

Question

filter或filter_by怎样使用in查询同一字段的一组值，比如查询name为"zhang","wang","li"的记录，sql如下：

select * from table1 where name in ("zhang","wang","li");

Answer 1

用 Column 对象 的 in_ 方法。
filter需要传递的参数为表达式，此处刚好。
filter_by需要传递关键字参数，所以此处in_没法使用。

in_在OOP和非OOP两种模式中的使用-demo:

# 通用
from sqlalchemy import (
    create_engine,
    Column,
    Integer,
    String
)

# oop方式所需
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# 非oop方式所需
from sqlalchemy import (
    Table,
    MetaData,
)
from sqlalchemy.sql import func


Base = declarative_base()

class Table1(Base):
    __tablename__ = 'table1'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(55))

    def __init__(self, name):
        super(Table1, self).__init__()
        self.id = None
        self.name = name

    def __repr__(self):
        return '<Table1 {0}>'.format(self.id if not self.id is None else '')

uri = 'sqlite:///:memory:'
engine = create_engine(uri)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine, autocommit=False)

############# 添加对象, 填充测试数据
session = Session()
for name in 'zhang,wang,li,gong,chen,zhao'.split(','):
    obj = Table1(name)
    session.add(obj)
session.commit()

lst_filter = ['wang', 'li', 'zhao']
############# OOP方式查询
# 查询总数
Q = session.query(Table1)
print(Q.count())
# 筛选
Q = Q.filter(Table1.name.in_(lst_filter))
result = Q.all()
print(result)
session.close()

############# 非OOP方式查询
metadata = MetaData(bind=engine)
table = Table('table1', metadata, autoload=True)

# 查询总数
stmt = func.count(table).select()
print(stmt.execute().fetchone())
# 筛选
stmt = table.select().where(table.c.name.in_(lst_filter))
rp = stmt.execute()
result = rp.fetchall()
# print(result)
print(len(result))

engine.dispose()

执行结果:

6
[<Table1 2>, <Table1 3>, <Table1 6>]
(6,)
3

参考:

• in_

  Implement the in operator.
  In a column context, produces the clause a IN other. “other” may be a tuple/list of column expressions, or a select() construct.

• filter

  apply the given filtering criterion to a copy of this Query, using SQL expressions.

• filter_by

  apply the given filtering criterion to a copy of this Query, using keyword expressions.

Answer 2

Table.query.filter(name in ("zhang","wang","li"))