Python+Flask+MySQL项目报错：jinja2 UndefinedError问题求助

我正在开发一个基于Python+Flask+MySQL的小型项目，包含登录、注册、表格列表、上传Excel创建表格、表格查看、CRUD操作及登出功能。查看数据库表格时出现以下错误：

Error: "jinja2.exceptions.UndefinedError: 'tuple object' has no attribute 'keys'"

我的Python代码如下：

from flask import Flask, render_template, request, redirect, url_for, session, flash
from flask_mysqldb import MySQL
from werkzeug.security import generate_password_hash, check_password_hash
import os
import pandas as pd
import pymysql


app = Flask(__name__)
app.secret_key = 'your-secret-key'

# Configure the upload folder
app.config['UPLOAD_FOLDER'] = 'uploads'

# MySQL configurations
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = '1234'
app.config['MYSQL_DB'] = 'userprofile'
mysql = MySQL(app)


@app.route('/', methods=['GET'])
def index():
    return render_template('index.html')


@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']

        cur = mysql.connection.cursor()
        cur.execute("SELECT * FROM users WHERE username = %s", (username,))
        user = cur.fetchone()
        cur.close()

        if user and check_password_hash(user[2], password):
            session['user_id'] = user[0]
            session['username'] = user[1]
            return redirect('/tables')
        else:
            flash('Invalid username or password', 'error')
            return render_template('login.html')

    return render_template('login.html')


@app.route('/signup', methods=['GET', 'POST'])
def signup():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        hashed_password = generate_password_hash(password)

        cur = mysql.connection.cursor()
        cur.execute("INSERT INTO users (username, password) VALUES (%s, %s)", (username, hashed_password))
        mysql.connection.commit()
        cur.close()

        flash('Registration successful! Please login.', 'success')
        return redirect(url_for('login'))

    return render_template('signup.html')


@app.route('/logout')
def logout():
    session.clear()
    return redirect(url_for('login'))


@app.route('/tables', methods=['GET'])
def tables():
    if 'user_id' not in session:
        return redirect(url_for('login'))

    cur = mysql.connection.cursor()
    cur.execute("SHOW TABLES")
    tables = [table[0] for table in cur.fetchall()]
    cur.close()

    return render_template('tables.html', tables=tables)

@app.route('/upload', methods=['POST'])
def upload():
    if 'user_id' not in session:
        return redirect(url_for('login'))

    file = request.files['file']
    if file.filename == '':
        flash('No file selected', 'error')
        return redirect(url_for('tables'))

    # Save the uploaded file to a temporary location
    file_path = os.path.join(app.config['UPLOAD_FOLDER'], file.filename)
    file.save(file_path)

    # Read the Excel file and create table in the database
    try:
        df = pd.read_excel(file_path)
        table_name = os.path.splitext(file.filename)[0]

        # MySQL connection and cursor
        conn = pymysql.connect(
            host=app.config['MYSQL_HOST'],
            user=app.config['MYSQL_USER'],
            password=app.config['MYSQL_PASSWORD'],
            db=app.config['MYSQL_DB'],
            cursorclass=pymysql.cursors.DictCursor
        )
        cur = conn.cursor()

        # Drop table if exists
        cur.execute(f"DROP TABLE IF EXISTS `{table_name}`")

        # Create table
        columns = ", ".join([f"`{column}` VARCHAR(255)" for column in df.columns])
        create_table_query = f"CREATE TABLE `{table_name}` ({columns})"
        cur.execute(create_table_query)

        # Insert data
        for _, row in df.iterrows():
            values = ", ".join([f"'{str(value)}'" for value in row])
            insert_query = f"INSERT INTO `{table_name}` VALUES ({values})"
            cur.execute(insert_query)

        # Commit the changes
        conn.commit()

        flash(f'Table {table_name} created successfully', 'success')
    except Exception as e:
        flash(f'Error uploading file: {str(e)}', 'error')

    # Remove the temporary file
    os.remove(file_path)

    return redirect(url_for('tables'))


@app.route('/view_table/<table_name>', methods=['GET'])
def view_table(table_name):
    if 'user_id' not in session:
        return redirect(url_for('login'))

    cur = mysql.connection.cursor()
    cur.execute(f"SELECT * FROM {table_name}")
    rows = cur.fetchall()
    cur.close()

    return render_template('view_table.html', table_name=table_name, rows=rows)


