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>