django-师生班级增删改查(pymql方式)

PythonWeb框架 专栏收录该内容
50 篇文章 1 订阅

看下效果图(只看老师的,另外班级的、学生的都类似)
这里写图片描述

url配置

from django.conf.urls import url
from . import views

urlpatterns = [
    url(r'^class_list/', views.class_list, name="class_list"),
    url(r'^class_add/', views.class_add),
    url(r'^class_delete/$', views.class_delete, name="class_delete"),
    url(r'^class_edit/$', views.class_edit, name="class_edit"),

    url(r'^student_list/', views.student_list, name="student_list"),
    url(r'^student_delete/', views.student_delete, name="student_delete"),
    url(r'^student_edit/', views.student_edit, name="student_edit"),
    url(r'^student_add/', views.student_add, name="student_add"),

    url(r'^teacher_list/', views.teacher_list, name="teacher_list"),
    url(r'^teacher_delete/', views.teacher_delete, name="teacher_delete"),
    url(r'^teacher_edit/', views.teacher_edit, name="teacher_edit"),
    url(r'^teacher_add/', views.teacher_add, name="teacher_add"),

    url(r'^teacher_search/', views.teacher_search, name="teacher_search"),

]

view视图

from django.shortcuts import render, HttpResponse, redirect, reverse

import pymysql

from db_tools import db_helper


def class_list(request):
    conn = pymysql.connect(host="localhost",
                           database="curd",
                           user="root",
                           password="root",
                           charset="utf8",
                           )

    cursor = conn.cursor(pymysql.cursors.DictCursor)

    # ((1, '全栈1期'), (2, '全栈2期'), (3, '全栈3期'))
    # [{'id': 1, 'cname': '全栈1期'}, {'id': 2, 'cname': '全栈2期'}, {'id': 3, 'cname': '全栈3期'}]
    sql = "select * from class;"
    cursor.execute(sql)
    class_list = cursor.fetchall()

    print("class_list %s " % class_list)
    conn.close()
    cursor.close()

    return render(request, "class_list.html", {"class_list": class_list})


def class_add(request):
    if request.method == "POST":
        cname = request.POST.get("cname")
        print("add cname is %s" % cname)

        sql = "insert into class(cname) VALUES (%s);"

        db_helper.updata(sql, cname)

        return redirect("/class_list/")

    return render(request, "class_add.html")


def class_delete(request):
    class_id = request.GET.get("class_id")
    print("class_id%s" % class_id)

    sql = "delete from class WHERE id=%s;"

    db_helper.updata(sql, class_id)

    return redirect(reverse("class_list"))


def class_edit(request):
    if request.method == "POST":
        cid = request.POST.get("cid")
        cname = request.POST.get("cname")

        print("cid %s cname %s " % (cid, cname))

        sql = "update class set cname=%s WHERE id=%s;"

        db_helper.updata(sql, [cname, cid])

        return redirect(reverse("class_list"))

    cid = request.GET.get("class_id")
    print("class_cid %s", cid)
    sql = "select id,cname from class WHERE id=%s;"

    class_info = db_helper.fetch(sql, cid)

    print("class_info %s" % class_info)

    return render(request, "class_edit.html", {"class_info": class_info})


def student_list(request):
    sql = "select student.id,sname,cname from student" \
          " LEFT JOIN class c ON student.cid = c.id;;"

    student_list = db_helper.fetch(sql, type="fetch_all")

    print("student_list %s" % student_list)

    return render(request, "student_list.html", {"student_list": student_list})


def student_delete(request):
    pass
    student_id = request.GET.get("student_id")

    sql = "delete from student WHERE id=%s;"
    db_helper.updata(sql, student_id)

    return redirect(reverse("student_list"))


def student_add(request):
    if request.method == "POST":
        sname = request.POST.get("sname")
        class_id = request.POST.get("class_list")
        print("sname %s class_name %s" % (sname, class_id))

        sql = "insert into student(sname, cid) VALUES (%s,%s);"

        db_helper.updata(sql, [sname, class_id])
        return redirect(reverse("student_list"))

    sql = "select * from class;"

    class_list = db_helper.fetch(sql, type="fetch_all")

    return render(request, "student_add.html", {"class_list": class_list})


