数据库附件表

CREATE TABLE attachments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    record_id INT NOT NULL, -- 关联的主表记录ID
    file_name VARCHAR(255) NOT NULL, -- 文件名
    file_path VARCHAR(255) NOT NULL, -- 文件存储路径
    file_type VARCHAR(50), -- 文件类型（如：image/jpeg, application/pdf）
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 创建时间
    FOREIGN KEY (record_id) REFERENCES main_table(id) -- 外键约束
);

from flask import Flask, request, redirect, url_for
import os

app = Flask(__name__)

@app.route('/upload', methods=['POST'])
def upload_file():
    if 'file' not in request.files:
        return redirect(request.url)
    file = request.files['file']
    if file.filename == '':
        return redirect(request.url)
    if file:
        filename = secure_filename(file.filename)
        file_path = os.path.join(app.config['UPLOAD_FOLDER'], filename)
        file.save(file_path)
        
        # 插入附件表
        cursor.execute("INSERT INTO attachments (record_id, file_name, file_path, file_type) VALUES (%s, %s, %s, %s)",
                       (record_id, filename, file_path, file.content_type))
        db.commit()
        
        return 'File successfully uploaded'

if __name__ == '__main__':
    app.run(debug=True)

@app.route('/view_attachments/<int:record_id>')
def view_attachments(record_id):
    cursor.execute("SELECT file_name, file_path, file_type FROM attachments WHERE record_id = %s", (record_id,))
    attachments = cursor.fetchall()
    return render_template('attachments.html', attachments=attachments)