【开源】使用Python+Flask+Mysql快速开发一个用户增删改查系统

项目演示

项目本身很简单，增删改查是几乎所有系统的骨架。正所谓万丈高楼平地起，学会了增删改查，航母就指日可待了：），光速入门，直接看演示图：

项目地址

https://github.com/mudfish/python-flask-user-crud

Flask框架介绍

说白了就是一个Web框架，能够让你快速开发出Python web应用。简单易用，大家直接看官网就行：
https://flask.palletsprojects.com/en/3.0.x/quickstart/

开发步骤

开发工具

懒得折腾Pycharm了，直接Vscode安装pyhon和flask插件即可，也是比较丝滑的。

准备静态文件

主要用了Bootstrap5和Jquery这两个前端框架，一个是UI，一个是js。
都放到static文件夹下面：

开发入口文件

这个就是flask运行的文件，里面包括了启动入口，端口号和业务逻辑接口。

from flask import Flask, render_template, request, redirect, url_for, flash
import pymysql.cursors



# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='123456',
                             db='user_test',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
    
app = Flask(__name__)

# 保持数据库连接
def getconnection():
    connection.ping(reconnect=True)
    return connection
    
# 首页
@app.route('/')
def index():
    try:
        with getconnection().cursor() as cursor:
            sql = "SELECT * FROM `tb_user`"
            cols = ['id', 'name', 'age','gender','phone']
            cursor.execute(sql)
            result = cursor.fetchall()
            cursor.close()
            return render_template("index.html", items=result, cols=cols, success='')
    except Exception as e:
        cursor.close()
        return render_template("index.html", items=[], cols=[], success='Can\'t view index: ' + str(e))
    
# 搜索
@app.route('/search')
def search():
    keyword = request.args.get('keyword').strip()
    try:
        with getconnection().cursor() as cursor:
            sql = "SELECT * FROM `tb_user` where name like concat('%%',%s,'%%')"
            cols = ['id', 'name', 'age','gender','phone']
            cursor.execute(sql,(keyword))
            result = cursor.fetchall()
            # print(result)
            cursor.close()
            return render_template("index.html", items=result, keyword=keyword, cols=cols, success='')
    except Exception as e:
        cursor.close()
        return render_template("index.html", items=[], cols=[], success='search error: ' + str(e))


@app.route('/toAddPage')
def toAddPage():
 return render_template('add.html')

@app.route('/toEditPage/<int:id>')
def toEditPage(id):
    # print(id)
    try:
        with getconnection().cursor() as cursor:
            sql = "select * from `tb_user` where id=%s"
            cursor.execute(sql, (id))
            result = cursor.fetchone()
            cursor.close()
            return render_template("edit.html", item=result, success='')
    except Exception as e:
        cursor.close()
        return render_template("edit.html", success='Can\'t edit User: ' + str(e))

@app.route('/add', methods=['POST'])
def add():
    name = request.form['name'].strip()
    age = request.form['age'].strip()
    gender = request.form['gender'].strip()
    phone = request.form['phone'].strip()
    try:
        with getconnection().cursor() as cursor:
            sql = "INSERT INTO `tb_user` (`name`, `age`,`gender`,`phone`) VALUES (%s, %s,%s,%s)"
            cursor.execute(sql, (name, age,gender,phone))
            cursor.close()
            return redirect(url_for("index"))
    except Exception as e:
        cursor.close()
        return render_template("add.html", success='Can\'t add User: ' + str(e))

@app.route('/edit',methods=['POST'])
def edit():
    id = request.form['id'].strip()
    name = request.form['name'].strip()
    age = request.form['age'].strip()
    phone = request.form['phone'].strip()
    gender = request.form['gender'].strip()
    try:
        with getconnection().cursor() as cursor:
            sql = "update `tb_user` set name=%s,age=%s,gender=%s,phone=%s where id=%s"
            cursor.execute(sql, (name, age,gender,phone,id))
            cursor.close()
            return redirect(url_for("index"))
    except Exception as e:
        cursor.close()
        return render_template("edit.html", success='Can\'t edit User: ' + str(e))

