求助：基于Flask与AWS RDS MySQL实现HTML表格行的编辑与删除

现有实现回顾 #

当前已完成表单数据插入AWS RDS MySQL，并在HTML表格展示所有数据，核心代码如下：

Flask 路由代码 #

@app.route('/insert',methods = ['post'])    
def insert():
    if request.method == 'POST':
        name = request.form['name']
        email = request.form['email']
        gender = request.form['optradio']
        comment = request.form['comment']
        db.insert_details(name,email,comment,gender)
        all_details = db.all_data()
        return render_template('index.html',all_details= all_details)

db.py 数据库操作代码 #

import pymysql
import aws_credentials as rds
conn = pymysql.connect (host='A_RDS_DATABASE.amazonaws.com',
                        user='user',
                        password='Password',
                        database='DB',
                        )

def insert_details(name,email,comment,gender):
    cur=conn.cursor()
    cur.execute("INSERT INTO Details (name,email,comment,gender) VALUES (%s,%s,%s,%s)",(name,email,comment,gender))
    conn.commit()

def all_data():
    cur=conn.cursor()
    cur.execute("SELECT * FROM Details")
    return cur.fetchall()

前端表格展示代码 #

<table class="GeneratedTable">
  <thead>
    <tr>
      <th>ID</th>
      <th>Name</th>
      <th>Email</th>
      <th>Gender</th>
      <th>Comment</th>
      <th>操作</th> <!-- 新增操作列 -->
    </tr>
  </thead>
  <tbody>
    {% for row in all_details %}
    <tr>
      <td>{{row[0]}}</td>
      <td>{{row[1]}}</td>
      <td>{{row[2]}}</td>
      <td>{{row[3]}}</td>
      <td>{{row[4]}}</td>
      <td>
        <!-- 后续添加编辑、删除按钮 -->
      </td>
    </tr>
    {% endfor %}
  </tbody>
</table>

实现删除功能 #

1. 在db.py中添加删除数据的函数 #

def delete_details(id):
    cur=conn.cursor()
    cur.execute("DELETE FROM Details WHERE id = %s", (id,))
    conn.commit()

2. 添加Flask删除路由 #

@app.route('/delete/<int:id>', methods=['GET'])
def delete(id):
    db.delete_details(id)
    all_details = db.all_data()
    return render_template('index.html', all_details=all_details)

3. 前端表格添加删除按钮 #

在表格的操作列中添加删除链接：

<td>
  <a href="/delete/{{row[0]}}" onclick="return confirm('确定要删除这条数据吗？')">删除</a>
</td>

实现编辑更新功能 #

1. 在db.py中添加查询单条数据和更新数据的函数 #

def get_detail_by_id(id):
    cur=conn.cursor()
    cur.execute("SELECT * FROM Details WHERE id = %s", (id,))
    return cur.fetchone()

def update_details(id, name, email, comment, gender):
    cur=conn.cursor()
    cur.execute("UPDATE Details SET name=%s, email=%s, comment=%s, gender=%s WHERE id=%s", 
                (name, email, comment, gender, id))
    conn.commit()

2. 添加Flask编辑路由（GET显示表单，POST提交更新） #

@app.route('/edit/<int:id>', methods=['GET', 'POST'])
def edit(id):
    detail = db.get_detail_by_id(id)
    if request.method == 'POST':
        name = request.form['name']
        email = request.form['email']
        gender = request.form['optradio']
        comment = request.form['comment']
        db.update_details(id, name, email, comment, gender)
        all_details = db.all_data()
        return render_template('index.html', all_details=all_details)
    # GET请求返回编辑表单页面
    return render_template('edit.html', detail=detail)

3. 创建编辑表单页面（edit.html） #

<!DOCTYPE html>
<html>
<head>
    <title>编辑数据</title>
</head>
<body>
    <h1>编辑数据</h1>
    <form method="POST">
        <div>
            <label>Name:</label>
            <input type="text" name="name" value="{{detail[1]}}" required>
        </div>
        <div>
            <label>Email:</label>
            <input type="email" name="email" value="{{detail[2]}}" required>
        </div>
        <div>
            <label>Gender:</label>
            <input type="radio" name="optradio" value="Male" {% if detail[3] == 'Male' %}checked{% endif %}> Male
            <input type="radio" name="optradio" value="Female" {% if detail[3] == 'Female' %}checked{% endif %}> Female
        </div>
        <div>
            <label>Comment:</label>
            <textarea name="comment" required>{{detail[4]}}</textarea>
        </div>
        <button type="submit">保存修改</button>
        <a href="/">返回列表</a>
    </form>
</body>
</html>

4. 前端表格添加编辑按钮 #

在操作列中添加编辑链接：

<td>
  <a href="/edit/{{row[0]}}">编辑</a>
  <a href="/delete/{{row[0]}}" onclick="return confirm('确定要删除这条数据吗？')">删除</a>
</td>

内容的提问来源于stack exchange，提问作者K595