ORM之练习

灰太狼 2021-11-02 09:34 295阅读 0赞

## 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)

转载于:https://www.cnblogs.com/lxfpy/p/10893253.html