ORM之练习

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',
        },
    }
}
# 即为你的Django项目配置上一个名为django.db.backends的logger实例即可查看翻译后的SQL语句。 

在Python脚本中加载Django环境

import os

if __name__ == '__main__':
    # 加载Django项目的配置信息
    os.environ.setdefault("DJANGO_SETTINGS_MODULE", "web01.settings")
    # 导入Django，并启动Django项目
    import django
    django.setup()

    from app01 import models

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

ORM练习

models.py，手动插入数据

from django.db import models


# Create your models here.


class Author(models.Model):
    nid = models.AutoField(primary_key=True)
    name = models.CharField(max_length=32)
    age = models.IntegerField()

    # 与AuthorDetail建立一对一的关系
    authorDetail = models.OneToOneField(to="AuthorDetail", on_delete=models.CASCADE)


class AuthorDetail(models.Model):
    nid = models.AutoField(primary_key=True)
    birthday = models.DateField()
    telephone = models.BigIntegerField()
    addr = models.CharField(max_length=64)


class Publish(models.Model):
    nid = models.AutoField(primary_key=True)
    name = models.CharField(max_length=32)
    city = models.CharField(max_length=32)
    email = models.EmailField()


class Book(models.Model):
    nid = models.AutoField(primary_key=True)
    title = models.CharField(max_length=32)
    publishDate = models.DateField()
    price = models.DecimalField(max_digits=5, decimal_places=2)

    # 与Publish建立一对多的关系,外键字段建立在多的一方
    publish = models.ForeignKey(to="Publish", to_field="nid", on_delete=models.CASCADE)
    # 与Author表建立多对多的关系,ManyToManyField可以建在两个模型中的任意一个，自动创建第三张表
    authors = models.ManyToManyField(to='Author', )


class Employee(models.Model):
    name = models.CharField(max_length=32)
    age = models.IntegerField()
    salary = models.BigIntegerField()
    province = models.CharField(max_length=32)
    dept = models.ForeignKey(to="Dept")


class Dept(models.Model):
    name = models.CharField(max_length=32)

test.py

from django.test import TestCase

# Create your tests here.

import os


if __name__ == '__main__':
    os.environ.setdefault("DJANGO_SETTINGS_MODULE", "ormdemo.settings")

    import django

    django.setup()
    from app01 import models

    # #####################基于对象查询(子查询)##############################
    #                按字段（publish）
    # 一对多   book  ----------------->  publish
    #               <-----------------
    #                 book_set.all()

    正向查询按字段：
    1.查询python这本书籍的出版社的邮箱
    python = models.Book.objects.filter(title="python").first()
    print(python.publish.email)

    反向查询按   表名小写_set.all()
    2.苹果出版社出版的书籍名称
    publish_obj = models.Publish.objects.filter(name="苹果出版社").first()
    for obj in publish_obj.book_set.all():
        print(obj.title)

    按字段（authors.all()）
    多对多   book  ----------------------->  author
                  <-----------------------
                     book_set.all()

    3. 查询python作者的年龄
    python = models.Book.objects.filter(title="python").first()
    for author in python.authors.all():
        print(author.name, author.age)

    4. 查询alex出版过的书籍名称
    alex = models.Author.objects.filter(name="alex").first()
    for book in alex.book_set.all():
        print(book.title)

    按字段 authorDetail
    多对多   author  ---------------->  authordetail
                    <----------------
                     按表名  author

    5.查询alex的手机号
    alex = models.Author.objects.filter(name='alex').first()
    print(alex.authorDetail.telephone)

    6.查询家在山东的作者名字
    ad_list = models.AuthorDetail.objects.filter(addr="shandong")
    for ad in ad_list:
        print(ad.author.name)

    '''
    对应sql:

       select publish_id from Book where title="python"
       select email from Publish where nid =   1


    '''

    #####################基于queryset和__查询（join查询）############################
    正向查询：按字段  反向查询：表名小写

    1.查询python这本书籍的出版社的邮箱
    ret = models.Book.objects.filter(title="python").values("publish__email")
    print(ret.query)
    '''
    select publish.email from Book
    left join Publish on book.publish_id=publish.nid
    where book.title="python"
    '''

    2.苹果出版社出版的书籍名称
    方式1：
    ret1 = models.Publish.objects.filter(name="苹果出版社").values("book__title")
    print("111111111====>", ret1.query)
    方式2：
    ret2 = models.Book.objects.filter(publish__name="苹果出版社").values("title")
    print("2222222222====>", ret2.query)

    3.查询alex的手机号
    方式1：
    ret = models.Author.objects.filter(name="alex").values("authorDetail__telephone")
    方式2：
    models.AuthorDetail.objects.filter(author__name="alex").values("telephone")

    4.查询手机号以151开头的作者出版过的书籍名称, 以及书籍对应的出版社名称
    ret = models.Book.objects.filter(authors__authorDetail__telephone__startswith="151").values('title',"publish__name")
    print(ret.query)

    聚合与分组
    ---聚合---
    from django.db.models import Avg, Sum, Count, Max
    1. 查询所有书籍的价格总和
    ret = models.Book.objects.aggregate(s=Sum("price"))
    print(ret)
    2. 查询所有作者的平均年龄
    ret = models.Author.objects.aggregate(a=Avg("age"))
    print(ret)
    ---分组---
    1. 查询每一个部门 名称以及对应人数
    ret = models.Dept.objects.values('id').annotate(c=Count("employee__id")).values('name','c')
    print(ret.query)
    """
    关键点:
        1. queryset对象.annotate(),    它才有这个方法
        2. annotate进行分组统计,按前面的select的字段进行group by
        3. annotate()返回值依然是queryset对象,增加了分组统计之后的键值对

    """
    2. 查询每一个作者的名字 及出版过的书籍最高价格
    ret = models.Author.objects.values("name").annotate(m=Max("book__price")).values("name","m")
    print(ret)
    3. 查询每一个出版社出版过的书籍的平均价格
    ret = models.Publish.objects.values("name").annotate(a=Avg("book__price")).values("name","a")
    print(ret)
    4. 查询每一本书籍的作者个数
    ret = models.Book.objects.values("title").annotate(c=Count("authors__name")).values("title","c")
    print(ret)
    5. 查询每一个分类名称以及对应的文章数
    ret = models.Ger
    ret = models.Category.object.values("title").annotate(c=Count("article__nid")).values("title","c")

    6. 统计不止一个作者的图书名称
    ret = models.Book.objects.values("title").annotate(c=Count("authors__name")).filter(c__gt=1)
    print(ret)