SQLAlchemy多条件查询（当某个条件为空时就忽略该条件，
所有条件为空时就查询全部）
1.并且关系 and
1671年1from sqlalchemy import and_
2
3query.filter(and_(Ur.name == 'ed', Ur.fullname == 'Ed Jones'))
碱性食物都有什么4
5query.filter(Ur.name == 'ed', Ur.fullname == 'Ed Jones')
6
7query.filter(Ur.name == 'ed').filter(Ur.fullname == 'Ed Jones')
备注：Ur 是定义好的数据模型
异界之农家记事
2.或者关系 or
1from sqlalchemy import or_
2
3query.filter(or_(Ur.name == 'ed', Ur.name == 'wendy'))
3.按关键词匹配 match
query.filter(Ur.name.match('wendy'))
实际应⽤：在Python FastAPI框架中，如何利⽤ sqlalchemy 实现多条件查询，当某个条件为空时就忽略该条件，所有条件为空时就查询全部
1#!/usr/bin/env python
2赵云的老婆是谁
3from typing import Optional
红糖泡枸杞
4
5# 导⼊FastAPI模块
6from fastapi import FastAPI, File, UploadFile
7
8from sqlalchemy import or_
9
芭比兔10# 创建app实例
11app = FastAPI()
⽅法⼀： 这种⽅式传参必须以json格式（类似于字典）进⾏；这种⽅式的弊端在于，如果查询所有数据（没有筛选条件），则必须得传个空字典 {}
1# 定义数据模型
2class Filter(BaModel):
3 budget_year: Optional[str] = None
4 project_name: Optional[str] = None
5 project_code: Optional[str] = None
6
7 budget_quarter: Optional[int] = None
8 belong_center: Optional[str] = None
9 department_group: Optional[str] = None
10 system_code: Optional[str] = None
11
12
("/budget/views_filter")
14async def lect_budget_filter(budget_item: Filter):
15 # 多条件查询，当某个条件为空时就忽略该条件，所有条件为空时就查询全部
16 budget_static_filter = ssion.query(BudgetStatic).filter(
17 or_(BudgetStatic.budget_year == budget_item.budget_year, budget_item.budget_year == None),
18 or_(BudgetStatic.project_name == budget_item.project_name, budget_item.project_name == None),
19 or_(BudgetStatic.project_code == budget_item.project_code, budget_item.project_code == None),
20 ).all()
21 budget_dynamic_filter = ssion.query(BudgetDynamic).filter(
22 or_(BudgetDynamic.budget_quarter == budget_item.budget_quarter, budget_item.budget_quarter == None),
中国少年朗诵23 or_(BudgetDynamic.belong_center == budget_item.belong_center, budget_item.belong_center == None),
24 or_(BudgetDynamic.department_group == budget_item.department_group, budget_item.department_group == None),
25 or_(BudgetDynamic.system_code == budget_item.system_code, budget_item.system_code == None),
26 ).all()
27 return budget_dynamic_filter
("/budget/views_test")
2async def lect_budget_test(
3 budget_year: Optional[str] = None,
密度最小的气体4 project_name: Optional[str] = None,
5 project_code: Optional[str] = None,
6 budget_quarter: Optional[int] = None,
7 belong_center: Optional[str] = None,
8 department_group: Optional[str] = None,
9 system_code: Optional[str] = None
10):
11 # 多条件查询，当某个条件为空时就忽略该条件，所有条件为空时就查询全部
12 budget_static_filter = ssion.query(BudgetStatic).filter(
13 or_(BudgetStatic.budget_year == budget_year, budget_year == None),
14 or_(BudgetStatic.project_name == project_name, project_name == None ),
15 or_(BudgetStatic.project_code == project_code, project_code == None)
16 ).all()
17 budget_dynamic_filter = ssion.query(BudgetDynamic).filter(
18 or_(BudgetDynamic.budget_quarter == budget_quarter, budget_quarter == None),
19 or_(BudgetDynamic.belong_center == belong_center, belong_center == None),
20 or_(BudgetDynamic.department_group == department_group, department_group == None),
21 or_(BudgetDynamic.system_code == system_code, system_code == None)
22 ).all()
23 return budget_dynamic_filter
备注：BudgetStatic 和 budget_dynamic_filter 都是定义好的数据模型
