Django：ORM补充
from django.db import models # Create your models here. from django.db import models # Create your models here. class Teachers(models.Model): ''' 教师表 ''' name=models.CharField(max_length=32)
''' 单表的增删改查: 增 Teachers.objects.create(name='root') obj = Teachers(name='root') obj.save() 查 Teachers.objects.all() Teachers.objects.filter(id=1) Teachers.objects.filter(id__gt=1) Teachers.objects.filter(id=1,name='root') 删 Teachers.objects.filter(id=1).delete() 改 Teachers.objects.all().update(name='alex') '''
class Student(models.Model): ''' 学生表 ''' username = models.CharField(max_length=32) age = models.IntegerField() gender = models.BooleanField() cs = models.ForeignKey(Classes,on_delete=models.CASCADE)
''' 一对多的增删改查: 增: Student.objects.create(username='alex',age=18,gender='man',cs_id=1) Student.objects.create(username='alex',age=18,gender='man',cs=Classes.object.filter(id=1).first()) 查: ret = Student.object.all() for i in ret: print(i.id) print(i.username) print(i.age) print(i.gender) print(i.cs_id) print(i.cs.id) print(i.cs.name) 删: Student.objects.filter(id=1).delete() Student.objects.filter(cs_id=1).delete() cid = input('请输入要删除的班级id') Student.objects.filter(cs_id=cid).delete() 跨表删除 cname = input('请输入要删除的班级名称') Student.objects.filter(cs__name=cname).delete() 改: Student.objects.all().update(cs_id=1) '''
class Classes(models.Model): ''' 班级表 ''' title = models.CharField(max_length=32) m = models.ManyToManyField('Teachers')
''' 班级表: id title 1 1班 2 2班 3 3班 教师表: id name 1 Alex 2 老妖 3 瞎驴 4 Eric 5 Eric 多对多的表操作 ManyToMany models中没有第三张表,不能直接create操作 只能通过字段m进行操作 #增 obj1 = Classes.objects.filter(id=1).first() obj1.m.add(2) obj1.m.add(3) obj1.m.add([4,5]) obj2 = Classes.objects.filter(id=2).first() obj2.m.add(2) obj2.m.add(3) obj2.m.add([4,5]) 班级_教师表: id 班级id 教师id 1 1 2 2 1 3 3 1 4 4 1 5 5 2 2 6 2 3 7 2 4 8 2 5 #删除与清空 obj1.m.remove(2) obj2.m.clear() 班级_教师表: id 班级id 教师id 2 1 3 3 1 4 4 1 5 #改---->重置 obj1.m.set([2,3,4]) 班级_教师表: id 班级id 教师id 2 1 3 3 1 4 9 1 2 查: 查询一班的所有老师姓名 obj = Classes.objects.filter(id=1).first() ret = obj.m.all()#ret是一个教师列表[老师obj1(id,name),老师obj2(id,name)...] '''
all values values_list 的区别:
#显示所有学生的id 姓名以及所在班级名称 ''' 方式一:all() 对象集合 stu_list = Student.objects.all() [obj,obj,obj,...] #stu_list是对象集合 select * from tb; for stu in stu_list: print(stu.id,stu.username,stu.cs.title) 方式二:values() 字典集合 stu_list = Student.objects.all().values('id','username','cs__title') [{id:1,username:"xx",title:"1班"},{id:2,username:"yy",title:"2班"},...] #stu_list是字典集合 for stu in stu_list: print(stu['id'],stu['username'],stu['cs__title']) #方式三:values_list() 元组集合 stu_list = Student.objects.all().values_list('id','username','cs__title') [(1,"root","1班"),(2,"alex","2班"),...] #stu_list是元组集合 for stu in stu_list: print(stu.0,stu.1,stu.2) ''' # class School(models.Model): # ''' # 校区表 # ''' # name=models.CharField(max_length=32) #找到3班的所有学生(跨一张表) #Student.objects.filter(cs__title='3班') #学生表-->班级表-->校区表 #假设班级表有外键fk连接校区表(id,name) #stu_list = Student.objects.all().values('id','username','cs__title','cs__fk__name')
一对多的正反向查询:
正向查询(推荐):
def test(request): #正向查询 obj = models.Student.objects.filter(cs__title='1班') print(obj) return HttpResponse("test")
反向查询(默认)
def test(request): #反向查询(默认):(小写)类名_set 注意:obj.student_set仅关联到子表,并没有查询,必须使用all()方法 obj = models.Classes.objects.filter(title='1班').first() print(obj.student_set) #app01.Student.None print(obj.student_set.all()) #<QuerySet [<Student: Student object (1)>, <Student: Student object (2)>]> return HttpResponse("test")
反向查询(自定义)
*********************models********************* class Student(models.Model): ''' 学生表 ''' username = models.CharField(max_length=32) age = models.IntegerField() gender = models.BooleanField() cs = models.ForeignKey(Classes,on_delete=models.CASCADE,related_name='ssss') *********************views********************* def test(request) #反向查询:ssss 注意:obj.ssss仅关联到子表,并没有查询,必须使用all()方法 obj = models.Classes.objects.filter(title='1班').first() print(obj.ssss) #app01.Student.None print(obj.ssss.all()) #<QuerySet [<Student: Student object (1)>, <Student: Student object (2)>]> return HttpResponse("test")
实例:
def test(request): # #反向查询:自定义 # ret = models.Classes.objects.all().values('id','title','ssss') # print(ret) #1班两个学生,2班两个学生,3班无学生 # #<QuerySet [{'id': 1, 'title': '1班', 'ssss': 1}, {'id': 1, 'title': '1班', 'ssss': 2}, {'id': 2, 'title': '2班', 'ssss': 3}, {'id': 2, 'title': '2班', 'ssss': 4}, {'id': 3, 'title': '3班', 'ssss': None}]> ret1 = models.Student.objects.all().values('username','cs__title') #left join ret2 = models.Classes.objects.all().values('title','ssss__username') #left join
#left join:主表的所有记录都显示
print(ret1)#<QuerySet [{'username': '小东北', 'cs__title': '1班'}, {'username': '中东北', 'cs__title': '1班'}, {'username': '大东北', 'cs__title': '2班'}, {'username': '老东北', 'cs__title': '2班'}]> print(ret2)#<QuerySet [{'title': '1班', 'ssss__username': '小东北'}, {'title': '1班', 'ssss__username': '中东北'}, {'title': '2班', 'ssss__username': '大东北'}, {'title': '2班', 'ssss__username': '老东北'}, {'title': '3班', 'ssss__username': None}]> return HttpResponse("test")
多对多的增删改查以及正反向查询:
def test(request): #多对多操作 #增: #添加教师记录 # models.Teachers.objects.create(name='alex') # models.Teachers.objects.create(name='xialv') # models.Teachers.objects.create(name='SB') # models.Teachers.objects.create(name='wupeiqi') # #给id=1的班级分配教师(正向) # obj = models.Classes.objects.filter(id=1).first() # obj.m.add(1) # obj.m.add(2) # obj.m.add(3) # #给id=2的教师安排班级(反向) # obj = models.Teachers.objects.filter(id=2).first() # # obj.classes_set.add(3) # obj.sssss.add(2) #查: # #获取所有班级记录 # cls_list = models.Classes.objects.all() # #循环输出所有班级信息以及该班级的所有任课教师 # for cls in cls_list: # print(cls.id,cls.title) # for row in cls.m.all(): # print('------',row.name) #改: #set重置,有重复的不动 obj = models.Teachers.objects.filter(id=2).first() ''' id classes_id teacher_id 1 1 2 2 2 2 3 3 2 ''' obj.sssss.set([1,2,4]) ''' id classes_id teacher_id 1 1 2 2 2 2 4 4 2 ''' #删: obj.sssss.remove(1) ''' id classes_id teacher_id 2 2 2 4 4 2 ''' return HttpResponse("test")
字典与元组对象无法通过.add()等方法进行增删改查
filter() all()得到的是对象,内部的外键可以使用add() remove()等方法进行增删改查 obj = models.Classes.objects.filter(id=1).first() obj.m.add(1) values() values_list得到的是字典或元组对象,内部的外键是int类型,无法使用add()等方法 v = models.Classes.objects.all().values('id','title','m','m__name') print(v) v = models.Teachers.objects.all().values('id','name','sssss__title') print(v)
浙公网安备 33010602011771号