Django之ORM練習

標籤：2018年   ext   span   efi   mode   its   號碼   int   sel   

一、表結構

# 書class Book(models.Model):    title = models.CharField(max_length=32)    publish_date = models.DateField(auto_now_add=True)    price = models.DecimalField(max_digits=5, decimal_places=2)    memo = models.TextField(null=True)    # 建立外鍵，關聯publish    publisher = models.ForeignKey(to="Publisher")    # 建立多對多關聯author    author = models.ManyToManyField(to="Author")    def __str__(self):        return "<Book object: {} {}>".format(self.id, self.title)# 出版社class Publisher(models.Model):    name = models.CharField(max_length=32)    city = models.CharField(max_length=32)    def __str__(self):        return "<Publisher object: {} {}>".format(self.id, self.name)# 作者class Author(models.Model):    name = models.CharField(max_length=32)    age = models.IntegerField()    phone = models.CharField(max_length=11)    def __str__(self):        return "<Author object: {} {}>".format(self.id, self.name)

二、資料

-- ------------------------------ Records of app01_author-- ----------------------------INSERT INTO `app01_author` VALUES (‘1‘, ‘金老闆‘, ‘18‘, ‘15512351234‘);INSERT INTO `app01_author` VALUES (‘2‘, ‘小哪吒‘, ‘20‘, ‘15312341234‘);INSERT INTO `app01_author` VALUES (‘3‘, ‘Alex‘, ‘73‘, ‘15512341234‘);-- ------------------------------ Records of app01_publisher-- ----------------------------INSERT INTO `app01_publisher` VALUES (‘1‘, ‘沙河出版社‘, ‘北京‘);INSERT INTO `app01_publisher` VALUES (‘2‘, ‘西二旗出版社‘, ‘北京‘);INSERT INTO `app01_publisher` VALUES (‘3‘, ‘張江出版社‘, ‘上海‘);INSERT INTO `app01_publisher` VALUES (‘4‘, ‘沙河出版社‘, ‘上海‘);-- ------------------------------ Records of app01_book-- ----------------------------INSERT INTO `app01_book` VALUES (‘1‘, ‘跟金老闆學開車‘, ‘2018-08-03‘, ‘12.90‘, null, ‘1‘);INSERT INTO `app01_book` VALUES (‘2‘, ‘跟金老闆學開潛艇‘, ‘2017-08-10‘, ‘9.99‘, null, ‘1‘);INSERT INTO `app01_book` VALUES (‘3‘, ‘跟老男孩學思想‘, ‘2018-09-03‘, ‘39.99‘, null, ‘2‘);INSERT INTO `app01_book` VALUES (‘4‘, ‘跟egon學喊麥‘, ‘2018-06-12‘, ‘0.99‘, null, ‘4‘);-- ------------------------------ Records of app01_book_author-- ----------------------------INSERT INTO `app01_book_author` VALUES (‘3‘, ‘1‘, ‘1‘);INSERT INTO `app01_book_author` VALUES (‘4‘, ‘1‘, ‘2‘);INSERT INTO `app01_book_author` VALUES (‘5‘, ‘2‘, ‘1‘);INSERT INTO `app01_book_author` VALUES (‘2‘, ‘2‘, ‘2‘);INSERT INTO `app01_book_author` VALUES (‘6‘, ‘3‘, ‘3‘);INSERT INTO `app01_book_author` VALUES (‘7‘, ‘4‘, ‘3‘);

 三、練習題目 

尋找所有書名裡包含金老闆的書尋找出版日期是2018年的書尋找出版日期是2017年的書名尋找價格大於10元的書尋找價格大於10元的書名和價格尋找memo欄位是空的書尋找在北京的出版社尋找名字以沙河開頭的出版社尋找“沙河出版社”出版的所有書籍尋找每個出版社出版價格最高的書籍價格尋找每個出版社的書名以及出的書籍數量尋找作者名字裡面帶“小”字的作者尋找年齡大於30歲的作者尋找手機號是155開頭的作者尋找手機號是155開頭的作者的姓名和年齡尋找每個作者寫的價格最高的書籍價格尋找每個作者的姓名以及出的書籍數量尋找書名是“跟金老闆學開車”的書的出版社尋找書名是“跟金老闆學開車”的書的出版社所在的城市尋找書名是“跟金老闆學開車”的書的出版社的名稱尋找書名是“跟金老闆學開車”的書的出版社出版的其他書籍的名字和價格尋找書名是“跟金老闆學開車”的書的所有作者尋找書名是“跟金老闆學開車”的書的作者的年齡尋找書名是“跟金老闆學開車”的書的作者的手機號碼尋找書名是“跟金老闆學開車”的書的作者們的姓名以及出版的所有書籍名稱和價錢

四、答案

#! /usr/bin/env python3# -*- coding: utf-8 -*-import osif __name__ == ‘__main__‘:    os.environ.setdefault("DJANGO_SETTINGS_MODULE", "day03.settings")    import django    django.setup()    from app01 import models    from django.db.models import Avg, Max, Min, Count    # print(models.Book.objects.filter(author__name=‘金老闆‘))    # print(models.Book.objects.filter(publish_date__year=2018))    # print(models.Book.objects.filter(publish_date__year=2017))    # print(models.Book.objects.filter(price__gt=10))    # print(models.Book.objects.filter(price__gt=10).values(‘price‘, ‘title‘))    # print(models.Book.objects.filter(memo__isnull=True))    # print(models.Publisher.objects.filter(city=‘北京‘))    # print(models.Publisher.objects.filter(name__istartswith=‘沙河‘))    # print(models.Book.objects.filter(publisher__name__istartswith=‘沙河‘))    # print(models.Book.objects.values(‘publisher__name‘).annotate(max_price = Max(‘price‘)).values(‘title‘, ‘publisher__name‘, ‘max_price‘))    # print(models.Book.objects.values(‘publisher__name‘).annotate(count=Count(‘id‘)).values(‘title‘, ‘count‘))    # print(models.Author.objects.filter(name__contains=‘小‘).values())    # print(models.Author.objects.filter(phone__istartswith=155).values_list())    # print(models.Author.objects.filter(phone__istartswith=155).values_list(‘name‘, ‘age‘))    # print(models.Book.objects.values(‘author__name‘).annotate(max=Max(‘price‘)).values(‘title‘, ‘price‘))    # print(models.Author.objects.values(‘name‘).annotate(count=Count(‘book__id‘)).values(‘name‘,‘count‘))    # print(models.Book.objects.filter(title=‘跟金老闆學開車‘).values(‘publisher__name‘))    # print(models.Book.objects.filter(title=‘跟金老闆學開車‘).values_list(‘publisher__city‘))    # print(models.Book.objects.filter(title=‘跟金老闆學開車‘).values(‘publisher__name‘))    # print(models.Publisher.objects.filter().exclude(book__title=‘跟金老闆學開車‘).values(‘book__title‘, ‘book__price‘))    # print(models.Book.objects.filter(title=‘跟金老闆學開車‘).values(‘author__name‘))    # print(models.Author.objects.filter(book__title=‘跟金老闆學開車‘).values(‘name‘,‘age‘))    # print(models.Publisher.objects.filter(book__title=‘跟金老闆學開車‘).values(‘book__author__name‘, ‘book__author__phone‘))    # ret = models.Book.objects.filter(title=‘跟金老闆學開車‘).values_list(‘author__name‘)    # for i in ret:    #     print(models.Book.objects.filter(author__name=i[0]).values_list(‘author__name‘,‘title‘,‘price‘))

 

Django之ORM練習