Flask 数据库前后端交互案例
- 目录结构
- templates目录
- base.html
- header.html
- left.html
- 首页
- 职员管理页面
- 添加员工界面
- 员工编辑页面
- 员工详情界面
- 后台
- main.py
- app.py
- models.py
- views.py
- 数据库数据
- position.sql
- person.sql
- permission.sql
- department.sql
目录结构
静态文件链接:https://pan.baidu.com/s/1aapt_kPHw7Tkg0KUDQJsOg
提取码:zht1
templates目录
base.html
templates/base.html
<!DOCTYPE html>
<html lang="en"><head><meta charset="utf-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width, initial-scale=1"><meta name="description" content=""><meta name="author" content=""><title>{% block title %}{% endblock %}</title><!-- Bootstrap Core CSS --><link href="static/lib/sb-admin/vendor/bootstrap/css/bootstrap.min.css" rel="stylesheet"><!-- MetisMenu CSS --><link href="static/lib/sb-admin/vendor/metisMenu/metisMenu.min.css" rel="stylesheet"><!-- Custom CSS --><link href="static/lib/sb-admin/dist/css/sb-admin-2.css" rel="stylesheet"><!-- Custom Fonts --><link href="static/lib/sb-admin/vendor/font-awesome/css/font-awesome.min.css" rel="stylesheet" type="text/css"><link href="static/css/index.css" rel="stylesheet" type="text/css"><!--[if lt IE 9]><script src="https://oss.maxcdn.com/libs/html5shiv/3.7.0/html5shiv.js"></script><script src="https://oss.maxcdn.com/libs/respond.js/1.4.2/respond.min.js"></script><![endif]--></head><body>
<div id="wrapper"><!-- Navigation --><nav class="navbar navbar-default navbar-static-top" role="navigation" style="margin-bottom: 0">{% include 'header.html' %}{% include 'left.html' %}</nav>{% block Page_Content %}{% endblock %}
</div>
<!-- jQuery -->
<script src="static/lib/sb-admin/vendor/jquery/jquery.min.js"></script><!-- Bootstrap Core JavaScript -->
<script src="static/lib/sb-admin/vendor/bootstrap/js/bootstrap.min.js"></script><!-- Metis Menu Plugin JavaScript -->
<script src="static/lib/sb-admin/vendor/metisMenu/metisMenu.min.js"></script><!-- Custom Theme JavaScript -->
<script src="static/lib/sb-admin/dist/js/sb-admin-2.js"></script><script src="static/lib/echarts/js/echarts.min.js"></script><script src="static/js/index.js"></script>
</body>
</html>
header.html
templates/header.html
<div class="navbar-header"><button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse"><span class="sr-only">Toggle navigation</span><span class="icon-bar"></span><span class="icon-bar"></span><span class="icon-bar"></span></button><a class="navbar-brand" href="./index.html">XXXXX考勤管理系统</a></div><!-- /.navbar-header --><ul class="nav navbar-top-links navbar-right"><li><a>欢迎:<span>{{ request.cookies.person_name}}</span></a></li><li><a href="/logout">退出</a></li><li><a href="./profile.html">个人中心</a></li></ul>
left.html
templates/left.html
<div class="navbar-default sidebar" role="navigation"><div class="sidebar-nav navbar-collapse"><ul class="nav" id="side-menu"><li><a href="/"><i class="fa fa-dashboard fa-fw"></i> 首页</a></li><li><a href="/news"><i class="fa fa-envelope-o fa-fw"></i> 新闻管理</a></li><li><a href="/permission"><i class="fa fa-table fa-fw"></i> 权限管理</a></li><li><a href="#"><i class="fa fa-bar-chart-o fa-fw"></i> 考勤管理<span class="fa arrow"></span></a><ul class="nav nav-second-level"><li><a href="/attendance_me">个人考勤</a></li><li><a href="/attendance_subordinate">下属考勤</a></li></ul></li><li><a href="#"><i class="fa fa-user fa-fw"></i> 人事管理<span class="fa arrow"></span></a><ul class="nav nav-second-level"><li><a href="/department">部门管理</a></li><li><a href="/person">职员管理</a></li></ul><!-- /.nav-second-level --></li></ul></div><!-- /.sidebar-collapse -->
</div>
首页
templates/index.html
{% extends "base.html" %}
{% block title %}首页{% endblock %}
{% block Page_Content %}<!-- Page Content --><div id="page-wrapper"><div class="container-fluid"><div class="row"><div class="col-lg-12"><h1 class="page-header">首页</h1></div><!-- /.col-lg-12 --></div><!-- /.row --><div class="row"><div class="col-lg-12"><div id="show"></div><div class="col-lg-6"><div class="panel panel-default"><div class="panel-heading">公司新闻<a class="pull-right" href="./news.html">查看更多...</a></div><div class="panel-body"><div class="col-lg-6"><a href="./detail_news.html">XXX简介</a></div><div class="col-lg-6 text-right">2020-05-06 16:37:39</div><div class="col-lg-6"><a href="#">新闻99</a></div><div class="col-lg-6 text-right">2020-05-06 16:37:39</div><div class="col-lg-6"><a href="#">新闻98</a></div><div class="col-lg-6 text-right">2020-05-06 16:37:39</div><div class="col-lg-6"><a href="#l">新闻97</a></div><div class="col-lg-6 text-right">2020-05-06 16:37:39</div></div></div></div><div class="col-lg-6"><div class="panel panel-default"><div class="panel-heading">考勤情况<a class="pull-right" href="attendance_me.html">查看更多...</a></div><div class="panel-body"><div class="col-lg-6">摔伤了,请假3天。</div><div class="col-lg-6 text-right">已驳回</div><div class="col-lg-6">住院5天</div><div class="col-lg-6 text-right">已通过</div><div class="col-lg-6">因家里有事,调休1天</div><div class="col-lg-6 text-right">已驳回</div><div class="col-lg-6">请年假1天</div><div class="col-lg-6 text-right">申请中</div></div></div></div></div><!-- /.col-lg-12 --></div></div><!-- /.container-fluid --></div>
{% endblock %}
职员管理页面
templates/person.html
{% extends "base.html" %}
{% block title %}职员管理{% endblock %}
{% block Page_Content %}<!-- Page Content --><div id="page-wrapper"><div class="container-fluid"><div class="row"><div class="col-lg-12"><h1 class="page-header">职员管理</h1></div><!-- /.col-lg-12 --></div><!-- /.row --><div class="row"><div class="col-lg-12"><div class="row"><div class="col-lg-8">{% if Filter==0%}<a class="btn btn-default active" href="/person?filter=all">全部</a>{% else %}<a class="btn btn-default" href="/person?filter=all">全部</a>{% endif %}{% for department in department_list %}{% if Filter==department.id %}<a class="btn btn-default active" href="/person?filter={{ department.id }}">{{ department.name }} </a>{% else %}<a class="btn btn-default" href="/person?filter={{ department.id }}">{{ department.name }} </a>{% endif %}{% endfor %}</div><form role="form" class="col-lg-3" method="get" action="/person">{% if Filter!=0 %}<input type="hidden" name="filter" value="{{ Filter }}">{% endif %}<div class="form-group input-group"><input type="text" name="search" class="form-control" placeholder="输入要搜索的雇员的名字"value=""><span class="input-group-btn"><button class="btn btn-default" type="submit"><i class="fa fa-search"></i></button></span></div></form><div class="col-lg-1"><a class="btn btn-default" href="/add_person">添加</a></div></div><div class="row"><table class="table"><thead><tr><th>职员用户名</th><th>职员部门</th><th>职员职位</th><th>操作</th></tr></thead><tbody>{% for person in person_list %}<tr><td>{{ person.nickname }}</td><td>{{ person.position.dept.name }}</td><td>{{ person.position.name }}</td><td><a class="btn btn-default" href="/detail_person?id={{ person.id }}">详情</a><a class="btn btn-default" href="/update_person?id={{ person.id }}">编辑</a><a class="btn btn-default" href="/del_person?id={{ person.id }}">删除</a></td></tr>{% endfor %}</tbody><tfoot><tr><td colspan="4"><ul class="pagination"><li class="active"><a href="#">1</a></li><li><a href="#">2</a></li><li><a href="#">3</a></li><li><a href="#">4</a></li><li><a href="#">5</a></li></ul></td></tr></tfoot></table></div></div><!-- /.col-lg-12 --></div></div><!-- /.container-fluid --></div><!-- /#page-wrapper -->
{% endblock %}
添加员工界面
templates/add_person.html
{% extends "base.html" %}
{% block title %}个人中心-添加{% endblock %}
{% block Page_Content %}<!-- Page Content --><div id="page-wrapper"><div class="container-fluid"><div class="row"><div class="col-lg-12"><h1 class="page-header">个人中心-添加</h1></div><!-- /.col-lg-12 --></div><!-- /.row --><div class="row"><div class="col-lg-12"><div class="row"><p></p><form method="post" enctype="multipart/form-data" action="/add_person"><div class="form-group"><label class="control-label">用户名:</label><input class="form-control" type="text" name="username"><label class="text-primary username_error"></label></div><div class="form-group"><label class="control-label">密码:</label><input class="form-control" type="password" name="password"><label class="text-primary password_error"></label></div><div class="form-group"><label class="control-label">职位:</label><select class="form-control" name="position_id">{% for position in position_list %}<option value="{{ position.id }}">{{ position.name }}</option>{% endfor %}</select></div><div class="form-group"><button class="btn btn-primary btn-block">提交</button></div></form></div></div><!-- /.col-lg-12 --></div></div><!-- /.container-fluid --></div>
{% endblock %}
员工编辑页面
templates/edit_person.html
{% extends "base.html" %}
{% block title %}职员管理-编辑{% endblock %}
{% block Page_Content %}<!-- Page Content --><div id="page-wrapper"><div class="container-fluid"><div class="row"><div class="col-lg-12"><h1 class="page-header">职员管理-编辑</h1></div><!-- /.col-lg-12 --></div><!-- /.row --><div class="row"><div class="col-lg-12"><div class="row"><p></p><form method="post" enctype="multipart/form-data" action="/update_person?id={{ person.id }}"><div class="form-group"><label class="control-label">用户名:</label><input class="form-control" type="text" name="username" value="{{ person.username}}"><label class="text-primary username_error"></label></div><div class="form-group"><label class="control-label">密码:</label><input class="form-control" type="password" name="password" value="{{person.password}}"><label class="text-primary password_error"></label></div><div class="form-group"><label class="control-label">昵称:</label><input class="form-control" type="text" name="nickname" value="{{ person.nickname }}"></div><div class="form-group"><label class="control-label">性别:</label><select class="form-control" name="gender">{% if person.gender == '男'%}<option value="男" selected>男</option><option value="女">女</option>{% else %}<option value="男" >男</option><option value="女" selected>女</option>{% endif %}</select></div><div class="form-group"><label class="control-label">年龄:</label><input class="form-control" type="number" name="age" value="{{ person.age }}"></div><div class="form-group"><label class="control-label">电话:</label><input class="form-control" type="text" name="phone" value="{{ person.phone }}"></div><div class="form-group"><label class="control-label">邮箱:</label><input class="form-control" type="text" name="email" value="{{ person.email}}"></div><div class="form-group"><label class="control-label">头像:</label><input type="file" name="photo"></div><div class="form-group"><label class="control-label">地址:</label><textarea class="form-control" name="address">{{ person.address }}</textarea></div><div class="form-group"><label class="control-label">职位:</label><select class="form-control" name="position_id">{% for position in position_list %}{% if person.position.id==loop.index%}<option value="{{ position.id }}" selected>{{ position.name }}</option>{% else %}<option value="{{ position.id }}">{{ position.name }}</option>{% endif %}{% endfor %}</select></div><div class="form-group"><button class="btn btn-primary btn-block">提交</button></div></form></div></div><!-- /.col-lg-12 --></div></div><!-- /.container-fluid --></div>{% endblock %}
员工详情界面
templates/detail_person.html
{% extends "base.html" %}
{% block title %}职员管理-详情{% endblock %}
{% block Page_Content %}
<!-- Page Content --><div id="page-wrapper"><div class="container-fluid"><div class="row"><div class="col-lg-12"><h1 class="page-header">职员管理-详情</h1></div><!-- /.col-lg-12 --></div><!-- /.row --><div class="row"><div class="col-lg-12"><div class="row"><table class="table"><tr><td>用户名</td><td>{{person.username}}</td></tr><tr><td>密码</td><td>{{person.password}}</td></tr><tr><td>昵称</td><td>{{person.nickname}}</td></tr><tr><td>性别</td><td>{{person.gender}}</td></tr><tr><td>年龄</td><td>{{person.age}}</td></tr><tr><td>工号</td><td>{{person.workid}}</td></tr><tr><td>电话</td><td>{{person.phone}}</td></tr><tr><td>邮箱</td><td>{{person.email}}</td></tr><tr><td>照片</td><td>{{person.photo}}</td></tr><tr><td>地址</td><td>{{person.address}}</td></tr><tr><td>绩效</td><td>{{person.score}}</td></tr><tr><td>职位</td><td>{{ person.position.name }}</td></tr><tr><td>部门</td><td>{{ person.position.dept.name }}</td></tr></table></div></div><!-- /.col-lg-12 --></div></div><!-- /.container-fluid --></div>
{% endblock %}
后台
main.py
#控制文件
from app import app
from models import db
from views import *if __name__ == '__main__':# 删除所有表# db.drop_all()# 创建所有表db.create_all()app.run(debug=True)
app.py
import os
from flask import Flask,request
from flask import render_template,redirect
# 引入表格模块
from flask_sqlalchemy import SQLAlchemy
#导入pymysql
import pymysql
#用pymysql 代替 MySqldb
pymysql.install_as_MySQLdb()
app=Flask(__name__)
# 获取文件路径
BASE_DIR=os.path.abspath(os.path.dirname(__file__))
# 配置数据库sqlite
app.config['SQLALCHEMY_DATABASE_URI']='sqlite:///'+os.path.join(BASE_DIR,'sqlite3.db')
#配置数据库mysql
# app.config['SQLALCHEMY_DATABASE_URI']='mysql://root:root@127.0.0.1:3306/test'
# 创建一个父类让python中的类去继承 就可映射成表格
db=SQLAlchemy(app)
models.py
#存放类模块
from app import db
#抽象父类
class Base(db.Model):__abstract__=Trueid = db.Column(db.INT, primary_key=True, autoincrement=True)#添加员工def save(self):# 保存对象db.session.add(self)# 提交事务db.session.commit()def updte(self):db.session.commit()def delete(self):# 删除对象db.session.delete(self)# 提交事务db.session.commit()
#职位
class Position(Base):__tablename__="position"name=db.Column(db.String(32)) #职位名称level=db.Column(db.Integer) #级别#关系属性#为了查询方便,flask提供了关系属性,为了方便查询,但是不会在数据库中生成相应的字段persons=db.relationship("Person",backref='position')department_id=db.Column(db.Integer,db.ForeignKey("department.id"))#当知道职位查询所有的员工时,通过 职位对象.persons就可以获得所有的员工# 当员工查询对应的职位的时候,通过 员工对象.position就可获得对应的职位
# 职员
class Person(Base):"""雇员表"""__tablename__ = "person"username = db.Column(db.VARCHAR(100))password = db.Column(db.VARCHAR(64))nickname = db.Column(db.String(64), nullable=True) # 昵称gender = db.Column(db.String(8), nullable=True) # 性别age = db.Column(db.Integer, nullable=True) # 年龄workid = db.Column(db.String(32), nullable=True) # 工号phone = db.Column(db.String(64), nullable=True) # 电话email = db.Column(db.String(64), nullable=True) # 邮箱photo = db.Column(db.String(64), nullable=True) # 照片address = db.Column(db.Text, nullable=True) # 地址score = db.Column(db.Float, nullable=True) # 绩效#关系属性#职位和员工一对多关系position_id=db.Column(db.Integer,db.ForeignKey("position.id"))
#职位与权限中间表
permission_position=db.Table('permission_position',db.Column("id",db.Integer,primary_key=True,autoincrement=True),db.Column("position_id",db.Integer,db.ForeignKey("position.id")), #关联positiondb.Column("permission_id",db.Integer,db.ForeignKey("permission.id")), #关联permission
)
#权限表
class Permission(Base):__tablename__="permission"name=db.Column(db.String(32)) #权限名称desc=db.Column(db.String(128)) #权限描述#关系属性positions=db.relationship('Position',# 和模型类进行关联backref='permissions',secondary=permission_position)
#部门表
class Department(Base):__tablename__='department'#实体属性name=db.Column(db.String(32))description=db.Column(db.String(128))#关系属性positions=db.relationship('Position',backref='dept')
views.py
python">from app import *
from models import *
import hashlib
import time
from functools import wraps
from flask import request
# 登录装饰器
def Login_Decorator(func):@wraps(func)def inner():person_name = request.cookies.get('person_name')if person_name:return func()else:return redirect('login')return inner
@app.route('/')
@Login_Decorator
def index():return render_template('index.html')
# 职员管理
@app.route('/person')
@Login_Decorator
def person():res=request.args.get("filter")print(res)search=request.args.get("search")department_list = Department.query.all()flag = Falseif res=="all" or res==None:Filter=0person_list = Person.query.all()else:flag=TrueFilter=int(res)pos_list=Position.query.filter(Position.department_id==Filter).all()person_list=[ps for person in pos_list for ps in person.persons] #遍历所有部门的成员存入字典if search!=None:if flag:person_list=[person for person in person_list if search in person.nickname]else:person_list=Person.query.filter(Person.nickname.like("%"+search+"%")).all()return render_template("person.html",**locals())
# 添加员工
@app.route('/add_person',methods=["GET","POST"])
def add_person():if request.method=="GET":position_list=Position.query.all()return render_template("add_person.html",position_list=position_list)else:username=request.form.get("username")password=hashlib.md5(request.form.get("password").encode()).hexdigest() #md5进行加密position_id=request.form.get("position_id")user=Person(username=username,password=password,position_id=position_id)user.save()return redirect("/person")# 删除员工
@app.route('/del_person')
@Login_Decorator
def del_person():id=request.args.get('id')person=Person.query.get(id)person.delete()return redirect("/person")# 编辑员工
@app.route('/update_person',methods=["GET","POST"])
@Login_Decorator
def update_person():id = request.args.get('id')person = Person.query.get(id)if request.method=="GET":position_list = Position.query.all()return render_template("edit_person.html",**locals())else:username=request.form.get("username")password=hashlib.md5(request.form.get("password").encode()).hexdigest() #md5进行加密nickname=request.form.get("nickname")gender=request.form.get("gender")age=request.form.get("age")phone=request.form.get("phone")email=request.form.get("email")adress=request.form.get("address")position_id=request.form.get("position_id")#文件保存photo=request.files.get("photo")if len(photo.filename)!=0:#替换成新图片时删除以前图片,并判断是否是第一次添加print(person.photo.startswith("/static/"))if person.photo.startswith("/static/"):os.remove(BASE_DIR+person.photo)path=os.path.join(BASE_DIR,"static/img",photo.filename)photo.save(path)photo_name="/static/img/"+photo.filenameelse:photo_name = person.photoperson.username=usernameperson.password=passwordperson.nickname=nicknameperson.gender=genderperson.age=ageperson.phone=phoneperson.email=emailperson.adress=adressperson.photo=photo_nameperson.position_id=position_idperson.updte()return redirect("/person")#员工详情
@app.route('/detail_person')
@Login_Decorator
def detail_person():id = request.args.get('id')person = Person.query.get(id)return render_template("detail_person.html", person=person)#登录
@app.route('/login', methods=['GET', 'POST'])
def login():msg = ''print("asd")if request.method == 'POST':# 1.获取用户名和密码username = request.form.get('username')password=hashlib.md5(request.form.get("password").encode()).hexdigest()# 2.查询数据库 (用户名和密码)person_obj = Person.query.filter(Person.username == username, Person.password == password).first()# 3.判断if person_obj:# (1).正确 --->> 重定向到首页response = redirect('/')# 4.将用户名保存到cookie中,用于首页显示response.set_cookie('person_name', person_obj.nickname)return response# (2).错误--->> 提示错误信息msg = '用户名或者密码错误'# get方式或者用户名校验失败后返回登录页面return render_template('login.html', msg=msg)#登录退出
@app.route('/logout')
@Login_Decorator
def logout():response = redirect('/login')# 1.清除所有的cookieresponse.delete_cookie('person_name')# 2.重定向到登录界面return response
数据库数据
position.sql
INSERT INTO "position"("id", "name", "level", "department_id") VALUES (1, '市场部_部长', 1, 1);
INSERT INTO "position"("id", "name", "level", "department_id") VALUES (2, '市场部_主任', 2, 1);
INSERT INTO "position"("id", "name", "level", "department_id") VALUES (3, '人事部_部长', 3, 5);
INSERT INTO "position"("id", "name", "level", "department_id") VALUES (4, '人事部_主任', 4, 5);
INSERT INTO "position"("id", "name", "level", "department_id") VALUES (5, '技术部_部长', 5, 2);
INSERT INTO "position"("id", "name", "level", "department_id") VALUES (6, '技术部_主任', 6, 2);
INSERT INTO "position"("id", "name", "level", "department_id") VALUES (7, '新媒体部_部长', 7, 4);
INSERT INTO "position"("id", "name", "level", "department_id") VALUES (8, '新媒体部_主任', 8, 4);
INSERT INTO "position"("id", "name", "level", "department_id") VALUES (9, '财务部_部长', 9, 3);
INSERT INTO "position"("id", "name", "level", "department_id") VALUES (10, '财务部_主任', 10, 3);
person.sql
INSERT INTO "position"("id", "name", "level", "department_id") VALUES (1, '市场部_部长', 1, 1);
INSERT INTO "position"("id", "name", "level", "department_id") VALUES (2, '市场部_主任', 2, 1);
INSERT INTO "position"("id", "name", "level", "department_id") VALUES (3, '人事部_部长', 3, 5);
INSERT INTO "position"("id", "name", "level", "department_id") VALUES (4, '人事部_主任', 4, 5);
INSERT INTO "position"("id", "name", "level", "department_id") VALUES (5, '技术部_部长', 5, 2);
INSERT INTO "position"("id", "name", "level", "department_id") VALUES (6, '技术部_主任', 6, 2);
INSERT INTO "position"("id", "name", "level", "department_id") VALUES (7, '新媒体部_部长', 7, 4);
INSERT INTO "position"("id", "name", "level", "department_id") VALUES (8, '新媒体部_主任', 8, 4);
INSERT INTO "position"("id", "name", "level", "department_id") VALUES (9, '财务部_部长', 9, 3);
INSERT INTO "position"("id", "name", "level", "department_id") VALUES (10, '财务部_主任', 10, 3);
permission.sql
INSERT INTO "permission"("id", "name", "desc") VALUES (1, '新闻管理', '对新闻进行增删改查');
INSERT INTO "permission"("id", "name", "desc") VALUES (2, '人事管理', '对人事进行增删改查');
INSERT INTO "permission"("id", "name", "desc") VALUES (3, '考勤管理', '对考勤进行增删改查');
INSERT INTO "permission"("id", "name", "desc") VALUES (4, '权限管理', '对权限进行增删改查');
department.sql
INSERT INTO "department"("id", "name", "description") VALUES (1, '市场部', '负责市场相关');
INSERT INTO "department"("id", "name", "description") VALUES (2, '技术部', '负责技术相关');
INSERT INTO "department"("id", "name", "description") VALUES (3, '财务部', '负责财务相关');
INSERT INTO "department"("id", "name", "description") VALUES (4, '新媒体部', '负责新媒体相关');
INSERT INTO "department"("id", "name", "description") VALUES (5, '人事部', '负责人事相关');