|
|
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 ttkbootstrap.toast import ToastNotification
|
|
|
|
|
|
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="123123", database="stu_sys", host="127.0.0.1", port=3306)
|
|
|
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.data_frame = pd.DataFrame([]) # 创建一个空的 DataFrame。而不是执行获得并限制返回数量为0
|
|
|
|
|
|
# 定义字体和按钮的格式
|
|
|
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()
|
|
|
|
|
|
# 获取下拉框中的table_name以供后续装载等使用
|
|
|
def select_value(self, event):
|
|
|
self.table_name = self.select_stringVar.get()
|
|
|
|
|
|
# 固定的版本
|
|
|
def create_widget_label(self):
|
|
|
# 不再需要查询数据库获取表名，也不需要创建下拉框
|
|
|
# data_frame = pd.read_sql("show tables", self.conn)
|
|
|
# tables = [i[0] for i in data_frame.values.tolist()]
|
|
|
|
|
|
style = ttk.Style()
|
|
|
# 设置 Label 的字体和字体大小
|
|
|
style.configure('TLabel', font=('Arial', 20)) # 根据需要调整字体大小
|
|
|
|
|
|
# 创建 Label 控件以显示表名
|
|
|
label = ttk.Label(self, text=self.table_name, font=self.font_style, style='TLabel')
|
|
|
label.grid(column=0, row=0)
|
|
|
# 创建装载按钮
|
|
|
Button(self, text="装载", width=9, command=self.load).grid(column=1, row=0, padx=5)
|
|
|
|
|
|
# 创建小控件，包括下拉按钮以及装载按钮
|
|
|
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('<<ComboboxSelected>>', 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="123123", database="stu_sys", host="127.0.0.1", port=3306)
|
|
|
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 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_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 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() # 初始化一个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('<<ComboboxSelected>>', 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=20, pady=5, sticky=W)
|
|
|
# 创建一个复选框，可能用于指示选择状态
|
|
|
checkbutton_var = IntVar()
|
|
|
checkbutton = Checkbutton(self, text='', variable=checkbutton_var)
|
|
|
checkbutton.grid(row=i + 1, column=j * 2, padx=0, pady=5, sticky=E)
|
|
|
# 创建一个文本输入框，用于输入或显示字段值
|
|
|
entry = Entry(self, font=self.font_style, width=12)
|
|
|
# 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)
|
|
|
# 配置并创建一个"构造SQL"按钮
|
|
|
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)
|
|
|
# 创建一个文本框，用于显示或编辑SQL命令
|
|
|
self.sql_text = Text(self, height=3, font=self.font_style, width=46)
|
|
|
self.sql_text.grid(row=8, column=0, rowspan=2, columnspan=7, sticky=W)
|
|
|
# 创建一个"执行SQL"按钮，用于执行文本框中的SQL命令
|
|
|
Button(self, text="\n执行SQL\n", command=self.execute_sql, width=8,
|
|
|
).grid(column=8, row=8, rowspan=2, padx=10)
|
|
|
# 插入默认的查询SQL
|
|
|
self.sql_text.insert(1.0, self.default_query_sql)
|
|
|
|
|
|
def execute_sql(self):
|
|
|
cursor = self.conn.cursor()
|
|
|
sql = get_text_content(self.sql_text).lower().strip() # 获取文本框中的sql语句
|
|
|
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:
|
|
|
# 如果执行过程中遇到pymysql相关错误，则弹出错误信息框
|
|
|
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.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)
|
|
|
# ...course与sc的装载表的创建方式类似
|
|
|
load_element2 = LoadElement(self.left_frame, "course")
|
|
|
load_element2.pack(side=TOP, pady=10)
|
|
|
load_element3 = LoadElement(self.left_frame, "sc")
|
|
|
load_element3.pack(side=TOP, pady=10)
|
|
|
|
|
|
# 以下是消息框部分的内容
|
|
|
|
|
|
# 创建右侧框架并打包定位
|
|
|
self.right_frame = Frame(self.master, height=self.attr["height"])
|
|
|
self.right_frame.pack(side=RIGHT, anchor=NW, pady=10, padx=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()
|
