I am doing a small Python + Flask + MySQL project, which will do the functionality, including login, sign up, table listing, table creation from an uploaded Excel file, table view, CRUD operations, and logout functionality.
In that I received the below error while viewing the table in the database.
Error: " jinja2.exceptions.UndefinedError: 'tuple object' has no attribute 'keys' "
My Python code:
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)
