2.数据库基本操作

一、GET请求和POST请求

1. GET请求和POST请求
都属于HTTP协议规定的请求方法

2. 什么时候用GET请求?
1. 浏览器想要得到一个HTML页面的时候
2. 搜索引擎查询关键字的时候 www.sogo.com/web/?query=迪丽热巴

3. 什么时候用POST?
1. 向后端提交数据
1. 大段的数据
2. 包含隐私的数据
3. 上传文件

4. 实际中GET和POST的应用场景
1. GET:
1. 直接在浏览器地址栏输入URL访问网站
2. a标签
2. POST:
1. 登录注册
2. 修改(新增)大段的数据
3. 上传文件

 二、图书管理系统表结构设计
图书管理系统的三种角色
1. 出版社
2. 书
3. 作者
总结关系:
1. 一本书 只能 有一个出版社
2. 一本书 能有 多个作者
3. 一个作者 能写 多本书

出版社和书: 一对多 --> 外键
书和作者: 多对多 --> 用第三张表做关联

 

models.py

from django.db import models


# 出版社
class Publish(models.Model):
    id = models.AutoField(primary_key=True)  # 自增的ID主键
    # 创建一个varchar(64)的唯一的不为空的字段
    name = models.CharField(unique=True, null=False, max_length=30)


# 书
class Book(models.Model):
    id = models.AutoField(primary_key=True)  # 自增的ID主键
    # 创建一个varchar(64)的唯一的不为空的字段
    title = models.CharField(max_length=64, null=False, unique=True)
    # 和出版社关联的外键字段
    publisher = models.ForeignKey(to="Publish", on_delete=models.DO_NOTHING)


# 作者
class Author(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=64, null=False, unique=True)
    # 告诉ORM 我这张表和book表是多对多的关联关系,ORM自动帮我生成了第三张表
    book = models.ManyToManyField(to="Book")

    def __str__(self):
        return "<Author object:{}".format(self.name)

 

三、 python3.7连接mysql要在项目中的__init__.py进行配置

在项目目录下的__init__.py添加如下使pymysql代替MySQldb连接mysql数据库

import pymysql

pymysql.install_as_MySQLdb()

 

数据库中已经存在数据，进行字段修改或者添加时出现如下图情况:

选择1）的话，要在修改的字段加个defalut默认值

选择2)的话，需要手动加个默认值

2

"北京"

 

查\增\删\改操作

        1. 查
            book_list = models.Book.objects.all()  --> 书对象的列表
            
        2. 增
            new_book_obj = models.Book.objects.create(
                title="新书的名字",
                # publisher=publusher_obj,
                publisher_id=7
            )
        3. 删除
            models.Book.objects.get(id=10).delete()
            
        4. 修改
            book_obj = models.Book.objects.get(id=9)
            book_obj.title=request.POST.get("book_title")
            book_obj.publisher_id=9
            book_obj.save()

 

 urls.py

"""tetdjango URL Configuration

The `urlpatterns` list routes URLs to views. For more information please see:
    https://docs.djangoproject.com/en/2.0/topics/http/urls/
Examples:
Function views
    1. Add an import:  from my_app import views
    2. Add a URL to urlpatterns:  path('', views.home, name='home')
Class-based views
    1. Add an import:  from other_app.views import Home
    2. Add a URL to urlpatterns:  path('', Home.as_view(), name='home')
Including another URLconf
    1. Import the include() function: from django.urls import include, path
    2. Add a URL to urlpatterns:  path('blog/', include('blog.urls'))
"""
from django.contrib import admin
from django.urls import path
from app import views

urlpatterns = [
    path('admin/', admin.site.urls),
    path('publish/', views.publish_list),
    path('add_publish/', views.add_publish),
    path('del/', views.delete),
    path('update/', views.update),

    path('book_list/', views.book_list),
    path('add_book/', views.add_book),
    path('del_book/', views.del_book),
    path('update_book/', views.update_book),

    # 作者相关的对应关系
    path('author_list/', views.author_list),  # 展示作者
    path('add_author/', views.add_author),  # 添加作者
    path('delete_author/', views.delete_author),  # 删除作者
    path('edit_author/', views.edit_author),  # 编辑作者
]

 

