Django ORM多表操作

准备数据表

models.py

from django.db import models


# 作者表
class Author(models.Model):
    name = models.CharField(max_length=32)
    age = models.IntegerField()
    # 一对一, to_field 关联字段不填默认是 id,
    # on_delete=models.CASCADE 级联删除 models.SET_NULL 不级联删除但是设置为空
    # 生成表字段之后,会字段变为authorDetail_id
    # django2.x中必须要写on_delete, 否则报错
    authorDetail = models.OneToOneField(to="AuthorDetail", to_field="id", on_delete=models.CASCADE)

    def __str__(self):
        return self.name



# 作者详细信息表
class AuthorDetail(models.Model):
    birthday = models.DateField()
    telephone = models.CharField(max_length=32)  # 设置为字符串,方便做模糊查询
    addr = models.CharField(max_length=64)

    def __str__(self):
        return self.addr


# 出版社表
class Publish(models.Model):
    name = models.CharField(max_length=32)
    city = models.CharField(max_length=32)
    email = models.EmailField()  			# 实际是charField

    def __str__(self):
        return self.name


# 书籍表
class Book(models.Model):
    title = models.CharField(max_length=32)
    publishDate = models.DateField()
    price = models.DecimalField(max_digits=5, decimal_places=2)
    # 一对多 related_name 代替反向查询表名
    publish = models.ForeignKey(
        to="Publish", 
        on_delete=models.CASCADE,
        # related_name='xx'				# 起别名
    )
    # 多对多
    authors = models.ManyToManyField(to='Author')
    # 点赞
    good = models.IntegerField(default=1)
    # 评论
    comment = models.IntegerField(default=1)

    def __str__(self):
        return f'{self.id:0>3}---{self.title}'

视图

views.py

一对一

增

# 方式1
new_author_detail = models.AuthorDetail.objects.create(
    birthday='1979-08-08',
    telephone='1383838438',
    addr='北京市'
)
models.Author.objects.create(
    name='李四',
    age=40,
    authorDetail=new_author_detail
)

# 方式2
obj = models.AuthorDetail.objects.filter(addr='武汉市').first()
models.Author.objects.create(
    name='张三',
    age=20,    
    authorDetail_id=obj.id
)

删

# 级联删除
models.AuthorDetail.objects.get(id=2).delete()
# 单独删除
models.Author.objects.get(id=4).delete()

改

author_obj = models.Author.objects.filter(id=5).update(
    age = 10,
    authorDetail = models.AuthorDetail.objects.get(id=5)
    # authorDetail_id = models.AuthorDetail.objects.get(id=5).id
)

查

基于对象的查询

# 1 正向查询，通过属性(字段)名
author_obj = models.Author.objects.filter(name='alex').first()
print(author_obj.authorDetail)              # authorDetail 对象
print(author_obj.authorDetail.telephone)

# 2 反向查询,通过小写的类(表)名
author_detail_obj = models.AuthorDetail.objects.get(telephone='1383838438')
print(author_detail_obj.author)             # author 对象
print(author_detail_obj.author.name)

基于双下划线的查询

# 正向查询1
obj = models.Author.objects.filter(name='alex').values('authorDetail__telephone')
print(obj)
print(obj.first()['authorDetail__telephone'])
# 正向查询2
obj = models.Author.objects.get(authorDetail__telephone='1383838438')
print(obj.name)
# 反向查询1
obj = models.AuthorDetail.objects.filter(author__name='alex').first()
print(obj.telephone)
# 反向查询2
obj = models.AuthorDetail.objects.filter(telephone='1383838438').values('author__name')
print(obj)
print(obj[0]['author__name'])

一对多

增

# 方式1
models.Book.objects.create(
    title='床头故事',
    publishDate='2019-07-22',
    price=3,
    publish=models.Publish.objects.get(id=3),
)
# 方式2
models.Book.objects.create(
    title='床头故事2',
    publishDate='2019-07-22',
    price=3.5,
    publish_id = models.Publish.objects.get(id=3).id,
)

删

# 级联删除
models.Publish.objects.get(id=3).delete()
# 单独删除
models.Book.objects.get(id=5).delete()

改

models.Book.objects.filter(pk=6)		# pk(primary key)，不管字段名是什么，pk代表主键
models.Book.objects.filter(id=6).update(
    publish = models.Publish.objects.get(id=5),
    # publish_id = models.Publish.objects.get(id=5).id
)
# ORM无级联更新(一对一, 一对多)
# models.Publish.objects.filter(id=4).update(id=1)  # 报错

查

基于对象的查询

# 1 正向查询，通过属性(字段)名
book_obj = models.Book.objects.filter(title='床头的故事').first()
print(book_obj.publish)
print(book_obj.publish.name)

