Python + Flask + MySQL项目中的错误

ws51t4hk  于 2023-06-25  发布在  Python
关注(0)|答案(1)|浏览(107)

我正在做一个小的Python + Flask + MySQL项目,它将完成功能,包括登录,注册,表列表,从上传的Excel文件创建表,表视图,CRUD操作和注销功能。
我在查看数据库中的表时收到了以下错误。
错误:“jinja2.exceptions.UndefinedError:“元组对象”没有属性“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)
i1icjdpr

i1icjdpr1#

view_table.html文件(您提供的代码中没有)是最有可能发现错误的地方。你的MySQL查询结果,view_table函数中的行,是元组而不是字典,这导致了错误。
让我们更改代码,以访问模板中的每一行作为字典,这样您就可以访问数据作为字典:

@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(pymysql.cursors.DictCursor)
    cur.execute(f"SELECT * FROM {table_name}")
    rows = cur.fetchall()
    cur.close()

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

这就是你如何在html/jinja2中迭代它:

<table>
    <thead>
        <tr>
            {% for key in rows[0].keys() %}
                <th>{{ key }}</th>
            {% endfor %}
        </tr>
    </thead>
    <tbody>
        {% for row in rows %}
            <tr>
                {% for value in row.values() %}
                    <td>{{ value }}</td>
                {% endfor %}
            </tr>
        {% endfor %}
    </tbody>
</table>

相关问题