Django多表操作

1、多表操作之模型创建

1、图书表：book，作者表：author，作者详情表：authordetail，出版社表：publish，图书作者表(第三张中间表)
2、作者跟作者详情：是一对一，关联字段写在哪一方都可以
3、图书跟出版社：是一对多，一对多关系一旦确立，关联字段写在多的一方
4、图书和作者：是多对多，多对多的关系需要建立第三张表（可以自动生成）

from django.db import models

class Publish(models.Model):
    publish_id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=32, verbose_name='出版社名称')
    phone = models.CharField(max_length=32, verbose_name='出版社电话')
    email = models.EmailField(verbose_name='出版社邮箱')
    addr = models.CharField(max_length=32, verbose_name='出版社地址')

    class Meta:
        db_table = 'publish'

        verbose_name = '出版社'
        verbose_name_plural = verbose_name

class Author(models.Model):
    author_id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=32, verbose_name='作者名称')
    age = models.SmallIntegerField(verbose_name='年龄')
    # 一对一的本质是  ForeignKey + unique
    author_detail = models.OneToOneField(to='AuthorDetail', to_field='author_detail_id', on_delete=models.CASCADE)

    # 等同于如下:
    # author_detail = models.ForeignKey(to='AuthorDetail', to_field='author_detail_id', unique=True,
    #                                   on_delete=models.CASCADE)

    class Meta:
        db_table = 'author'

        verbose_name = '作者'
        verbose_name_plural = verbose_name

GENDER_CHOICE = (
    (1, '男'),
    (2, '女')
)

class AuthorDetail(models.Model):
    author_detail_id = models.AutoField(primary_key=True)
    phone = models.BigIntegerField(verbose_name='手机号')
    gender = models.CharField(choices=GENDER_CHOICE, max_length=1, verbose_name='性别')
    addr = models.CharField(max_length=32, verbose_name='地址')

    class Meta:
        db_table = 'author_detail'

        verbose_name = '作者详情'
        verbose_name_plural = verbose_name

class Book(models.Model):
    book_id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=32, null=True, default='未知书名', db_index=True, verbose_name='书名')
    price = models.DecimalField(max_digits=5, decimal_places=2, verbose_name='价格')
    publish_time = models.DateTimeField(auto_now_add=True, verbose_name='添加时间')
    # to = Publish表示跟Publish表做关联(ForeignKey，一对多)
    # to_field ='id'表示跟哪个字段做关联,不写默认跟主键做关联
    publish = models.ForeignKey(to=Publish, to_field='publish_id', on_delete=models.CASCADE)
    # 自动创建出第三张表(这句话会自动创建第三张表)
    # authors在数据库中不存在该字段，没有to_field
    # 默认情况: 第三张表有id字段，当前Book表的主键和Author表的主键字段
    authors = models.ManyToManyField(to=Author)

    def __str__(self):
        return self.name

    class Meta:
        db_table = 'book'

        index_together = [
            ('book_id', 'name')
        ]

        unique_together = (
            ('name', 'publish')
        )

        verbose_name = '图书表'
        verbose_name_plural = verbose_name

补充说明

2.x版本的django
外键字段必须加 参数：on_delete
1.x版本不需要，默认就是级联删除
假设，
删除出版社，该出版社出版的所有图书也都删除，on_delete=models.CASCADE
删除出版社，该出版社出版的图书不删除，设置为空on_delete=models.SET_NULL,null=True
删除出版社，该出版社出版的图书不删除，设置为默认on_delete=models.SET_DEFAULT,default=0

2、一对一添加记录

# 添加作者和作者详情
author_detail = models.AuthorDetail.objects.create(phone='13911110298', gender=1, addr='南京')
models.Author.objects.create(name='allen', age=18, author_detail=author_detail)

author_detail = models.AuthorDetail.objects.create(phone='13913450298', gender=1, addr='南京')
models.Author.objects.create(name='jack', age=19, author_detail=author_detail)

总结

