Django多表操作

Django多表ORM设计规则

1.关联的表之间建议建立外键，但是可以取消关联关系（db_constraint=False）

2.关联表之间的外键字段建议采用对应类名的全小写

3.采用关联表的主键或对象均可以进行操作

创建models：

from django.db import models

# Create your models here.


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 = models.ForeignKey(to='Publish', to_field='id')
    author = models.ManyToManyField(to='Author')
    publish_date = models.DateField()


class Publish(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=32)
    address = models.CharField(max_length=32)


class Author(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=32)


class AuthorDetail(models.Model):
    id = models.AutoField(primary_key=True)
    age = models.IntegerField()
    sex = models.CharField(max_length=10, default='male')
    author = models.OneToOneField(to='Author')

一对多关系（增删改）

注：1.关系中，多依赖于一；2.外键关联默认有级联删除，需要手动明确外键的级联删除（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='资治通鉴', 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)

一对一关系（增删改）

注：通过外键所在表决定依赖关系

# 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])

跨表查询

1.正向逆向概念：从存放外键的表到关系表称之为正向跨表查询，反之称为逆向跨表查询；

2.正向查询通过外键属性名进行跨表查询；

3.逆向查询通过关联表对应类名小写进行跨表查询。

基于对象的跨表查询：

在跨表查询的规则上，跨表查询的结果为多条数据时需要在字段后添加_set。

# 1、一对多表查询
# 需求：打印第一本书的出版社名
# book = Book.objects.first()  # type:
# 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="资治通鉴").first()  # type: Book
# # print(book.author)
# for author in book.author.filter(name__endswith="xx"):
#     print(author.name)

# 4、
# 需求：第二个作者写过的书们的书名
# 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)

基于双下划线的跨表查询：

注：filter方法与values方法支持__查询规则。

# 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)