Django orm练习

ORM练习题

models生成

from django.db import models

# Create your models here.
# 书籍管理
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)
    publisher = models.ForeignKey(to='Publisher')  # 创建外键
    author = models.ManyToManyField('Author')   # 多对多关系创建第三张表

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

    __repr__ = __str__

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)

执行导入

# 终端执行
# 生成文件
python manage.py makemigrations

# 导入数据库
python manage.py migrate

数据插入

-- ----------------------------
-- 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');

习题

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

from app03 import models

# 查找所有书名里包含金老板的书
ret = models.Book.objects.filter(title__contains='金老板')
books = ','.join([i.title for i in ret])
print(books)

# 查找出版日期是2018年的书
ret = models.Book.objects.filter(publish_date__year='2018')
books = ','.join([i.title for i in ret])
print(books)

# 查找出版日期是2017年的书名
ret = models.Book.objects.filter(publish_date__year='2017').values('title')
for i in ret:
    print(i.get('title'))


# 查找价格大于10元的书
ret = models.Book.objects.filter(price__gt=10)
print(ret)

# 查找价格大于10元的书名和价格
ret = models.Book.objects.filter(price__gt=10).values('title','price')
for i in ret:
    print(i.get('title'),i.get('price'))

# 查找memo字段是空的书
ret = models.Book.objects.filter(memo__isnull=True)
for i in ret:
    print(i)

# 查找在北京的出版社
ret = models.Publisher.objects.filter(city='北京')
print(ret)

# 查找名字以沙河开头的出版社
ret = models.Publisher.objects.filter(name__startswith='沙河')
print(ret)

# 查找“沙河出版社”出版的所有书籍
ret = [ i  for i in models.Book.objects.all() if i.publisher.name == '沙河出版社']
print(ret)

# 查找每个出版社出版价格最高的书籍价格
ret = models.Publisher.objects.all()
for i in ret:
    book =  models.Book.objects.filter(publisher=i).order_by('-price').first()
    if book:print(i.name,book.price)

# 查找每个出版社的名字以及出的书籍数量
ret = models.Publisher.objects.all()
for i in ret:
    num = models.Book.objects.filter(publisher_id=i.id).count()
    print(i.name,num)

# 查找作者名字里面带“小”字的作者
ret = models.Author.objects.filter(name__contains='小')
print(ret)

# 查找年龄大于30岁的作者
ret = models.Author.objects.filter(age__gt=30)
print(ret)

# 查找手机号是155开头的作者
ret = models.Author.objects.filter(phone__startswith='155')
for i in ret:print(i)


# 查找手机号是155开头的作者的姓名和年龄
ret = models.Author.objects.filter(phone__startswith='155').values('name','age')
for i in ret:print(i['name'],i['age'])

# 查找每个作者写的价格最高的书籍价格
ret = models.Author.objects.all()
for i in ret:
    ret = [ b.price for b in models.Book.objects.all() if i in b.author.all()]
    print(i.name,max(ret))

# 查找每个作者的姓名以及出的书籍数量
author = models.Author.objects.all()
for i in author:
    ret = models.Book.objects.filter(author=i).count()
    print(i.name,ret)

# 查找书名是“跟金老板学开车”的书的出版社
ret = models.Book.objects.filter(title='跟金老板学开车')
for i in ret:
    print(i.publisher)

# 查找书名是“跟金老板学开车”的书的出版社所在的城市
ret = models.Book.objects.filter(title='跟金老板学开车')
for i in ret:
    print(i.publisher.name,i.publisher.city)

# 查找书名是“跟金老板学开车”的书的出版社的名称
ret = models.Book.objects.filter(title='跟金老板学开车')
for i in ret:
    print(i.publisher.name)


# 查找书名是“跟金老板学开车”的书的出版社出版的其他书籍的名字和价格
ret = models.Book.objects.filter(title='跟金老板学开车')[0]
obj = models.Book.objects.filter(publisher_id=ret.publisher_id)
for i in obj:
    if i != ret:print(i.title, i.price)
    else:continue

# 查找书名是“跟金老板学开车”的书的所有作者
ret = models.Book.objects.get(title='跟金老板学开车').author.all()
print(ret)

# 查找书名是“跟金老板学开车”的书的作者的年龄
ret = models.Book.objects.get(title='跟金老板学开车').author.all()
for i in ret:print(i.name,i.age)

# 查找书名是“跟金老板学开车”的书的作者的手机号码
ret = models.Book.objects.get(title='跟金老板学开车').author.all()
for i in ret:print(i.name,i.phone)

# 查找书名是“跟金老板学开车”的书的作者们的姓名以及出版的所有书籍名称和价钱
ret = models.Book.objects.get(title='跟金老板学开车').author.all()
for i in ret:
    rec = [n for n in models.Book.objects.filter(author=i).values_list('title','price')]
    print(i.name,rec)