1、email可以不传email，本质就是varchar（admin中会判断）
2、新增作者详情：
author_detail=author_detail
author_detail=author_detail.author_detail_id
3、写在表模型中的author_detail字段，到数据库中会变成author_detail_id(ForeignKey)
4、查到author对象以后
author.author_detail 对象
author.author_detail_id id号，数字

3、一对多添加记录

# 添加出版社(北京出版社,上海出版社)
models.Publish.objects.create(name='北京出版社', phone=13945678123, email='123@qq.com', addr='北京')
models.Publish.objects.create(name='上海出版社', phone=13845678776, email='234@qq.com', addr='上海')

# 为北京出版社出版社增加图书西游记,三国演义,红楼梦
publish_obj = models.Publish.objects.get(name='北京出版社')
models.Book.objects.create(name='西游记', price=19.87, publish=publish_obj)
models.Book.objects.create(name='三国演义', price=28.64, publish_id=publish_obj.publish_id)
models.Book.objects.create(name='红楼梦', price=39.18, publish_id=2)

4、多对多添加，修改，删除记录

自动创建的表，表模型就拿不到，book.authors代指表模型

# 增加
# 给西游记这本书新增两个作者allen和jack
book_obj = models.Book.objects.get(name='西游记')
author_obj = models.Author.objects.get(name='allen')
author_obj2 = models.Author.objects.get(name='jack')
book_obj.authors.add(author_obj)  # 代指中间表book.authors

# 删除
# 为西游记这本书删除叫allen的作者
book_obj.authors.remove(author_obj)

# 先清空，再add,前提是不存在的作者
# 为西游记这本书重新设置两个作者allen和jack
book_obj.authors.set([author_obj, author_obj2])
book_obj.authors.set([author_obj.author_id, author_obj2.author_id])  # 也支持放id

# 清空
# 删除西游记这本书的所有作者
book_obj.authors.clear()

5、跨表查询的两种方式

基于对象的跨表查询：子查询
基于双下划线的跨表查询：关联查询，连表查询

6、基于对象的跨表查询（正向反向）

6.1、一对一

# 查询所有住址在南京的作者的姓名
author_detail_obj = models.AuthorDetail.objects.filter(addr='南京')
for author_detail in author_detail_obj:
    print(author_detail.author.name)
# (0.001) SELECT `author_detail`.`author_detail_id`, `author_detail`.`phone`, `author_detail`.`gender`, `author_detail`.`addr` FROM `author_detail` WHERE `author_detail`.`addr` = '南京'; args=('南京',)
# (0.001) SELECT `author`.`author_id`, `author`.`name`, `author`.`age`, `author`.`author_detail_id` FROM `author` WHERE `author`.`author_detail_id` = 1; args=(1,)
# (0.001) SELECT `author`.`author_id`, `author`.`name`, `author`.`age`, `author`.`author_detail_id` FROM `author` WHERE `author`.`author_detail_id` = 2; args=(2,)

# 查询allen作者的地址
# 正向
author_obj = models.Author.objects.get(name='allen')
print(author_obj.author_detail.addr)
# (0.001) SELECT `author`.`author_id`, `author`.`name`, `author`.`age`, `author`.`author_detail_id` FROM `author` WHERE `author`.`name` = 'allen'; args=('allen',)
# (0.001) SELECT `author_detail`.`author_detail_id`, `author_detail`.`phone`, `author_detail`.`gender`, `author_detail`.`addr` FROM `author_detail` WHERE `author_detail`.`author_detail_id` = 1; args=(1,)

6.2、一对多

# 查询主键为1的书籍的出版社所在的城市
book_obj = models.Book.objects.get(pk=1)  # 第一次查询
print(book_obj.publish.addr)
# 内部又执行了一次查询，根据publish_id查询publish
# (0.001) SELECT `book`.`book_id`, `book`.`name`, `book`.`price`, `book`.`publish_time`, `book`.`publish_id` FROM `book` WHERE `book`.`book_id` = 1; args=(1,)
# (0.001) SELECT `publish`.`publish_id`, `publish`.`name`, `publish`.`phone`, `publish`.`email`, `publish`.`addr` FROM `publish` WHERE `publish`.`publish_id` = 1; args=(1,)