views.py

from django.shortcuts import render, redirect, HttpResponse
from app import models


# 1.查看出版社"
def publish_list(request):
    # 去数据库查出所有的出版社,填充到HTML中,给用户返回，根据id进行排序
    ret = models.Publish.objects.all().order_by("id")
    return render(request, "app/publish_list.html", {"publish_list": ret})


# 2.添加出版社
def add_publish(request):
    error_msg = ""
    if request.method == "POST":
        new_name = request.POST.get("add", None)
        if new_name:
            # 把获取的出版社名字添加到数据库中
            models.Publish.objects.create(name=new_name)
            # 出版社添加成功后，跳转到出版社页面
            return redirect("/publish/")
        else:
            error_msg = "出版社名字不能为空"

    return render(request, "app/publish_add.html", {"error": error_msg})


# 删除出版社
def delete(request):
    del_id = request.GET.get("id", None)  # 字典取值,娶不到默认为None
    if del_id:
        obj_id = models.Publish.objects.get(id=del_id)
        # 删除
        obj_id.delete()
        return redirect("/publish/")
    else:
        return HttpResponse("要删除的数据不存在!")


# 修改出版社
def update(request):
    if request.method == "POST":
        # 接收提交的参数
        sub_id = request.POST.get("id")
        sub_name = request.POST.get("pubname")
        # 获取对象
        update_obj = models.Publish.objects.get(id=sub_id)
        # 修改出版社名
        update_obj.name = sub_name
        # 把修改提交到数据库，记住一定要保存，不保存的话数据是不会进入数据库的
        update_obj.save()
        return redirect("/publish/")

    # 获取id和出版名
    id = request.GET.get("id")
    if id:
        publish_obj = models.Publish.objects.get(id=id)
        return render(request, "app/update_publish.html", {"publishsher": publish_obj})
    else:
        return HttpResponse("编辑的出版社不存在")


# 2.1查看所有的书籍
def book_list(request):
    book_obj = models.Book.objects.all().order_by("id")
    return render(request, "app/book_list.html", {"book_list": book_obj})


# 2.2添加书籍
def add_book(request):
    # post请求
    if request.method == "POST":
        book_title = request.POST.get("book_title", None)
        publisher_id = request.POST.get("publisher")
        # 添加的书名不为空，添加书籍
        if book_title:
            models.Book.objects.create(
                title=book_title,
                publisher_id=publisher_id
            )

            # 用出版社对象创建
            # publisher_obj = models.Publisher.objects.get(id=publisher_id)
            # models.Book.objects.create(title=book_title, publisher=publisher_obj)
            return redirect("/book_list/")
    # get请求
    publisher_obj = models.Publish.objects.all()
    return render(request, "app/add_book.html", {"publisher_list": publisher_obj})


# 2.3根据id删除书籍
def del_book(request):
    # 获取要删除的id
    book_id = request.GET.get("id")
    # 获取对象
    book_obj = models.Book.objects.get(id=book_id)
    book_obj.delete()
    return redirect("/book_list/")


# 2.4修改书籍
def update_book(request):
    if request.method == "POST":
        book_id = request.POST.get("id")
        new_title = request.POST.get("book_name")
        new_publisher_id = request.POST.get("publisher")
        # 根据id获取对象
        update_book_obj = models.Book.objects.get(id=book_id)
        # 修改
        update_book_obj.title = new_title
        update_book_obj.publisher_id = new_publisher_id
        # 将修改提交到数据库
        update_book_obj.save()
        # 返回书籍列表页面,查看是否编辑成功
        return redirect("/book_list/")
    book_id = request.GET.get("id", None)
    if book_id:
        book_obj = models.Book.objects.get(id=book_id)
        # 把数据回显
        ret = models.Publish.objects.all()
        return render(
            request,
            "app/update_book.html",
            {"book_obj": book_obj, "publisher_list": ret}
        )


