Of Stack

DjangoORM Exercises and Answers

  • 2021-07-22 10:20:41
  • OfStack

1. Table structure in modles 


# Publishing house 
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)
# Books 
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)
  # Create a foreign key , Association Publisher
  Publisher = models.ForeignKey(to='Publisher')

  def __str__(self):
    return "<Book object: {} {}>".format(self.id, self.title

# Author 
class Author(models.Model):
  name = models.CharField(max_length =32)
  age = models.IntegerField()
  phone = models.CharField(max_length=11)
  # Create a many-to-many association 
  books = models.ManyToManyField(to='Book')

  def __str__(self):
    return "<Author object: {} {}>".format(self.id, self.name)

2. Topic 


"""
 Find all books with Boss Kim in their titles 
 Find the publication date is 2018 A book in 
 Find the publication date is 2017 The title of the book in 
 Find price greater than 10 Yuan's book 
 Find price greater than 10 The title and price of yuan 
 Find memo Books with empty fields 

 Find publishers in Beijing 
 Find a publishing house whose name begins with Shahe 

 Find all the books published by Shahe Publishing House 
 Find the highest price books published by each publishing house 
 Find the name of each publishing house and the number of books published 

 Find the author with the word "small" in his name 
 Find age greater than 30 An author of ten years old 
 Find the mobile phone number is 155 The author of the beginning 
 Find the mobile phone number is 155 Name and age of the author at the beginning 

 Find the highest price books written by each author 
 Find the name of each author and the number of books published 

 Find the publisher of the book titled "Learning to Drive with Boss Kim." 
 Find the city where the publisher of the book titled "Learn to Drive with Boss Kim" is located 
 Find the name of the publisher of the book titled "Learning to Drive with Boss Kim." 
 Find the names and prices of other books published by the publisher of the book titled "Learn to Drive with Boss Kim." 

 Find all the authors of the book titled "Learn to Drive with Boss Kim." 
 Find the age of the author of the book titled "Learning to Drive with Boss Kim." 
 Find the mobile phone number of the author of the book titled "Learning to Drive with Boss Kim." 
 Find the names of the authors of the book entitled "Learn to Drive with Boss King" and the names and prices of all the books published 

"""

 Title

3. Test data 


-- ----------------------------
-- Records of app01_author
-- ----------------------------
INSERT INTO `app01_author` VALUES ('1', ' Boss Kim ', '18', '15512351234');
INSERT INTO `app01_author` VALUES ('2', ' Little Nezha ', '20', '15312341234');
INSERT INTO `app01_author` VALUES ('3', 'Alex', '73', '15512341234');
 
-- ----------------------------
-- Records of app01_publisher
-- ----------------------------
INSERT INTO `app01_publisher` VALUES ('1', ' Shahe Publishing House ', ' Beijing ');
INSERT INTO `app01_publisher` VALUES ('2', ' West 2 Flag Publishing House ', ' Beijing ');
INSERT INTO `app01_publisher` VALUES ('3', ' Zhangjiang Publishing House ', ' Shanghai ');
INSERT INTO `app01_publisher` VALUES ('4', ' Shahe Publishing House ', ' Shanghai ');
 
-- ----------------------------
-- Records of app01_book
-- ----------------------------
INSERT INTO `app01_book` VALUES ('1', ' Learn to drive with Boss Kim ', '2018-08-03', '12.90', null, '1');
INSERT INTO `app01_book` VALUES ('2', ' Learn to drive submarines from Boss Kim ', '2017-08-10', '9.99', null, '1');
INSERT INTO `app01_book` VALUES ('3', ' Learn ideas from old boys ', '2018-09-03', '39.99', null, '2');
INSERT INTO `app01_book` VALUES ('4', ' Follow egon Learn to shout wheat ', '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', '2', '1');
INSERT INTO `app01_book_author` VALUES ('5', '1', '2');
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', '3', '4');

 Test data

4. Answers 


import os
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "ORMHomework.settings")
import django
django.setup()

from app01 import models
from django.db.models import Max, Min, Sum, Avg, Count

#  Find all books with Boss Kim in their titles 
ret1= models.Book.objects.filter(title__contains=' Boss Kim ')
# print (ret1)

# Find the publication date is 2018 A book in 
ret2 = models.Book.objects.filter(publish_date__year=2018)
# print (ret2)

# Find the publication date is 2017 The title of the book in 
ret3 = models.Book.objects.filter(publish_date__year=2017).values('title')
# print (ret3)

