• [django]django的orm查询


    实体 实体
    出版社 category
    作者 tag
    文章

    先学习一下基础的增删查改

    django orm增删改查: https://www.cnblogs.com/iiiiiher/articles/8870906.html

    
    ## 增加
    - 方法1:
    People.objects.create(usernmae='maotai',age=22);
    
    - 方法2:
    p = People()
    p.name = "maomao"
    p.age = 23
    p.save()
    
    
    ## 更新
    
    ## 查询: crud,重点是查询
    - 查询所有列
    People.objects.all()
    [obj,obj2]
    
    - 查询某几列
    People.objects.all().values("id","username")
    [{name:maotai,age:22},{name:maomao,age:22}]
    
    People.objects.all().values_list("id","username")
    [(1, 'maotai'), (2, 'maomai')]
    
    
    People.objects.all()
    <QuerySet [<People: name: maotai, age: 22>, <People: name: maomai, age: 23>, <People: name: maodou, age: 24>]>
    People.objects.all().values('id','username')
    <QuerySet [{'id': 1, 'username': 'maotai'}, {'id': 2, 'username': 'maomai'}, {'id': 3, 'username': 'maodou'}]>
    People.objects.all().values_list('id','username')
    <QuerySet [(1, 'maotai'), (2, 'maomai'), (3, 'maodou')]>
    
    
    ## 命令行查看真实sql
    print(People.objects.all().query)
    
    from django.shortcuts import render,HttpResponse
    
    # Create your views here.
    from app01.models import *
    
    def index(request):
    
        return render(request,"index.html")
    
    def addbook(request):
    
        # b=Book(name="python基础",price=99,author="mao",pub_date="2017-12-12")
        # b.save()
        Book.objects.create(name="linux",price=78,author="maotai",pub_date="2016-12-12")
        #Book.objects.create(**dic)
    
        return HttpResponse("添加成功")
    
    
    def update(request):
    
        Book.objects.filter(author="mao").update(price=999)
    
        # b=Book.objects.get(author="maotai")
        # b.price=120
        # b.save()
        # print(b)#<QuerySet [<Book: Book object>]>
        # print(type(b))
    
        return HttpResponse("修改成功!")
    
    
    def delete(req):
    
        Book.objects.filter(author="maotai").delete()
    
        return HttpResponse("success")
    
    def select(req):
        # book_list=Book.objects.all()
        # print(book_list)
        # print(book_list[0])#Book object
        # book_list = Book.objects.filter(id=2)
        # book_list = Book.objects.all()[::2]
        # book_list = Book.objects.all()[::-1]
        # book_list = Book.objects.first()
        # book_list = Book.objects.last()
        # book_list = Book.objects.get(id=2)#只能取出一条记录时才不报错
        # ret1=Book.objects.filter(author="maotai").values("name")
        # ret2=Book.objects.filter(author="mao").values_list("name","price")
        # book_list=Book.objects.exclude(author="mao").values("name","price")
        # print(ret1)
        # print(ret2)
    
        # book_list= Book.objects.all().values("name").distinct()
        # book_count= Book.objects.all().values("name").distinct().count()
        # print(book_count)
    
        #万能的  __
    
        # book_list=Book.objects.filter(price__gt=50).values("name","price")
        book_list=Book.objects.filter(name__contains="P").values_list("name","price")
    
    
        return render(req,"index.html",{"book_list":book_list})
    

    终极吐血整理版--2018年4月23日 17:56:27

    实体 实体
    出版社 category
    作者 tag
    文章
    一对多
        创建
            2种方法
        查询
            1,python这本书对应的出版社的名字
            2,邮电出版社出版的所有书
    多对多
        创建(目的创建book和author关联关系)
            book_obj.publish # 这本书对应的出版社,对象
            book_obj.author  # 这个书的作者集合, querysetset
        
            book_obj.objects.all()
            book_obj.objects.add(*author_obj)
        查询
            1,通author找出他的book: 龟叔写的书找出来
            2,python这本书的作者
            
    -
    1, 一对多: 
        1, python这本书对应的出版社的名字
            Book.objects.filter(name="python").values('publish__name')
            Publish.objects.filter(book__name="python").values('name')
    
    
        2, 邮电出版社出版的所有书
            print(Publish.objects.filter(name="邮电出版社").values('book__name'))
            print(Book.objects.filter(publish__name="邮电出版社").values('name'))
    
    -
    
    2, 多对多查询
        1,通author找出他的book: 龟叔写的书找出来
            print(Book.objects.filter(author__name="龟叔"))
            print(Author.objects.filter(name="龟叔").values('book__name'))
    
        2,python这本书的作者
            print(Book.objects.filter(name="python").values('author__name'))
            print(Author.objects.filter(book__name="python").values('name'))
    
    

    聚合查询和分组查询

    聚合:
    
    from django.db.models import Avg, Max, Min, Sum, Count
    -
        print(Book.objects.aggregate(Min('price')))
        print(Book.objects.aggregate(Sum('price')))
        print(Book.objects.aggregate(Avg('price')))
    
        龟叔出的书的价格总和
            print(Book.objects.filter(author__name="龟叔").aggregate(Sum('price')))
            print(Book.objects.filter(author__name="龟叔").aggregate(guishu_money = Sum('price')))
    -
        龟叔出了几本书?
            print(Book.objects.filter(author__name="龟叔").aggregate(Count('name')))
    
    -
    分组聚合:  
        每个作者的出的书的价格和.
            print(Book.objects.all().values("author__name").annotate(Sum("price")))
            print(Book.objects.values("author__name").annotate(Sum("price"))) #Book.objects.values分组,聚合
            
            # <QuerySet [{'author__name': None, 'price__sum': 108}, {'author__name': '林纳斯', 'price__sum': 89}, {'author__name': '毛台', 'price__sum': 89}, {'author__name': '龟叔', 'price__sum': 188}]>
    -
    
            SELECT
                "app03_author"."name",
                SUM("app03_book"."price") AS "price__sum"
            FROM
                "app03_book"
            LEFT OUTER JOIN "app03_book_author" ON (
                "app03_book"."id" = "app03_book_author"."book_id"
            )
            LEFT OUTER JOIN "app03_author" ON (
                "app03_book_author"."author_id" = "app03_author"."id"
            )
            GROUP BY
                "app03_author"."name"
            LIMIT 21;
    
        查各个出版社出版的最便宜的书的价格(分组和聚合不一定用在复合查询)
            print(Publish.objects.values('name').annotate(Min("book__price")))
            print(Publish.objects.values('name').annotateabc=Min("book__price")))
    

    F查询和Q查询

    - F/Q 且,或,非关系查询
    
    from django.db.models import F,Q
    
    
        1, 逗号: 且的关系
            print(Book.objects.get(name="python", price=77))
    
        2, F查询: 每本书price+10
            # Book.objects.all().update(price=price+10) #这种是错误的
            Book.objects.all().update(price=F('price') + 10)
        
        3, Q查询: 或的关系
            print(Book.objects.filter(Q(name='go') | Q(price=109)).values('name', 'price'))
        4, ~: 非的关系
            print(Book.objects.filter(Q(name='go') | ~Q(price=109)).values('name', 'price'))
        5,__contains: 包含关系
            print(Book.objects.filter(Q(name__contains='go')).values('name', 'price'))
    
    

    queryset的特性([缓存]惰性机制)

    queryset缓存:
    
    1,只有调用结果时,才会执行sql
        res = Book.objects.all() # 可以理解为一堆sql
        print(res)               # 此时才会执行sql
    
    2,queryset具备缓存特性,
        res = Book.objects.all() 
        for i in res: #第一次去执行sql,得到结果缓存
            print(i)
            
        print("*"*100)#第二次直接从缓存中取出数据
        for i in res:
            print(i)
            
    3,缓存带来的危害, 中途遇到更新时,无法获取到最新值.
        res = Book.objects.all()
        for i in res:
            print(i.price) # 打印原价
    
        Book.objects.all().update(price=200)
    
        for i in res:
            print(i.price) # 更新原价
    4,加入只想判断结果res.exists()
    
        res = Book.objects.filter(price=100)
        if res.exists(): #不会将一坨数据扔进缓存
            print('ok')
    
    5.如果结果非常巨大(返回迭代器)
        objs = Book.objects.all().iterator()
    注: 迭代器只允许迭代一次.
    
    可迭代
        # print(objs[1])
        # print(objs[1:4])
        # print(objs[::-1])
    可切片
    -
    

    models操作

    参考

    思路:
    
    给publish插数据
    给book插数据
    给author插数据
    
    给book_author插数据
        调用外键:     Book.publish # 这本书对应出版社的一条记录(对象)
        调用多对多键: Book.author  # 这本书绑定关系的作者的记录(queryset)
        book_obj.author.all()
        book_obj.author.add(author_obj)
        book_obj.author.remove(author_obj)
    
        author_obj = Author.objects.all()
        book_obj.author.add(*author_obj)
        
    通过book找它的author: 
        python这本书的作者
            Author.objects.filter(book__name="python")
    
    通过author找它的book
        龟叔出过的书的name+price(用系统自带的manytomany方法)
            Book.objects.filter(author__name="龟叔").values('name', 'price')
    
    
    自定义book_author表:
        查出id=2的book的author:(两张表借助第三张表book_author取数据)
        
        方法1:
            book_obj = Book.objects.get(id=2)
            book_obj.book_author_set.all() # book_author的queryset对象
            print(book_obj.book_author_set.all()[0].author) # 查出作者.
        方法2:
            Book.objects.filter(book_author__author_id__name="龟叔").values('name','price')
    
    
    多对多:
        创建多对多关系:
            author = models.ManytoManyFiled("Author")
            
        book对象它的所有的关联作者: obj = book_obj.authors.all()
            绑定多对多的关系:       obj.add(*QuerySet)
                                    obj.add(author_obj)
            如果想向第三张表插入值的方式绑定:
                手动创建第三张表
                    class Book_Author(models.Model):
                        book_id = models.ForeignKey(Book, on_delete=models.CASCADE)
                        author_id = models.ForeignKey(Author, on_delete=models.CASCADE)
                    Book_Author.objects.create(book_id=2,author_id=3)
        掌握: 通过filter value(双下划线)实现多对多的关联查询
            龟叔出过的书的name+price(用系统自带的manytomany方法)
                Book.objects.filter(author__name="龟叔").values('name', 'price')
    
    1.聚合
    2.分组
    3.F查询和Q查询
    
    多表查询:
        一对多
            1,sql
            2,set
            3,__
                图书---2种-->出版社
                图书<--1种--出版社
    
        多对多
            1,自动
                操作第三张表, 为第三张表添加记录.
                调用外键:     Book.publish # 这本书对应出版社的一条记录(对象)
                
                调用多对多键: Book.author  # 这本书绑定关系的作者的记录(对象)
            2,手动
            
            通过book找它的author
            通过author找它的book
            
            给book添加author记录
                即手动添加book_author关系
                    1,取出一本书 book_obj
                    2,取出一个作者author_obj
                    3,book_obj.add(author_obj)
    
    F查询和Q查询
    
    1.创建
        1,p = People
          p.name = "mt"
          p.save()
        2,People.objects.create(name="mt")
    
    2.查询(重点****)
        def home(request):
            book_list = Book.objects.filter(id=1)
            book_list = Book.objects.exclude(author='maotai').values('name', 'price')
            
            # filter/all 取出的是一个queryset
            book_list = Book.objects.all()
            book_list = Book.objects.all()[::2]
            book_list = Book.objects.all()[::-1]
    
            # first/last get取到的是一个实例
            book_list = Book.objects.first()
            book_list = Book.objects.last()
            book_list = Book.objects.get(id=1) # 结果超过一个后会报错
    
            res = Book.objects.filter(author='maotai').values("name")              # 结果是字典形式,kv
            res = Book.objects.filter(author='maotai').values_list("name","price") # 结果是列表形式,好像只有key
            # select name from book where author='maotai';
    
            book_list = Book.objects.filter(author='maotai').values("name").distinct() # filter某些字段时, 去重
            book_count = Book.objects.all().values("name").count()   # 记录条数
            
            # 模糊匹配
            book_list = Book.objects.filter(name__contains="mao").values_list("name","price")
            book_list = Book.objects.filter(id__gt=5).values_list('name','price')
    
    3,删除
        book_list = Book.objects.exclude(author='maotai').delete()
    
    - 方法小结:
    all()&filter()
        queryset.update()
        queryset.delete()
    
    first/last&get
        obj.save()
    
    
    思路:
    创建publish出版社/图书表(一对多)---添加图书的两种方法(主要是给publish_id赋值)
                                        1, 
                                            Book.objects.create(name="流畅的python", price=89, pub_date="2017-06-06", publish_id=3)
                                        2, 
                                            publish_obj = Publish.objects.filter(name="人民出版社")[0]
                                            Book.objects.create(name="go action", price=22, pub_date="2018-04-04", publish=publish_obj)
    -
    1. 通过book查publish消息?
        1,def __str__
        2,查询publish的其他字段
        
    2. 查看人民出版社出版过哪些书?
        1,方法1: 正向
        2,方法2: 逆向查: 利用django自带的book_set
        
    多表操作(一对多):
        1,正向查询
            pub_obj = Publish.objects.get(name="人民出版社")
            res = Book.objects.filter(publish=pub_obj).values('name', 'price')
        2,逆向查询,利用django提供的book_set
            pub_obj = Publish.objects.filter(name="人民出版社")[0]
            print(pub_obj.book_set.all())
            print(type(pub_obj.book_set.all()))
    
    - 
    外键两种写法:
        1,外键加引号
            publish = models.ForeignKey("publish", on_delete=models.CASCADE, verbose_name="出版社")
        2,外键不加引号.(外键model需要先定义)
            publish = models.ForeignKey(Publish, on_delete=models.CASCADE, verbose_name="出版社")
    
    
    
    
    通过__方法查找结果:
        1,逆向查找: 外键__名称
        - 查出人民出版社出版的图书
        res = Book.objects.filter(publish__name="人民出版社").values('name', 'price') # 这里的publish是外键名
        print("3,通过__方法查找结果: ", res)
        print("-" * 50)
    
        2,正向查找: 主表__name
        2.1
        # python这本书的出版社名字
        print(Publish.objects.filter(book__name="python").values('name')) # 这里的book是表名, 这里name是publish表里的字段name
        return HttpResponse("查询完毕, 结果请查看console")
    
        2.2通过逆向查
        res = Book.objects.filter(name="python").values("publish__name") # select publish__name
    
    
    栗子
    思路: 一对多: 这个出版社出版过哪些书? -- 这本书是哪个出版社出版的?
          多对多: id=3的作者的信息
    
        1. 人民出版社出版的图书的名字
            1.1
            pub_obj = Publish.objects.get(name="人民出版社")
            res = Book.objects.filter(publish=pub_obj).values('name', 'price')
            
            1.2
            pub_obj = Publish.objects.filter(name="人民出版社")[0]
            print(pub_obj.book_set.all())
            
            1.3
            res = Book.objects.filter(publish__name="人民出版社").values('name', 'price')
        
    
        2. python这本书的出版社名字
            2.1逆向(主表名__)
            res = Publish.objects.filter(book__name="python").values('name')
        
            2.2正向(外键名__)
            res = Book.objects.filter(name="python").values("publish__name")
    
        3. 在北京出版社出版的书
        Book.objects.filter(publish__city__="北京").values('name')
        
        4. 2017年上半年出的书的出版社名字
        res = Book.objects.filter(pub_date__lt="2017-07-01", pub_date__gt="2017-01-01").values('publish_name')
    
        5, 多对多的关系, id=3的所有作者信息
        book_obj = Book.objects.get(id=3)
        book_obj.author.all()
        print(type(book_obj.author.all()))
    
    

    数据模拟:

    publish
    author
    book
        Foreign_key: publish
        ManyToMany:  author
    
    核心点:
        一对多创建关系
        一对多查询
    
    
    publish:
    1   人民出版社   北京
    2   邮电出版社   西安
    3   机械出版社   上海
    
    
    book:
    id  name    price    pub_date     publish
    1   python  89       2017-01-07       1
    2   go      99       2017-04-01       2
    3   java    39       2017-06-01       3
    4   ios     69       2017-12.24       3
    
    
    
    Author:
    id  name    age
    1   龟叔    17
    2   林纳斯  20
    3   毛台    25
    1   Jeff    42
    
    
    book_author:
    id  book_id    author_id
    1   1          1
    2   1          2
    3   2          3
    4   2          3
    
    
    - models
    
    class Publish(models.Model):
        name = models.CharField(max_length=40, verbose_name="出版社")
        city = models.CharField(max_length=40, verbose_name="出版社所在城市")
    
        def __str__(self):
            return self.name
    
        class Meta:
            verbose_name = "出版社"
            verbose_name_plural = verbose_name
    
    
    class Author(models.Model):
        name = models.CharField(max_length=40, verbose_name="作者")
        age = models.IntegerField(default=17, verbose_name="年龄")
    
        def __str__(self):
            return self.name
    
        class Meta:
            verbose_name = "作者"
            verbose_name_plural = verbose_name
    
    
    class Book(models.Model):
        name = models.CharField(max_length=40, verbose_name="书名")
        price = models.IntegerField(default=99, verbose_name="价格")
        pub_date = models.DateField(verbose_name="出版时间")
        publish = models.ForeignKey(Publish, on_delete=models.CASCADE, verbose_name="出版社")
    
        # author = models.ManyToManyField(Author, verbose_name="作者")
    
        def __str__(self):
            return self.name
    
        class Meta:
            verbose_name = "图书"
            verbose_name_plural = verbose_name
    
    
    class Book_Author(models.Model):
        book_id = models.ForeignKey(Book, on_delete=models.CASCADE)
        author_id = models.ForeignKey(Author, on_delete=models.CASCADE)
    
    
    - views
    
    def addbook(request):
        # 1.创建出版社
        # Publish.objects.create(name="人民出版社",city="北京")
        #
        # p = Publish()
        # p.name = "邮电出版社"
        # p.city = "西安"
        # p.save()
        #
        # p2 = Publish(name="机械出版社")
        # p2.city = "上海"
        # p2.save()
        #
        # print(Publish.objects.all())
    
        # 2,创建图书
        # Book.objects.create(name="python", price=89,pub_date='2017-01-07', publish_id=1)
        # Book.objects.create(name="go", price=99, pub_date='2017-04-01',publish_id=2)
        # Book.objects.create(name="ios", price=39,pub_date='2017-06-01', publish_id=3)
        # Book.objects.create(name="java", price=69, pub_date='2017-12-24',publish_id=3)
    
        # 3,创建作者
        # Author.objects.create(name="龟叔",age=17)
        # Author.objects.create(name="林纳斯",age=20)
        # Author.objects.create(name="毛台",age=25)
        # Author.objects.create(name="Jeff",age=33)
    
        # 龟叔还写了ios
        # book_obj = Book.objects.get(name="ios")
        # author_obj = Author.objects.get(id=1)
        # print(book_obj.author.all()) # ios谁写的?
        # # book_obj.author.add(author_obj)
    
        # 龟叔出过的书
        # res = Book.objects.filter(author__name="龟叔").values('name', 'price')
        # print(res)
        #
        # # python这本书谁写的
        # res2 = Author.objects.filter(book__name="python")
        # print(res2)
        #
        Book_Author.objects.create(book_id=2,author_id=1)
    
        book_obj = Book.objects.get(id=2)
        # book_obj.book_author_set.all() # book_author的queryset对象
        print(book_obj.book_author_set.all()[0].author)
    
        Book.objects.filter(book_author__author_id__name="龟叔").values('name','price')
    
    
        # 创建自定义book_author表
        Book.objects.filter(book_author__author__name="alex").values("name","price")
        return HttpResponse("结果请查看console")
    

    查看sql

    追加扫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',
            },
        }
    }
    
  • 相关阅读:
    [gj]三国攻势图
    [svc]msmtp+mutt发附件,发邮件给多个人
    [sh]清理memcached缓存
    [svc]jdk1.7.0_13(系列)下载url
    [svc]linux查看主板型号及内存硬件信息
    [svc][op]如何查看当前Ubuntu系统的版本
    [svc][bg]phabricator-zh_CN汉化包
    [na]台式机装原版Win2008R2
    [svc]salt源码安装软件和yum安装软件
    JSTL的相关使用
  • 原文地址:https://www.cnblogs.com/iiiiiher/p/8910595.html
Copyright © 2020-2023  润新知