Django ORM 实现数据的多表增删改查

一、创建模型和表

假定下面这些概念、字段与关系：

作者模型：一个作者有姓名和年龄。
作者详细模型：把作者的详情放到详情表，手机号，家庭住址信息。
作者详情模型和作者模型之间是一对一的关系（one-to-one）。
出版社模型：出版社有名称，所在城市以及email。
书籍模型：书籍有书名和价格、出版日期。
一本书可能会有多个作者，一个作者也可以写多本书，所以作者和书籍的关系就是多对多的关联关系(many-to-many)。
一本书只应该由一个出版商出版，所以出版商和书籍是一对多关联关系(one-to-many)。
书跟作者是多对多关系，利用Django 的建表语句，可以新生成一张“关系表”---> book2author。

ORM中的外键创建和mysql几乎一样，以创建图书表，出版社表，作者表和作者详情表。

两张表	关系	方法	外键位置
书与出版社	一对多关系	ForeignKey（to='出版社表'）	一对多关系也是建在多的一方，建在书的表里
书与作者	多对多关系	ManyToManyField（to='作者表'）	多对多关系，可以不用自己创建第三张表
作者与作者详情	一对一关系	OneToOneField=(to='作者详情表')	一对一关系，建在查询频率较高的表中，建在作者表里

三个关键字里面的参数，to用于指定跟哪张表有关系，自动关联主键。to_field\to_fields，也可以自己指定关联字段。

ManyToManyField不会在表中创建实际的字段，而是告诉 Django ORM 自动创建第三张关系表。

ForeignKey、OneToOneField会在字段的后面自动添加 _id 后缀，如果你在定义模型类的时候自己添加了该后缀那么迁移的时候还会添加，所以不要自己加下划线id后缀。

1 创建模型

from django.db import models

# 出版社
class Publish(models.Model):
    # 主键
    nid = models.AutoField(primary_key = True)
    name = models.CharField(max_length = 55)
    city = models.CharField(max_length = 55)
    # email有特定的格式！
    email = models.EmailField()
    def __str__(self):
        return 'nid: %s, name: %s, city: %s, email: %s\n' % (self.nid, self.name, self.city, self.email)

# 作者详细
class AuthorDetail(models.Model):
    nid = models.AutoField(primary_key = True)
    # 日期的格式
    birthday = models.DateField()
    # 手机号
    telephone = models.BigIntegerField()
    addr = models.CharField(max_length = 55)
    def __str__(self):
        return 'nid: %s, birthday: %s, telephone: %s, addr: %s\n' % (self.nid, self.birthday, self.telephone, self.addr)

# 作者表
class Author(models.Model):
    nid = models.AutoField(primary_key = True)
    name = models.CharField(max_length = 55)
    # 年龄，int 类型的小数字就可以
    age = models.IntegerField()
    
    # 由于作者与作者详细表是一对一的关系：所以选择在作者表中这样建立外键
    # 注意这里还是只写 authordetail 就可以了，_id 程序会自动给加的！
    # 注意这里on_delete一定要加！
    # 一对一！
    authordetail = models.OneToOneField(to = 'AuthorDetail', to_field = 'nid', on_delete = models.CASCADE)
    def __str__(self):
        return 'nid: %s, name: %s, age: %s, authordetail: %s\n' % (self.nid, self.name, self.age, self.authordetail)

# 书籍
class Book(models.Model):
    nid = models.AutoField(primary_key = True)
    title = models.CharField(max_length = 55)
    # 出版日期，日期格式
    pub_date = models.DateField()
    # 价格，最大位数5位，小数后保留两位
    price = models.DecimalField(max_digits = 5, decimal_places = 2)
	
    # 与出版社表关联的字段 publish_id
    # 注意自己写的时候只写publish就可以了！Django会自动补上_id
    # 注意：on_delete必须要加上！！！
    # 注意 null=true表示允许为空值
    # 一对多！
    publish = models.ForeignKey(to = 'Publish', to_field = 'nid', on_delete = models.CASCADE, null = True)
    
    # 书跟作者是多对多的关系。理论上需要新建一张关系表。分别将其与书籍表与作者表关联起来！
    authors = models.ManyToManyField(to = 'Author')
    def __str__(self):
        return 'nid: %s, title: %s, pub_date: %s, price: %s, publish: %s, authors: %s\n' % (self.nid, self.title, self.pub_date, self.price, self.publish, self.authors)
    """
    create table book2author(
        id int primary_key auto_increment,
        book_id int,0
        author_id int,
        foreign_key (book_id) references Book(nid),
        foreign_key (author_id) references Author(nid),   
    );
    """

2 执行命令

python manage.py makemigrations
python manage.py migrate
# 查看一下数据库中是否生成了 "5" 张表

3 录入数据

models.Publish.objects.create(name = '东方出版社',city = "beijing", email = "dongfang@beijing.com")
models.Publish.objects.create(name = '京东出版社',city = "shanghai", email = "jingdong@shanghai.com")
models.Publish.objects.create(name = '立方出版社',city = "guangzhou", email = "wuliu@guangzhou.com")

