学生表增删改查

views.py

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

def classes(request):
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123.com', db='mysite', charset='utf8')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    cursor.execute("select id,title from class")
    class_list = cursor.fetchall()
    cursor.close()
    conn.close()

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



def add_class(request):
    if request.method == "GET":
        return render(request, 'add_class.html')
    elif request.method == "POST":
        v = request.POST.get("title")
        conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123.com', db='mysite', charset='utf8')
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        cursor.execute("insert into class(title) values(%s)", [v,])
        conn.commit()
        cursor.close()
        conn.close()

        return redirect('/classes/')


def del_class(request):
    nid = request.GET.get('nid')
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123.com', db='mysite', charset='utf8')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    cursor.execute("delete from class where id=%s", [nid, ])
    conn.commit()
    cursor.close()
    conn.close()

    return redirect('/classes/')


def edit_class(request):
    if request.method == "GET":
        nid = request.GET.get('nid')
        conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123.com', db='mysite', charset='utf8')
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        cursor.execute("select id,title from class where id = %s", [nid, ])
        result = cursor.fetchone()
        cursor.close()
        conn.close()
        print(result)
        return render(request, 'edit_class.html', {'result': result})
    elif request.method == "POST":
        nid = request.GET.get('nid')
        title = request.POST.get('title')
        # print(nid,title)
        conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123.com', db='mysite', charset='utf8')
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        cursor.execute("update class set title=%s where id = %s", [title,nid,])
        conn.commit()
        cursor.close()
        conn.close()
        return redirect('/classes/')


def students(request):
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123.com', db='mysite', charset='utf8')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    cursor.execute("SELECT student.id,student.name,class.title from student LEFT JOIN class  on student.class_id=class.id;")
    student_list = cursor.fetchall()
    cursor.close()
    conn.close()
    return render(request,'students.html',{'student_list':student_list})


def add_student(request):
    if request.method == "GET":
        conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123.com', db='mysite', charset='utf8')
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        cursor.execute("SELECT id,title from class;")
        class_list = cursor.fetchall()
        cursor.close()
        conn.close()
        return render(request, 'add_student.html', {'class_list': class_list})
    else:
        class_id=request.POST.get("class_id")
        name=request.POST.get("name")

        conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123.com', db='mysite', charset='utf8')
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        cursor.execute("insert into student(name,class_id) values(%s,%s)",[name,class_id])
        conn.commit()
        cursor.close()
        conn.close()
        return redirect('/students/')


from utils import sqlhelper
def edit_student(request):
    if request.method == "GET":
        nid = request.GET.get("nid")
        class_list = sqlhelper.get_list("select id,title from class",[])
        current_student_info=sqlhelper.get_one('select id,name,class_id from student where id=%s',[nid,])
        return render(request, 'edit_student.html',{'class_list':class_list,'current_student_info':current_student_info})
    else:
        nid = request.GET.get("nid")
        name = request.POST.get('name')
        class_id = request.POST.get('class_id')
        sqlhelper.modify('update student set name=%s,class_id=%s where id=%s',[name,class_id,nid,])
        return redirect('/students/')

 urls.py

"""mysite20180307 URL Configuration

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

urlpatterns = [
    url(r'^admin/', admin.site.urls),
    url(r'^classes/', views.classes),
    url(r'^add_class/', views.add_class),
    url(r'^del_class/', views.del_class),
    url(r'^edit_class/', views.edit_class),
    url(r'^students/', views.students),
    url(r'^add_student/', views.add_student),
    url(r'^edit_student/', views.edit_student),
]

edit_student

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

<h3>编辑学生</h3>
<form method="POST" action="/edit_student/?nid={{ current_student_info.id }}">
{#    <input type="text" style="display: none" value="{{ result.id }}">#}
    <p>学生姓名：<input type="text" name="name" value="{{ current_student_info.name }}"></p>
    <p>
        所属班级：
        <select name="class_id" >
            {% for row in class_list %}
                {% if row.id == current_student_info.class_id %}
                    <option selected value="{{ row.id }}">{{ row.title }}</option>
                {% else %}
                    <option value="{{ row.id }}">{{ row.title }}</option>
                {% endif %}
            {% endfor %}
        </select>
    </p>
    <input type="submit" name="提交">
</form>

</body>
</html>

add_student

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<h3>添加学生</h3>
<form method="POST" action="/add_student/">
    <p>学生名称：<input type="text" name="name"></p>
    <p>
        <select name="class_id">
            {% for row in class_list %}
                <option value="{{ row.id }}">{{ row.title }}</option>
            {% endfor %}
        </select>
    </p>
    <input type="submit" value="提交">
</form>
</body>
</html>

students

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<h1>学生列表</h1>
<div>
    <a href="/add_student/">添加</a>
</div>
<table>
    <thead>
    <tr>
        <th>ID</th>
        <th>学生姓名</th>
        <th>所属班级</th>
        <th>操作</th>
    </tr>
    </thead>
    <tbody>
    {% for row in student_list %}
        <tr>
            <td>{{ row.id }}</td>
            <td>{{ row.name }}</td>
            <td>{{ row.title }}</td>
            <td><a href="/del_class/?nid={{ row.id }}">删除</a></td>
            <td><a href="/edit_student/?nid={{ row.id }}">编辑</a></td>
        </tr>
    {% endfor %}
    </tbody>
</table>

</body>
</html>

sqlhelper

import pymysql

def get_list(sql,args):
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123.com', db='mysite', charset='utf8')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    cursor.execute(sql,args)
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    return result


def get_one(sql,args):
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123.com', db='mysite', charset='utf8')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    cursor.execute(sql,args)
    result = cursor.fetchone()
    cursor.close()
    conn.close()
    return result


def modify(sql,args):
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123.com', db='mysite', charset='utf8')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    cursor.execute(sql, args)
    conn.commit()
    cursor.close()
    conn.close()