Django中ORM系统多表数据操作
一,多表操作之增删改查
1.在seting.py文件中配置数据库连接信息
2.创建数据库关联关系models.py
from django.db import models # Create your models here. class Book(models.Model): title = models.CharField( max_length=32) pub_date=models.DateField() price=models.DecimalField(max_digits=5,decimal_places=2)
#以book表为基本表创建与出版社表的关联关系关联字段publish_id Django会自动拼接_id publish=models.ForeignKey(to="Publish",to_field="id",on_delete=models.CASCADE,null=True)
#以book表为基本表创建通过与第三方表book2author与author表的多对多关联关系, authors=models.ManyToManyField("Author",db_table="book2authors") # 创建关系表 def __str__(self): return self.title class Publish(models.Model): name=models.CharField( max_length=32) city=models.CharField( max_length=32) email=models.CharField(max_length=32) def __str__(self): return self.name class Author(models.Model): name=models.CharField( max_length=32) age=models.IntegerField() #books=models.ManyToManyField("Book")
#以author表为基本表通过创建ad字段关联AuthorDetail作者详情表的一对一的关联关系 ad=models.OneToOneField("AuthorDetail",null=True,on_delete=models.CASCADE) def __str__(self): return self.name class AuthorDetail(models.Model): birthday=models.DateField() telephone=models.BigIntegerField() addr=models.CharField( max_length=64) # author=models.OneToOneField("Author",on_delete=models.CASCADE) def __str__(self): return str(self.telephone) ''' class book_authors(models.Model): book=models.ForeignKey("Book",on_delete=models.CASCADE) author=models.ForeignKey("Author",on_delete=models.CASCADE) ''' class Emp(models.Model): name=models.CharField(max_length=32) age=models.IntegerField() salary=models.DecimalField(max_digits=8,decimal_places=2) dep=models.CharField(max_length=32) province=models.CharField(max_length=32) class Article(models.Model): title=models.CharField(max_length=32) comment_num=models.IntegerField() poll_num=models.IntegerField() def __str__(self):return self.title
3 在启动函数时,在项目文件setiing.py文件中配置相应的库
4 在url分发器中配置网页与视图函数的映射关系
5 在视图函数views.py中封装对数据库操作的功能,
from django.shortcuts import render,HttpResponse,redirect
# Create your views here.
from app01 import models
def add(request):
################################## 绑定一对多关系 ##############################
# 方式1:
#book=models.Book.objects.create(title="linux",price=122,pub_date="2012-12-12",publish_id=1)
# 方式2:
# pub_obj=models.Publish.objects.filter(name="橘子出版社").first()
# book=models.Book.objects.create(title="php",price=122,pub_date="2012-12-12",publish=pub_obj)
# print(book.title)
# print(book.publish_id)
# print(book.publish) # book书籍出版社对象
# 查询go出版社的邮箱
# models.Publish.objects.filter(id= book.publish_id).first().email
# book.publish.email
########################## 绑定多对多的关系;无非是在关系表创建记录 ##########
# linux这本书绑定两个作者:alex,egon
# linux=models.Book.objects.filter(title="linux").first()
# alex=models.Author.objects.filter(name="alex").first()
# egon=models.Author.objects.filter(name="egon").first()
# print(linux.price)
# print(linux.publish)
#linux.authors.add(alex,egon)
#linux.authors.add(1)
#linux.authors.add(*[1,2])
#linux.authors.remove(alex,egon)
#linux.authors.clear()
#linux.authors.set([1,])
'''
#KEY:关联属性:authors
class Book(models.Model):
title = models.CharField( max_length=32)
pub_date=models.DateField()
price=models.DecimalField(max_digits=5,decimal_places=2)
publish=models.ForeignKey(to="Publish",to_field="id",on_delete=models.CASCADE,null=True)
authors=models.ManyToManyField("Author",db_table="book2authors") # 创建关系表
def __str__(self):
return self.title
'''
###############
# 正向操作按字段,反向操作按表名小写
linux = models.Book.objects.filter(title="linux").first()
go = models.Book.objects.filter(title="go").first()
alex = models.Author.objects.filter(name="alex").first()
# 给alex作者绑定两本书籍: linux,go
alex.book_set.add(linux,go)
return HttpResponse("添加成功!")
def query(request):
'''
一 基于对象的跨表查询( 子查询:以上一次的查询结果作为下一次的查询条件)
(1)一对多
正向查询:按字段 book.publish
Book对象 ---------------------------------- > Publish 对象
<---------------------------------
反向查询:按表名小写_set.all()
(2)多对多
正向查询:按字段 book.authors.all()
Book对象 ---------------------------------- > Author 对象
<---------------------------------
反向查询:按表名小写_set.all()
(2)一对一
正向查询:按字段 book.ad
Author 对象 ---------------------------------- > AuthorDetail 对象
<---------------------------------
反向查询:按表名小写
二 基于双下划綫的跨表查询:
KEY:通知ORM引擎如何跨表: 正向查询按字段,反向查询按表名小写
:param request:
:return:
'''
重点1############一基于对象的跨表查询#######################
#(1)一对多
# 1 查询linux这本书籍的出版社的地址
# book=models.Book.objects.filter(title="linux").first()
# print(book.publish.city)
# 2 查询苹果出版社出版的所有书籍
# publish=models.Publish.objects.filter(name="苹果出版社").first()
# queryset=publish.book_set.all()
# print(queryset) # <QuerySet [<Book: linux>, <Book: python>]>
# (2)多对多
# 1 查询linux书籍的所有作者
# linux=models.Book.objects.filter(title="linux").first()
# queryset=linux.authors.all() # <QuerySet [<Author: alex>]>
# print(queryset)
# 2 查询alex作者出版过得所有书籍
# alex=models.Author.objects.filter(name="alex").first()
# queryset=alex.book_set.all()
# print(queryset) # <QuerySet [<Book: linux>, <Book: go>]>
# (3)一对一
# 1 查询alex的手机号
# alex = models.Author.objects.filter(name="alex").first()
# print(alex.ad.telephone)
# 2 查询手机号为911的作者的名字
# ad=models.AuthorDetail.objects.filter(telephone=911).first()
# print(ad.author.name)
重点2###基于双下划线的跨表查询(join查询)#################################
# 1 查询linux这本书籍的出版社的地址
'''
SELECT app01_publish.city from app01_book INNER JOIN app01_publish
ON app01_book.publish_id = app01_publish.id
WHERE app01_book.title ="linux"
'''
# 方式1
# queryset=models .Book.objects.filter(title="linux").values("price","publish__city")
# print(queryset)
# # 方式2
# queryset=models.Publish.objects.filter(book__title="linux").values("city")
# print(queryset)
# 2 查询linux书籍的所有作者
#queryset=models.Book.objects.filter(title="linux").values("authors__name")
#queryset=models.Book.objects.filter(title__startswith="l").values("authors__name") # ********
#print(queryset) # <QuerySet [{'authors__name': 'alex'}, {'authors__name': 'alex'}, {'authors__name': 'egon'}]>
# queryset=models.Author.objects.filter(book__title="linux").values("name")
# 3 查询alex的手机号
# queryset=models.Author.objects.filter(name="alex").values("ad__telephone")
# queryset=models.AuthorDetail.objects.filter(author__name="alex").values("telephone")
# print(queryset) # <QuerySet [{'telephone': 110}]>
# 重点3 连续跨表
# 4 查询人民出版社出版过的所有书籍的名字以及作者的姓名
# queryset=models.Book.objects.filter(publish__name="人民出版社").values("title","authors__name")
# models.Author.objects.filter(book__publish__name="人民出版社").values("book__title","name")
# 5 手机号以151开头的作者出版过的所有书籍名称以及出版社名称
queryset=models.Book.objects.filter(authors__ad__telephone__contains="1").values("title","publish__name")
print(queryset)
return HttpResponse("查询成功!")
重点4:###################### 分组查询 #####################################
# 单表分组查询
#queryset=models.Emp.objects.all() # select * from emp
# queryset=models.Emp.objects.values("name") # select name from emp;
# print(queryset)
'''
单表分组查询:
#查询每一个部门名称以及对应的员工数
sql:
select dep,Count(*) from emp group by dep;
select dep,AVG(salary) from emp group by dep;
orm:
queryset=models.Emp.objects.values("dep").annotate(c=Count("*"))
'''
from django.db.models import Avg,Count,Max,Min
# 查询每一个部门的人数
# queryset=models.Emp.objects.values("dep").annotate(c=Count("*"))
# print(queryset) # <QuerySet [{'dep': '销售部', 'c': 1}, {'dep': '人事部', 'c': 2}]>
#
# # 查询每一个省份的平均薪水
# queryset=models.Emp.objects.values("province").annotate(avg_salary=Avg("salary"))
# print(queryset) # <QuerySet [{'province': '山东', 'avg_salary': 4500.0}, {'province': '河北', 'avg_salary': 5000.0}]>
############### 多表分组查询
# 1 查询每一个出版社的名字和出版过的书籍的平均价格
'''
-- sql语句:
SELECT app01_publish.name,AVG(app01_book.price) from app01_book LEFT JOIN app01_publish on
app01_book.publish_id = app01_publish.id
group by app01_publish.id,app01_publish.name
'''
# queryset=models.Publish.objects.values("id","name").annotate(avg_price=Avg("book__price"))
# queryset=models.Publish.objects.values("id","name","email","city").annotate(avg_price=Avg("book__price"))
#打印结果
# [{"id":1,"name":"苹果出版社","eamil":"123","city":"beijing",'avg_price': 119.0},
{"id":1,"name":"橘子出版社","eamil":"123","city":"beijing",'avg_price': 155.333333.0}]
# queryset=models.Publish.objects.all().annotate(avg_price=Avg("book__price"))
# print(queryset) #<QuerySet [<Publish: 苹果出版社>, <Publish: 橘子出版社>]>
# for obj in queryset:
# print(obj.name,obj.avg_price)
# 2 查询每一个作者的名字以及出版书籍的个数
queryset=models.Author.objects.annotate(c=Count("book")).values("name","c")
print(queryset) # <QuerySet [{'name': 'alex', 'c': 2}, {'name': 'egon', 'c': 2}]>
# 3 查询每一个书籍的名称以及作者的个数
queryset=models.Book.objects.annotate(c=Count("authors")).values("title","c")
print(queryset)
# 4 查询作者个数大于1 的每一本书籍的名称和作者个数
queryset=models.Book.objects.annotate(c=Count("authors")).filter(c__gt=1).values("title","c")
print(queryset) # <QuerySet [{'title': 'python', 'c': 2}, {'title': 'go', 'c': 2}]>
# 5 查询书籍名称包含"h"的书籍名称和作者个数
queryset=models.Book.objects.filter(title__contains="h").annotate(c=Count("authors")).values("title","c")
重点5############# F查询与Q查询
# F查询
from django.db.models import F,Q,Avg
# 1 查询评论数大于100的文章
# queryset=models.Article.objects.filter(comment_num__gt=100)
# print(queryset)
# 2 查询评论数大于点赞数的文章
# queryset=models.Article.objects.filter(comment_num__gt=F("poll_num"))
# print(queryset) # <QuerySet [<Article: 那一夜>]>
# 3 查询点赞数大于两倍评论数
# queryset=models.Article.objects.filter(poll_num__gt=F("comment_num")*2)
# print(queryset) # <QuerySet [<Article: 那一天>]>
# 4 将所有的书籍的价格提高100元
# models.Book.objects.all().update(price=F("price")+100)
# Q查询
# 5 查询价格大于300或者名称以p开头的书籍
# Q : & | ~
# queryset=models.Book.objects.filter(Q(title__startswith="p")&Q(price__gt=300))
# print(queryset) # <QuerySet [<Book: python>, <Book: php>, <Book: pJS>]>
# # 5 查询价格大于300或者不是2019年一月份的书籍
# lq=Q(price__gt=300)|~Q(Q(pub_date__year=2019)&Q(pub_date__month=1))
queryset = models.Book.objects.filter(q)
# print(queryset)
6 刷新网页来测试数据库相关功能
实例1ORM 多表关联图书管理系统
第一步 创建项目,先创建模型对数据库进行操作
并配置数据库信息
1 创建表
from django.db import models # Create your models here. class Book(models.Model): title = models.CharField( max_length=32) pub_date=models.DateField() price=models.DecimalField(max_digits=5,decimal_places=2) #以Book表为基本表创建与Publish表的外键关联关系关联字段publish_id Django自动拼接_id,on_delete=models.CASCADE:级联删除 publish=models.ForeignKey(to="Publish",to_field="id",on_delete=models.CASCADE,null=True) #以Book表为基本表创建第三方表book2authors与Author表建立多对多的关联关系 authors=models.ManyToManyField("Author",db_table="book2authors") # 创建关系表 def __str__(self): return self.title class Publish(models.Model): name=models.CharField( max_length=32) city=models.CharField( max_length=32) email=models.CharField(max_length=32) def __str__(self): return self.name class Author(models.Model): name=models.CharField( max_length=32) age=models.IntegerField() #books=models.ManyToManyField("Book") #以作者表Author为基本表创建与作者详情表AuthorDetail的一对一的关联关系 ad=models.OneToOneField("AuthorDetail",null=True,on_delete=models.CASCADE) def __str__(self): return self.name class AuthorDetail(models.Model): birthday=models.DateField() telephone=models.BigIntegerField() addr=models.CharField( max_length=64) # author=models.OneToOneField("Author",on_delete=models.CASCADE) def __str__(self): return str(self.telephone)
2 配置数据库信息
DATABASES = { 'default': { 'ENGINE': 'django.db.backends.mysql', 'NAME':'duo', # 要连接的数据库，连接前需要创建好 'USER':'root', # 连接数据库的用户名 'PASSWORD':'123', # 连接数据库的密码 'HOST':'127.0.0.1', # 连接主机，默认本级 'PORT':3306 # 端口 默认3306 }, }
3 启动项目:通过模型在目标数据库中创建相应的表,
#注意:在建表之前必须先创建好seting.py文件中mysql配置的库 在建表之前要在url.py路由分发的同级目录下的启动文件__init__.py中导入 import pymysql pymysql.install_as_MySQLdb() 然后在项目路径文件下执行以下命令在mysql中创建表 python manage.py makemigrations python manage.py migrate
第二步 创建URL与该URL调用的视图函数之间的映射表
from django.contrib import admin from django.urls import path,re_path from duo_books import views urlpatterns = [ path('admin/', admin.site.urls), path('add/', views.add), path('query/', views.query), path('books/', views.books), path('books/add/', views.addbook), re_path('books/delete/(\d+)/', views.delbook), re_path('books/edit/(\d+)/', views.editbook),
第三步 创建视图函数中对数据的操作进行封装
def books(request): queryset = models.Book.objects.all() return render(request, "books.html", {"queryset": queryset}) def delbook(request, id): models.Book.objects.filter(pk=id).delete() return redirect("/books/") def addbook(request): if request.method == "POST": data = request.POST.dict() data.pop("csrfmiddlewaretoken") data.pop("author_list") book = models.Book.objects.create(**data) # 保证提交键值对的键必须和数据库表字段一致 # 为书籍绑定作者关系 author_list = request.POST.getlist("author_list") print(author_list) # ['1', '2'] book.authors.add(*author_list) return redirect("/books/") else: publish_list = models.Publish.objects.all() author_list = models.Author.objects.all() return render(request, 'addbook.html', locals()) def editbook(request,edit_book_id): edit_book = models.Book.objects.filter(pk=edit_book_id).first() if request.method=="POST": # 方式1: # title=request.POST.get("title") # price=request.POST.get("price") # pub_date=request.POST.get("pub_date") # publish_id=request.POST.get("publish_id") # author_list=request.POST.getlist("author_list") # models.Book.objects.filter(pk=edit_book_id).update(title=title,price=price,pub_date=pub_date,publish_id=publish_id) # update只有queryset才能调用 # edit_book.authors.set(author_list) # 方式2: data=request.POST.dict() data.pop("csrfmiddlewaretoken") author_list=data.pop("author_list") models.Book.objects.filter(pk=edit_book_id).update(**data) # 保证提交键值对的键必须和数据库表字段一致 # 为书籍绑定作者关系 author_list=request.POST.getlist("author_list") edit_book.authors.set(author_list) return redirect("/books/") else: publish_list=models.Publish.objects.all() author_list=models.Author.objects.all() return render(request,'editbook.html',locals())
第四步 创建静态文件夹static放置Jquery,css,bootstrap样式文件
修改Django配置,完成对静态文件的配置
STATIC_URL = '/static/' STATICFILES_DIRS=[ os.path.join(BASE_DIR,"static") # 静态文件的物理路径 ]
第五步 在模板文件夹template下建立相应的前端页面
添加功能页面
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Title</title> <!-- 最新版本的 Bootstrap 核心 CSS 文件 --> <link rel="stylesheet" href="/static/bootstrap/css/bootstrap.css"> </head> <body> <h3>添加书籍</h3> <div class="row"> <div class="col-md-4 col-md-offset-3"> <form action="/books/add/" method="post"> {% csrf_token %} <div class="form-group"> <label for="title">书籍名称</label> <input class="form-control" type="text" id="title" placeholder="名称" name="title"> </div> <div class="form-group"> <label for="price"> 价格</label> <input class="form-control" type="text" id="price" placeholder="价格" name="price"> </div> <div class="form-group"> <label for="pub_date">出版日期</label> <input class="form-control" type="date" id="pub_date" placeholder="出版日期" name="pub_date"> </div> <div class="form-group"> <label for="publish_id">出版社</label> <select class="form-control" name="publish_id" id=""> {% for publish in publish_list %} <option value="{{ publish.pk }}">{{ publish.name }}</option> {% endfor %} </select> </div> <div class="form-group"> <label for="author">作者</label> <select class="form-control" name="author_list" id="author" multiple> {% for author in author_list %} <option value="{{ author.pk }}">{{ author.name }}</option> {% endfor %} </select> </div> <input type="submit"value="submit" class="btn btn-default pull-right"> </form> </div> </div> </body> </html>
查看功能页面(含删除功能)
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Title</title> <script src="/static/jquery-3.1.js"></script> <!-- 最新版本的 Bootstrap 核心 CSS 文件 --> <link rel="stylesheet" href="/static/bootstrap/css/bootstrap.css"> </head> <body> <h3>书籍列表</h3> <div class="row"> <div class="col-md-8 col-md-offset-2"> <a href="/books/add/" class="btn btn-primary"> 添加书籍</a> <table class="table table-striped table-hover"> <thead> <tr> <th>ID</th> <th>书籍名称</th> <th>价格</th> <th>出版社</th> <th>出版日期</th> <th>作者</th> <th>操作</th> </tr> </thead> <tbody> {% for book in queryset %} <tr> <td>{{ forloop.counter }}</td> <td>{{ book.title }}</td> <td>{{ book.price }}</td> <td>{{ book.publish }}</td> <td>{{ book.pub_date|date:"Y/m/d" }}</td> <td> {% for author in book.authors.all %} <span>{{ author.name }}</span> {% endfor %} </td> <td> <a href="/books/delete/{{ book.pk }}">删除</a> <a href="/books/edit/{{ book.pk }}">编辑</a> </td> </tr> {% endfor %} </tbody> </table> </div> </div> <script> $("h3").click(function () { $(this).css("color","red") }) </script> </body> </html>
编辑功能的页面
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Title</title> <!-- 最新版本的 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>编辑书籍</h3> <div class="row"> <div class="col-md-4 col-md-offset-3"> <form action="" method="post"> {% csrf_token %} <div class="form-group"> <label for="title">书籍名称</label> <input class="form-control" type="text" id="title" placeholder="名称" name="title" value="{{ edit_book.title }}"> </div> <div class="form-group"> <label for="price"> 价格</label> <input class="form-control" type="text" id="price" placeholder="价格" name="price" value="{{ edit_book.price }}"> </div> <div class="form-group"> <label for="pub_date">出版日期</label> <input class="form-control" type="date" id="pub_date" placeholder="出版日期" name="pub_date" value="{{ edit_book.pub_date|date:'Y-m-d' }}"> </div> <div class="form-group"> <label for="publish_id">出版社</label> <select class="form-control" name="publish_id" id=""> {% for publish in publish_list %} {% if publish == edit_book.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="author">作者</label> <select class="form-control" name="author_list" id="author" 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="submit" class="btn btn-default pull-right"> </form> </div> </div> </body> </html>