@app.route('/edit_row/<table_name>/<int:row_id>', methods=['GET', 'POST'])
def edit_row(table_name, row_id):
    if 'user_id' not in session:
        return redirect(url_for('login'))

    cur = mysql.connection.cursor()
    cur.execute(f"SELECT * FROM {table_name} WHERE id = %s", (row_id,))
    row = cur.fetchone()

    if request.method == 'POST':
        new_values = tuple(request.form.get(f'field_{i}') for i in range(len(row)))
        query = f"UPDATE {table_name} SET {', '.join([f'field_{i} = %s' for i in range(len(row))])} WHERE id = %s"
        cur.execute(query, new_values + (row_id,))
        mysql.connection.commit()
        flash('Row updated successfully', 'success')
        return redirect(url_for('view_table', table_name=table_name))

    cur.close()
    return render_template('edit_row.html', table_name=table_name, row=row)


@app.route('/delete_row/<table_name>/<int:row_id>', methods=['GET'])
def delete_row(table_name, row_id):
    if 'user_id' not in session:
        return redirect(url_for('login'))

    cur = mysql.connection.cursor()
    cur.execute(f"DELETE FROM {table_name} WHERE id = %s", (row_id,))
    mysql.connection.commit()
    cur.close()

    flash('Row deleted successfully', 'success')
    return redirect(url_for('view_table', table_name=table_name))


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

错误原因及修复方案 #

1. 核心错误原因 #

Flask-MySQLdb默认返回**元组（tuple）**类型的查询结果，但你的Jinja2模板中大概率在尝试用字典的方式访问数据（比如row['column_name']或row.keys()），而元组不支持这种键名访问，只能通过索引（如row[0]）获取值，因此触发该错误。

2. 直接修复：使用字典游标 #

修改所有查询数据的函数，创建游标时指定DictCursor，让查询结果以字典形式返回，支持键名访问：

# 在view_table、edit_row、delete_row等函数中修改游标创建代码
cur = mysql.connection.cursor(pymysql.cursors.DictCursor)

注：代码中已导入pymysql，无需额外导入

3. 额外问题修复 #

（1）上传Excel时缺少主键

你上传Excel创建表格的代码中没有添加自增主键id，导致后续编辑、删除操作的WHERE id = %s会失效。修改创建表格的语句：

# 原创建语句
# create_table_query = f"CREATE TABLE `{table_name}` ({columns})"
# 修改后添加主键
create_table_query = f"CREATE TABLE `{table_name}` (id INT AUTO_INCREMENT PRIMARY KEY, {columns})"

（2）编辑功能的列名错误

edit_row函数中用field_{i}作为列名，但实际表格列名是Excel中的列名，需先获取列名再构造更新语句：

@app.route('/edit_row/<table_name>/<int:row_id>', methods=['GET', 'POST'])
def edit_row(table_name, row_id):
    if 'user_id' not in session:
        return redirect(url_for('login'))

    cur = mysql.connection.cursor(pymysql.cursors.DictCursor)
    # 获取表格所有列名
    cur.execute(f"DESCRIBE {table_name}")
    columns = [col['Field'] for col in cur.fetchall()]
    # 获取要编辑的行
    cur.execute(f"SELECT * FROM {table_name} WHERE id = %s", (row_id,))
    row = cur.fetchone()

    if request.method == 'POST':
        # 构造更新字段（排除id）
        update_pairs = [f"`{col}` = %s" for col in columns if col != 'id']
        new_values = tuple(request.form[col] for col in columns if col != 'id')
        query = f"UPDATE `{table_name}` SET {', '.join(update_pairs)} WHERE id = %s"
        cur.execute(query, new_values + (row_id,))
        mysql.connection.commit()
        flash('Row updated successfully', 'success')
        return redirect(url_for('view_table', table_name=table_name))

    cur.close()
    return render_template('edit_row.html', table_name=table_name, row=row, columns=columns)

对应模板edit_row.html也要改成用列名渲染：

{% for col in columns %}
  {% if col != 'id' %}
  <div class="form-group">
    <label>{{ col }}</label>
    <input type="text" name="{{ col }}" value="{{ row[col] }}" class="form-control">
  </div>
  {% endif %}
{% endfor %}

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