# 北京出版社出版的所有书籍
publish_obj = models.Publish.objects.get(name='北京出版社')  # 第一次查询了出版社
book_list = publish_obj.book_set.all()
print(book_list)
# 表名小写_set,第二次,根据出版社id,查询所有书
# (0.000) SELECT `publish`.`publish_id`, `publish`.`name`, `publish`.`phone`, `publish`.`email`, `publish`.`addr` FROM `publish` WHERE `publish`.`name` = '北京出版社'; args=('北京出版社',)
# (0.001) SELECT `book`.`book_id`, `book`.`name`, `book`.`price`, `book`.`publish_time`, `book`.`publish_id` FROM `book` WHERE `book`.`publish_id` = 1 LIMIT 21; args=(1,)

6.3、多对多

# 西游记所有作者的名字以及手机号
book_obj = models.Book.objects.get(name='西游记')
authors_list = book_obj.authors.all()
for author in authors_list:
    print(author.name)
    print(author.author_detail.phone)
# (0.001) SELECT `book`.`book_id`, `book`.`name`, `book`.`price`, `book`.`publish_time`, `book`.`publish_id` FROM `book` WHERE `book`.`name` = '西游记'; args=('西游记',)
# (0.001) SELECT `author`.`author_id`, `author`.`name`, `author`.`age`, `author`.`author_detail_id` FROM `author` INNER JOIN `book_authors` ON (`author`.`author_id` = `book_authors`.`author_id`) WHERE `book_authors`.`book_id` = 1; args=(1,)
# (0.000) SELECT `author_detail`.`author_detail_id`, `author_detail`.`phone`, `author_detail`.`gender`, `author_detail`.`addr` FROM `author_detail` WHERE `author_detail`.`author_detail_id` = 1; args=(1,)
# (0.000) SELECT `author_detail`.`author_detail_id`, `author_detail`.`phone`, `author_detail`.`gender`, `author_detail`.`addr` FROM `author_detail` WHERE `author_detail`.`author_detail_id` = 2; args=(2,)

# 查询allen出过的所有书籍的名字
    author_obj = models.Author.objects.get(name='allen')
    book_list = author_obj.book_set.all()
    for book in book_list:
        print(book.name)
# (0.000) SELECT `author`.`author_id`, `author`.`name`, `author`.`age`, `author`.`author_detail_id` FROM `author` WHERE `author`.`name` = 'allen'; args=('allen',)
# (0.001) SELECT `book`.`book_id`, `book`.`name`, `book`.`price`, `book`.`publish_time`, `book`.`publish_id` FROM `book` INNER JOIN `book_authors` ON (`book`.`book_id` = `book_authors`.`book_id`) WHERE `book_authors`.`author_id` = 1; args=(1,)

# 地址为南京的作者写的所有书和名字
author_detail_obj = models.AuthorDetail.objects.filter(addr='南京')
for author_detail in author_detail_obj:
    print(author_detail.author.book_set.all())
    print(author_detail.author.name)
# (0.001) SELECT `author_detail`.`author_detail_id`, `author_detail`.`phone`, `author_detail`.`gender`, `author_detail`.`addr` FROM `author_detail` WHERE `author_detail`.`addr` = '南京'; args=('南京',)
# (0.000) SELECT `author`.`author_id`, `author`.`name`, `author`.`age`, `author`.`author_detail_id` FROM `author` WHERE `author`.`author_detail_id` = 1; args=(1,)
# (0.001) SELECT `book`.`book_id`, `book`.`name`, `book`.`price`, `book`.`publish_time`, `book`.`publish_id` FROM `book` INNER JOIN `book_authors` ON (`book`.`book_id` = `book_authors`.`book_id`) WHERE `book_authors`.`author_id` = 1 LIMIT 21; args=(1,)
# (0.001) SELECT `author`.`author_id`, `author`.`name`, `author`.`age`, `author`.`author_detail_id` FROM `author` WHERE `author`.`author_detail_id` = 2; args=(2,)
# (0.001) SELECT `book`.`book_id`, `book`.`name`, `book`.`price`, `book`.`publish_time`, `book`.`publish_id` FROM `book` INNER JOIN `book_authors` ON (`book`.`book_id` = `book_authors`.`book_id`) WHERE `book_authors`.`author_id` = 2 LIMIT 21; args=(2,)

