酱狗的杂七杂八(叁)

模型层

​ ORM操作

# 单表查询表
class User(models.Model):
    name = models.CharField(max_length=32)
    age = models.IntegerField()
    register_time = models.DateField()

# 多表查询表
class Book(models.Model):
    title = models.CharField(max_length=32)
    price = models.DecimalField(max_digits=8,decimal_places=2)
    publish_date = models.DateField(auto_now_add=True)
    # 外键关系
    publish = models.ForeignKey(to='Publish')	# 一对多关系
    authors = models.ManyToManyField(to='Author')  # 多对多关系
    
class Publish(models.Model):
    name = models.CharField(max_length=32)
    addr = models.CharField(max_length=32)
    email = models.EmailField()  # 对应就是varchar类型
    
class Author(models.Model):
    name = models.CharField(max_length=32)
    age = models.IntegerField()
    authordetail = models.OneToOneField(to='AuthorDetail')	# 一对一关系
    
class AuthorDetail(models.Model):
    phone = models.CharField(max_length=32)
    addr = models.CharField(max_length=32)

单表查询

# 新增数据
'''
	基于 create 创建
    user_obj = models.User.objects.create(name="Ethan", age=18, register_time="2019-6-12")
	基于对象的绑定方法创建
    user_obj = models.User(name="Jason", age=88, register_time="2019-6-6")
    user_obj.save()
    from datetime import datetime
    current_time = datetime.now()
    models.User.objects.create(name="Bilibili",age=20, register_time=current_time)

'''

# 修改数据
"""
	基于对象
    user_obj = models.User.objects.filter(name="Ethan").first()
    user_obj.age = 21
    user_obj.save()
	基于 queryset
    user_obj = models.User.objects.filter(name="Ethan").update(age=22)
"""

# 删除数据
"""
	基于 queryset
    models.User.objects.filter(name="Jason").delete()
	基于对象
    user_obj = models.User.objects.filter(name="Jason").first()
    user_obj.delete()
"""

# 查询数据
"""
	# all() 返回全部数据,为queryset类型
    result = models.User.objects.all()
    print(result)

    # filter(**kwargs) 条件查询，返回queryset
    result = models.User.objects.filter(name="Bilibili")
    print(result)

    # get(**kwargs) 与filter一样都是条件查询,直接返回对象，但匹配不到数据则报错，不建议使用
    result = models.User.objects.get(name="Ethan")
    print(result)

    # exclude(**kwargs) 返回非满足条件的对象
    result = models.User.objects.exclude(name="Ethan")
    print(result)

    # order_by(*field_name) 根据字段名排序,默认升序，加负号则降序
    result = models.User.objects.order_by("age")
    print(result)
    result = models.User.objects.order_by("-age")
    print(result)

    # reverse() 在被 order_by() 后使用，反序
    result = models.User.objects.order_by("age").reverse()
    print(result)

    # count() 返回查询数据的条数
    # result = models.User.objects.count()
    # result = models.User.objects.all().count()
    result = models.User.objects.filter(name="Ethan").count()
    print(result)

    # first() 返回第一个数据对象
    result = models.User.objects.first()
    print(result)
    result = models.User.objects.last()     # 返回最后一个对象
    print(result)
    result = models.User.objects.all()[0]   # 不支持负索引
    print(result)

    # exists() 判断数据是否为空
    result = models.User.objects.all().exists()
    print(result)
    result = models.User.objects.filter(name="BB").exists()
    print(result)

    # values() 以列表套字典的形式返回对应字段名的值
    result = models.User.objects.values("name")
    print(result)
    result = models.User.objects.values("name", "age")
    print(result)

    # values_list() 以列表套元组的形式返回对应字段名的值
    result = models.User.objects.values_list("name", "age")
    print(result)

    # distinct() 去重
    result = models.User.objects.all().distinct()
    print(result)
"""

