学员管理系统
涉及的表：
- 班级
- 学生
- 老师
涉及的表操作
单表操作：
- 增
- 删
- 改
- 查
一对多操作：
- 增
- 删
- 改
- 查
多对多操作:
- 增
- 删
- 改
- 查
1 表结构
班级表：
Create Table: CREATE TABLE `class` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`class_name` char(24) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `class_name` (`class_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8
学生表：
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`student_name` char(24) NOT NULL,
`class_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_student_class_class_id` (`class_id`),
CONSTRAINT `fk_student_class_class_id` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8
老师表：
CREATE TABLE `teacher` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`teacher_name` char(24) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `teacher_name` (`teacher_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8
老师班级关系表：
CREATE TABLE `teacher_class` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`teacher_id` int(11) NOT NULL,
`class_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_teacher_class_class_id` (`class_id`),
KEY `fk_teacher_class_teacher_id` (`teacher_id`),
CONSTRAINT `fk_teacher_class_class_id` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`),
CONSTRAINT `fk_teacher_class_teacher_id` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=82 DEFAULT CHARSET=utf8
2 项目目录结构
.
|____.idea
| |____inspectionProfiles
| | |____profiles_settings.xml
| |____misc.xml
| |____modules.xml
| |____StuMS.iml
| |____workspace.xml
|____app01
| |______pycache__
| | |____views.cpython-36.pyc
| |____views.py
|____db.sqlite3
|____manage.py
|____static
|____StuMS
| |______init__.py
| |______pycache__
| | |______init__.cpython-36.pyc
| | |____settings.cpython-36.pyc
| | |____urls.cpython-36.pyc
| | |____wsgi.cpython-36.pyc
| |____settings.py
| |____urls.py
| |____wsgi.py
|____templates
| |____add_class.html
| |____classes.html
| |____edit_class.html
3 代码
urls.py:
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),
]
3.1 功能1：查看班级列表
app01.views.py:
def classes(request):
conn = pymysql.connect(
host='localhost',
port=3306,
user='root',
passwd='123456',
db='db03',
charset='utf8'
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.execute(
'select id,class_name from class order by id;'
)
class_list = cursor.fetchall()
cursor.close()
conn.close()
return render(request, 'classes.html', {'class_list': class_list})
templates.classes.html:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<h1>班级列表</h1>
<div>
<a href="/add_class/">添加班级</a>
</div>
<table>
<thead>
<tr>
<th>ID</th>
<th>班级名称</th>
<th>>操作</th>
</tr>
</thead>
<tbody>
{% for row in class_list %}
<tr>
<td>{{ row.id }}</td>
<td>{{ row.class_name }}</td>
<td>
<a href="/del_class/?id={{ row.id }}">删除</a>
<a href="/edit_class/?id={{ row.id }}">编辑</a>
</td>
</tr>
{% endfor %}
</tbody>
</table>
</body>
</html>
3.2 功能2：添加班级
app01.views.py
def add_class(request):
if request.method == 'GET':
return render(request, 'add_class.html')
elif request.method == 'POST':
class_name = request.POST.get('class_name')
conn = pymysql.connect(
host='localhost',
port=3306,
user='root',
passwd='123456',
db='db03',
charset='utf8'
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.execute(
'insert into class(class_name) values(%s);',
[class_name, ]
)
conn.commit()
cursor.close()
conn.close()
return redirect('/classes/')
templates.add_class.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<h1>添加班级</h1>
<form action="/add_class/" method="post">
<input type="text" name="class_name">
<input type="submit" value="提交">
</form>
</body>
</html>
3.3 功能3：删除班级
app01.views.py
def del_class(request):
class_id = request.GET.get('id')
conn = pymysql.connect(
host='localhost',
port=3306,
user='root',
passwd='123456',
db='db03',
charset='utf8'
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.execute(
'delete from class where id=%s;',
[class_id, ]
)
conn.commit()
cursor.close()
conn.close()
return redirect('/classes/')
3.4 功能4：编辑班级
app01.views.py:
def edit_class(request):
if request.method == 'GET':
class_id = request.GET.get('id')
conn = pymysql.connect(
host='localhost',
port=3306,
user='root',
passwd='123456',
db='db03',
charset='utf8'
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.execute(
'select id,class_name from class where id=%s;',
[class_id, ]
)
result = cursor.fetchone()
cursor.close()
conn.close()
return render(request, 'edit_class.html', {'result': result})
elif request.method == 'POST':
class_id = request.GET.get('id')
# class_id = request.POST.get('id')
class_name = request.POST.get('class_name')
conn = pymysql.connect(
host='localhost',
port=3306,
user='root',
passwd='123456',
db='db03',
charset='utf8'
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.execute(
'update class set class_name=%s where id=%s;',
[class_name, class_id, ]
)
conn.commit()
cursor.close()
conn.close()
return redirect('/classes/')
templates.edit_class.html:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<h1>编辑班级</h1>
<form action="/edit_class/?id={{ result.id }}" method="post">
{# <input type="text" name="id" value="{{ result.id }}" style="display: none">#}
<input type="text" name="class_name" value="{{ result.class_name }}">
<input type="submit" value="提交">
</form>
</body>
</html>