@app.route('/remove/<int:id>/')
def remove(id):
    try:
        with getconnection().cursor() as cursor:
            sql = "delete from `tb_user` where id=%s"
            cursor.execute(sql, (id))
            cursor.close()
            return redirect(url_for("index"))
    except Exception as e:
        cursor.close()
        return render_template("index.html", success='Can\'t remove User: ' + str(e))

@app.errorhandler(404)
def page_not_found(error):
    return render_template('page_not_found.html'), 404

@app.errorhandler(500)
def system_error(error):
    return render_template('500.html'), 500

if __name__ == '__main__':
    # 静态文件缓存自动刷新
    app.jinja_env.auto_reload = True
    app.run(host='127.0.0.1',port=8001, debug=True)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128

开发html文件

后端接口有了，接下来就是web端发起调用，完成增删改查交互操作了。
此处flask提供了简单易用的渲染语法，请看：

首页

<!DOCTYPE html>
<html lang="en">
   <head>
       <meta charset="UTF-8">
       <meta name="viewport" content="width=device-width, initial-scale=1.0">
       <link href="{{ url_for('static', filename = 'css/bootstrap.min.css') }}"
           rel="stylesheet">
       <title>首页</title>
   </head>
   <body>
       <div class="container">
           <div class="row justify-content-center align-items-center g-1">
               <div class="col-6 pt-5">
                   <!-- search -->
                   <form action="/search" method="get">
                       <div class="input-group mb-3">
                           <input type="text" class="form-control" placeholder
                               aria-label="Example text with button addon"
                               aria-describedby="button-addon1" name="keyword" {% if keyword%} value="{{keyword}}" {% endif %}>
                           <button class="btn btn-primary" type="submit"
                               id="button-addon1">查询</button>
                               <a class="btn btn-warning " href="/toAddPage">新增</a>
                       </div>
                   </form>

                   

                   <div
                       class="table-responsive">
                       <table
                           class="table table-primary">
                           <thead>
                               <tr>
                                   <th scope="col">ID</th>
                                   <th scope="col">姓名</th>
                                   <th scope="col">性别</th>
                                   <th scope="col">年龄</th>
                                   <th scope="col">联系方式</th>
                                   <th scope="col">操作</th>
                               </tr>
                           </thead>
                           <tbody>
                               {% for item in items %}
                               <tr>
                                   {% for col in cols %}
                                   <td>{{ item[col] }}</td>
                                   {% endfor %}
                                   <!-- 补操作列 -->
                                   <td>
                                       <a class="btn btn-sm btn-primary"
                                           href="{{url_for('toEditPage',id=item['id'])}}">编辑</a>
                                       <a class="btn btn-sm btn-danger"
                                           href="{{url_for('remove',id=item['id'])}}"
                                           onclick="return confirm('确定删除吗');" >删除</a>
                                   </td>
                               </tr>
                               {% endfor %}

                           </tbody>
                       </table>
                       <div class="bg-warning  ">{{success}}</div>

                   </div>
               </div>
           </div>
       </div>

       <script
           src="{{url_for('static',filename='js/jquery.min.js')}}"></script>

   </body>
</html>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

新增页面