# 双下划线查询
"""
	# 查询年纪大于30岁的用户
    result = models.User.objects.filter(age__gt=30)
    print(result)
    # 查询年纪小于30岁的用户
    result = models.User.objects.filter(age__lt=30)
    print(result)
    # 查询年纪大于等于20岁的用户
    result = models.User.objects.filter(age__gte=20)
    print(result)
    # 查询年纪小于等于20岁的用户
    result = models.User.objects.filter(age__lte=20)
    print(result)
    # 查询年纪为20或22或30的用户
    result = models.User.objects.filter(age__in=[20, 22, 30])
    print(result)
    # 查询年纪在20~30岁之间的用户
    result = models.User.objects.filter(age__range=[20, 30])
    print(result)

    # 查询指定日注册的用户
    result = models.User.objects.filter(register_time__day=12)
    print(result)

    # 查询名字中包含字母b的用户
    result = models.User.objects.filter(name__contains="n")     # 区别大小写
    print(result)
    result = models.User.objects.filter(name__icontains="n")    # 不区分大小写
    print(result)

    # 查询名字以Eth开头的用户
    result = models.User.objects.filter(name__startswith="Eth")   # 区分大小写
    print(result)

    # 查询名字以li结尾的用户
    result = models.User.objects.filter(name__endswith="li")
    print(result)
"""

多表查询

一对多

# 新增数据
"""
	直接写 id
    models.Book.objects.create(title="火影", price=99.9, publish_id=1)
    传数据对象
    publish_obj = models.Publish.objects.filter(pk=2).first()
    models.Book.objects.create(title="海贼王", price=88.8, publish=publish_obj)
"""
# 修改数据
"""
	queryset 修改
    models.Book.objects.filter(pk=3).update(publish_id=1)  # 传id

    publish_obj = models.Publish.objects.filter(pk=1).first()
    models.Book.objects.filter(pk=2).update(publish=publish_obj)  # 传对象

    对象修改
    book_obj = models.Book.objects.filter(pk=2).first()
    book_obj.publish_id = 2  # 传id
    book_obj.save()

    book_obj = models.Book.objects.filter(pk=2).first()
    publish_obj = models.Publish.objects.filter(pk=1).first()
    book_obj.publish = publish_obj  # 传对象
    book_obj.save()
"""
# 删除数据
"""
	queryset删除
	models.Book.objects.filter(pk=2).delete()
    models.Publish.objects.filter(pk=1).delete()
	
	对象删除
    book_obj = models.Book.objects.filter(pk=2).first()
    book_obj.delete()
"""

多对多

# 添加绑定关系 add
# add支持传数字或者对象，并且可以传多个
"""
 	直接绑定id
    book_obj = models.Book.objects.filter(pk=2).first()
    book_obj.authors.add(1)
    book_obj.authors.add(2,3)

    直接绑定对象
    book_obj = models.Book.objects.filter(pk=3).first()
    author_obj = models.Author.objects.filter(pk=5).first()
    author_obj2 = models.Author.objects.filter(pk=2).first()
    author_obj3 = models.Author.objects.filter(pk=1).first()
    book_obj.authors.add(author_obj)
    book_obj.authors.add(author_obj2, author_obj3)
"""
# 修改绑定关系 set
#  set(args)  args必须为可迭代对象
"""
	直接通过id修改
    book_obj = models.Book.objects.filter(pk=3).first()
    book_obj.authors.set((5,))
    book_obj.authors.set((1, 2, 3))  # 先清空后修改

    直接通过对象修改
    单个修改
    book_obj = models.Book.objects.filter(pk=2).first()
    author_obj = models.Author.objects.filter(pk=1).all()
    book_obj.authors.set(author_obj)
    批量修改
    author_obj2 = models.Author.objects.filter(pk=2).first()
    author_obj3 = models.Author.objects.filter(pk=3).first()
    author_obj4 = models.Author.objects.filter(pk=5).first()
    book_obj.authors.set((author_obj2, author_obj3, author_obj4))
"""
# 删除绑定关系 remove
"""
	通过id删除
    book_obj = models.Book.objects.filter(pk=3).first()
    #book_obj.authors.remove(1)
    # book_obj.authors.remove(2,3)

    通过对象删除
    book_obj = models.Book.objects.filter(pk=3).first()
    author_obj = models.Author.objects.all().first()
    book_obj.authors.remove(author_obj) # 单个删除

    author_list = models.Author.objects.all() # 批量删除
    book_obj.authors.remove(*author_list)  # 需要将queryset打散
"""
# 清空 clear()
"""
	book_obj = models.Book.objects.filter(pk=3).first()
    book_obj.authors.clear()
"""

