SQLAlchemy 多条件查询（当某个条件为空时就忽略该条件，所有条件为空时就查询全部）

1.并且关系 and

from sqlalchemy import and_
 
query.filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))
 
query.filter(User.name == 'ed', User.fullname == 'Ed Jones')
 
query.filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')

备注：User 是定义好的数据模型

2.或者关系 or

from sqlalchemy import or_

query.filter(or_(User.name == 'ed', User.name == 'wendy'))

3.按关键词匹配 match

query.filter(User.name.match('wendy'))

实际应用：在Python FastAPI框架中，如何利用 sqlalchemy 实现 多条件查询，当某个条件为空时就忽略该条件，所有条件为空时就查询全部

#!/usr/bin/env python

from typing import Optional

# 导入FastAPI模块
from fastapi import FastAPI, File, UploadFile

from sqlalchemy import or_

# 创建app实例
app = FastAPI()

方法一：
这种方式传参必须以json格式（类似于字典）进行；这种方式的弊端在于，如果查询所有数据（没有筛选条件），则必须得传个空字典 {}

# 定义数据模型
class Filter(BaseModel):
    budget_year: Optional[str] = None
    project_name: Optional[str] = None
    project_code: Optional[str] = None

    budget_quarter: Optional[int] = None
    belong_center: Optional[str] = None
    department_group: Optional[str] = None
    system_code: Optional[str] = None


@app.get("/budget/views_filter")
async def select_budget_filter(budget_item: Filter):
    # 多条件查询，当某个条件为空时就忽略该条件，所有条件为空时就查询全部
    budget_static_filter = session.query(BudgetStatic).filter(
        or_(BudgetStatic.budget_year == budget_item.budget_year, budget_item.budget_year == None),
        or_(BudgetStatic.project_name == budget_item.project_name, budget_item.project_name == None),
        or_(BudgetStatic.project_code == budget_item.project_code, budget_item.project_code == None),
    ).all()
    budget_dynamic_filter = session.query(BudgetDynamic).filter(
        or_(BudgetDynamic.budget_quarter == budget_item.budget_quarter, budget_item.budget_quarter == None),
        or_(BudgetDynamic.belong_center == budget_item.belong_center, budget_item.belong_center == None),
        or_(BudgetDynamic.department_group == budget_item.department_group, budget_item.department_group == None),
        or_(BudgetDynamic.system_code == budget_item.system_code, budget_item.system_code == None),
    ).all()
    return budget_dynamic_filter

方法二：
这种方式传参直接在url后面加?连接，多个参数用&拼接，例如：http://192.168.56.1:8080/budget/views_test?budget_quarter=0&department_group=运维研发组

@app.get("/budget/views_test")
async def select_budget_test(
        budget_year: Optional[str] = None,
        project_name: Optional[str] = None,
        project_code: Optional[str] = None,
        budget_quarter: Optional[int] = None,
        belong_center: Optional[str] = None,
        department_group: Optional[str] = None,
        system_code: Optional[str] = None
):
    # 多条件查询，当某个条件为空时就忽略该条件，所有条件为空时就查询全部
    budget_static_filter = session.query(BudgetStatic).filter(
        or_(BudgetStatic.budget_year == budget_year, budget_year == None),
        or_(BudgetStatic.project_name == project_name, project_name == None ),
        or_(BudgetStatic.project_code == project_code, project_code == None)
    ).all()
    budget_dynamic_filter = session.query(BudgetDynamic).filter(
        or_(BudgetDynamic.budget_quarter == budget_quarter, budget_quarter == None),
        or_(BudgetDynamic.belong_center == belong_center, belong_center == None),
        or_(BudgetDynamic.department_group == department_group, department_group == None),
        or_(BudgetDynamic.system_code == system_code, system_code == None)
    ).all()
    return budget_dynamic_filter

备注：BudgetStatic 和 budget_dynamic_filter 都是定义好的数据模型

 

参考文章：https://www.jianshu.com/p/c800aea5e51e