# 地址为南京的作者写的所有书的出版社名字
author_detail_obj = models.AuthorDetail.objects.filter(addr='南京')
    for author_detail in  author_detail_obj:
        book_list = author_detail.author.book_set.all()
        for book in book_list:
            print(book.publish.name)
# (0.001) SELECT `author_detail`.`author_detail_id`, `author_detail`.`phone`, `author_detail`.`gender`, `author_detail`.`addr` FROM `author_detail` WHERE `author_detail`.`addr` = '南京'; args=('南京',)
# (0.000) SELECT `author`.`author_id`, `author`.`name`, `author`.`age`, `author`.`author_detail_id` FROM `author` WHERE `author`.`author_detail_id` = 1; args=(1,)
# (0.001) SELECT `book`.`book_id`, `book`.`name`, `book`.`price`, `book`.`publish_time`, `book`.`publish_id` FROM `book` INNER JOIN `book_authors` ON (`book`.`book_id` = `book_authors`.`book_id`) WHERE `book_authors`.`author_id` = 1; args=(1,)
# (0.000) SELECT `publish`.`publish_id`, `publish`.`name`, `publish`.`phone`, `publish`.`email`, `publish`.`addr` FROM `publish` WHERE `publish`.`publish_id` = 1; args=(1,)
# (0.000) SELECT `author`.`author_id`, `author`.`name`, `author`.`age`, `author`.`author_detail_id` FROM `author` WHERE `author`.`author_detail_id` = 2; args=(2,)
# (0.001) SELECT `book`.`book_id`, `book`.`name`, `book`.`price`, `book`.`publish_time`, `book`.`publish_id` FROM `book` INNER JOIN `book_authors` ON (`book`.`book_id` = `book_authors`.`book_id`) WHERE `book_authors`.`author_id` = 2; args=(2,)
# (0.001) SELECT `publish`.`publish_id`, `publish`.`name`, `publish`.`phone`, `publish`.`email`, `publish`.`addr` FROM `publish` WHERE `publish`.`publish_id` = 1; args=(1,)

7、多对多基于双下划线的跨表查询

7.1、一对多

# 查询北京出版社出版过的所有书籍的名字与价格
# 方式一
res = models.Publish.objects.filter(name='北京出版社').values('book__name','book__price')  # <QuerySet [{'book__name': '西游记', 'book__price': Decimal('19.87')}, {'book__name': '三国演义', 'book__price': Decimal('28.64')}]>
print(res)  # (0.001) SELECT `book`.`name`, `book`.`price` FROM `publish` LEFT OUTER JOIN `book` ON (`publish`.`publish_id` = `book`.`publish_id`) WHERE `publish`.`name` = '北京出版社' LIMIT 21; args=('北京出版社',)

# 方式二
res = models.Book.objects.filter(publish__name='北京出版社').values('name', 'price')  # <QuerySet [{'name': '西游记', 'price': Decimal('19.87')}, {'name': '三国演义', 'price': Decimal('28.64')}]>
print(res)  # (0.001) SELECT `book`.`name`, `book`.`price` FROM `book` INNER JOIN `publish` ON (`book`.`publish_id` = `publish`.`publish_id`) WHERE `publish`.`name` = '北京出版社' LIMIT 21; args=('北京出版社',)

7.2、多对多