def student_edit(request):
    if request.method == "POST":
        sid = request.POST.get("sid")
        sname = request.POST.get("sname")

        class_selected = request.POST.get("class_list")

        print(sid, sname, class_selected)

        sql = "update student set sname=%s,cid=%s WHERE id=%s;"

        db_helper.updata(sql, [sname, class_selected, sid])

        return redirect(reverse("student_list"))
    student_id = request.GET.get("student_id")
    student_sql = "select * from student WHERE id=%s;"

    student_info = db_helper.fetch(student_sql, student_id)

    class_sql = "select * from class;"

    class_list = db_helper.fetch(class_sql, type="fetch_all")

    return render(request, "student_edit.html", {"student_info": student_info, "class_list": class_list})


# 转换前

[
    {'id': 1, 'tname': 'eggon', 'cname': '全栈1期'},
    {'id': 2, 'tname': 'env_j', 'cname': '全栈1期'},
    {'id': 3, 'tname': 'alex', 'cname': '全栈1期'},
    {'id': 1, 'tname': 'eggon', 'cname': '全栈2期'},
    {'id': 2, 'tname': 'env_j', 'cname': '全栈2期'},
    {'id': 3, 'tname': 'alex', 'cname': '全栈2期'},
    {'id': 1, 'tname': 'eggon', 'cname': '全栈3期~'},
    {'id': 2, 'tname': 'env_j', 'cname': '全栈3期~'},
    {'id': 3, 'tname': 'alex', 'cname': '全栈3期~'}
]

# 转换后
[{'id': 1, 'tname': 'eggon', 'cname': ['全栈1期', '全栈2期', '全栈3期~']},
 {'id': 2, 'tname': 'env_j', 'cname': ['全栈1期', '全栈2期', '全栈3期~']},
 {'id': 3, 'tname': 'alex', 'cname': ['全栈1期', '全栈2期', '全栈3期~']}]


def converse_teacher_list(data):
    tmp = {}

    for i in data:
        key = i["id"]
        if key not in tmp:
            tmp[key] = {"id": key, "tname": i["tname"], "cname": [i["cname"]]}

        else:
            tmp[key]["cname"].append(i["cname"])

    return list(tmp.values())


def teacher_list(request):
    teacher_sql = "select teacher.id,tname,cname from teacher " \
                  "LEFT JOIN class_teacher  ON teacher.id = class_teacher.tid" \
                  " LEFT JOIN class c ON class_teacher.cid = c.id;"

    db = db_helper.DBHelper()

    teacher_list = db.fetch(teacher_sql, type="fetch_all")
    db.conn.close()
    print("teacher_list_before", teacher_list)
    teacher_list = converse_teacher_list(teacher_list)

    print("teacher_list", teacher_list)

    return render(request, "teacher_list.html", {"teacher_list": teacher_list})


def teacher_delete(request):
    teacher_id = request.GET.get("teacher_id")

    db = db_helper.DBHelper()

    sql = "delete from teacher WHERE id=%s;"
    db.update(sql, teacher_id, type="exec_one")
    db.conn.close()

    return redirect(reverse("teacher_list"))


# 添加老师

def teacher_add(request):
    if request.method == "POST":
        tname = request.POST.get("tname")
        class_list = request.POST.getlist("class_list")

        print(tname, class_list)
        sql1 = "insert into teacher(tname) VALUES (%s);"

        sql2 = "insert into class_teacher(tid, cid) VALUES (%s,%s);"

        with db_helper.DBHelper() as db:
            last_id = db.insert(sql1, tname, "insert_one")

            params = [[last_id, cid] for cid in class_list]
            print(params, "----params-----")
            db.insert(sql2, params, "insert_many")

        return redirect(reverse("teacher_list"))

    sql = "select * from class;"
    with db_helper.DBHelper() as db:
        class_list = db.fetch(sql, type="fetch_all")
    return render(request, "teacher_add.html", {"class_list": class_list})


