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