# 查询allen出过的所有书籍的名字, 价格
# 方式一
res = models.Book.objects.filter(authors__name='allen').values_list('name', 'price')  # <QuerySet [('西游记', Decimal('19.87'))]>
print(res)  # (0.000) SELECT `book`.`name`, `book`.`price` FROM `book` INNER JOIN `book_authors` ON (`book`.`book_id` = `book_authors`.`book_id`) INNER JOIN `author` ON (`book_authors`.`author_id` = `author`.`author_id`) WHERE `author`.`name` = 'allen' LIMIT 21; args=('allen',)

# 方式二
res = models.Author.objects.filter(name='allen').values_list('book__name', 'book__price')  # <QuerySet [('西游记', Decimal('19.87'))]>
print(res)  # (0.001) SELECT `book`.`name`, `book`.`price` FROM `author` LEFT OUTER JOIN `book_authors` ON (`author`.`author_id` = `book_authors`.`author_id`) LEFT OUTER JOIN `book` ON (`book_authors`.`book_id` = `book`.`book_id`) WHERE `author`.`name` = 'allen' LIMIT 21; args=('allen',)


# 查询allen的手机号
# 方式一
res = models.Author.objects.filter(name='allen').values_list('author_detail__phone')  # <QuerySet [(18911110298,)]>
print(res)  # (0.001) SELECT `author_detail`.`phone` FROM `author` INNER JOIN `author_detail` ON (`author`.`author_detail_id` = `author_detail`.`author_detail_id`) WHERE `author`.`name` = 'allen' LIMIT 21; args=('allen',)

# 方式二
res = models.AuthorDetail.objects.filter(author__name='allen').values_list('phone')  # <QuerySet [(18911110298,)]>
print(res)  # (0.000) SELECT `author_detail`.`phone` FROM `author_detail` INNER JOIN `author` ON (`author_detail`.`author_detail_id` = `author`.`author_detail_id`) WHERE `author`.`name` = 'allen' LIMIT 21; args=('allen',)

8、连续跨表查询

# 查询北京出版社出版过的所有书籍的名字以及作者的姓名
# 方式一
res = models.Publish.objects.filter(name='北京出版社').values_list('book__name', 'book__authors__name')  # <QuerySet [('西游记', 'allen'), ('西游记', 'jack'), ('三国演义', None)]>
print(res)  # (0.001) SELECT `book`.`name`, `author`.`name` FROM `publish` LEFT OUTER JOIN `book` ON (`publish`.`publish_id` = `book`.`publish_id`) LEFT OUTER JOIN `book_authors` ON (`book`.`book_id` = `book_authors`.`book_id`) LEFT OUTER JOIN `author` ON (`book_authors`.`author_id` = `author`.`author_id`) WHERE `publish`.`name` = '北京出版社' LIMIT 21; args=('北京出版社',)

# 方式二
res = models.Book.objects.filter(publish__name='北京出版社').values_list('name','authors__name')  # <QuerySet [('西游记', 'allen'), ('西游记', 'jack'), ('三国演义', None)]>
print(res)  # (0.000) SELECT `book`.`name`, `author`.`name` FROM `book` INNER JOIN `publish` ON (`book`.`publish_id` = `publish`.`publish_id`) LEFT OUTER JOIN `book_authors` ON (`book`.`book_id` = `book_authors`.`book_id`) LEFT OUTER JOIN `author` ON (`book_authors`.`author_id` = `author`.`author_id`) WHERE `publish`.`name` = '北京出版社' LIMIT 21; args=('北京出版社',)

# 方式三
res = models.Author.objects.filter(book__publish__name='北京出版社').values_list('book__name', 'name')  # <QuerySet [('西游记', 'allen'), ('西游记', 'jack')]>
print(res)  # (0.001) SELECT `book`.`name`, `author`.`name` FROM `author` INNER JOIN `book_authors` ON (`author`.`author_id` = `book_authors`.`author_id`) INNER JOIN `book` ON (`book_authors`.`book_id` = `book`.`book_id`) INNER JOIN `publish` ON (`book`.`publish_id` = `publish`.`publish_id`) WHERE `publish`.`name` = '北京出版社' LIMIT 21; args=('北京出版社',)

# 手机号以139开头的作者出版过的所有书籍名称以及出版社名称
# 方式一
res = models.AuthorDetail.objects.filter(phone__startswith=139).values_list('author__book__name','author__book__publish__name')  # <QuerySet [('西游记', '北京出版社')]>
print(res)  # (0.001) SELECT `book`.`name`, `publish`.`name` FROM `author_detail` LEFT OUTER JOIN `author` ON (`author_detail`.`author_detail_id` = `author`.`author_detail_id`) LEFT OUTER JOIN `book_authors` ON (`author`.`author_id` = `book_authors`.`author_id`) LEFT OUTER JOIN `book` ON (`book_authors`.`book_id` = `book`.`book_id`) LEFT OUTER JOIN `publish` ON (`book`.`publish_id` = `publish`.`publish_id`) WHERE `author_detail`.`phone` LIKE BINARY '139%' LIMIT 21; args=('139%',)

# 方式二
res = models.Book.objects.filter(authors__author_detail__phone__startswith=139).values_list('name','publish__name')  # <QuerySet [('西游记', '北京出版社')]>
print(res)  # (0.000) SELECT `book`.`name`, `publish`.`name` FROM `book` INNER JOIN `book_authors` ON (`book`.`book_id` = `book_authors`.`book_id`) INNER JOIN `author` ON (`book_authors`.`author_id` = `author`.`author_id`) INNER JOIN `author_detail` ON (`author`.`author_detail_id` = `author_detail`.`author_detail_id`) INNER JOIN `publish` ON (`book`.`publish_id` = `publish`.`publish_id`) WHERE `author_detail`.`phone` LIKE BINARY '139%' LIMIT 21; args=('139%',)

# 方式三
res = models.Publish.objects.filter(book__authors__author_detail__phone__startswith=139).values_list('book__name','name')  # <QuerySet [('西游记', '北京出版社')]>
print(res)  # (0.001) SELECT `book`.`name`, `publish`.`name` FROM `publish` INNER JOIN `book` ON (`publish`.`publish_id` = `book`.`publish_id`) INNER JOIN `book_authors` ON (`book`.`book_id` = `book_authors`.`book_id`) INNER JOIN `author` ON (`book_authors`.`author_id` = `author`.`author_id`) INNER JOIN `author_detail` ON (`author`.`author_detail_id` = `author_detail`.`author_detail_id`) WHERE `author_detail`.`phone` LIKE BINARY '139%' LIMIT 21; args=('139%',)

9、安装模块相关

pip3 install xxx    
# 本质是去https://pypi.python.org/simple，搜这个模块，会根据你的平台下载在一个安装包（windows平台是whl）,下载完，再安装

# pip安装失败的情况
# 我们可以绕过它，有了whl文件以后，需要自己安装
# https://www.lfd.uci.edu/~gohlke/pythonlibs/#opencv
pip3 install xxx.whl   

# 官方库没有上传到pypi，官方也不给制作whl文件
# 如何安装  包(setup.py)
到达安装目录，setup.py所在的目录
python setup.py build
python setup.py install

# 配置清华源，豆瓣源的本质
豆瓣源会把pypi，包拉到自己的服务器上，以后你再下，去它的服务器上下，所以速度快

10、外键关系是否需要建立

1、关联字段与外键约束没有必然的联系(建关联字段是为了进行查询，建约束是为了不出现脏数据)
2、默认情况,关联关系建好以后,外键约束就自然建立了
3、工作开发过程中,外键约束一般不建(影响效率),都是人为约束(代码约束)
	db_constraint=False
4、表模型和数据库表的对应，不要直接修改表(可以这么做,但是不建议),要修改表模型,同步到表中