Djiango 连接数据库mysql 的基本数据操作
1、单表操作
(1) .增加操作
1 # 增加操作 2 def add_person(request): 3 # 1.使用save() 方法 4 # person_obj = models.Person() 5 # person_obj.name = 'wanwu' 6 # person_obj.age = 18 7 # person_obj.gender = False 8 # person_obj.address = 'shanghai' 9 # person_obj.save() 10 # 2.使用create() 方法 11 # person_obj = models.Person.objects.create(name='lsi', age=20, gender=True, address='shenzhen') 12 # print(person_obj.id) 13 14 models.Goods.objects.create(name='苹果', xl=1000, kc=2000) 15 models.Goods.objects.create(name='香蕉', xl=1000, kc=500) 16 models.Goods.objects.create(name='梨', xl=1000, kc=700)
(2) .查询操作
1 def find_person(request): 2 # 1.get() 返回值是一个具体对象。如果不存在则报错！！！,返回多了也报错 3 # person_obj = models.Person.objects.get(id=2) 4 # print(person_obj) 5 6 # 2.first() 和last()方法 返回值是一个具体的对象。 7 # ret = models.Person.objects.last() 8 # print(ret) 9 # 3.all() 返回一个QuerySet对象，类似于列表。 10 # person_obj_list = models.Person.objects.all() 11 # print(person_obj_list) 12 # # <QuerySet [<Person: Person object (1)>, <Person: Person object (2)>, <Person: Person object (3)>]> 13 # for person_obj in person_obj_list: 14 # print(person_obj.name) 15 # 4.filter() 16 # person_obj_list = models.Person.objects.filter(age=18) 17 # print(person_obj_list) # <QuerySet [<Person: Person object (1)>, <Person: Person object (3)>]> 18 # 多个条件之间使用并且的关系。 19 # ret = models.Person.objects.filter(age=18, name='zss') 20 # print(ret) 21 # 5.排序order_by() -id 表示降序。 22 # ret = models.Person.objects.all().order_by('-id') 23 # print(ret) 24 25 # 6.双下划线查询 26 # ret = models.Person.objects.filter(age__gte=18) 27 # print(ret) 28 # ret = models.Person.objects.filter(name__startswith='z') 29 # print(ret) 30 31 # 7.聚合函数aggregate() 32 from django.db.models import Count, Max, Min, Sum, Avg, F, Q 33 # 求年龄的平均值 34 # ret = models.Person.objects.all().aggregate(Avg('age')) 35 # print(ret) # {'age__avg': 18.6667} 36 37 # 8.分组 annotate() 38 # 按性别来分组，求人数 39 # ret = models.Person.objects.all().values('gender').annotate(Count('gender')) 40 # print(ret) 41 # 9. F和Q查询 42 # F 查询：同一个模型类中，不同属性进行比较。 43 # 查询销量(xl)大于库存(kc)的商品 44 # ret = models.Goods.objects.filter(xl__gt=F('kc')) 45 # print(ret) 46 # Q查询：可以实现复杂的查询 not (~)、and (&)、or (|) 关系的使用。 47 # 查询库存大于1000 、或者销量等1000的商品 48 # ret = models.Goods.objects.filter(Q(xl=1000) | Q(kc__gt=1000)) 49 # print(ret) 50 # ret = models.Goods.objects.all()[2] # 索引 51 # print(ret) 52 # ret = models.Goods.objects.all()[1:] # 切片 53 # print(ret) 54 return HttpResponse('find person...')
(3) .修改操作
1 def update_person(request): 2 # 1.使用save() 3 # person_obj = models.Person.objects.get(id=2) 4 # person_obj.age = 23 5 # person_obj.save() 6 # 2. 使用update()方法，查询出多个来统一修改 7 ret = models.Person.objects.filter(name='lsi').update(age=22) 8 print(ret) 9 10 return HttpResponse('update person...')
(4) .删除操作
# 删除操作 def delete_person(request): person_obj = models.Person.objects.get(id=1) person_obj.delete() return HttpResponse('delete person...')
2、一对一操作
(1) .模型类
1 class User(models.Model): 2 id = models.AutoField(primary_key=True) 3 name = models.CharField(max_length=32) 4 age = models.IntegerField(default=10) 5 6 7 class IdCard(models.Model): 8 id = models.AutoField(primary_key=True) 9 address = models.TextField(default='beijing') 10 cardnum = models.CharField(max_length=32) # 身份证号 11 # 一对一关联 12 # to=User :关联的表。on_delete:删除策略。 13 # models.CASCADE：级联删除，删除一的一方的时候，另外一方也删除。 14 # related_name='card':表示反向查询的时候使用属性。如果不写，反向查询的时候默认值为类名小写idcard 15 user = models.OneToOneField(to=User, on_delete=models.CASCADE, related_name='card') # 在数据库中会自动添加_id 16 17 18 19 20 21 22 23 def one_add(request): 24 # user_obj = models.User() 25 # user_obj.name = 'zs' 26 # user_obj.age = 18 27 # user_obj.save() 28 # idcard_obj = models.IdCard() 29 # idcard_obj.cardnum = '131126333' 30 # idcard_obj.address = 'beijing' 31 # idcard_obj.user = user_obj # 设置关系--> 赋值对象 32 # idcard_obj.save() 33 34 user_obj = models.User() 35 user_obj.name = 'lsi' 36 user_obj.age = 20 37 user_obj.save() 38 39 idcard_obj = models.IdCard() 40 idcard_obj.cardnum = '131126444' 41 idcard_obj.address = 'shangai' 42 idcard_obj.user_id = user_obj.id # 设置关系--> 赋值id 43 idcard_obj.save() 44 45 return HttpResponse('one add...') 46 47 48 # 查询 49 50 def one_find(request): 51 # 正向查询 52 # 1.查询id 为1 的身份证对应的人。 53 # card_obj = models.IdCard.objects.get(id=1) 54 # user_obj = card_obj.user # 通过关系属性获取对象 55 # print(user_obj, user_obj.name) # User object (1) 56 57 # 反向查询 58 # 2.查询zs 对应的身份证对象 59 user_obj = models.User.objects.filter(name='zs').first() 60 # card_obj = user_obj.idcard # 类名小写，直接获取到对象。 61 card_obj = user_obj.card # 使用的related_name属性的值。 62 print(card_obj) 63 return HttpResponse('one find...') 64 65 66 def one_delete(request): 67 # 删除zs 68 user_obj = models.User.objects.get(id=1) 69 user_obj.delete() # 级联删除身份证 70 return HttpResponse('one delete...')
3、一对多操作
1 # 部门表 2 class Dept(models.Model): 3 id = models.AutoField(primary_key=True) 4 name = models.CharField(max_length=32) 5 6 7 class Employee(models.Model): 8 id = models.AutoField(primary_key=True) 9 name = models.CharField(max_length=32) 10 # 设置外键 11 dpet = models.ForeignKey(to=Dept, on_delete=models.CASCADE, related_name='emps') 12 13 14 15 16 17 # 一对多操作 18 19 def onetomany_add(request): 20 dept_obj = models.Dept.objects.create(name='市场部') 21 emp_obj = models.Employee() 22 emp_obj.name = 'lsi' 23 emp_obj.dpet = dept_obj # 赋值对象即可 24 emp_obj.save() 25 26 emp_obj1 = models.Employee() 27 emp_obj1.name = 'ww' 28 emp_obj1.dpet = dept_obj 29 emp_obj1.save() 30 31 emp_obj2 = models.Employee() 32 emp_obj2.name = 'zl' 33 emp_obj2.dpet = dept_obj 34 emp_obj2.save() 35 36 return HttpResponse('onetomany_add...') 37 38 39 # 查询 40 41 def onetomany_find(request): 42 # 正向查询 43 # 1.查询lsi 对应的部门 44 # emp_obj = models.Employee.objects.filter(name='lsi').first() 45 # print(emp_obj.dpet) # 获取部门对象 46 47 # 反向查询 48 # 2.查询市场部对应的所有员工 49 dept_obj = models.Dept.objects.get(name='市场部') 50 # ret = dept_obj.employee_set.all() # 使用类名小写_set.方法() 51 ret = dept_obj.emps.all() # 设置查询名称 52 print(ret) 53 54 return HttpResponse('onetomany_find...') 55 56 57 # 删除 58 def onetomany_delete(request): 59 # 1.删除lsi 60 # emp_obj = models.Employee.objects.filter(name='lsi').first() 61 # emp_obj.delete() 62 63 # 2.删除市场部,会级联删除对应的员工。 64 dept_obj = models.Dept.objects.get(name='市场部') 65 dept_obj.delete() 66 67 return HttpResponse('onetomany_delete...')
4、多对多操作
1 class Teacher(models.Model): 2 id = models.AutoField(primary_key=True) 3 name = models.CharField(max_length=32) 4 salary = models.IntegerField(default=1000) 5 6 7 class Student(models.Model): 8 id = models.AutoField(primary_key=True) 9 name = models.CharField(max_length=32) 10 age = models.IntegerField(default=20) 11 # 多对多关系 12 teachers = models.ManyToManyField(to=Teacher) 13 14 15 16 17 18 def many_add(request): 19 std_obj1 = models.Student() 20 std_obj1.name = 'zs' 21 std_obj1.age = 10 22 std_obj1.save() 23 std_obj2 = models.Student() 24 std_obj2.name = 'sli' 25 std_obj2.save() 26 27 tea_obj = models.Teacher() 28 tea_obj.name = 'laowang' 29 tea_obj.save() 30 31 tea_obj1 = models.Teacher() 32 tea_obj1.name = 'laoli' 33 tea_obj1.save() 34 # 设置关系 35 std_obj1.teachers.add(tea_obj, tea_obj1) 36 std_obj2.teachers.add(tea_obj, tea_obj1) 37 38 return HttpResponse('many to many add...') 39 40 41 def many_find(request): 42 # 1.zs 被哪些老师教过 43 # std_obj = models.Student.objects.get(name='zs') 44 # ret = std_obj.teachers.all() 45 # print(ret) 46 47 # 2.laowang 教过哪些学生 48 tea_obj = models.Teacher.objects.get(name='laowang') 49 ret = tea_obj.student_set.all() # 类名小写_set.方法名称() 50 print(ret) 51 return HttpResponse('many to many find...')
