import uuid import random from tkinter import messagebox import tkinter.font as tkFont from ttkbootstrap import * import ttkbootstrap as ttk from ttkbootstrap.constants import * import pandas as pd import pymysql from BaseWindow import BaseWindow import warnings warnings.simplefilter(action='ignore', category=UserWarning) # 添加第三个任务 def get_text_content(text): return text.get("0.0", "end").replace("\n", " ") class LoadElement(Frame): def __init__(self, master, table_name): super().__init__(master) self.master = master self.table_name = table_name self.data = [] self.conn = pymysql.connect(user="root", password="Wyz010810", database="SCT", host="127.0.0.1", port=3306) self.data_frame = pd.read_sql(f"select * from {self.table_name} limit 0", self.conn) #左侧三框框数据 self.font_style = tkFont.Font(family="Lucida Grande", size=20) self.button_font = tkFont.Font(family="Lucida Grande", size=10) self.create_widget() self.select_value(None) self.create_tree_widget() def select_value(self, event): self.table_name = self.select_stringVar.get() def create_widget(self): data_frame = pd.read_sql("show tables", self.conn) tables = [i[0] for i in data_frame.values.tolist()] style = ttk.Style() # 设置字体和字体大小 style.configure('TCombobox', font=('Arial', 100)) self.select_stringVar = StringVar() self.select_stringVar.set(self.table_name) combo = ttk.Combobox(self, width=10, height=1, textvariable=self.select_stringVar, font=self.font_style, style="TCombobox") combo["values"] = tables combo.bind('<>', self.select_value) combo.grid(column=0, row=0) Button(self, text="装载", width=9, command=self.load).grid(column=1, row=0, padx=5) def create_tree_widget(self): # 创建画布，用于展示装载后的数据 frame = Frame(self, width=50, height=250) frame.grid(row=1, column=0, columnspan=2, sticky=NSEW, pady=5) frame.grid_columnconfigure(0, weight=1) frame.grid_rowconfigure(0, weight=1) columns = self.data_frame.keys().tolist() style = ttk.Style() style.configure("Custom.Treeview.Heading", font=("宋体", 15)) style.configure("Custom.Treeview", rowheight=30, font=("宋体", 15)) tree = ttk.Treeview(frame, columns=columns, show="headings", style="Custom.Treeview", selectmode=BROWSE) # 根据dataFrame结构生成表 for i in columns: tree.heading(column=i, text=i) for i in columns: tree.column(i, anchor=CENTER, width=80) if columns.index(i) == 1 or columns.index(i) == 0: tree.column(i, anchor=CENTER, width=120) for row in self.data_frame.itertuples(): tree.insert(parent="", index=END, values=[row[i] for i in range(1, len(row))]) y_scrollbar = ttk.Scrollbar(self, orient='vertical', command=tree.yview) x_scrollbar = ttk.Scrollbar(frame, orient="horizontal", command=tree.xview) tree.configure(yscrollcommand=y_scrollbar.set) tree.config(height=6) tree.grid(row=0, column=0, columnspan=2, sticky=NSEW, pady=5) y_scrollbar.grid(row=1, column=3, sticky='ns') x_scrollbar.grid(row=2, column=0, columnspan=2, sticky='ew') frame.grid_propagate(False) def load(self): # 加载数据 self.data_frame = pd.read_sql(f"select * from {self.table_name}", self.conn) self.create_tree_widget() class FormElement(Frame): def __init__(self, master, node): super().__init__(master) self.node = node self.data = [] self.conn = pymysql.connect(user="root", password="Wyz010810", database="SCT", host="127.0.0.1", port=3306) self.font_style = tkFont.Font(family="Lucida Grande", size=20) self.button_font = tkFont.Font(family="Lucida Grande", size=10) self.table_names = [] self.form_content = {} self.get_table_names() self.table_name = self.table_names[0] self.default_query_sql = f"SELECT * FROM {self.table_name}" self.create_form() self.select_table(None) self.create_treeview(pd.read_sql(self.default_query_sql, con=self.conn)) def get_table_names(self): """ 获取该数据库下所有的表 :return: """ data_frame = pd.read_sql("show tables", self.conn) self.table_names = [i[0] for i in data_frame.values.tolist()] def select_table(self, event): """ 当下拉框选择表之后，触发该函数，并将tableName改为选择的tableName :param event: :return: """ for value in self.form_content.values(): value[0].grid_forget() value[2].grid_forget() value[3].grid_forget() self.form_content.clear() self.table_name = self.select_table_name.get() self.create_form() self.sql_text.delete(0.0, END) self.sql_text.insert(1.0, f"SELECT * FROM {self.table_name}") def get_field_names(self): """ 获取所有数据表的所有字段 :return: 返回表内的所有字段 """ data_frame = pd.read_sql(f"desc {self.table_name}", self.conn) """ field_names = [] for field in data_frame["Field"]: field_names.append(f"{self.table_name}.{field}") return field_names """ return data_frame["Field"].values.tolist() def create_form(self): """ 创建表单区域，根据数据表自动生成表单 :return: """ self.select_table_name = StringVar() self.select_table_name.set(self.table_name) combo = ttk.Combobox(self, width=8, height=1, textvariable=self.select_table_name, font=self.font_style, style="TCombobox") combo["values"] = self.table_names combo.bind('<>', self.select_table) combo.grid(column=0, row=0, sticky=W) fields = self.get_field_names() num_fields = len(fields) num_rows = (num_fields + 1) // 2 for i in range(num_rows): for j in range(2): field_idx = i * 2 + j if field_idx >= num_fields: break field_name = fields[field_idx] label = Label(self, text=field_name, font=self.font_style) label.grid(row=i + 1, column=j * 2, padx=5, pady=5, sticky=W) checkbutton_var = IntVar() checkbutton = Checkbutton(self, text='', variable=checkbutton_var) checkbutton.grid(row=i + 1, column=j * 2, padx=5, pady=5, sticky=E) entry = Entry(self, font=self.font_style) entry.grid(row=i + 1, column=j * 2 + 1, padx=5, pady=5, sticky=W) self.form_content[field_name] = (entry, checkbutton_var, label, checkbutton) style = Style() style.configure("custom.TButton", padding=(10, 40, 10, 40)) Button(self, text="构造SQL", command=self.make_sql, width=8, style="custom.TButton" ).grid(column=8, row=0, rowspan=8) self.sql_text = Text(self, height=3, font=self.font_style, width=62) self.sql_text.grid(row=8, column=0, rowspan=2, columnspan=7, sticky=W) Button(self, text="\n执行SQL\n", command=self.execute_sql, width=8, ).grid(column=8, row=8, rowspan=2, padx=10) self.sql_text.insert(1.0, self.default_query_sql) def execute_sql(self): cursor = self.conn.cursor() # 获取文本框中的sql语句 sql = get_text_content(self.sql_text).lower().strip() try: cursor.execute(sql) # 如果是查询语句 if sql.startswith("select"): data_frame = pd.read_sql(sql, con=self.conn) self.create_treeview(data_frame) self.node.message.insert(0, f"{sql}执行成功！共查询到{len(data_frame)}条数据-{uuid.uuid4()}-true") # uuid标识事件的唯一性 return # 如果不是查询语句 self.node.message.insert(0, f"{sql}执行成功！-{uuid.uuid4()}-true") except pymysql.Error as e: messagebox.showerror(str(e.args[0]), str(e.args[1])) self.node.message.insert(0, f"{sql}执行失败！{e}-{uuid.uuid4()}-false") finally: cursor.close() self.node.update_message() def create_treeview(self, data_frame): frame = Frame(self, height=580) frame.grid(row=10, column=0, columnspan=10, rowspan=5, sticky=NSEW, pady=10) frame.grid_columnconfigure(0, weight=1) frame.grid_rowconfigure(0, weight=1) unique_columns = data_frame.columns[~data_frame.columns.duplicated()] columns = unique_columns.tolist() # 获取查询的列名，将其转换为列表 tree = ttk.Treeview(frame, columns=columns, show="headings", style="mystyle.Treeview", height=50, selectmode=BROWSE) style = ttk.Style() style.configure("Treeview.Heading", font=self.font_style) style.configure("Treeview", rowheight=30, font=self.font_style, borderwidth=1) # 根据dataFrame结构生成表 for i in columns: tree.heading(column=i, text=i) for i in columns: tree.column(i, anchor=CENTER, minwidth=100, width=100) for row in data_frame.itertuples(): tree.insert(parent="", index=END, values=[row[i] for i in range(1, len(row))]) tree.grid(row=0, column=0, sticky=NSEW, pady=10) y_scrollbar = ttk.Scrollbar(frame, orient='vertical', command=tree.yview) tree.configure(yscrollcommand=y_scrollbar.set) y_scrollbar.grid(row=0, column=1, sticky='ns') frame.grid_propagate(False) def make_sql(self): sql = f"SELECT * FROM {self.table_name}" conditions = [] select_fields = [] condition_statements = [ "{} LIKE '{}'", "{} = '{}'", "NOT ({} != '{}')", "{} IN ('{}')" ] for field, (entry, checkbox_var, i, j) in self.form_content.items(): if entry.get(): # conditions.append("{} like '{}'".format(field, entry.get())) # conditions.append("{} = '{}'".format(field, entry.get())) # conditions.append("NOT ({} != '{}')".format(field, entry.get())) # conditions.append("{} IN ('{}')".format(field, entry.get())) conditions.append(random.choice(condition_statements).format(field, entry.get())) if checkbox_var.get() == 1: select_fields.append("{}".format(field)) if conditions: sql = "SELECT * FROM {} WHERE {}".format(self.table_name, " AND ".join(conditions)) print(sql) if select_fields: sql = sql.replace("*", ",".join(select_fields)) self.sql_text.delete(0.0, END) self.sql_text.insert(1.0, sql) class StuSys_X4(BaseWindow): def __init__(self, master, attr): super().__init__(master, attr) self.create_left_plate() self.create_right_plate() self.message = [] def create_left_plate(self): self.left_frame = Frame(self.master, height=self.attr["height"]) self.right_frame = Frame(self.master, height=self.attr["height"]) self.right_frame.pack(side=RIGHT, anchor=NW, pady=10, padx=10) self.left_frame.pack(side=LEFT, anchor=NW, pady=10, padx=10) load_element1 = LoadElement(self.left_frame, "student") load_element1.pack(side=TOP, pady=10) load_element2 = LoadElement(self.left_frame, "course") load_element2.pack(side=TOP, pady=10) load_element2 = LoadElement(self.left_frame, "sc") load_element2.pack(side=TOP, pady=10) message_frame = Frame(self.right_frame, width=50, height=180) message_frame.pack(side=TOP, pady=10, fill=BOTH) self.canvas = Canvas(message_frame, width=280, height=1000, bg="#f0f5fa") self.canvas.configure(background="#f0f5fa") y_scrollbar = Scrollbar(message_frame, orient="vertical", command=self.canvas.yview) x_scrollbar = Scrollbar(message_frame, orient="horizontal", command=self.canvas.xview) y_scrollbar.pack(side=RIGHT, fill=BOTH) x_scrollbar.pack(side=BOTTOM, fill=BOTH) self.canvas.pack(side=TOP, fill=BOTH) self.canvas.configure(yscrollcommand=y_scrollbar.set) self.canvas.configure(xscrollcommand=x_scrollbar.set) def update_message(self): self.canvas.delete(ALL) for message in self.message: index = self.message.index(message) self.canvas.create_text(0, 20 * index, anchor="nw", text=message.split("-")[0], font=("", 15), fill="black" if message.split("-")[-1] == "true" else "red") self.canvas.update() self.canvas.configure(scrollregion=self.canvas.bbox("all")) def create_right_plate(self): self.right_frame = Frame(self.master, height=self.attr["height"]) self.right_frame.pack(side=LEFT, anchor=NW, pady=10, padx=10) form_element = FormElement(self.right_frame, self) form_element.pack(side=TOP, pady=10, anchor=NE) if __name__ == '__main__': """ minty, lumen, sandstone, yeti, pulse, united, morph, journal, darkly, superhero, solar cyborg, vapor, simplex, cerculean, """ style = "morph" root = Window(themename=style) screenwidth = root.winfo_screenwidth() screenheight = root.winfo_screenheight() root.columnconfigure(0, weight=1) root.rowconfigure(1, weight=1) root_attr = { "width": screenwidth * 0.88, "height": screenheight * 0.90, } alignstr = '%dx%d+%d+%d' % (root_attr['width'], root_attr['height'], (screenwidth - root_attr['width']) / 2, (screenheight - root_attr['height']) / 2) root.geometry(alignstr) root.resizable(width=False, height=False) app = StuSys_X4(root, root_attr) ttk.Style().configure("TButton", font="-size 18") root.mainloop()