python+mysql搭建不同角色登入验证，完成数据导出和导入操作

import tkinter as tk
from tkinter import messagebox, filedialog
import pandas as pd
import pymysql

def login():
    username = entry_username.get()
    password = entry_password.get()
    role = role_var.get()

    if username and password and role:
        try:
            conn = pymysql.connect(
                host='127.0.0.1',
                user='root',
                password='root',
                database='chaxu'
            )
            cursor = conn.cursor()
            if role == "学生":
                cursor.execute("SELECT * FROM student WHERE student_user=%s AND student_password=%s", (username, password))
            elif role == "教师":
                cursor.execute("SELECT * FROM teacher WHERE teacher_user=%s AND teacher_password=%s", (username, password))
            result = cursor.fetchone()
            cursor.close()
            if result:
                messagebox.showinfo("登录成功", f"欢迎使用 {result[3]} 工具")
                open_main_window(conn, role)
            else:
                messagebox.showerror("错误", "用户名或密码错误")
        except pymysql.MySQLError as e:
            messagebox.showerror("错误", f"无法连接到数据库: {str(e)}")
    else:
        messagebox.showerror("错误", "请输入所有必填信息")


def open_main_window(conn, role):
    main_window = tk.Toplevel()
    main_window.title("数据导入导出工具")
    main_window.geometry("500x400")

    #做导出操作，注意主键值不能重复
    def import_data():
        file_path = filedialog.askopenfilename(title="选择Excel文件", filetypes=[("Excel files", "*.xlsx")])
        if file_path:
            try:
                # 指定student_number列应该被读取为整型
                data = pd.read_excel(file_path, converters={'student_number': int})
                cursor = conn.cursor()
                for _, row in data.iterrows():
                    if role == "学生":
                        # 确保student_number被转换为整型
                        student_data = (
                        int(row['student_number']), row['student_class'], row['student_sex'], row['student_name'],
                        row['student_user'], row['student_password'])
                        cursor.execute(
                            "INSERT INTO student (student_number, student_class, student_sex, student_name, student_user, student_password) VALUES (%s, %s, %s, %s, %s, %s)", student_data
                        )
                    elif role == "教师":
                        # 确保student_number被转换为整型
                        teacher_data = (
                        int(row['teacher_number']), row['teacher_class'], row['teacher_sex'], row['teacher_name'],
                        row['teacher_user'], row['teacher_password'])
                        cursor.execute(
                            "INSERT INTO teacher (teacher_number, teacher_class, teacher_sex, teacher_name, teacher_user, teacher_password) VALUES (%s, %s, %s, %s, %s, %s)",teacher_data
                        )
                conn.commit()
                cursor.close()
                # 将消息显示在Text
                text_area.insert(tk.END, "数据已成功导入数据库\n")
                text_area.see(tk.END)
                text_area.update_idletasks()
            except Exception as e:
                messagebox.showerror("错误", f"导入数据时发生错误: {str(e)}")
    #做导出操作
    def export_data():
        cursor = conn.cursor()
        if role == "学生":
            cursor.execute("SELECT * FROM student")
        elif role == "教师":
            cursor.execute("SELECT * FROM teacher")
        rows = cursor.fetchall()
        columns = [desc[0] for desc in cursor.description]
        cursor.close()
        data = pd.DataFrame(rows, columns=columns)
        export_path = filedialog.asksaveasfilename(defaultextension=".xlsx", filetypes=[("Excel files", "*.xlsx")])
        if export_path:
            data.to_excel(export_path, index=False)
            text_area.insert(tk.END, "数据已成功导出数据库\n")
            text_area.see(tk.END)


    # 创建一个Frame来容纳按钮，导入数据，导出数据，+text显示
    button_frame = tk.Frame(main_window)
    button_frame.pack(pady=10)

    # 创建导入按钮
    import_button = tk.Button(button_frame, text="导入数据", command=lambda: import_data())
    import_button.pack(side=tk.LEFT, padx=5)  # 左侧按钮，右侧间距5

    # 创建导出按钮
    export_button = tk.Button(button_frame, text="导出数据", command=lambda: export_data())
    export_button.pack(side=tk.LEFT, padx=5)  # 右侧按钮，左侧间距5

    # 创建一个Text小部件，用于显示消息
    text_area = tk.Text(main_window, width=50, height=3)
    text_area.pack(expand=True, fill='both')

# 主窗口
root = tk.Tk()
root.title("登录")
root.geometry("500x300")
welcome_label = tk.Label(root, text="欢迎使用Jason工具", font=("Helvetica", 20))
welcome_label.pack(pady=10)
role_var = tk.StringVar(value="学生")
# 将角色、学生和教师的lable标签
frame = tk.Frame(root)
frame.pack(pady=5)

label_role = tk.Label(frame, text="角色:")
label_role.pack(side=tk.LEFT, padx=5)

radio_student = tk.Radiobutton(frame, text="学生", variable=role_var, value="学生")
radio_student.pack(side=tk.LEFT, padx=5)

radio_teacher = tk.Radiobutton(frame, text="教师", variable=role_var, value="教师")
radio_teacher.pack(side=tk.LEFT, padx=5)

label_username = tk.Label(root, text="用户名:")
label_username.pack(pady=5)
entry_username = tk.Entry(root)
entry_username.pack(pady=5)

label_password = tk.Label(root, text="密码:")
label_password.pack(pady=5)
entry_password = tk.Entry(root, show='*')
entry_password.pack(pady=5)

login_button = tk.Button(root, text="登录", command=login)
login_button.pack(pady=10)
root.mainloop()