def teacher_edit(request):
    if request.method == "POST":
        tid = request.POST.get("tid")
        tname = request.POST.get("tname")
        clist = request.POST.getlist("class_list")

        sql1 = "update teacher set tname=%s WHERE id=%s;"

        sql2 = "delete from class_teacher WHERE tid=%s;"
        sql3 = "insert into class_teacher(tid, cid) VALUES(%s,%s);"

        with db_helper.DBHelper() as db:
            db.insert(sql1, [tname, tid], type="insert_one")

            params = [[tid, cid] for cid in clist]

            print("params---", params)
            db.insert(sql2, tid, type="insert_one")

            db.insert(sql3, params, type="insert_many")

        return redirect(reverse("teacher_list"))

    teacher_id = request.GET.get("teacher_id")
    teacher_sql = "SELECT teacher.id,tname,cname from teacher LEFT JOIN class_teacher teacher2 " \
                  "ON teacher.id = teacher2.tid LEFT JOIN class " \
                  "ON teacher2.cid = class.id WHERE teacher.id=%s"

    class_sql = "select * from class;"

    with db_helper.DBHelper() as db:
        class_list = db.fetch(class_sql, type="fetch_all")

        teacher_info = db.fetch(teacher_sql, teacher_id, type="fetch_all")
        teacher_info = teacher_converse(teacher_info)
        print("class_list", class_list)

        print("teacher_info", teacher_info)

    return render(request, "teacher_edit.html",
                  {"class_list": class_list, "teacher_info": teacher_info})


# [{'id': 21, 'tname': '王永飞', 'cname': '全栈1期'},
#  {'id': 21, 'tname': '王永飞', 'cname': '全栈2期'}]


def teacher_converse(data):
    tmp = {}

    for i in data:
        key = i["id"]
        if key not in tmp:
            tmp[key] = {"id": key, "tname": i["tname"], "cname": [i["cname"]]}

        else:
            tmp[key]["cname"].append(i["cname"])

    return list(tmp.values())[0]


# [{'id': 4, 'tname': 'wfwerew', 'cname': '全栈2期'},
#  {'id': 5, 'tname': 'werwerw', 'cname': '全栈2期'},
#  {'id': 4, 'tname': 'wfwerew', 'cname': '全栈3期~'},
#  {'id': 5, 'tname': 'werwerw', 'cname': '全栈3期~'}]

def teacher_search(request):
    if request.method == "POST":
        search_str = request.POST.get("search_str")

        teacher_sql = "SELECT teacher.id, tname, cname from teacher LEFT JOIN" \
                      " class_teacher on teacher.id = class_teacher.tid LEFT JOIN " \
                      "class ON class_teacher.cid = class.id WHERE tname LIKE %s;"
        args = ["%{}%".format(search_str), ]

        with db_helper.DBHelper() as db:
            teacher_list = db.fetch(teacher_sql, args=["%{}%".format(search_str), ], type="fetch_all")

            teacher_list = magic(teacher_list)

            print("teacher_list", teacher_list)

        return render(request, "teacher_list.html", {"teacher_list": teacher_list})


def magic(data_o):
    tmp = {}
    for i in data_o:
        if i["id"] not in tmp:
            tmp[i["id"]] = {"id": i["id"], "tname": i["tname"], "cname": [i["cname"], ]}
        else:
            tmp[i["id"]]["cname"].append(i["cname"])
    data = list(tmp.values())
    return data

db_helper.py

from django.shortcuts import render, redirect, HttpResponse

import pymysql

"""

    1. 创建班级表
        -- 创建班级表
        CREATE TABLE class(
          id INT AUTO_INCREMENT PRIMARY KEY ,
          cname CHAR(20) NOT NULL UNIQUE
        )ENGINE =innodb DEFAULT CHARSET="utf8";

    2. 创建老师表
        -- 创建老师表
        CREATE TABLE teacher(
          id INT AUTO_INCREMENT PRIMARY KEY ,
          tname CHAR(20) NOT NULL UNIQUE
        )ENGINE=innodb DEFAULT CHARSET="utf8";

    3. 学生表
        -- 创建学生表
        CREATE TABLE student(
          id INT AUTO_INCREMENT PRIMARY KEY,
          sname CHAR(20) NOT NULL UNIQUE ,
          cid INT,FOREIGN KEY (cid) REFERENCES class(id) ON DELETE CASCADE ON UPDATE CASCADE
        )ENGINE=innodb DEFAULT CHARSET="utf8";

    4. 班级和老师关系表
        -- 创建班级和老师关系表
        CREATE TABLE class_2_teacher(
          id INT AUTO_INCREMENT PRIMARY KEY ,
          tid INT, FOREIGN KEY (tid) REFERENCES teacher(id) ON DELETE CASCADE ON UPDATE CASCADE,
          cid INT, FOREIGN KEY (cid) REFERENCES  class(id) ON DELETE CASCADE ON UPDATE CASCADE
        )engine=innodb DEFAULT CHARSET="utf8";

"""

