Django——数据库查询

>>> cong = User.objects.get(pk=1)
>>> cong
<User: user:cong>
>>> cong.diary.values('content')
<QuerySet [{'content': '今天天气晴'}]>

查看cong的日记。
这里cong.diary是因为定义了related_name=‘diary’

class Diary(models.Model):
    user = models.ForeignKey(User, related_name='diary', on_delete=models.SET_NULL, blank=True, null=True)
    content = models.TextField()
    create_time = models.IntegerField()

所以，user.diary就相当于Diary。

>>> users = User.objects.all()
>>> users
<QuerySet [<User: user:cong>, <User: user:ming>]>

>>> users = list(User.objects.all())
>>> users
[<User: user:cong>, <User: user:ming>]

>>> users = User.objects.filter(username='cong', id=1)
>>> users
<QuerySet [<User: user:cong>]>

>>> users = User.objects.filter(username='cong', id=1).exclude(id=1)
>>> users
<QuerySet []>

过滤年龄大于20的user

>>> users = User.objects.all().exclude(age__gt=20)
>>> users
<QuerySet [<User: user:cong>]>

模糊查找

>>> users =User.objects.filter(username__contains='on')
>>> users
<QuerySet [<User: user:cong>]>

>>> groups = cong.group
>>> groups
<django.db.models.fields.related_descriptors.create_forward_many_to_many_manager.<locals>.ManyRelatedManager object at 0x000001C83610EA60>

>>> groups.values('name')
<QuerySet [{'name': 'IT'}, {'name': '开发'}]>

>>> groups.first()
<Group: Group object (1)>
>>> groups.last()
<Group: Group object (2)>

原生SQL方式：

>>> users = User.objects.raw('select * from app_user')
>>> users
<RawQuerySet: select * from app_user>
>>> list(users)
[<User: user:cong>, <User: user:ming>]

反向查询：反向查询是 related_name__id，两个下划线id

>>> user = User.objects.filter(diary__id=3)
>>> user
<QuerySet []>

聚合方法

>>> from django.db.models import Avg, Count, Sum

>>> user = User.objects.all().aggregate(Avg('age'))
>>> user
{'age__avg': 24.0}

通过user.related name, 然后在 .values (‘字段’) ，括号里是字段名，一定要用引号引起来。

>>> user = User.objects.get(pk=1)
>>> user.diary.values('content')
<QuerySet [{'content': '今天天气晴'}]>

ps：

>>> user = User.objects.filter(diary__id=1)
>>> user
<QuerySet [<User: user:cong>]>
>>> content = user.diary.values('content')
Traceback (most recent call last):
  File "<console>", line 1, in <module>
AttributeError: 'QuerySet' object has no attribute 'diary'

>>> user = User.objects.get(pk=1)
>>> user.diary.values('content')
<QuerySet [{'content': '今天天气晴'}]>
>>> user
<User: user:cong>

这里 第一种方法报错了，因为他返回的是queryset，第二种可以。
注意一下吧，就是要get一个User的对象，才可以使用related name.values的方法。