# 基于对象的表查询
"""正向查询"""
"""
	查询书籍是火影忍者的出版社邮箱
    book_obj = models.Book.objects.filter(title="火影").first()
    email = book_obj.publish.email
    print(email)

	查询书籍是口袋妖怪的作者的姓名
    book_obj = models.Book.objects.filter(title="口袋妖怪").first()
    name = book_obj.authors  # app03.Author.None
    name = book_obj.authors.all()
    print(name)

	查询作者为阿大的电话号码
    author_obj = models.Author.objects.filter(name="阿大").first()
    phone = author_obj.authordetail.phone
    print(phone)
"""
"""反向查询"""
"""
	查询出版社是Bilibili的书籍         #一对多的反向查询
    publish_obj = models.Publish.objects.filter(name="Bilibili").first()
    book = publish_obj.book_set.all()
    print(book)

	查询作者阿二写过的所有书籍           #多对多的反向查询
    author_obj = models.Author.objects.filter(name="阿二").first()
    books = author_obj.book_set.all()
    print(books)

	查询电话号码是999的作者姓名          #一对一的反向查询
    detail_obj = models.AuthorDetail.objects.filter(phone=999).first()
    name = detail_obj.author.name
    print(name)
"""

# 基于下划线的查询
"""正向查询"""
"""
	查询书籍为海贼王的出版社地址
    address = models.Book.objects.filter(title="海贼王").values('publish__addr')
    print(address)

	查询书籍为名侦探柯南的作者的姓名
    name = models.Book.objects.filter(title="名侦探柯南").values('authors__name')
    print(name)

	查询作者为阿三的家乡
    address = models.Author.objects.filter(name="阿三").values('authordetail__addr')
    print(address)
"""
"""反向查询"""
"""
	查询 Cilicili 出版社出版的书籍
    books = models.Publish.objects.filter(name="Cilicili").values('book__title')
    print(books)

	查询 电话号码为 114 的作者姓名
    name = models.AuthorDetail.objects.filter(phone=114).values('author__name')
    print(name)

	查询数据为口袋妖怪的作者的电话号码
    phone = models.Book.objects.filter(title="口袋妖怪").values('authors__authordetail__phone')
    print(phone)
"""

# 聚合查询
"""
	from django.db.models import Max, Min, Count, Sum, Avg

	查询指定或者所有书籍的作者个数
    count = models.Book.objects.aggregate(count_num=Count('authors'))
    count = models.Book.objects.filter(pk=3).aggregate(count_num=Count('authors'))
    print(count)

	查询所有出版社出版的书的平均价格
    avg_price = models.Publish.objects.aggregate(avg_price=Avg('book__price'))
    print(avg_price)
"""

# 分组查询
"""
	统计每个出版社出版的书的平均价格
    price = models.Publish.objects.annotate(price=Avg('book__price')).values('price')
    print(price)

	统计各出版社最便宜的书的价格
    price = models.Publish.objects.annotate(price = Min('book__price')).values('price')
    print(price)

	统计每个作者书的总价
    sum = models.Author.objects.annotate(sum_price=Sum('book__price')).values('sum_price')
    print(sum)

"""