django queryset

[sfPPP]

django queryset:
1.如果对queryset进行遍历，那么每一行都是一个字典
set的取值方法： set.属性
dict的取值方法： dict["属性"]
2.聚合函数,分组queryset.values.annotate(
别名1：sum("属性1")
别名2：sum("属性2")
)
3.order_by（）按某个属性那个进行排序
order_by("属性1") 递增排序
order_by("-属性1") 递减排序
4.字典dict 集合list
声明方法： dict res_dict={}
list res_list=[]
res_dict = {
'name': name,
'sum_score': ad["sum_score"],
'last_edit_time': ad["last_edit_time"],
'sid': ad["user_id"]
}
将dict存入list
list.append("dict")
5.外键查询 event_task是外键
id_event_tasks = {row.id: row for row in ems}
answers = event_models.EventUserSubmitLog.objects.filter(event_task__in=id_event_tasks.keys())

   values_list('username') 取某一字段的值
   first() 取集合第一个
 6.extra 取别名
    SELECT name AS tag_name FROM blog_tag;

      这样的语句，就可以用 select 来实现，如下：

	 tags = Tag.objects.all().extra(select={'tag_name': 'name'})

7.values_list 取这两个字段的值
User.objects.values_list('id','name')
8.除什么以外
User.objects.exclude(status=User.USER.DELETE）
9.给某字段取别名
extra(select={'submit_time': 'create_time'})

执行原生SQL

#
  # from django.db import connection, connections
  # cursor = connection.cursor()  # cursor = connections['default'].cursor()
  # cursor.execute("""SELECT * from auth_user where id = %s""", [1])
  # row = cursor.fetchone()

python mysql-orm

一些常用的筛选

        # 获取个数
        #
        # models.Tb1.objects.filter(name='seven').count()

        # 大于，小于
        #
        # models.Tb1.objects.filter(id__gt=1)              # 获取id大于1的值
        # models.Tb1.objects.filter(id__gte=1)              # 获取id大于等于1的值
        # models.Tb1.objects.filter(id__lt=10)             # 获取id小于10的值
        # models.Tb1.objects.filter(id__lte=10)             # 获取id小于10的值
        # models.Tb1.objects.filter(id__lt=10, id__gt=1)   # 获取id大于1 且 小于10的值

        # in
        #
        # models.Tb1.objects.filter(id__in=[11, 22, 33])   # 获取id等于11、22、33的数据
        # models.Tb1.objects.exclude(id__in=[11, 22, 33])  # not in

        # isnull
        # Entry.objects.filter(pub_date__isnull=True)

        # contains
        #
        # models.Tb1.objects.filter(name__contains="ven")
        # models.Tb1.objects.filter(name__icontains="ven") # icontains大小写不敏感
        # models.Tb1.objects.exclude(name__icontains="ven")

        # range
        #
        # models.Tb1.objects.filter(id__range=[1, 2])   # 范围bettwen and

        # 其他类似
        #
        # startswith，istartswith, endswith, iendswith,

        # order by
        #
        # models.Tb1.objects.filter(name='seven').order_by('id')    # asc
        # models.Tb1.objects.filter(name='seven').order_by('-id')   # desc

        # group by
        #
        # from django.db.models import Count, Min, Max, Sum
        # models.Tb1.objects.filter(c1=1).values('id').annotate(c=Count('num'))
        # SELECT "app01_tb1"."id", COUNT("app01_tb1"."num") AS "c" FROM "app01_tb1" WHERE "app01_tb1"."c1" = 1 GROUP BY "app01_tb1"."id"

        # limit 、offset
        #
        # models.Tb1.objects.all()[10:20]

        # regex正则匹配，iregex 不区分大小写
        #
        # Entry.objects.get(title__regex=r'^(An?|The) +')
        # Entry.objects.get(title__iregex=r'^(an?|the) +')

        # date
        #
        # Entry.objects.filter(pub_date__date=datetime.date(2005, 1, 1))
        # Entry.objects.filter(pub_date__date__gt=datetime.date(2005, 1, 1))

        # year
        #
        # Entry.objects.filter(pub_date__year=2005)
        # Entry.objects.filter(pub_date__year__gte=2005)

        # month
        #
        # Entry.objects.filter(pub_date__month=12)
        # Entry.objects.filter(pub_date__month__gte=6)

        # day
        #
        # Entry.objects.filter(pub_date__day=3)
        # Entry.objects.filter(pub_date__day__gte=3)

        # week_day
        #
        # Entry.objects.filter(pub_date__week_day=2)
        # Entry.objects.filter(pub_date__week_day__gte=2)

        # hour
        #
        # Event.objects.filter(timestamp__hour=23)
        # Event.objects.filter(time__hour=5)
        # Event.objects.filter(timestamp__hour__gte=12)

        # minute
        #
        # Event.objects.filter(timestamp__minute=29)
        # Event.objects.filter(time__minute=46)
        # Event.objects.filter(timestamp__minute__gte=29)

        # second
        #
        # Event.objects.filter(timestamp__second=31)
        # Event.objects.filter(time__second=2)
        # Event.objects.filter(timestamp__second__gte=31)

联表查询

obj1.get(id=1).obj2_id # 获取到关联obj2对象
　　obj.get_user_type_display() # 显示choice定义的名称：“model字段的参数里有choice”，
  html里的写法：{{ obj.get_user_type_display }}
  
  反向操作：
　　　在被关联的类里定义方法
　　　　def get_obj_num(self):

      return obj_set.all() # 获取到所有被关联的对象，obj无论大小写