SQLAlchemy or_的使用

最新推荐文章于 2024-07-25 09:48:00 发布

XinShun

最新推荐文章于 2024-07-25 09:48:00 发布

阅读量1.8k

点赞数 2

分类专栏： Python 文章标签：大数据

本文链接：https://blog.csdn.net/XinShun/article/details/125760749

版权

Python 专栏收录该内容

22 篇文章 0 订阅

订阅专栏

1、参考https://blog.csdn.net/qq_34989829/article/details/111545430

https://blog.csdn.net/weixin_44737199/article/details/94215679

2、代码

master = aliased(MasterInfo, name='a')
detail = aliased(DetailInfo, name='b')
query = db.session.query(master)
query = query.filter((now >= master.BEGINDATE) & (now <= master.ENDDATE))
if hasattr(body, 'TYPE') and body.TYPE:
	query = query.filter(or_(master.TYPE == body.type, master.type == '全部'))

query = (query
		 .outerjoin(detail, master.ID == detail.MASTERID)
		 )
or_filter = []
if hasattr(body, 'DST') and body.DST and hasattr(body, 'AST') and body.AST:
	or_filter.append(and_((master.RELATION == 2),
						  (detail.DST == body.DST) &
						  (detail.AST == body.AST)
						  )
					 )
if hasattr(body, 'NAME') and body.NAME:
	or_filter.append(and_((master.RELATION == 3),
						  (detail.NAME == body.NAME)
						  )
					 )

if hasattr(body, 'APS') and body.APS:
	ap_list = body.APS.split(',')
	if ap_list:
		or_filter.append(and_((master.APS == 1),
							  (detail.APS.in_(ap_list))
							  )
						 )
if or_filter:
	query = query.filter(or_(*or_filter))
query = query.order_by(master.CREATEDATE)
items = query.all()

3、生成的SQL如下：

SELECT ...
FROM [MASTER_INFO] AS a 
LEFT OUTER JOIN [DETAIL_INFO] AS b 
ON a.[ID] = b.[MASTERID] 
WHERE a.[BEGINDATE] <= %(BEGINDATE_1)s 
AND a.[ENDDATE] >= %(ENDDATE_1)s 
AND (a.[TYPE] = %(TYPE_1)s OR a.[TYPE] = %(TYPE_2)s) 
AND (
(a.[RELATION] = %(RELATION_1)s AND b.[DST] = %(DST_1)s AND b.[AST] = %(AST_1)s)
OR a.[RELATION] = %(RELATION_2)s AND b.[NAME] = %(NAME_1)s 
OR a.[RELATION] = %(RELATION_3)s AND b.[APS] IN (__[POSTCOMPILE_APS_1])
) 
ORDER BY a.[CREATEDATE]

4、sql中and和or的优先级, and的优先级大于or，通俗理解其实or查询其实会把条件分为左右两边来查，python中and和or的优先级也类似，所以or后面的不再需要括号提高优先级。