一 .djago项目书籍管理(增删改查)
models from django.db import models # Create your models here. class Book(models.Model): nid=models.AutoField(primary_key=True) title=models.CharField(max_length=32) price=models.DecimalField(max_digits=8,decimal_places=2) # 999999.99 pub_date=models.DateTimeField() # "2012-12-12" comment_count=models.IntegerField(default=100) poll_count=models.IntegerField(default=100) publish=models.ForeignKey(to="Publish",on_delete=models.CASCADE) # 级联删除 authors=models.ManyToManyField(to="Author") def __str__(self): return self.title class Publish(models.Model): nid = models.AutoField(primary_key=True) name=models.CharField(max_length=32) email=models.CharField(max_length=32) def __str__(self): return self.name
class Author(models.Model): nid = models.AutoField(primary_key=True) name=models.CharField(max_length=32) age=models.IntegerField() email=models.CharField(max_length=32) ad=models.OneToOneField(to="AuthorDetail",on_delete=models.CASCADE) #books=models.ManyToManyField("Book") def __str__(self): return self.name class AuthorDetail(models.Model): addr=models.CharField(max_length=32) tel=models.IntegerField() #author=models.OneToOneField("Author",on_delete=models.CASCADE) def __str__(self): return self.addr # class Author2Book(models.Model): # nid = models.AutoField(primary_key=True) # book=models.ForeignKey(to="Book",on_delete=models.CASCADE) # author=models.ForeignKey(to="Author",on_delete=models.CASCADE) ############################################################### class Emp(models.Model): name=models.CharField(max_length=32) age=models.IntegerField(default=20) dep=models.CharField(max_length=32) pro=models.CharField(max_length=32) salary=models.DecimalField(max_digits=8,decimal_places=2)
urls from django.contrib import admin from django.urls import path,re_path from app01 import views urlpatterns = [ path('admin/', admin.site.urls), path('addrecord/', views.addrecord), path('query/', views.query), path('query2/', views.query2), ############## 图书管理系统 ############# path("books/",views.book_view), re_path("^$",views.book_view), path("books/add/",views.book_add), re_path("^books/edit/(?P<edit_book_id>\d+)$",views.book_edit), re_path("^books/delete/(?P<del_book_id>\d+)$",views.book_del), ]
views from django.shortcuts import render,HttpResponse,redirect # Create your views here. from app01.models import Book,Publish,Author,AuthorDetail,Emp def addrecord(request): ''' 添加记录 ''' # 一对多的添加方式： #pub_obj=Publish.objects.filter(name="橙子出版社").first() # book=Book.objects.create( # title="python", # price=120, # pub_date="2012-12-12", # publish_id=1, # #publish=pub_obj # # )
# 多对多的添加方式: # 方式1 # alex=Author.objects.filter(name="alex").first() # egon=Author.objects.filter(name="egon").first() # book.authors.add(alex,egon) # 方式2： # book.authors.add(1,2) # 方式3： # book.authors.add(*[1,2]) ####### # egon=Author.objects.filter(name="egon").first() # book=Book.objects.filter(nid=4).first() # #book.authors.remove(1) # book.authors.clear() ###### 解除再绑定 book = Book.objects.filter(nid=3).first() book.authors.clear() book.authors.add(1) book.authors.set(1) ''' app01_book_authors id book_id author_id 1 3 1 2 3 2 ''' return HttpResponse("添加成功") def query(request): ######################### 基于对象的跨表查询 ############################# ####### 一对多 ########## ''' 正向查询：关联属性所在的表查询关联表记录 反向查询 ----正向查询按字段：book.publish Book------------------------------------>Publish <----------------------------------- 反向查询表名小写_set.all()：pub_obj.book_set.all() ''' # 1 查询python这本书出版社的名字和邮箱 # book=Book.objects.filter(title="python").first() # pub_obj=Publish.objects.filter(nid=book.publish_id).first() # print(pub_obj.name) # print(pub_obj.email) ######################## # book = Book.objects.filter(title="python").first() # print(book.publish) # 与book这本书关联出版社对象 # print(book.publish.name) # 与book这本书关联出版社对象 # print(book.publish.email) # 与book这本书关联出版社对象 # 2 查询苹果出版社出版的所有的书籍的名称 # pub_obj=Publish.objects.get(name="苹果出版社") # print(pub_obj.book_set.all()) # queryset # print(pub_obj.book_set.all().values("title")) # queryset ####### 多对多 ########## ''' 正向查询按字段 book.authors.all() Book -------------------------------------->Author <-------------------------------------- 反向查询按表名小写_set.all()： alex.book_set.all() ''' # 查询python这本书籍的作者的年龄 book=Book.objects.filter(title="python").first() ret=book.authors.all().values("age") # 与这本书关联的左右作者的queryset的集合 print(ret) # #查询alex出版过的所有的书籍名称 # alex=Author.objects.filter(name="alex").first() # print(alex.book_set.all()) ####### 一对一 ########## ''' 正常查询安字段：alex.ad Author -----------------------------------------> AuthorDetail <------------------------------------------ 反向查询按表名小写 ad.author ''' # 查询alex的手机号 # alex = Author.objects.filter(name="alex").first() # print(alex.ad.tel) # # 查询手机号为110的作者的名字 # ad=AuthorDetail.objects.filter(tel=110).first() # print(ad.author.name) return HttpResponse("查询成功")
def query2(request): ################基于双下划线的跨表查询（基于join实现的）################ # KEY：正向查询按字段，反向查询按表明小写 # 1 查询python这本书出版社的名字 # ret=Book.objects.filter(title="python").values("price") # ret=Book.objects.filter(title="python").values("publish__name") # print(ret) # ret=Publish.objects.filter(book__title="python").values("name") # print(ret) # 2 查询苹果出版社出版的所有的书籍的名称 # ret=Publish.objects.filter(name="苹果出版社").values("book__title") # ret=Book.objects.filter(publish__name="苹果出版社") # 3 查询python这本书籍的作者的年龄 # ret=Book.objects.filter(title="python").values("authors__age") # print(ret) # ret=Author.objects.filter(book__title="python").values("age") # # # 4 查询alex出版过的所有的书籍名称 # ret1=Book.objects.filter(authors__name="alex").values("title") # ret2=Author.objects.filter(name="alex").values("book__title") # print(ret1,ret2) # 5 查询alex的手机号 # ret=Author.objects.filter(name="alex").values("ad__tel") # ret=AuthorDetail.objects.filter(author__name="alex").values("tel") # 6 查询手机号为110的作者的名字 # ret=AuthorDetail.objects.filter(tel=110).values("author__name") # ret=Author.objects.filter(ad__tel=110).values("name") ########### 连续跨表 ############### # 查询苹果出版社出版过的所有书籍的名字以及作者的姓名 # ret=Publish.objects.filter(name="苹果出版社").values("book__title","book__authors__name") # ret=Book.objects.filter(publish__name="苹果出版社").values("title","authors__name") # ret=Book.objects.filter(publish__name="苹果出版社").values("title","authors__name") # print(ret) # 手机号以110开头的作者出版过的所有书籍名称以及出版社名称 # 方式1: # ret=Author.objects.filter(ad__tel__startswith=110).values_list("book__title","book__publish__name") # print(ret) # 方式2： # ret=AuthorDetail.objects.filter(tel__startswith=110).values("author__book__title","author__book__publish__name") # # # 方式3： # ret=Book.objects.filter(authors__ad__tel__startswith=110).values("title","publish__name") ################ 聚合 分组 ################ ''' emp id name dep pro salary 1 alex 教学部 山东 1000 2 mjj 教学部 山东 3000 3 林海峰 保安部 山东 5000 4 peiqi 人事部 河北 10000 select Count(id) from emp; select AVG(salary) from emp; select dep,AVG(salary) from emp group by dep select pro,Count(1) from emp group by pro ''' # 聚合 # 查询所有书籍的平均价格 from django.db.models import Avg,Max,Sum,Min,Count # ret=Book.objects.all().aggregate(priceAvg=Avg("price")) # print(ret) # {'priceAvg': 142.0} # # 查询所有书籍的个数 # ret=Book.objects.all().aggregate(c=Count(1)) # print(ret) # {'c': 4} # 分组 # 单表分组查询 # 查询书籍表每一个出版社id以及对应的书籍个数 # key： annotate()前values哪一个字段就按哪一个字段group by # ret=Book.objects.values("publish_id").annotate(c=Count(1)) # print(ret) # # 查询每一个部门的名称以及对应员工的平均薪水 # ret=Emp.objects.values("dep").annotate(avg_salary=Avg("salary")) # print(ret) # [{'dep': '教学部', 'avg_salary': 2500.0}, {'dep': '保洁部', 'avg_salary': 3500.0}, {'dep': '保安部', 'avg_salary': 4000.0}]> # # 查询每一个省份的名称以及对应的员工最大年龄 # ret=Emp.objects.values("pro").annotate(max_age=Max("age")) # print(ret) # <QuerySet [{'pro': '山东省', 'max_age': 123}, {'pro': '河南省', 'max_age': 23}, {'pro': '河北省', 'max_age': 56}]> # 单表按主键分组没有意义 # Emp.objects.values("id").annotate() # 跨表分组查询 ''' select app01_publish.name,COUNT(1) from app01_book INNER JOIN app01_publish ON app01_book.publish_id=app01_publish.nid GROUP BY app01_publish.nid ''' # 1 查询每一个出版社的名称以及对应的书籍平均价格 # 方式1： # ret=Publish.objects.values("name","email").annotate(avg_price=Avg("book__price")) # print(ret) # <QuerySet [{'name': '苹果出版社', 'avg_price': 117.0}, {'name': '橙子出版社', 'avg_price': 112.0}, {'name': '西瓜出版社', 'avg_price': 222.0}]> # 方式2： # ret=Publish.objects.all().annotate(avg_price=Avg("book__price")).values("name","email","avg_price") # print(ret) # <QuerySet [<Publish: 苹果出版社>, <Publish: 橙子出版社>, <Publish: 西瓜出版社>]> # 方式3： # ret=Publish.objects.annotate(avg_price=Avg("book__price")).values("name","email","avg_price") # print(ret) # <QuerySet [<Publish: 苹果出版社>, <Publish: 橙子出版社>, <Publish: 西瓜出版社>]> # 2 查询每一个作者的名字以及出版的书籍的最高价格 # ret=Author.objects.values("pk","name").annotate(max_price=Max("book__price")) # print(ret) # ret=Author.objects.annotate(maxprice=Max("book__price")).values("name","maxprice") # print(ret) # 3 查询每一个书籍的名称以及对应的作者的个数 # ret=Book.objects.values("title").annotate(c=Count("authors")) # print(ret) # <QuerySet [{'title': 'python', 'authors__count': 2}, {'title': 'linux', 'authors__count': 1}, {'title': 'go', 'authors__count': 1}, {'title': 'java', 'authors__count': 0}]> # ret=Book.objects.annotate(c=Count("authors")).values("title","c") # print(ret) # 4 查询作者数不止一个的书籍名称以及作者个数 # ret=Book.objects.annotate(c=Count("authors__name")).filter(c__gt=1).values("title","c") # print(ret) # 5 根据一本图书作者数量的多少对查询集 QuerySet进行排序 # ret=Book.objects.annotate(c=Count("authors__name")).order_by("c") # 6 统计每一本以py开头的书籍的名称以及作者个数 # ret=Book.objects.filter(title__startswith="py").annotate(c=Count("authors__name")) ################ F 与 Q ################ from django.db.models import F,Q # # 查询评论数大于100的所有的书籍名称 # ret=Book.objects.filter(comment_count__gt=100).values("title") # # 查询评论数大于2倍点赞数的所有的书籍名称 # ret=Book.objects.filter(comment_count__gt=F("poll_count")*2) # print(ret) # 给每一本书籍的价格提升100 # Book.objects.all().update(price=100+F("price")) # 查询价格大于300或者评论数大于3000的书籍 # ret=Book.objects.filter(price__gt=300,comment_count__gt=3000) # print(ret) # 与 & 或 | 非 ~ # ret = Book.objects.filter(Q(price__gt=300)|~Q(comment_count__gt=3000)) # print(ret) ret = Book.objects.filter(Q(Q(price__gt=300) | ~Q(comment_count__gt=3000))&Q(poll_count__gt=2000)) ret = Book.objects.filter(Q(Q(price__gt=300) | ~Q(comment_count__gt=3000)),poll_count__gt=2000) #ret = Book.objects.filter(poll_count__gt=2000,Q(Q(price__gt=300) | ~Q(comment_count__gt=3000))) print(ret) return HttpResponse("查询成功") ##################### 图书管理系统 视图函数 ##################
def book_view(request): book_list=Book.objects.all() return render(request,"book_view.html",{"book_list":book_list}) def book_add(request): if request.method=="GET": publish_list=Publish.objects.all() author_list=Author.objects.all() return render(request,"book_add.html",{"publish_list":publish_list,"author_list":author_list}) else: title=request.POST.get("title") price=request.POST.get("price") pub_date=request.POST.get("pub_date") publish_id=request.POST.get("publish_id") authors=request.POST.getlist("authors") print(request.POST) print(authors) book=Book.objects.create(title=title,price=price,pub_date=pub_date,publish_id=publish_id) book.authors.add(*authors) return redirect("/books/") def book_edit(request,edit_book_id): edit_book = Book.objects.filter(pk=edit_book_id).first() if request.method=="GET": publish_list = Publish.objects.all() author_list = Author.objects.all() return render(request,"book_edit.html",{"edit_book":edit_book,"publish_list":publish_list,"author_list":author_list}) else: title = request.POST.get("title") price = request.POST.get("price") pub_date = request.POST.get("pub_date") publish_id = request.POST.get("publish_id") authors = request.POST.getlist("authors") print(request.POST) print(authors) Book.objects.filter(pk=edit_book_id).update(title=title,price=price,pub_date=pub_date,publish_id=publish_id) edit_book.authors.set(authors) return redirect("/books/") def book_del(request,del_book_id): Book.objects.filter(pk=del_book_id).delete() return redirect("/books/")
templates
book_view.html 展示页面
<!DOCTYPE html> <html lang="zh-CN"> <head> <meta charset="UTF-8"> <title>Title</title> <meta name="viewport" content="width=device-width, initial-scale=1"> <!-- 最新版本的 Bootstrap 核心 CSS 文件 --> <link rel="stylesheet" href="/static/bootstrap/css/bootstrap.css"> </head> <body> <h1 style="text-align:center;padding:20px 0; font-weight:bold;">首页书籍系统</h1> <div class="container"> <div class="row"> <div class="col-md-8 col-md-offset-2"> <table class="table table-bordered table-hover table-striped"> <thead> <tr> <th>编号</th> <th>书籍名称</th> <th>价格</th> <th>出版日期</th> <th>出版社</th> <th>作者</th> <th>操作</th> </tr> </thead> <tbody> {% for book in book_list %} <tr> <td>{{ forloop.counter }}</td> <td>{{ book.title }}</td> <td>{{ book.price }}</td> <td>{{ book.pub_date|date:"Y-m-d" }}</td> <td>{{ book.publish.name }}</td> <td> {% for author in book.authors.all %} <span>{{ author.name }}</span> {% if not forloop.last %} , {% endif %} {% endfor %} </td> <td> <a href="/books/delete/{{ book.pk }}" class="btn btn-danger btn-sm">删除</a> <a href="/books/edit/{{ book.pk }}" class="btn btn-warning btn-sm">编辑</a> </td> </tr> {% endfor %} </tbody> </table> <a href="/books/add/" class="btn btn-primary pull-right">添加书籍</a> </div> </div> </div> <script src="/static/js/jquery.js"></script> <script> $("h3").click(function () { $(this).css("color","green") }) </script> </body> </html>
book_edit.html 编辑页面
<!DOCTYPE html> <html lang="zh-CN"> <head> <meta charset="UTF-8"> <title>Title</title> <meta name="viewport" content="width=device-width, initial-scale=1"> <!-- 最新版本的 Bootstrap 核心 CSS 文件 --> <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@3.3.7/dist/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous"> </head> <body> <h3 style="text-align:center;padding:20px 0; font-weight:bold;">编辑书籍</h3> <div class="container"> <div class="row"> <div class="col-md-6 col-md-offset-3"> <form action="" method="post"> {% csrf_token %} <div class="form-group"> <label for="title">书籍名称</label> <input class="form-control" value="{{ edit_book.title }}" type="text" name="title" id="title"> </div> <div class="form-group"> <label for="price">价格</label> <input class="form-control" value="{{ edit_book.price }}" type="text" name="price" id="price"> </div> <div class="form-group"> <label for="pub_date">出版日期</label> <input class="form-control" value="{{ edit_book.pub_date|date:'Y-m-d' }}" type="date" name="pub_date" id="pub_date"> </div> <div class="form-group"> <label for="pub_date">出版社</label> <select name="publish_id" id="" class="form-control"> {% for publish in publish_list %} {% if edit_book.publish == publish %} <option selected value="{{ publish.pk }}">{{ publish.name }}</option> {% else %} <option value="{{ publish.pk }}">{{ publish.name }}</option> {% endif %} {% endfor %} </select> </div> <div class="form-group"> <label for="pub_date">作者</label> <select name="authors" id="" class="form-control" multiple> {% for author in author_list %} {% if author in edit_book.authors.all %} <option selected value="{{ author.pk }}">{{ author.name }}</option> {% else %} <option value="{{ author.pk }}">{{ author.name }}</option> {% endif %} {% endfor %} </select> </div> <input type="submit" value="提交" class="btn btn-default pull-right"> </form> </div> </div> </div> </body> </html>
book_add.html 添加页面
<!DOCTYPE html> <html lang="zh-CN"> <head> <meta charset="UTF-8"> <title>Title</title> <meta name="viewport" content="width=device-width, initial-scale=1"> <!-- 最新版本的 Bootstrap 核心 CSS 文件 --> <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@3.3.7/dist/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous"> </head> <body> <h3 style="text-align:center;padding:20px 0; font-weight:bold;">添加书籍</h3> <div class="container"> <div class="row"> <div class="col-md-6 col-md-offset-3"> <form action="" method="post"> {% csrf_token %} <div class="form-group"> <label for="title">书籍名称</label> <input class="form-control" type="text" name="title" id="title"> </div> <div class="form-group"> <label for="price">价格</label> <input class="form-control" type="text" name="price" id="price"> </div> <div class="form-group"> <label for="pub_date">出版日期</label> <input class="form-control" type="date" name="pub_date" id="pub_date"> </div> <div class="form-group"> <label for="pub_date">出版社</label> <select name="publish_id" id="" class="form-control"> {% for publish in publish_list %} <option value="{{ publish.pk }}">{{ publish.name }}</option> {% endfor %} </select> </div> <div class="form-group"> <label for="pub_date">作者</label> <select name="authors" id="" class="form-control" multiple> {% for author in author_list %} <option value="{{ author.pk }}">{{ author.name }}</option> {% endfor %} </select> </div> <input type="submit" value="提交" class="btn btn-default pull-right"> </form> </div> </div> </div> </body> </html>
settings中 logging日志 LOGGING = { 'version': 1, 'disable_existing_loggers': False, 'handlers': { 'console':{ 'level':'DEBUG', 'class':'logging.StreamHandler', }, }, 'loggers': { 'django.db.backends': { 'handlers': ['console'], 'propagate': True, 'level':'DEBUG', }, } }
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
