Django 08 模型层多表操作
Django 模型层多表操作
一、Django多表ORM设计规则
'''
1. 关联的表之间建议建立外键,但可以取消关联关系(db_constraint=False)
2. 关联表之间的外键字段建议采用对应类名的全小写
3. 采用关联表的主键或对象均能进行操作
'''
设计Models
# 一对多:出版社(一) 书籍(多,外键在多的一方,依赖于出版社)
# 一对一:作者详情(一) 作者(一,外键在任意一方均可,一旦外键放在作者中,作者依赖于作者详情)
# 多对多:作者(多)书籍(多)建立关系表(存放两个表的外键信息 => 将建表转化为关系对应字段)
# Book书籍:id name price publish_date publish(publish_id)
class Book(models.Model):
id = models.AutoField(primary_key=True)
name = models.CharField(max_length=20)
price = models.DecimalField(max_digits=5, decimal_places=2)
publish_date = models.DateField()
publish = models.ForeignKey(to='Publish', to_field='id')
# 多对多关系字段,该字段不会再book表中形成字段,是用来创建关系表的
author = models.ManyToManyField(to='Author')
# Author作者:id name
class Author(models.Model):
id = models.AutoField(primary_key=True)
name = models.CharField(max_length=20)
# author_detail = models.ForeignKey(to='AuthorDetail', to_field='id', unique=True)
author_detail = models.OneToOneField(to='AuthorDetail', to_field='id')
# AuthorDetail作者详情: id age telephone info
class AuthorDetail(models.Model):
id = models.AutoField(primary_key=True)
age = models.IntegerField()
telephone = models.IntegerField()
# 存大文本
info = models.TextField()
# Publish出版社:id name address
class Publish(models.Model):
id = models.AutoField(primary_key=True)
name = models.CharField(max_length=20)
address = models.CharField(max_length=64)
二、一对多关系 Book 依赖于 Publish
规则:
'''
1. 关系中 『多』 依赖于 『一』
2. Django 1.x外键关联默认有级联删除,2.x需要手动明确外键的级联删除(on_delete=models.CASCADE)
'''
1、增
# 主键操作,外键字段为关联对象主键
publish = Publish.objects.create(name='小女孩出版社', address='山东')
publish_id = publish.id
Book.objects.create(name='死亡Python', price=66.66, publish_date='2018-8-8', publish_id=publish_id)
# 对象操作,外键对象为关联对象
publish = Publish.objects.first()
Book.objects.create(name='inPingMei', price=888.88, publish_date='1970-1-1', publish=publish)
2、删
# 默认有级联删除,关联数据贵全被删除
book = Book.objects.last() # type: Book
book.delete()
Publish.objects.first().delete()
3、改
publish = Publish.objects.last()
book = Book.objects.last() # type: Book
book.publish = publish
book.save()
publish_id = Publish.objects.last().id
Book.objects.filter(name='死亡Python').update(publish_id=publish_id)
三、一对一
规则:通过外键所在表决定依赖关系
Author作者:id name
AuthorDetail作者详情: id age telephone info
一对一关系 Author 依赖于 AuthorDetail
1、增
detail = AuthorDetail.objects.create(age=8, telephone=13766668888, info='一个字,帅')
Author.objects.create(name="Owen", author_detail=detail)
Author.objects.create(name="Liuxx", author_detail_id=detail.id) # 三句一起执行,两个作者使用一条详情,不满足唯一约束
detail = AuthorDetail.objects.create(age=88, telephone=13722222222, info='一个字,二')
Author.objects.create(name="Liuxx", author_detail_id=detail.id)
2、删:拥有级联删除
AuthorDetail.objects.last().delete()
3、改:一对一关系不考虑连表更新
四、多对多
规则:
'''
1. 多对多关系存在关系表,关系表建议采用ManyToManyField字段处理
2. 需要手动创建关系表时,在字段中明确through与through_field值
'''
# 多对多:Book 与 Author表均可以单独操作,关联关系存在于 关系表
# 问题:如果获取关系表,通过Book的对象获得author属性,代表关系表的对象
# book = Book.objects.first() # type: Book
# book.author 代表关系表的对象
detail = AuthorDetail.objects.create(age=88, telephone=13744444444, info='一个字,衰')
Author.objects.create(name="Egon", author_detail_id=detail.id)
b1 = Book.objects.first() # type: Book
b2 = Book.objects.all()[1] # type: Book
a1 = Author.objects.first() # type: Author
a2 = Author.objects.all()[1] # type: Author
1、增 add()
book.author.add(*authors)
b1.author.add(a1.id, a2.id)
b2.author.add(a1, a2)
2、删
将第二本书的关系记录全部清除
b2.author.clear()
通过 主键 或 对象 操作指定某条记录
b1.author.remove(a1)
b1.author.remove(a2.id)
3、改
清除该本书的之前所有关系记录,添加新的关系
b1.author.clear()
b1.author.add(a1)
去除新数据中不存在的值,添加新数据中新有的值,与之前重复的值保留
b2.author.set([a2.id])
a3 = Author.objects.last()
b2.author.set([a2.id, a3.id])
连表查询规则
- 正向逆向概念:从存放外键的表到关系表称之为正向跨表查询,反之称之为逆向查询
- 正向查询通过外键属性名进行跨表查询
- 逆向查询通过关联表对应类名小写进行跨表查询
五、基于对象的多表查询
在连表查询规则规则之上,逆向查询遇到多条结果,在类名后再添加_set
1、一对多表查询
需求:打印第一本书的出版社名
book = Book.objects.first() # 查询得到书对象
print(book.publish.name)
需求:第一个出版社出版过的书们的名字,
publish = Publish.objects.first() # 查询得到出版社对象
print(publish.book_set.all())
for book in publish.book_set.all():# 得到书的类名
print(book.name)
需求:打印第二本书的出版社地址
address = Book.objects.all()[1].publish.address
print(address)
需求:第二个出版社出版过的书们的价格
publish = Publish.objects.all()[1] # type: Publish
for book in publish.book_set.all():
print(book.price)
2、一对一
author = Author.objects.first() # type: Author
print(author.author_detail.telephone)
author_detail = AuthorDetail.objects.last() # type: AuthorDetail
print(author_detail.author.name)
3、多对多
需求:第一本书的作者们的姓名
book = Book.objects.filter(name="inPingMei").first() # type: Book
# print(book.author)
for author in book.author.filter(name__endswith="xx"):
print(author.name)
需求:第二个作者写过的书们的书名
author = Author.objects.all()[1] # type: Author
for book in author.book_set.all():
print(book.name)
需求:第一个出版社出版过的书们的名字与价格
publish = Publish.objects.first()
books = publish.book_set.all() # 数据所有字段数据都被保留存放在对象(们)中
for book in books:
print(book.name, book.price)
books_infos = publish.book_set.values('name', 'price') # 数据指定字段数据被保留存放在列表中
print(books_infos)
5、多级跨表查询
需求:第一个出版社出版过书们的作者们的电话号码
publish = Publish.objects.first() # type: Publish
books = publish.book_set.all()
for book in books: # type: Book
authors = book.author.all() # type: Author
for author in authors: # type: Author
print(author.author_detail.telephone)
六、基于双下划线的跨表查询
注:不能出现对象
1、一对多
需求:第一本书的出版社名
publish_name = Book.objects.filter(id=5).values('publish__name')
print(publish_name)
需求:第一个出版社出版过的书们,
books_infos = Publish.objects.filter(id=2).values('book__name', 'book__price')
print(books_infos)
2、一对一
需求: 查询所有大于80岁作者的名字与实际年龄
authors_infos = Author.objects.filter(author_detail__age__gt=80).values('name', 'author_detail__age')
print(authors_infos)
多级连表查询
多表关联:查询出版社在上海的出版过的所有书的 作者姓名、作者电话、具体出版社名 的相关信息
infos = Publish.objects.filter(address__contains='上海').values('book__author__name', 'book__author__author_detail__telephone', 'name')
print(infos)
走在从入门到放弃,最终删库跑路的路上