# 3.1作者列表
def author_list(request):
    # 查询所有的作者
    all_author = models.Author.objects.all()
    return render(request, "app/author_list.html", {"author_list": all_author})


# 添加作者
def add_author(request):
    if request.method == "POST":
        print("in post...")
        # 取到提交的数据
        new_author_name = request.POST.get("author_name")
        # post提交的数据是多个值的时候一定会要用getlist,如多选的checkbox和多选的select
        books = request.POST.getlist("books")
        # 创建作者
        new_author_obj = models.Author.objects.create(name=new_author_name)
        # 把新作者和书籍建立对应关系,自动提交
        new_author_obj.book.set(books)
        # 跳转到作者列表页面,查看是否添加成功!
        return redirect("/author_list/")

    # 查询所有的书籍
    ret = models.Book.objects.all()
    return render(request, "app/add_author.html", {"book_list": ret})


# 删除作者
def delete_author(request):
    # 从URL里面取到要删除的作者id
    delete_id = request.GET.get("id")
    # 根据ID值取到要删除的作者对象,直接删除
    # 1. 去作者表把作者删了
    # 2. 去作者和书的关联表,把对应的关联记录删除了
    models.Author.objects.get(id=delete_id).delete()
    # 返回作者列表页面
    return redirect("/author_list/")


# 编辑作者
def edit_author(request):
    # 如果编辑完提交数据过来
    if request.method == "POST":
        # 拿到提交过来的编辑后的数据
        edit_author_id = request.POST.get("author_id")
        new_author_name = request.POST.get("author_name")
        # 拿到编辑后作者关联的书籍信息
        new_books = request.POST.getlist("books")
        # 根据ID找到当前编辑的作者对象
        edit_author_obj = models.Author.objects.get(id=edit_author_id)
        # 更新作者的名字
        edit_author_obj.name = new_author_name
        # 更新作者关联的书的对应关系
        edit_author_obj.book.set(new_books)
        # 将修改提交到数据库
        edit_author_obj.save()
        # 返回作者列表页,查看是否编辑成功
        return redirect("/author_list/")

    # 从URL里面取要编辑的作者的id信息
    edit_id = request.GET.get("id")
    # 找到要编辑的作者对象
    edit_author_obj = models.Author.objects.get(id=edit_id)

    # 查询所有的书籍对象
    ret = models.Book.objects.all()
    return render(request, "app/edit_author.html", {"book_list": ret, "author": edit_author_obj})

templates模板

publish_list.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>出版社</title>
</head>
<body>

<a href="/add_publish/">添加出版社</a>
<table border="1px" style="width: 300px;">
    <tr>
        <th>序号</th>
        <th>id</th>
        <th>name</th>
        <th>操作</th>
    </tr>
    {% for publish in publish_list %}
        <tr>
            <td>{{ forloop.counter }}</td>
            <td>{{ publish.id }}</td>
            <td>{{ publish.name }}</td>
            <td><a href="/del/?id={{ publish.id }}">删除</a> <a
                    href="/update/?id={{ publish.id }}">修改</a></td>
        </tr>
    {% endfor %}
</table>
</body>
</html>

publish_add.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>添加出版社</title>
</head>
<body>
<h1>添加出版社</h1>
<form action="/add_publish/" method=post>
    <input type="text" name="add">
    <input type="submit" value="提交">
    <p style="color:red">{{ error }}</p>
</form>
</body>
</html>

update_publish.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>修改出版社</title>
</head>
<body>
<h1>编辑出版社</h1>
<form action="/update/" method="post">
    <input type="text" name="id" value="{{ publishsher.id }}">
    <input type="text" name="pubname" value="{{ publishsher.name }}">
    <input type="submit" value="提交">
</form>
</body>
</html>