# 2 反向查询,通过小写的类(表)名_set
publish_obj = models.Publish.objects.filter(name='太白出版社').first()
# 类似控制器，可以调用 all, filter, get, exclude
print(publish_obj.book_set.all())

# 3 反向查询 使用 related_name
# publish_obj = models.Publish.objects.filter(name='太白出版社').first()
# print(publish_obj.xx.all())

# 查询太白出版社出版了哪些书，及对应的作者名和作者地址
objs  = models.Book.objects.filter(publish__name='太白出版社')
lst = []
for obj in objs:
    dic = {
        'title': obj.title,
        'author_info' : []
    }
    for author in obj.authors.all():
        ath = {
            'name': author.name,
            'addr': author.authorDetail.addr
        }
        dic['author_info'].append(ath)
    lst.append(dic)
print(lst)

基于双下划线的查询

obj = models.Book.objects.filter(title='床头的故事').values('publish__name')
print(obj)
obj = models.Publish.objects.filter(book__title='床头的故事')
print(obj.name)
obj = models.Publish.objects.filter(xx__title='床头的故事')
print(obj)

obj = models.Publish.objects.filter(name='太白出版社').values('book__title', 'book__authors__name', 'book__authors__authorDetail__addr')
print(obj)

多对多

增

# 方式1
book_obj = models.Book.objects.get(id=4)
# author_obj对应的id
book_obj.authors.add(3, 4)
# 方式2
book_obj = models.Book.objects.get(id=5)
author1 = models.Author.objects.get(id=1)
author2 = models.Author.objects.get(id=2)
book_obj.authors.add(author1,author2)

删

# 方式1 remove(对应Author表的id字段)
book_obj = models.Book.objects.get(id=6)
book_obj.authors.remove(1)
# 删除多个
book_obj.authors.remove(3,5)

# 方式2
author = models.Author.objects.get(id=5)
book_obj.authors.remove(author)
book_obj.authors.clear()          # 删除所有
book_obj.authors.set(['5','3'])   # 删除所有然后添加(更新)

改

book_obj = models.Book.objects.get(id=6)
book_obj.authors.set(['5','3'])   # 删除所有然后添加(更新)

查

基于对象的查询

# 1 正向查询
book_obj = models.Book.objects.filter(title='床头的故事').first()
print(book_obj.authors.all())

# 2 反向查询
author_obj = models.Author.objects.filter(name='alex').first()
print(author_obj.book_set.all())

基于双下划线的查询

obj = models.Author.objects.filter(book__title='床头的故事')
print(obj)

obj = models.Book.objects.filter(title='床头的故事').values('authors__name')
print(obj)

obj = models.Book.objects.filter(authors__name='alex')
print(obj)

obj = models.Author.objects.filter(name='alex').values('book__title')
print(obj)

聚合、分组、F查询、Q查询

from django.db.models import Avg, Max, Min, Sum, Count, F, Q

聚合查询

obj = models.Book.objects.all().aggregate(Avg('price'))
print(obj)          # {'price__avg': Decimal('10.150000')}

# 起别名，平均值
obj = models.Book.objects.all().aggregate(a=Avg('price'))
print(obj)          # {'a': Decimal('10.150000')}

# 最大值
obj = models.Book.objects.all().aggregate(Max('price'), Min('price'))
print(obj)

# 求和
obj = models.Book.objects.all().aggregate(Sum('price'))
print(obj)

# 计数
obj = models.Book.objects.all().aggregate(Count('id'))
print(obj)

分组查询

#                                        分组条件               要统计的值
obj = models.Book.objects.all().values('publish').annotate(Avg('price'))
print(obj)

obj = models.Publish.objects.annotate(Avg('book__price')).values()
print(obj)

ret = models.Book.objects.all().values('publish_id').annotate(Count('id'))
print(ret)

F 查询

# 查询book表中点赞数大于评论数的书名
obj = models.Book.objects.filter(good__gt=F('comment'))
print(obj)

# 支持4则运算
obj = models.Book.objects.filter(good__gt=F('comment')*2)
print(obj)

# 让所有书籍价格+20
models.Book.objects.all().update(price = F('price')+20)

Q 查询

# 查询点赞数小于80或者评论数大于80的书籍书籍
ret = models.Book.objects.filter(~Q(good__gt=80)|Q(comment__gt=80))
print(ret)
# | 或, & 且 ~非

# 方式2
q_obj = Q()
q_obj.connector='OR'
q_obj.children.append(('good__gt', 80))
q_obj.children.append(('comment__gt', 80))

ORM执行原生SQL

from django.db import connection


ret = models.Publish.objects.raw('select * from app01_publish')
for i in ret:
    print(i.name,i.city)

cursor = connection.cursor()
cursor.execute("select * from app01_book")
ret = cursor.fetchall()
print(ret)

# 查看SQL语句
print(connection.queries)