Django 数据库综合运用（十四）

9.7 综合运用

---

9.7.1 准备数据模型

• models.py

from enum import unique

import blank as blank
from django.db import models

# Create your models here.


class User(models.Model):
    # unique=True 唯一索引不允许重复 blank=False 该值不允许为空
    username = models.CharField(max_length=20, unique=True, blank=False)
    age = models.SmallIntegerField(default=0)
    phone = models.CharField(db_index=True, blank=True, default=0, max_length=20)
    email = models.EmailField(default='')
    info = models.TextField()
    # auto_now_add=Tru 创建时添加时间 该时间只会更新一次
    create_time = models.DateTimeField(auto_now_add=True)
    # 更新操作将会修改时间
    update_time = models.DateTimeField(auto_now=True)

    def __str__(self):
        return str([self.username, self.age, self.phone, self.email, self.info, self.create_time, self.update_time])


# 一对一表关系 （需要设置 on_delete）
class Userprofile(models.Model):
    # 如果要声明 on_delete 那么 blank null 都要为 True
    user = models.OneToOneField(User, blank=True, null=True, on_delete=models.SET_NULL)
    birthday = models.CharField(max_length=50, blank=True, default='')


# 一对多表关系 （需要设置 on_delete）
class Userlog(models.Model):
    user = models.ForeignKey(User, related_name='user_log', blank=True, null=True, on_delete=models.SET_NULL)
    content = models.TextField()
    created_time = models.DateTimeField(auto_now_add=True)


# 多对多关系 （不用设置 on_delete）
class Group(models.Model):
    user = models.ManyToManyField(User, related_name='group')
    name = models.CharField(max_length=20)
    created_time = models.DateTimeField(auto_now_add=True)

9.7.2 准备数据

• views.py

import views as views
from django.shortcuts import render

# Create your views here.
from django.views import View
from app.models import User, Userprofile, Userlog, Group


class Index(View):
    def get(self, request):
        user = User()
        user.username = '小钟'
        user.age = 21
        user.phone = '12345678'
        user.email = 'xxx@qq.com'
        user.info = '伟大的好大好大互动哈偶都哈维活动'
        user.save()

        user_profile = Userprofile()
        user_profile.user = user
        user_profile.birthday = '2001.08.10'
        user_profile.save()

        user_log = Userlog()
        user_log.user = user
        user_log.content = '这是一份平平无奇的日志不信你看，但是好像它并没有写什么'
        user_log.save()

        user_group = Group()
        user_group.user = user
        user_group.name = '艾欧尼亚'
        user_group.save()

        return render(request, 'index.html', {"msg": '成功！'})
    

• ORM 查询

import datetime
from django.shortcuts import render

# Create your views here. 
from django.views import View
from app.models import User, Userprofile, Userlog, Group


class Index(View):
    def get(self, request):
    
        # 查询
        xiaozhong = User.objects.get(pk=1)    		# 或者   xiaozhong = User.objects.all()[0]
        data = xiaozhong.user_log.values('content')    # user_log 为你创建关系（一对多）时的别名（related_name='user_log'）

        print(data)

        # 集合查询
        user = User.objects.all()
        print(list(user))
        
        # 过滤查询
        user = User.objects.filter(username='小钟', id=1)
        print(user)

        # 过滤附加条件查询 年龄大于等于 22 的排除
        user = User.objects.all().exclude(age__gte=22)
        print(user)

        # 模糊查询
        user = User.objects.filter(username__contains="钟")
        print(user)
        
        # group 组查询
        user = User.objects.all()[0]
        groups = user.group
        print(groups.values('name'))
        
        # user = User.objects.raw('')
        return render(request, 'index.html', {"msg": '成功！'})
    

• 原生 sql 查询

import datetime
from django.shortcuts import render

# Create your views here. 
from django.views import View
from app.models import User, Userprofile, Userlog, Group


class Index(View):
    def get(self, request):
    
        # 查询表所有信息
        user = User.objects.raw('select * from app_user')
        print(list(user))
	
        # 反向查询  查询 user_log 表中 id 为 1 的 User 表中的信息 
        user = User.objects.filter(user_log__id=1)
        print(user)
	
        # 聚合查询
        user = User.objects.all().aggregate(Avg('age'))
        print(user)
        
        return render(request, 'index.html', {"msg": '成功！'})