DB_CONFIG = {

    "host": "localhost",
    "database": "curd",
    "user": "root",
    "password": "root",
    "charset": "utf8",
}


def updata(sql, args=None):
    try:
        conn = pymysql.connect(

            host=DB_CONFIG["host"],
            database=DB_CONFIG["database"],
            user=DB_CONFIG["user"],
            password=DB_CONFIG["password"],
            charset=DB_CONFIG["charset"],
        )

        cursor = conn.cursor(pymysql.cursors.DictCursor)

        cursor.execute(sql, args)
        conn.commit()
    except Exception as e:
        conn.rollback()

    finally:
        conn.close()
        cursor.close()


def fetch(sql, args=None, type="fetch_one"):
    try:
        conn = pymysql.connect(

            host=DB_CONFIG["host"],
            database=DB_CONFIG["database"],
            user=DB_CONFIG["user"],
            password=DB_CONFIG["password"],
            charset=DB_CONFIG["charset"],
        )

        cursor = conn.cursor(pymysql.cursors.DictCursor)

        cursor.execute(sql, args)

        if type == "fetch_one":

            fetch = cursor.fetchone()

        elif type == "fetch_all":
            fetch = cursor.fetchall()

        return fetch
    except Exception as e:
        conn.rollback()

    finally:
        conn.close()
        cursor.close()


class DBHelper():
    def __init__(self):
        self.conn = pymysql.connect(
            host="localhost",
            database="curd",
            user="root",
            password="root",
            charset="utf8"
        )

        self.cursor = self.conn.cursor(pymysql.cursors.DictCursor)

    def fetch(self, sql, args=None, type="fetch_one"):

        try:
            self.cursor.execute(sql, args)
            if type == "fetch_one":
                return self.cursor.fetchone()
            elif type == "fetch_all":
                return self.cursor.fetchall()
        except Exception as e:
            self.conn.rollback()

    def update(self, sql, args=None, type=None):

        try:

            if type == "exec_one":
                self.cursor.execute(sql, args)
            elif type == "exec_many":
                self.cursor.executemany(sql, args)

            self.conn.commit()
        except Exception as e:
            self.conn.rollback()

    def insert(self, sql, args, type=None):
        try:
            if type == "insert_one":
                pass

                self.cursor.execute(sql, args)
                self.conn.commit()
                return self.cursor.lastrowid
            elif type == "insert_many":
                self.cursor.executemany(sql, args)
                self.conn.commit()
                return self.cursor.lastrowid

        except Exception as e:
            self.conn.rollback()

    def get_list(self, sql, arg=None):
        self.cursor.execute(sql, arg)
        ret = self.cursor.fetchall()
        return ret

    def close(self):
        self.cursor.close()
        self.conn.close()

    def __exit__(self, exc_type, exc_val, exc_tb):
        if self.conn._closed == False:
            print(self.conn._closed)
            self.cursor.close()
            self.conn.close()

    def __enter__(self):
        return self

html页面

班级列表

<body>