models.Book.objects.create(title = 'Java', price = 199.93, pub_date = '2020-02-23', publish_id = 1)
models.Book.objects.create(title = 'golang', price = 219.5, pub_date = '2019-09-17', publish_id = 2)
models.Book.objects.create(title = 'python', price = 328.21, pub_date = '2020-11-15', publish_id = 3)
models.Book.objects.create(title = 'django', price = 150.6, pub_date = '2018-08-26', publish_id = 1)
models.Book.objects.create(title = 'gin', price = 110.23, pub_date = '2020-06-05', publish_id = 1)
models.Book.objects.create(title = 'spring', price = 138.17, pub_date = '2020-03-16', publish_id = 3)


models.AuthorDetail.objects.create(birthday = "1998-07-14", telephone = "13710398561", addr = "上海苏州")
models.AuthorDetail.objects.create(birthday = "2003-10-11", telephone = "17764251377", addr = "深圳")
models.AuthorDetail.objects.create(birthday = "1997-03-27", telephone = "15368482379", addr = "北京朝阳")
models.AuthorDetail.objects.create(birthday = "2002-11-21", telephone = "17622488326", addr = "杭州")
models.AuthorDetail.objects.create(birthday = "1998-05-16", telephone = "13642599728", addr = "成都")
models.AuthorDetail.objects.create(birthday = "2005-09-08", telephone = "15367536245", addr = "湖南长沙")


models.Author.objects.create(name = '小酒', age = 22, authordetail_id = 1)
models.Author.objects.create(name = '小美', age = 19, authordetail_id = 2)
models.Author.objects.create(name = '小花', age = 24, authordetail_id = 3)
models.Author.objects.create(name = '小爱', age = 20, authordetail_id = 4)
models.Author.objects.create(name = '小英', age = 23, authordetail_id = 5)
models.Author.objects.create(name = '小雨', age = 17, authordetail_id = 6)

二、一对多外键

# 一对多外键增删改查
# 方法一
publish_obj = models.Publish.objects.filter(nid = 1).first()
models.Book.objects.create(title='beego', price = 100.99, pub_date='2020-03-26', publish = publish_obj)

# 方法二
models.Book.objects.create(title = 'dubbo', price = 98.79, pub_date = '2015-12-16', publish_id = 3)

# 修改
models.Book.objects.filter(pk = 5).update(publish_id = 2)
publish_obj = models.Publish.objects.filter(pk = 1).first()
models.Book.objects.filter(pk = 5).update(publish = publish_obj)

# 删
models.Publish.objects.filter(pk = 2).delete()  # 级联删除

三、多对多外键

# 多对多增删改查
book_obj = models.Book.objects.filter(title = 'django').first()
xiaojiu = models.Author.objects.filter(nid = 1).first()
xiaoyu = models.Author.objects.filter(nid = 6).first()
book_obj.authors.add(xiaojiu, xiaoyu)   # 书籍name为django的书籍绑定一个主键为1和6的作者
# book_obj.authors.add(2, 3)
"""
add 给第三张关系表添加数据，括号内既可以传数字也可以传对象 并且都支持多个
"""

# 查询主键为 4 的书籍的所有作者的名字
bookVal = models.Book.objects.filter(nid = 4).first()
rets = bookVal.authors.all().values('name')
print(rets)
# 结果: <QuerySet [{'name': '小酒'}, {'name': '小雨'}]>

# 与这本书关联的所有作者对象集合 ---> QuerySet对象。[obj1, obj2,......]
bookAll = models.Book.objects.filter(nid = 4).first()
res = bookAll.authors.all()
print(res)

# 修改
book_obj = models.Book.objects.filter(nid = 4).first()
book_obj.authors.set([2, 3])  # 括号内必须给一个可迭代对象
book_obj.authors.set([3])  # 括号内必须给一个可迭代对象

author_obj = models.Author.objects.filter(pk = 4).first()
author_obj1 = models.Author.objects.filter(pk = 5).first()
book_obj.authors.set([author_obj, author_obj1])  # 括号内必须给一个可迭代对象
"""
set 括号内必须传一个可迭代对象，该对象内既可以数字也可以对象 并且都支持多个
"""

# 解除多对多关系，注意first得加
book_obj = models.Book.objects.filter(nid = 4).first()
# 注意这里的 4 代表 author_id
book_obj.authors.remove(4)
# 在第三张关系表中清空某个书籍与作者的绑定关系
book_obj.authors.clear()
"""
clear 括号内不要加任何参数
"""

正反向的概念

# 正向: 外键字段在我手上那么，我查你就是正向
# 反向: 外键字段如果不在手上，我查你就是反向
# book >>> 外键字段在书那儿(正向) >>> publish
# publish >>> 外键字段在书那儿(反向) >>> book
# 一对一和多对多正反向的判断也是如此

"""
正向查询按字段
反向查询按表名小写
    表名小写_set
    ...
"""

五、子查询(基于对象的跨表查询)

# 1 查询书籍主键为1的出版社
book_obj = models.Book.objects.filter(pk = 1).first()
# 书查出版社 正向
res = book_obj.publish
print(res)

