Python 【第十四章】django 数据操作之model 之多对多操作
model多对多操作
- 创建
a. 方式一：
class B2G(models.Model): #人工创建第三张表
b_id = models.ForeignKey('Boy')
g_id = models.ForeignKey('Girl')
class Boy(models.Model):
username = models.CharField(max_length=16)
class Girl(models.Model):
name = models.CharField(max_length=16)
b. 方式二：
class Boy(models.Model):
username = models.CharField(max_length=16)
# girl_set
class Girl(models.Model):
name = models.CharField(max_length=16)
b = models.ManyToManyField('Boy') #由django自动创建 第三张表
多对多例：
models.py
class Boy(models.Model): username = models.CharField(max_length=16) # girl_set class Girl(models.Model): name = models.CharField(max_length=16) b = models.ManyToManyField('Boy')
urls.py
from django.conf.urls import url from django.contrib import admin from app01 import views urlpatterns = [ # url(r'^admin/', admin.site.urls), url(r'^index/',views.index), url(r'^add_user_type/',views.add_user_type), url(r'^add_boy/', views.add_boy), url(r'^add_girl/', views.add_girl), url(r'^boy_to_girl/', views.boy_to_girl), ]
views.py
from django.shortcuts import render from django.shortcuts import HttpResponse from app01 import models from django import forms def add_user_type(request): q = request.GET.get('q', None) if q: models.UserType.objects.create(caption=q) return HttpResponse(q) def add_boy(request): boy = request.GET.get('v',None) if boy: models.Boy.objects.create(username=boy) return HttpResponse(boy) def add_girl(request): girl = request.GET.get('v',None) if girl: models.Girl.objects.create(name=girl) return HttpResponse(girl) def boy_to_girl(request): g1 = models.Girl.objects.get(id=1) print("女",g1.name) return HttpResponse("ok")
不用创建html文件，
直接浏览器输入add_boy/?v=李四和add_girl/?v=小红 可以对数据库添加 数据
然后 http://127.0.0.1:8000/boy_to_girl/ 可以取得数据
views.py中取出
def boy_to_girl(request): g1 = models.Girl.objects.get(id=1) print("女",g1.name) return HttpResponse("ok")
输出 效果
veiws.py中实现关联第三张表
def boy_to_girl(request): g1 = models.Girl.objects.get(id=1) print('女孩',g1.name) #g1.b是多对多字段 #g1.b.add('男') b1 = models.Boy.objects.get(id=1) g1.b.add(b1) return HttpResponse("OK")
获取关联数据
veiws.py
def boy_to_girl(request): # g1 = models.Girl.objects.get(id=1) # print('女孩',g1.name) # #g1.b是多对多字段 # #g1.b.add('男') # b1 = models.Boy.objects.get(id=1) # g1.b.add(b1) g1 = models.Girl.objects.get(id=1) ret = g1.b.all() #获取第三张表中girl_id = 1 所有数据 print(ret) return HttpResponse("OK")
再添加 关联，一个女对id 2的男
views.py
def boy_to_girl(request): g1 = models.Girl.objects.get(id=1) print('女孩',g1.name) #g1.b是多对多字段 #g1.b.add('男') b1 = models.Boy.objects.get(id=2) g1.b.add(b1) g1 = models.Girl.objects.get(id=1) ret = g1.b.all() #获取第三张表中girl_id = 1 所有数据 print(ret) return HttpResponse("OK")
输出 结果
操作：
添加：
正向
########### 增加数据 ###########
# 获取一个女孩对象
g1 = models.Girl.objects.get(id=1)
# 获取一个男孩对象
b1 = models.Boy.objects.get(id=1)
# 利用对对多字段b将男孩和女孩建立关系
g1.b.add(models.Boy.objects.get(id=1))
g1.b.add(1)
#一个女孩对所有男孩
bs = models.Boy.objects.all()
g1.b.add(*bs)
g1.b.add(*[1,2,3])
反向
b1 = models.Boy.objects.get(id=1)
b1.girl_set.add(1) #girl_set 是boy表中隐含
b1.girl_set.add(models.Girl.objects.all())
b1.girl_set.add(*[1,2,3,4])
删除：
g1 = models.Girl.objects.get(id=1)
g1.b.clear() # 清空和girl ID=1所关联的所有数据
例如：
def boy_to_girl(request): # g1 = models.Girl.objects.get(id=1) # print('女孩',g1.name) # #g1.b是多对多字段 # #g1.b.add('男') # b1 = models.Boy.objects.get(id=2) # g1.b.add(b1) # g1 = models.Girl.objects.get(id=1) # ret = g1.b.all() #获取第三张表中girl_id = 1 所有数据 # print(ret) # 删除第三张表中和女孩1关联的所有关联信息 g1 = models.Girl.objects.get(id=1) g1.b.clear() # 查询和女孩1关联所有男孩 g1 = models.Girl.objects.get(id=1) boy_list = g1.b.all() # for item in boy_list: # print(item) print(boy_list) return HttpResponse("OK")
g1.b.remove(2)
g1.b.remove(*[1,2])
备注： 同样也支持反向操作
查询：
正向查
g1 = models.Girl.objects.get(id=1) # SQL
g1.b.all() # SQL
g1.b.filter().count()
例子：
def add_girl(request): girl = request.GET.get('v',None) if girl: models.Girl.objects.create(name=girl) return HttpResponse(girl) def boy_to_girl(request): # g1 = models.Girl.objects.get(id=1) # print('女孩',g1.name) # #g1.b是多对多字段 # #g1.b.add('男') # b1 = models.Boy.objects.get(id=2) # g1.b.add(b1) # g1 = models.Girl.objects.get(id=1) # ret = g1.b.all() #获取第三张表中girl_id = 1 所有数据 # print(ret) # # 删除第三张表中和女孩1关联的所有关联信息 # g1 = models.Girl.objects.get(id=1) # g1.b.clear() # # # 查询和女孩1关联所有男孩 # g1 = models.Girl.objects.get(id=1) # boy_list = g1.b.all() # g1.b.remove(1) # print(boy_list) #添加和女孩1和 男孩1，2，3关联 # g1 = models.Girl.objects.get(id=1) # g1.b.add(*[1,2,3]) g1 = models.Girl.objects.get(id=1) ret = g1.b.filter().count() print(ret) return HttpResponse("OK")
反向查
b1 = models.Boy.objects.get(id=1)
b1.girl_set.all()
产生第三张表自动关联查询
models.Girl.objects.all().values('id','name', 'b__username')
例子：
def boy_to_girl(request): # g1 = models.Girl.objects.get(id=1) # print('女孩',g1.name) # #g1.b是多对多字段 # #g1.b.add('男') # b1 = models.Boy.objects.get(id=2) # g1.b.add(b1) # g1 = models.Girl.objects.get(id=1) # ret = g1.b.all() #获取第三张表中girl_id = 1 所有数据 # print(ret) # # 删除第三张表中和女孩1关联的所有关联信息 # g1 = models.Girl.objects.get(id=1) # g1.b.clear() # # # 查询和女孩1关联所有男孩 # g1 = models.Girl.objects.get(id=1) # boy_list = g1.b.all() # g1.b.remove(1) # print(boy_list) #添加和女孩1和 男孩1，2，3关联 # g1 = models.Girl.objects.get(id=1) # g1.b.add(*[1,2,3]) # g1 = models.Girl.objects.get(id=1) # ret = g1.b.filter().count() ret = models.Girl.objects.all().values('id','name', 'b__username') print(ret) return HttpResponse("OK")
读取产生SQL语句
models.Boy.objects.all().values('id','username', 'girl__name')
def boy_to_girl(request): # g1 = models.Girl.objects.get(id=1) # print('女孩',g1.name) # #g1.b是多对多字段 # #g1.b.add('男') # b1 = models.Boy.objects.get(id=2) # g1.b.add(b1) # g1 = models.Girl.objects.get(id=1) # ret = g1.b.all() #获取第三张表中girl_id = 1 所有数据 # print(ret) # # 删除第三张表中和女孩1关联的所有关联信息 # g1 = models.Girl.objects.get(id=1) # g1.b.clear() # # # 查询和女孩1关联所有男孩 # g1 = models.Girl.objects.get(id=1) # boy_list = g1.b.all() # g1.b.remove(1) # print(boy_list) #添加和女孩1和 男孩1，2，3关联 # g1 = models.Girl.objects.get(id=1) # g1.b.add(*[1,2,3]) # g1 = models.Girl.objects.get(id=1) # ret = g1.b.filter().count() ret = models.Boy.objects.all().values('id','username', 'girl__name') print(ret) return HttpResponse("OK")
读取产生SQL语句
输入结果
System check identified no issues (0 silenced). January 30, 2017 - 23:49:26 Django version 1.10.4, using settings 'untitled2.settings' Starting development server at http://127.0.0.1:8000/ Quit the server with CTRL-BREAK. SELECT "app01_boy"."id", "app01_boy"."username", "app01_girl"."name" FROM "app01_boy" LEFT OUTER JOIN "app01_girl_b" ON ("app01_boy"."id" = "app01_girl_b"."boy_id") LEFT OUTER JOIN "app01_girl" ON ("app01_girl_b"."girl_id" = "app01_girl"."id") [30/Jan/2017 23:49:38] "GET /boy_to_girl/ HTTP/1.1" 200 2
————————————————————————————————————————————————————
添加例子
def boy_to_girl(request): #添加和女孩1和 男孩1，2，3关联 g1 = models.Girl.objects.get(id=1) g1.b.add(*[1,2,3]) return HttpResponse("OK")
查询
def boy_to_girl(request): # g1 = models.Girl.objects.get(id=1) # print('女孩',g1.name) # #g1.b是多对多字段 # #g1.b.add('男') # b1 = models.Boy.objects.get(id=2) # g1.b.add(b1) # g1 = models.Girl.objects.get(id=1) # ret = g1.b.all() #获取第三张表中girl_id = 1 所有数据 # print(ret) # # 删除第三张表中和女孩1关联的所有关联信息 # g1 = models.Girl.objects.get(id=1) # g1.b.clear() # # # 查询和女孩1关联所有男孩 g1 = models.Girl.objects.get(id=1) boy_list = g1.b.all() # # # for item in boy_list: # # # print(item) print(boy_list) #添加和女孩1和 男孩1，2，3关联 # g1 = models.Girl.objects.get(id=1) # g1.b.add(*[1,2,3]) return HttpResponse("OK")
删除1 个
def boy_to_girl(request): # g1 = models.Girl.objects.get(id=1) # print('女孩',g1.name) # #g1.b是多对多字段 # #g1.b.add('男') # b1 = models.Boy.objects.get(id=2) # g1.b.add(b1) # g1 = models.Girl.objects.get(id=1) # ret = g1.b.all() #获取第三张表中girl_id = 1 所有数据 # print(ret) # # 删除第三张表中和女孩1关联的所有关联信息 # g1 = models.Girl.objects.get(id=1) # g1.b.clear() # # # 查询和女孩1关联所有男孩 g1 = models.Girl.objects.get(id=1) boy_list = g1.b.all() g1.b.remove(1) print(boy_list) #添加和女孩1和 男孩1，2，3关联 # g1 = models.Girl.objects.get(id=1) # g1.b.add(*[1,2,3]) return HttpResponse("OK")
