1.多对多查询

class Book(models.Model):
    title = models.CharField(max_length=32)
    price = models.DecimalField(max_digits=6, decimal_places=2)  # 9999.99
    sale = models.IntegerField()
    kucun = models.IntegerField()
    
    def __str__(self):
        return self.title


class Author(models.Model):
    name = models.CharField(max_length=32, )
    books = models.ManyToManyField(Book)

    def __str__(self):
        return self.name

# 基于对象的查询
mjj = models.Author.objects.get(pk=1)
# print(mjj.books)  #  ——》  关系管理对象
# print(mjj.books.all())

book_obj = models.Book.objects.filter(title='桃花侠大战菊花怪').first()
# 不指定related_name
# print(book_obj.author_set)  #  ——》  关系管理对象
print(book_obj.author_set.all())
# related_name='authors'
# print(book_obj.authors)  #  ——》  关系管理对象
print(book_obj.authors.all())

#跨表字段查询
ret  =  models.Author.objects.filter(books__title='菊花怪大战MJJ')
# print(ret)

# 不指定related_name
ret = models.Book.objects.filter(author__name='MJJ')
# 指定related_name='authors'
ret = models.Book.objects.filter(authors__name='MJJ')
# related_query_name='xxx' 优先级最高,了解
ret = models.Book.objects.filter(xxx__name='MJJ')
# print(ret)

2.关系管理对象的方法

mjj = models.Author.objects.get(pk=1)

all()  所关联的所有的对象

# print(mjj.books.all())
set  设置多对多的关系  set()内可以放 [id,id],也可以是[ 对象，对象 ]
# mjj.books.set([1,2])
# mjj.books.set(models.Book.objects.filter(pk__in=[1,2,3]))


add  添加多对多的关系   (id,id)   (对象，对象),如果存在的话不会再次新增
# mjj.books.add(4,5)
# mjj.books.add(* models.Book.objects.filter(pk__in=[4,5])) ()不能是qureset,加*打散

remove 删除多对多的关系  (id,id)   (对象，对象)
# mjj.books.remove(4,5)
# mjj.books.remove(* models.Book.objects.filter(pk__in=[4,5]))

clear()   清除所有的多对多关系
# mjj.books.clear()

create()
# obj = mjj.books.create(title='跟MJJ学前端',pub_id=1)
# print(obj)
# book__obj = models.Book.objects.get(pk=1)
#
# obj = book__obj.authors.create(name='taibai')
# print(obj)

3.分组聚合

• aggregate 合计的
• annotate 注释

#聚合函数
from app01 import models

from django.db.models import Max, Min, Avg, Sum, Count  #avg平均数
#具体用法
ret = models.Book.objects.filter(pk__gt=3).aggregate(Max('price'),avg=Avg('price'))
print(ret)


# 分组
# 统计每一本书的作者个数
ret = models.Book.objects.annotate(count=Count('author')) # annotate 注释


# 统计出每个出版社的最便宜的书的价格
# 方式一
ret = models.Publisher.objects.annotate(Min('book__price')).values()
# 方式二
ret = models.Book.objects.values('pub_id').annotate(min=Min('price'))

4.F和Q

• F

from django.db.models import F

# 比较两个字段sale和kuncun的值
ret=models.Book.objects.filter(sale__gt=F('kucun'))

# 只更新sale字段
models.Book.objects.all().update(sale=100)

# 取某个字段的值进行操作
models.Book.objects.all().update(sale=F('sale')*2+10)

• Q(条件)
  • | 或
  • & 与
  • ~ 非

from django.db.models import Q

ret = models.Book.objects.filter(Q(Q(pk__gt=3) | Q(pk__lt=2)) & Q(price__gt=50))
print(ret)

5.事务

• trans 反式
• action 行为
• atomic 原子

from django.db import transaction

try:  #try要写在with之外,不然with捕获不到错误信息
    with transaction.atomic():
        # 进行一系列的ORM操作
        models.Publisher.objects.create(name='xxxxx')
        models.Publisher.objects.create(name='xxx22')

except Exception as e :
    print(e)

6.在Python脚本中调用Django环境

import os

if __name__ == '__main__':
    os.environ.setdefault("DJANGO_SETTINGS_MODULE", "BMS.settings")
    import django
    django.setup()

    from app01 import models

    books = models.Book.objects.all()
    print(books)

7.Django终端打印SQL语句

在Django项目的settings.py文件中，在最后复制粘贴如下代码：

LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'handlers': {
        'console':{
            'level':'DEBUG',
            'class':'logging.StreamHandler',
        },
    },
    'loggers': {
        'django.db.backends': {
            'handlers': ['console'],
            'propagate': True,
            'level':'DEBUG',
        },
    }
}