# 2 查询书籍主键为2的作者 （多对多）
book_obj = models.Book.objects.filter(pk = 1).first()
# 书查作者 正向
author = book_obj.authors
print(author) # app.Author.None
authorAll = book_obj.authors.all()  # <QuerySet [<Author: Author object>, <Author: Author object>]>
for obj in authorAll:
    print(obj.name, obj.authordetail.telephone)

# 3 查询作者 小美信息
author_obj = models.Author.objects.filter(name = '小美').first()
res = author_obj.authordetail
print(res)

# 4 查询出版社是 立方出版社 的书
publish_obj = models.Publish.objects.filter(name = '立方出版社').first()
# 出版社查书  反向
bookSet = publish_obj.book_set
bookAll = publish_obj.book_set.all()
print(bookSet) # 立方出版社 app.Book.None
print(bookAll)

# 5 查询作者是 小爱 写过的书
author_obj = models.Author.objects.filter(name = '小爱').first()
# 作者查书      反向
bookSet = author_obj.book_set
bookAll = author_obj.book_set.all()
print(bookSet) # app.Book.None
print(bookAll)

# 6 查询手机号是 15368482379 的作者信息
author_detail_obj = models.AuthorDetail.objects.filter(telephone = 15368482379).first()
print(author_detail_obj)
res = author_detail_obj.author
print(res)

六、联表查询(基于双下划线的跨表查询)

# 1 查询 小酒 的手机号
res = models.Author.objects.filter(name = '小酒').values('authordetail__telephone')
print(res)
# 反向
res = models.AuthorDetail.objects.filter(author__name = '小酒')  # 拿作者姓名是 小酒 的作者详情
res = models.AuthorDetail.objects.filter(author__name = '小酒').values('telephone', 'author__name')
print(res)

# 2 查询书籍主键为1的出版社名称和书的名称
res = models.Book.objects.filter(pk = 1).values('title', 'publish__name')
print(res)
# 反向
res = models.Publish.objects.filter(book__nid = 1).values('name', 'book__title')
print(res)

# 3 查询书籍主键为1的作者姓名
res = models.Book.objects.filter(pk = 1).values('authors__name')
print(res)
# 反向
res = models.Author.objects.filter(book__nid = 1).values('name')
print(res)

# 4 查询书籍主键是1的作者的手机号
book author authordetail
res = models.Book.objects.filter(pk = 1).values('authors__authordetail__telephone')
print(res)

七、进阶练习（连续跨表）

# 练习: 查询 立方出版社 过的所有书籍的名字以及作者的姓名
# 正向查询
res = models.Book.objects.filter(publish__name = "立方出版社").values_list("title", "authors__name")
print(res)

# 反向查询
res = models.Publish.objects.filter(name = "立方出版社").values_list("book__title", "book__authors__name")
print(res)

# 练习: 手机号以 153 开头的作者出版过的所有书籍名称以及出版社名称
res = models.Book.objects.filter(authors__authordetail__telephone__regex="153").values_list("title", "publish__name")
print(res)
 
res = models.Author.objects.filter(authordetail__telephone__startswith = "153").values("book__title", "book__publish__name")
print(res)

八、多对多三种创建方式

1 自行创建第三张表（全自动）

优点：不需要你手动创建第三张表
缺点：第三张表不是你手动创建的，字段是固定的无法拓展

class Book(models.Model):
    title = models.CharField(max_length = 32, verbose_name = "书名")
    authors = models.ManyToManyField(to = "Author")
    
class Author(models.Model):
    name = models.CharField(max_length = 32, verbose_name = "作者姓名")

2 通过 ManyToManyField 自动创建第三张表（纯手动）了解即可

优点：第三张表可以任意拓展字段
缺点：orm 查询不方便

class Book(models.Model):
    title = models.CharField(max_length = 32, verbose_name = "书名")

# 通过 ORM 自带的 ManyToManyField 自动创建第三张表
class Author(models.Model):
    name = models.CharField(max_length = 32, verbose_name = "作者姓名")

class Author2Book(models.Model):
    author = models.ForeignKey(to = "Author", on_delete = models.CASCADE)
    book = models.ForeignKey(to = "Book", on_delete = models.CASCADE)

    class Meta:
        unique_together = ("author", "book")

3 设置 ManyToManyField 并指定自行创建的第三张表（半自动）推荐使用

优点：结合自动和手动优点
缺点：多对多字段不支持增删改查：add(), remove(), set(), clear()

class Book(models.Model):
    title = models.CharField(max_length = 32, verbose_name = "书名")

# 自己创建第三张表，并通过 ManyToManyField 指定关联
class Author(models.Model):
    name = models.CharField(max_length = 32, verbose_name = "作者姓名")
    books = models.ManyToManyField(to = "Book", through = "Author2Book", through_fields = ("author", "book"))
    # through 是表名称
    # through_fields 接受一个2元组 ('field1'，'field2')
    # field1 是定义ManyToManyField的模型外键的名(author)
    # field2 是关联目标模型（book）的外键名


class Author2Book(models.Model):
    author = models.ForeignKey(to = "Author", on_delete = models.CASCADE)
    book = models.ForeignKey(to = "Book", on_delete = models.CASCADE)