<table border="1px solid" style="text-align: center">

    <thead>

    <tr>

        <td>序号</td>
        <td>数据库id</td>
        <td>班级姓名</td>
        <td>班级操作</td>
    </tr>
    </thead>
    <tbody>

    {% for class in class_list %}
        <tr>


            <td>{{ forloop.counter }}</td>
            <td>{{ class.id }}</td>
            <td>{{ class.cname }}</td>
            <td>

                {#class_delete 注意是用""括起来 <a href="{% url 'class_delete' %}?class_id = {{ class.id }}">#}
                <a href="{% url "class_delete" %}?class_id={{ class.id }}">
                    <button>删除</button>
                </a>
                 <a href="{% url "class_edit" %}?class_id={{ class.id }}">
                    <button>编辑</button>
                </a>

            </td>


        </tr>
    {% endfor %}
    </tbody>


</table>

<a href="/class_add/">
    <button>添加班级</button>
</a>


</body>

班级添加

<form action="/class_add/" method="post">

    输如班级姓名:<input type="text" name="cname">

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

class_edit.html

<body>

<form action="{% url "class_edit" %}" method="post">

    修改班级名称:

    <input type="text" name="cid" hidden value="{{ class_info.id }}">

    <input type="text" name="cname" value="{{ class_info.cname }}">

    <input type="submit" value="修改提交">
</form>
</body>

student_list.html

<body>

<table border="1px solid" style="text-align: center">

    <thead>

    <tr>

        <td>序号</td>
        <td>学生id</td>
        <td>学生姓名</td>
        <td>所在班级</td>
        <td>学生操作</td>
    </tr>
    </thead>
    <tbody>

    {% for student in student_list %}
        <tr>


            <td>{{ forloop.counter }}</td>
            <td>{{ student.id }}</td>
            <td>{{ student.sname }}</td>
            <td>{{ student.cname }}</td>
            <td>

                <a href="{% url "student_delete" %}?student_id={{ student.id }}">
                    <button>删除</button>
                </a>
                 <a href="{% url "student_edit" %}?student_id={{ student.id }}">
                    <button>编辑</button>
                </a>

            </td>


        </tr>
    {% endfor %}
    </tbody>


</table>

<a href="{% url "student_add" %}">
    <button>添加学生</button>
</a>


</body>

student_add.html

<body>


<form action="/student_add/" method="post">

    输如同学姓名:<input type="text" name="sname">

    <select name="class_list" id="">

        {% for class in class_list %}

        <option value="{{ class.id }}">{{ class.cname }}</option>


        {% endfor %}
    </select>

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

</body>

student_edit.html

<body>

<form action="{% url "student_edit" %}" method="post">

    修改同学名称:

    <input type="text" name="sid" hidden value="{{ student_info.id }}">

    <input type="text" name="sname" value="{{ student_info.sname }}">

    <select name="class_list" id="">

        {% for class in class_list %}

            {% if student_info.cid == class.id %}


                <option selected value="{{ class.id }}">{{ class.cname }}</option>
            {% else %}

                <option value="{{ class.id }}">{{ class.cname }}</option>
            {% endif %}


        {% endfor %}

    </select>

    <input type="submit" value="修改提交">


</form>
</body>

teacher_list.html

<body>

<table border="1px solid" style="text-align: center">

    <thead>

    <tr>

        <td>序号</td>
        <td>数据库id</td>
        <td>老师姓名</td>
        <td>授课班级</td>
        <td>老师操作</td>
    </tr>
    </thead>
    <tbody>

    {% for teacher in teacher_list %}
        <tr>


            <td>{{ forloop.counter }}</td>
            <td>{{ teacher.id }}</td>
            <td>{{ teacher.tname }}</td>

            <td>
                {% for cname in teacher.cname %}

                    {% if forloop.last %}
                        {{ cname }}
                    {% else %}
                        {{ cname }},
                    {% endif %}
                {% endfor %}

            </td>


            <td>

                {#class_delete 注意是用""括起来 <a href="{% url 'class_delete' %}?class_id = {{ class.id }}">#}
                <a href="{% url "teacher_delete" %}?teacher_id={{ teacher.id }}">
                    <button>删除</button>
                </a>
                <a href="{% url "teacher_edit" %}?teacher_id={{ teacher.id }}">
                    <button>编辑</button>
                </a>

            </td>


        </tr>
    {% endfor %}
    </tbody>


</table>

<a href="/teacher_add/">
    <button>添加老师</button>
</a>


<form action="/teacher_search/" method="post">
    <input type="text" name="search_str">

    <button type="submit">搜索~~</button>
    </a>
</form>


</body>

teacher_add.html

<body>


<form action="/teacher_add/" method="post">

    输如老师姓名:<input type="text" name="tname">

    <select name="class_list" id="" multiple>

        {% for class in class_list %}

        <option value="{{ class.id }}">{{ class.cname }}</option>


        {% endfor %}
    </select>

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

</body>

teacher_edit.html

<body>

<form action="{% url "teacher_edit" %}" method="post">

    修改老师名称:

    <input type="text" name="tid" hidden value="{{ teacher_info.id }}">

    <input type="text" name="tname" value="{{ teacher_info.tname }}">

    <select name="class_list" id="" multiple>

        {% for class in class_list %}

            {% if class.cname in teacher_info.cname %}


                <option selected value="{{ class.id }}">{{ class.cname }}</option>
            {% else %}

                <option value="{{ class.id }}">{{ class.cname }}</option>
            {% endif %}


        {% endfor %}

    </select>

    <input type="submit" value="修改提交">


</form>
</body>
  • 0
    点赞
  • 1
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

打赏
文章很值,打赏犒劳作者一下
相关推荐
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页

打赏

进击的小猿

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值