# Find price greater than 10 Yuan's book 
ret4 =models.Book.objects.filter(price__gt=10)
# print(ret4)

# Find price greater than 10 The title and price of yuan 
ret5 = models.Book.objects.filter(price__gt=10).values('title','price')
# print(ret5)

#  Find memo Books with empty fields 
ret6 = models.Book.objects.filter(memo__isnull=True)
# print(ret6)

#-----------------------------------------------------------------------------
#  Find publishers in Beijing 
ret7 = models.Publisher.objects.filter(city=' Beijing ')
# print(ret7)

#  Find a publishing house whose name begins with Shahe 
ret8 = models.Publisher.objects.filter(name__startswith=' Shahe ')
# print(ret8)

# Find all the books published by Shahe Publishing House 
ret9 =models.Book.objects.filter(Publisher__name=' Shahe Publishing House ')
# print(ret9)

#  Find the highest price books published by each publishing house 
# ret10 = models.Publisher.objects.all().annotate(max=Max('book__price')).values('name','max')
# ret10 = models.Publisher.objects.annotate(max=Max('book_price')).values()
# for i in ret10 :
#   print(i)

#  Find the name of each publishing house and the number of books published 
ret11 = models.Publisher.objects.annotate(count=Count('book__title')).values('name','count')
# for i in ret11:
#   print(i)

#---------------------------------------------------------------------------------------------------------
# Find the author with the word "small" in his name 
ret12 = models.Author.objects.filter(name__contains=' Small ')
# print(ret12)

# Find age greater than 30 An author of ten years old 
ret13 = models.Author.objects.filter(age__gt=30)
# print(ret13)

# Find the mobile phone number is 155 The author of the beginning 
ret14 = models.Author.objects.filter(phone__startswith=155)
# print(ret14)

# Find the mobile phone number is 155 Name and age of the author at the beginning 
ret15 = models.Author.objects.filter(phone__startswith=155).values('name','age')
# print(ret15)

# Find the highest price books written by each author 
# ret16 = models.Author.objects.annotate(max=Max('books__price')).values('name','max')
ret16= models.Book.objects.values('author').annotate(max=Max('price')).values('author','max')
# for i in ret16:
#   print(i)

# Find the name of each author and the number of books published 
# ret17 = models.Author.objects.all().annotate(count=Count('books__title')).values('name','count')
# for i in ret17 :
#   print(i)

#-------------------------------------------------------------------------------------------------------
# Find the publisher of the book titled "Learning to Drive with Boss Kim." 
ret18 = models.Publisher.objects.filter(book__title=' Learn to drive with Boss Kim ')
# print (ret18)

# Find the city where the publisher of the book titled "Learn to Drive with Boss Kim" is located 
ret19 = models.Publisher.objects.filter(book__title=' Learn to drive with Boss Kim ').values('city')
# print(ret19)

# Find the name of the publisher of the book titled "Learning to Drive with Boss Kim." 
ret20 = models.Publisher.objects.filter(book__title=' Learn to drive with Boss Kim ').values('name')
# print(ret20)

# Find the names and prices of other books published by the publisher of the book titled "Learn to Drive with Boss Kim." 


pub_obj = models.Publisher.objects.get(book__title=' Learn to drive with Boss Kim ')
ret21= pub_obj.book_set.all().exclude(title=' Learn to drive with Boss Kim ').values('title','price')
print(ret21)

# Find all the authors of the book titled "Learn to Drive with Boss Kim." 
# ret22 = models.Author.objects.filter(books__title=' Learn to drive with Boss Kim ')
# print(ret22)

# Find the age of the author of the book titled "Learning to Drive with Boss Kim." 
# ret23 = models.Author.objects.filter(books__title=' Learn to drive with Boss Kim ').values('name','age')
# print(ret23)

# Find the mobile phone number of the author of the book titled "Learning to Drive with Boss Kim." 
# ret24 = models.Author.objects.filter(books__title=' Learn to drive with Boss Kim ').values('name','phone')
# print(ret24)

# Find the names of the authors of the book entitled "Learn to Drive with Boss King" and the names and prices of all the books published 

# ret25= models.Author.objects.filter(books__title=' Learn to drive with Boss Kim ')
# print(ret25)
# for i in ret25:
#   print(i.books.all().values('title','price'))

#
# ret = models.Book.objects.aggregate(Max('price'))
# print(ret)

ret25 = models.Author.objects.values('name','books__title','books__price').filter(books__title=' Learn to drive with Boss Kim ')
print(ret25)

 Answer
Related articles: