• ORM查询示例


    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,verbose_name="名称")#每一条数据的字段名
        city=models.CharField(verbose_name="城市", max_length=32)
        email=models.EmailField(verbose_name="邮箱")
    
        def __str__(self):
            return self.name  #每条数据的名称
        class Meta:
            verbose_name_plural = "出版社"#显示表名称为汉字,如果不加admin中的Publish后会加s
    
    
    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',)

    ##

    publisher = models.ForeignKey(
        to="Publisher",
        on_delete=models.CASCADE,  #删除数据时,应该怎么处理,默认的是级联操作
        related_name="books",    #反向查询时用来代替  表名_set
        related_query_name="xxoo",  #反向双下划线跨表查询是用来代替表名
        null=True
    )

    ORM查询

    import os
    
    if __name__ == '__main__':
        os.environ.setdefault("DJANGO_SETTINGS_MODULE", "orm.settings")
        import django
        django.setup()
    
        from app01 import models
        #单表查询
        book_obj = models.Book.objects.all()   #输出对象列表
        print(book_obj)
        for book in book_obj:
            print(book.title)
        print("=======")
        ret = models.Book.objects.values('title')  #输出字典形式的对象列表
        print(ret)
        for i in ret:
    
            print(i["title"])
            # print(i["price"])
            print("&&&&&&&&&&")
        ret = models.Book.objects.values_list('title', 'price')  # 输出元祖形式的对象列表
        print(ret)
        for i in ret:
            print(i[0])  #打印所有书籍的名字
            print(i[1]) #打印所有价格
        print("======================")
    
        #跨表查询  一对多
        #查询第一本书的出版社的名字 ----------- 查询语法格式:对象.关联字段.字段名
        ret = book_obj.first().publish.name
        print(ret)
        #查询所有书籍对应出版社的名字 -----------查询语法格式:关联字段__字段名
        ret = models.Book.objects.values("title","publish__name")
        print(ret)
        for i in ret:
            print(i["title"]) #打印所有书籍的名字
        #反向查询 查询第一个出版社出的书籍的名称
    
        publish_obj = models.Publish.objects.first()
        books = publish_obj.book_set.all()  #一个出版社有多本书,是一个列表对象
        print(books.values("title"))
        #查询所有出版社对应出的书的名字 -----------语法:表名__字段
        ret = models.Publish.objects.values("name","book__title")
        print(ret)
        #查询北京出版社所出的书  正向查询
        ret = models.Book.objects.filter(publish__name="北京出版社").values("title")
        print(ret)
        print("+++++++++++")
        #反向查询
        ret = models.Publish.objects.filter(name="北京出版社").values("book__title")
        print(ret)
        # 跨表查询  一对一
        #正向查询   ------- 查询语法格式:对象.关联字段.字段名
        author_obj = models.Author.objects.first()
        ret = author_obj.authorDetail.telephone
        print(ret)
        #反向查询 ------- 查询语法格式:对象.表名.字段名
        authordetail_obj = models.AuthorDetail.objects.first()
        ret = authordetail_obj.author.name
        print(ret)
        #查询所有作者的电话
        author_list = models.Author.objects.all()
        for i in author_list:
            print(i.authorDetail.addr)
        authordetail_list = models.AuthorDetail.objects.all()
        for i in authordetail_list:
            print(i.author.name)
        ret = models.Author.objects.values("authorDetail__telephone")
        for i in ret:
            print(i["authorDetail__telephone"])
        ret = models.AuthorDetail.objects.values("author__name")
        print(ret)
        for i in ret:
            print(i["author__name"])
        print("&&&&&&&&&&")
    
        #多对多查询
    
        #查询书名为web对应的作者
        book_list = models.Book.objects.filter(title="web").first() #取的是单个的对象
        ret = book_list.authors.all()
        print(ret)
        for name_list in ret:
            print(name_list.name,name_list.authorDetail.telephone)
    
        #查询所有书籍对应的作者
        book_list = models.Book.objects.all()
        for book_obj in book_list:  #取出单个的对象,单个对象对应多个,相当于一对多查询
            ret = book_obj.authors.all()
            for name_list in ret:
                print(book_obj.title,name_list.name,name_list.authorDetail.telephone)
        print("\\\\\\===========")
        author_list = models.Author.objects.all()
        for author_obj in author_list:
            # 取出单个的对象,单个对象对应多个,相当于一对多查询
            ret = author_obj.book_set.all()
            print(ret.values("title"))
            for i in ret:
                print(i.title)
    
        print("\\\\\\")
    
        # 查询所有书籍对应的作者 双下划线查询
        ret = models.Book.objects.values("title","authors__name")
        print(ret)
        for name_list in ret:
            print(name_list["title"],name_list["authors__name"])
    
        #查询小明出的书籍
        ret = models.Book.objects.filter(authors__name="小明").values("title")
        print(ret)
        #反向查询
        ret = models.Author.objects.filter(name="小明").values("book__title")
        print(ret)
        print("跨三张表查询")
        #书籍为web的作者的电话
        ret = models.Book.objects.filter(title="web").values("authors__name","authors__authorDetail__telephone")
        print(ret)
        ret = models.Author.objects.filter(book__title="web").values("authorDetail__telephone")
        print(ret)
        #查询第一个出版社出版书籍的作者
        ret = models.Publish.objects.first().book_set.all().values("authors__name")
        print(ret)
        ret = models.Book.objects.filter(publish__pk=1).values("authors__name")
        print(ret)
        #跨四个表查询
        # 查询第一个出版社出版书籍的作者的电话
        ret = models.Publish.objects.first().book_set.all().values("authors__authorDetail__telephone")
        print(ret)
        ret = models.Book.objects.filter(publish__pk=1).values("authors__authorDetail__telephone")
        print(ret)
        #查询第一个出版社出版书籍的详情列表
        publish_obj = models.Publish.objects.filter(pk=1).first()
        book = publish_obj.book_set.all()
        ret = models.AuthorDetail.objects.filter(author__nid=publish_obj.book_set.all())
        print('*************')
        #只取某几列
    
    #只取某几列query=[{},{},{}]里面是字典
    ret = models.Book.objects.all().values('title','price')

     for i in ret:
          print(i["title"])
      # print(i["price"])
      print("&&&&&&&&&&")
        #query=[(),(),()]里面是元祖
    ret = models.Book.objects.values_list('title', 'price')
    print(ret)
    for i in ret:
    print(i[0]) # 打印所有书籍的名字
    print(i[1]) # 打印所有价格
    print("======================")
    #query=[obj,obj]里面是对象
    ret = models.Book.objects.all().only('title','price')#only里面的字段最好和下面的对应,要不然性能会降低
    for item in ret:
    print(item.title,item.price,item.publishDate)#加上publishDate会降低性能
    ret = models.Book.objects.all().defer('title','price')#排除里面的字段

    print(ret)
    for item in ret:
    print(item.nid,item.title,)
    print('&&&&&&&&&')
    print(item.title,item.price)
    #性能相关 select_related和prefetch_related比object.all()性能高,一般用select_related
    ret = models.Book.objects.all()
    for item in ret:
    print(item.title,item.publish.city)#这样写会跨表,性能会降低

      #如果有foreignkey 或者onetoone可以用select_related,可以提高性能
      #query=[obj,obj]里面是对象
      ret = models.Book.objects.all().select_related('publish')#主动创建关联关系,一次链表,如果链表多,性能会差
        print('9999999999')
    for item in ret:
    print(item.title,item.publish.city)
      ret = models.Book.objects.all().prefetch_related('publish')#二次单表查询
        print('9999999999')
    for item in ret:
    print(item.title,item.publish.city)
      print("@@@@@@@@@@@")
        ret = models.Publish.objects.filter(nid__in=[1,3,4])
        print(ret)
        ret =models.Publish.objects.exclude(nid__in=[1,4]) #不包含/不等于1,4的数据
        print(ret)
        ret = models.Publish.objects.filter(nid__gt=1,nid__lt=4)  #大于1小于4
        print(ret)
        ret = models.Publish.objects.filter(nid__gte=1,nid__lte=3) #大于等于1小于等于3
        print(ret)
    
        ret = models.Publish.objects.filter(name__contains="北京")
        print(ret)
        ret = models.Publish.objects.filter(name__icontains="北京")
        print(ret)
        ret = models.Publish.objects.filter(nid__range=[1,3])
        print(ret)
        ret = models.Publish.objects.all().order_by("nid").reverse()
        print(ret)
    
    
        ##########   聚合 ##############
        from django.db.models import Avg,Sum,Max,Min,Count
        ret = models.Book.objects.all().aggregate(Avg("price"))
        print(ret)
        ret = models.Book.objects.all().aggregate(avg_price=Avg("price"))
        print(ret)
        ret = models.Book.objects.all().aggregate(Avg("price"),Sum("price"),Max("price"),Min("price"))
        print(ret)
        ret = models.Publish.objects.annotate(avg=Avg("book__price")).values("name","avg")
        print(ret)
    
        #################-----F和Q-----###########
    
        #F()来做这样的比较查
    
        from django.db.models import F,Q
        #询评论数大于收藏数的书籍
        models.Book.objects.filter(commnet_num__gt=F('keep_num'))
        #将每一本书的价格提高30元
        models.Book.objects.all().update(price=F("price") + 30)
      
      

      # Q查询
      from django.db.models import Q
      # 查询 卖出数大于1000,并且 价格小于100的所有书
      # ret = models.Book.objects.filter(maichu__gt=1000, price__lt=100)
      # print(ret)
      # 查询 卖出数大于1000,或者 价格小于100的所有书
      # ret = models.Book.objects.filter(Q(maichu__gt=1000) | Q(price__lt=100))
      # print(ret)
      # Q查询和字段查询同时存在时, 字段查询要放在Q查询的后面
      # ret = models.Book.objects.filter(Q(maichu__gt=1000) | Q(price__lt=100), title__contains="金老板")
      # print(ret)

     
    #查询作者名是小仙女或小魔女的
        models.Book.objects.filter(Q(authors__name="小仙女") | Q(authors__name="小魔女"))
        #查询出版年份是2017或2018,书名中带物语的所有书。
        models.Book.objects.filter(Q(publish_date__year=2018) | Q(publish_date__year=2017), title__icontains="物语")
  • 相关阅读:
    【SVN解决代码提交冲突】https://www.cnblogs.com/aaronLinux/p/5521844.html
    查询有2门及以上不及格科目的学生姓名及其平均成绩
    【Python】split
    【Python】文件处理
    【robotframework】打开浏览器提示:NoSuchWindowException: Message: Unable to get browser
    定位到新窗口
    8月1号
    【定位】https://blog.csdn.net/cyjs1988/article/details/76284289
    【Robotframework】脚本跑完后自动发送邮件
    jQuery Mobile Data 属性
  • 原文地址:https://www.cnblogs.com/chvv/p/10038727.html
Copyright © 2020-2023  润新知