book_list.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>查看书列表</title>
</head>
<body>
<h1>所有的书籍都在这里</h1>
<a href="/add_book/">添加书籍</a>
<table border="1px" style="width: 400px;">
    <tr>
        <th>id</th>
        <th>title</th>
        <th>publisher</th>
        <th>操作</th>
    </tr>
    {% for book in book_list %}
        <tr>
            <td>{{ book.id }}</td>
            <td>{{ book.title }}</td>
            <td>{{ book.publisher_id }}</td>
            <td><a href="/del_book/?id={{ book.id }}">删除</a> <a
                    href="/update_book/?id={{ book.id }}">修改</a></td>
        </tr>
    {% endfor %}
</table>
</body>
</html>

add_book.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>添加书籍</title>
</head>
<body>
<form action="/add_book/" method="post">
    书名:<input type="text" name="book_title"><br>
    出版社:
    <select name="publisher">
        {% for publisher in publisher_list %}
            <option value="{{ publisher.id }}">{{ publisher.name }}</option>
        {% endfor %}

    </select><br>
    <input type="submit" value="提交">
</form>
</body>
</html>

update_book.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>修改书籍</title>
</head>
<body>
<h1>编辑书籍</h1>
<form action="/update_book/" method="post">
    <input type="text" name="id" value="{{ book_obj.id }}"><br/>
    书名:
    <input type="text" name="book_name" value="{{ book_obj.title }}"><br/>
    出版社:
        <select name="publisher">


            {% for publisher in publisher_list %}

                {% if book_obj.publisher_id == publisher.id %}
                    {#  当前书籍关联的出版社才默认选中#}
                    <option selected value="{{ publisher.id }}">{{ publisher.name }}</option>
                {% else %}
                    {# 其他的出版社不选中 #}
                    <option value="{{ publisher.id }}">{{ publisher.name }}</option>
                {% endif %}
            {% endfor %}


        </select>
<br>
<input type="submit" value="提交">
</form>
</body>
</html>

author_list.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>作者列表</title>
</head>
<body>

<a href="/add_author/">添加新的作者</a>

<h1>所有的作者</h1>

<table border="1">
    <thead>
    <tr>
        <th>#</th>
        <th>ID</th>
        <th>名字</th>
        <th>作品</th>
        <th>操作</th>
    </tr>
    </thead>
    <tbody>
    {% for author in author_list %}
        <tr>
        <td>{{ forloop.counter }}</td>
        <td>{{ author.id }}</td>
        <td>{{ author.name }}</td>
        <td>
            {% for book in author.book.all %}
                {{ book.title }}&nbsp;
        {% endfor %}
        </td>
        <td>
            <a href="/delete_author/?id={{ author.id }}">删除</a>
            <a href="/edit_author/?id={{ author.id }}">编辑</a>
        </td>
        </tr>
    {% endfor %}
    </tbody>
</table>
</body>
</html>

add_author.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>添加作者</title>
</head>
<body>

<h1>添加作者</h1>

<form action="/add_author/" method="post">
    <p>
       作者姓名:<input type="text" name="author_name">
    </p>

    <p>
        作品:
        <select multiple name="books">
            {% for book in book_list %}
            <option value="{{ book.id }}">{{ book.title }}</option>
            {% endfor %}
        </select>
    </p>

    <p>
        <input type="submit" value="提交">
    </p>

</form>

</body>
</html>

edit_author.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>编辑作者</title>
</head>
<body>

<h1>编辑作者</h1>

<form action="/edit_author/" method="post">
    <input type="text" name="author_id" value="{{ author.id }}" style="display: none">
    <p>
       作者姓名:<input type="text" name="author_name" value="{{ author.name }}">
    </p>

    <p>
        作品:
        <select multiple name="books">
            {% for book in book_list %}
{#                如果当前这本书 在 当前作者关联的所有书 里面 #}
                {% if book in author.book.all %}
                    <option selected value="{{ book.id }}">{{ book.title }}</option>
                {% else %}
                <option  value="{{ book.id }}">{{ book.title }}</option>{% endif %}
            {% endfor %}
        </select>
    </p>

    <p>
        <input type="submit" value="提交">
    </p>

</form>

</body>
</html>