sqlalchemy常用操作

in

condition = []
if names:
    condition.append(Person.name.in_(names))
if start_time:
    condition.append(Person.time >= start_time)
if end_time:
    condition.append(Person.time <= end_time)

and

db.session.query(Person).filter(and_(*condition)).all()

计数

from sqlalchemy import func
total = db.session.query(func.count(Person.id)).filter(*condition).scalar()

排序

Person.query.order_by(Person.count.desc()).first()

最大值

session.query(func.max(Person.score)).scalar()

浮点数比较大小

session.query(Person).filter(func.abs(UserContest.max_score - max_score) < 1e-7)

postgresql数组查询

condition.append(text(f"country::jsonb ? '{query.country}'"))
 db.session.query(User).filter(*condition).order_by(User.id).offset(
        offset).limit(limit).all()