Flask+SQLAlchemy货运预约CRUD应用添加历史记录页问题

我开发了一个管理货运预约的CRUD应用，想要新增一个页面展示最近10条日程变更记录。逻辑是：当预约被创建/更新/删除时，把该预约相关数据存入新的数据库表，然后在History路由页面展示这个表的最新10条记录。但我尝试在各视图函数里添加二次提交，却无法向新表写入数据，现有代码如下：

class appts_db(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    carrier = db.Column(db.String(100), nullable=False)
    volume = db.Column(db.Integer)
    pickup_date = db.Column(db.String(10))

class log_db(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    appt_id = db.Column(db.Integer, db.ForeignKey('appts_db.id'))
    modified_on = db.Column(db.DateTime, default=datetime.now)
    action = db.Column(db.String(7))

@app.route('/create', methods=['GET', 'POST'])
def create():
    if request.method == 'POST':
        carrier_name = request.form['carrier']
        requested_volume = request.form['volume']
        pickup_date_input = request.form['pickup_date']

        new_appt = appts_db(carrier=carrier_name, volume=requested_volume,
            pickup_date=pickup_date_input)

        db.session.add(new_appt)
        db.session.commit()
        return redirect('/')

    else:
        return render_template('create.html')

@app.route('/update/<int:id>', methods=['GET', 'POST'])
def update(id):
    appt = appts_db.query.get_or_404(id)

    if request.method == 'POST':
        appt.carrier = request.form['carrier']
        appt.volume = request.form['volume_update']
        appt.pickup_date = request.form['pickup_date']

        db.session.commit()
        return redirect('/')
    
    else:
        return render_template('update.html', appt=appt)

@app.route('/delete/<int:id>')
def delete(id):
    appt_to_delete = appts_db.query.get_or_404(id)
    db.session.delete(appt_to_delete)
    db.session.commit()
    return redirect('/')

问题修复与功能实现 #

1. 完善日志表结构 #

当前log_db仅记录appt_id，若原预约被删除，关联信息会丢失。建议添加原预约关键字段到日志表，同时修正时间字段和动作字段的配置：

class log_db(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    appt_id = db.Column(db.Integer, db.ForeignKey('appts_db.id'))
    # 新增原预约关键信息字段
    carrier = db.Column(db.String(100))
    volume = db.Column(db.Integer)
    pickup_date = db.Column(db.String(10))
    # 使用SQLAlchemy内置函数处理时间，避免时区问题
    modified_on = db.Column(db.DateTime, default=db.func.now())
    # 扩展动作字段长度，支持完整动作描述
    action = db.Column(db.String(20))

2. 在各操作中添加日志记录 #

修改三个核心视图函数，在预约操作时同步写入日志：

@app.route('/create', methods=['GET', 'POST'])
def create():
    if request.method == 'POST':
        carrier_name = request.form['carrier']
        requested_volume = request.form['volume']
        pickup_date_input = request.form['pickup_date']

        new_appt = appts_db(carrier=carrier_name, volume=requested_volume,
            pickup_date=pickup_date_input)

        db.session.add(new_appt)
        db.session.commit()

        # 添加创建日志
        new_log = log_db(
            appt_id=new_appt.id,
            carrier=new_appt.carrier,
            volume=new_appt.volume,
            pickup_date=new_appt.pickup_date,
            action='创建'
        )
        db.session.add(new_log)
        db.session.commit()

        return redirect('/')
    else:
        return render_template('create.html')

@app.route('/update/<int:id>', methods=['GET', 'POST'])
def update(id):
    appt = appts_db.query.get_or_404(id)

    if request.method == 'POST':
        # 记录更新前的预约信息
        update_log = log_db(
            appt_id=appt.id,
            carrier=appt.carrier,
            volume=appt.volume,
            pickup_date=appt.pickup_date,
            action='更新'
        )
        db.session.add(update_log)

        # 更新预约信息
        appt.carrier = request.form['carrier']
        appt.volume = request.form['volume_update']
        appt.pickup_date = request.form['pickup_date']

        db.session.commit()
        return redirect('/')
    else:
        return render_template('update.html', appt=appt)

@app.route('/delete/<int:id>')
def delete(id):
    appt_to_delete = appts_db.query.get_or_404(id)

    # 先写入删除日志，保存预约原始信息
    delete_log = log_db(
        appt_id=appt_to_delete.id,
        carrier=appt_to_delete.carrier,
        volume=appt_to_delete.volume,
        pickup_date=appt_to_delete.pickup_date,
        action='删除'
    )
    db.session.add(delete_log)
    # 同时提交日志和删除操作
    db.session.delete(appt_to_delete)
    db.session.commit()

    return redirect('/')

3. 添加History路由与页面 #

新增路由查询最新10条日志，渲染展示页面：

@app.route('/history')
def history():
    # 按修改时间倒序，取最新10条记录
    recent_logs = log_db.query.order_by(log_db.modified_on.desc()).limit(10).all()
    return render_template('history.html', logs=recent_logs)

对应的history.html模板示例：

<h1>最近日程变更记录</h1>
<ul>
    {% for log in logs %}
    <li>
        {{ log.modified_on.strftime('%Y-%m-%d %H:%M') }} 
        - {{ log.action }} 预约：承运商{{ log.carrier }}，货量{{ log.volume }}，取货日期{{ log.pickup_date }}
    </li>
    {% endfor %}
</ul>

4. 数据库初始化 #

确保已创建日志表，可在Flask Shell中执行：

db.create_all()

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