Django查询集+联表查询
总是忘记,还是记录下来把…
模型的三种关系
查询书中含有西的英雄
假定书和英雄是一对多的关系
hbook写在Hero中,通过hbook找到book类
Hero.object.filter(hbook__name__contains='西')
一本书中有多少个英雄
需要分组:先values_list,再annotate
Hero.objects.values_list('hbook_id').annotate(Count('id'))
hbook_id:hbook在Hero类中,hbook__id找到书籍的id;
annotate('id'):id是自动对values_list自动生成的,第一个,第二个...
如果values_list()中不写某一列,都会列出来,如果写了[(id,个数),(2,3),…]
F对象
字段A和字段B的比较
求书,阅读量大于评论量的
from django.db.models import *
Book.objects.filter(bread__gt = F(‘bcomment’))
Q对象,或
求阅读量=2,且 评论量=10的书
Book.objects.filter(bcomment=10,bread=1)
求阅读量=2,或 评论量=10
一个Q表示一个条件,中间+ |
Book.objects.filter(Q(bread=2)|Q(bcomment=10))
分组
成交统计站,按照instrumentid,tradingday,ruleid分组
tradeVolumeSum=Ruletrade.objects.values(
'tradingday','ruleid','instrumentid').annotate(
volumetradedsum = Sum('volumetraded'))
# 结果打印
'''
<QuerySet [
{'tradingday': '20200520', 'ruleid': 0, 'instrumentid': 'au2010', 'volumetradedsum': 14, 'amounttrade':200},
{'tradingday': '20200521', 'ruleid': 0, 'instrumentid': 'ag2009', 'volumetradedsum': 394},
'''
raw
raw是用sql的方式取数据,主要用于两张表,多张表无法取数据的情况下
项目中成交统计用到这个:
关联t_ruletrade,t_instrument用instrumentid,exchangeid字段。
1.sql语句先调通
2.得到的返回值是RawQuerySet类型的
trade_RawQuerySet = Ruletrade.objects.raw('''SELECT
t1.TradingDay,
t1.TradeID,
t1.Direction,
t1.InstrumentID,
t1.ExchangeID,
t1.RuleID,
SUM(300*t1.VolumeTraded*t1.TradePrice) AS F_IF,
SUM(200*t1.VolumeTraded*t1.TradePrice) AS F_IC,
SUM(10000*t1.VolumeTraded*t1.TradePrice) AS F_TF_T,
SUM(t1.VolumeTraded/100*t1.TradePrice) AS B_D,
SUM(t1.VolumeTraded*t1.TradePrice) AS E_U,
SUM(t1.VolumeTraded) AS VolumeTradedSum,
t2.CommodityType AS InstrumentCommodityType,
t2.UnderlyingMultiple
FROM
t_ruletrade t1,
t_instrument t2
WHERE
t1.InstrumentID = t2.InstrumentID
AND t1.ExchangeID = t2.ExchangeID
GROUP BY
t1.InstrumentID, t1.RuleID, t1.TradingDay''')
3.取值:
result_list = []
for row in trade_RawQuerySet:
res_dict = {}
res_dict['tradingday'] = row.tradingday
res_dict['ruleid'] = row.ruleid
res_dict['instrumentid'] = row.instrumentid
res_dict['volumetradedsum'] = row.VolumeTradedSum
result_list.append(res_dict)
取值时,Ruletrade.objects.raw(),
Ruletrade中的字段用小写,
非Ruletrade的字段用select...SQL中的字段名获取值。