<!DOCTYPE html>
<html lang="en">
 <head>
   <meta charset="UTF-8">
   <meta name="viewport" content="width=device-width, initial-scale=1.0">
   <title>新增用户</title>
   <link href="{{ url_for('static', filename = 'css/bootstrap.min.css') }}"
     rel="stylesheet">
 </head>
 <body>
   <div class="container">
     <div class="row justify-content-center align-items-center g-1">
       <div class="col-6 pt-5">
         <div class="card">
           <div class="card-header">
             新增用户
           </div>
           <div class="card-body">

             <form action="/add" method="post">
               <div class="row mb-3">
                 <label for="colFormLabelSm"
                   class="col-sm-2 col-form-label col-form-label">姓名</label>
                 <div class="col-sm-10">
                   <input type="text" class="form-control form-control-sm"
                     id="colFormLabelSm" name="name" required>
                 </div>
               </div>
               <div class="row mb-3">
                 <label for="age" class="col-sm-2 col-form-label">年龄</label>
                 <div class="col-sm-10">
                   <input type="text" class="form-control" id="age" name="age"
                     required>
                 </div>
               </div>
               <div class="row mb-3">
                 <label for="inlineRadio1"
                   class="col-sm-2 col-form-label">性别</label>
                 <div class="col-3">
                   <input class="form-check-input" type="radio" name="gender"
                     id="gender01" value="男">
                   <label class="form-check-label" for="inlineRadio1">男</label>
                 </div>
                 <div class="col-2">
                   <input class="form-check-input" type="radio" name="gender"
                     id="gender02" value="女">
                   <label class="form-check-label" for="inlineRadio2">女</label>
                 </div>
               </div>
               <div class="row mb-3">
                 <label for="phone"
                   class="col-sm-2 col-form-label">联系电话</label>
                 <div class="col-sm-10">
                   <input type="text" class="form-control" id="phone"
                     name="phone" required>
                 </div>
               </div>
               <div
                 class="row mb-3 justify-content-center align-items-center ">
                 <div class="col-6">
                   <a type="button" class="btn btn-secondary " href="/">
                     取消
                   </a>
                   <button type="submit" class="btn btn-primary ">
                     保存
                   </button>
                 </div>

               </div>
             </form>

           </div>

           <div class="bg-warning  ">{{success}}</div>
         </div>
       </div>

     </div>
   </div>
 </body>
</html>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81

编辑页面

<!DOCTYPE html>
<html lang="en">
 <head>
   <meta charset="UTF-8">
   <meta name="viewport" content="width=device-width, initial-scale=1.0">
   <title>修改用户</title>
   <link href="{{ url_for('static', filename = 'css/bootstrap.min.css') }}"
     rel="stylesheet">
 </head>
 <body>
   <div class="container">
     <div class="row justify-content-center align-items-center g-1">
       <div class="col-6 pt-5">
         <div class="card">
           <div class="card-header">
             新增用户
           </div>
           <div class="card-body">
             <form action="/edit" method="post">
               {% if item %}
               <input type="hidden" name="id" value="{{item.id}}">
               <div class="row mb-3">
                 <!-- {{item}} -->
                 <label for="colFormLabelSm"
                   class="col-sm-2 col-form-label col-form-label">姓名</label>
                 <div class="col-sm-10">
                   <input type="text" class="form-control form-control-sm"
                     id="colFormLabelSm" name="name" value="{{item.name}}"
                     required>
                 </div>
               </div>
               <div class="row mb-3">
                 <label for="age" class="col-sm-2 col-form-label">年龄</label>
                 <div class="col-sm-10">
                   <input type="text" class="form-control" id="age" name="age"
                     value="{{item.age}}" required>
                 </div>
               </div>
               <div class="row mb-3">
                 <label for="gender" class="col-sm-2 col-form-label">性别</label>
                 <div class="col-3">
                   <input class="form-check-input" type="radio" name="gender"
                     id="gender01" value="男" {% if item.gender=="男" %} checked
                     {% endif %}>
                   <label class="form-check-label" for="gender01">男</label>
                 </div>
                 <div class="col-2">
                   <input class="form-check-input" type="radio" name="gender"
                     id="gender02" value="女" {% if item.gender=="女" %} checked
                     {% endif %}>
                   <label class="form-check-label" for="gender02">女</label>
                 </div>
               </div>
               <div class="row mb-3">
                 <label for="phone"
                   class="col-sm-2 col-form-label">联系电话</label>
                 <div class="col-sm-10">
                   <input type="text" class="form-control" id="phone"
                     name="phone" value="{{item.phone}}" required>
                 </div>
               </div>
               <div
                 class="row mb-3 justify-content-center align-items-center ">
                 <div class="col-6">
                   <a type="button" class="btn btn-secondary  " href="/">
                     取消
                   </a>
                   <button type="submit" class="btn btn-primary ">
                     保存
                   </button>
                 </div>
               </div>
               {% endif %}
             </form>
           </div>
         </div>
         <div class="bg-warning  ">{{success}}</div>
       </div>
     </div>
   </div>
 </body>
</html>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

收工

看完觉着有帮助的朋友，一键三连哈~~