二、源代码：
from tkinter import *
from tkinter.ttk import *
from tkinter import messagebox
from tkinter import filedialog
from tkinter import simpledialog
from tkinter.colorchooser import askcolor
import hashlib
import sqlite3
from sqlite3 import Error
import datetime
import os
import math
import xlrd
import xlwt
class my_main():
def __init__(self):
self.today_str = datetime.datetime.now().strftime('%Y%m%d') # 日期
self.ZT = None # 展台
self.zt_bg = "lightblue" # 展台背景色
self.zt_t = 10000 # 展台切换时间，黑认10秒。
self.dbFile = "data.db" # 数据库存文件名
self.is_login = False # 是否密码登录
self.conn = None # 数据库连接对象
self.cur = None # 数据库光标对象
self.main_table = "STUDENTS" # 学生表名
self.jf_table = "JFJL" # 加分记录表名
self.findIF = "" # 上次查询条件
self.tree_iid = "" # 表格iid
self.openDate() # 连接数据库存
self.state_txt = None # 状态栏内容变量
self.sys_config = self.auto_add_fen() # 系统配置参数
self.stusData = self.get_data(self.main_table, "") # 获得数据库数据
self.myGUI() # 显示界面
def myGUI(self): # 设置窗口界面
self.root = Tk()
'''------------------------------设置窗口属性----------------------------------'''
self.root.title("学生在校成长量化系统") # 设置窗口标题
self.root.resizable(True, True) #
self.root.state("zoomed")
'''------------------------------设置顶部工具栏---------------------------------------------'''
Style().theme_use("alt") # 改用alt 支持Style
Style().configure("f1.TFrame", background="SkyBlue", padx=10, pady=10)
top_win = Notebook(self.root, height=70, padding=5)
'''-------------导入导出工具栏-------------'''
f1 = Frame()
f1_bt1 = Button(f1, text="学生信息导入", command=self.import_excel)
f1_bt2 = Button(f1, text="学生信息导出n (查询结果)", command=self.out_stu_excel)
f1_bt3 = Button(f1, text="积分记录导出n (查询结果)", command=lambda: self.out_JFJL_excel("all"))
f1_bt1.pack(side=LEFT, fill=Y)
f1_bt2.pack(side=LEFT, fill=Y)
f1_bt3.pack(side=LEFT, fill=Y)
'''-------------数据查询工具栏-------------'''
f2 = Frame()
# STUDENTS表中 ID：学生编号 NAME：学生姓名 BJ：班级 JF：积分 XJ:星级 CH:称号 CZ:成长
f2_bt1 = Button(f2, text="编号查询", command=lambda: self.find_stus("ID"))
f2_bt2 = Button(f2, text="姓名查询", command=lambda: self.find_stus("NAME"))
f2_bt3 = Button(f2, text="班级查询", command=lambda: self.find_stus("BJ"))
f2_bt4 = Button(f2, text="所有学生", command=self.all_stus)
f2_bt1.pack(side=LEFT, fill=Y)
f2_bt2.pack(side=LEFT, fill=Y)
f2_bt3.pack(side=LEFT, fill=Y)
f2_bt4.pack(side=LEFT, fill=Y)
'''-------------数据操作工具栏-------------'''
# STUDENTS表中 ID：学生编号 NAME：学生姓名 BJ：班级 JF：积分 XJ:星级 CH:称号 CZ:成长
f3 = Frame()
f3_bt1 = Button(f3, text="查询结果加分", command=lambda: self.add_fen_win("加分", "all"))
f3_bt2 = Button(f3, text="查询结果减分", command=lambda: self.add_fen_win("减分", "all"))
f3_bt3 = Button(f3, text="学生信息修改", command=self.info_updata_win)
f3_bt4 = Button(f3, text="查询结果删除", command=lambda: self.finded_delete("find"))
f3_bt5 = Button(f3, text="添加学生数据", command=self.add_stu_win)
f3_bt1.pack(side=LEFT, fill=Y)
f3_bt2.pack(side=LEFT, fill=Y)
f3_bt3.pack(side=LEFT, fill=Y)
f3_bt4.pack(side=LEFT, fill=Y)
f3_bt5.pack(side=LEFT, fill=Y)
'''-------------密码管理工具栏-------------'''
f4 = Frame()
f4_bt1 = Button(f4, text="密码修改", command=self.mima_updata_win)
f4_bt2 = Button(f4, text="密码登录", command=self.mima_long_login_win)
f4_bt3 = Button(f4, text="密码退出", command=self.mima_out_login_win)
f4_bt1.pack(side=LEFT, fill=Y)
f4_bt2.pack(side=LEFT, fill=Y)
f4_bt3.pack(side=LEFT, fill=Y)
'''-------------信息展示工具栏-------------'''
f5 = Frame()
f5_bt1 = Button(f5, text="显示展台", command=self.display_ZT)
f5_bt2 = Button(f5, text="关闭展台", command=self.close_ZT)
f5_bt1.pack(side=LEFT, fill=Y)
f5_bt2.pack(side=LEFT, fill=Y)
'''-------------系统相关-------------'''
f6 = Frame()
f6_bt1 = Button(f6, text="展台背景颜色", command=self.zt_background)
f6_bt2 = Button(f6, text="展台切换时间", command=self.zt_time)
# f6_bt3 = Button(f6, text="系统注册")
f6_bt4 = Button(f6, text="关于", command=self.about_me)
f6_bt1.pack(side=LEFT, fill=Y)
f6_bt2.pack(side=LEFT, fill=Y)
# f6_bt3.pack(side=LEFT, fill=Y)
f6_bt4.pack(side=LEFT, fill=Y)
'''------------------顶级菜单------------------'''
top_win.add(f1, text="导入导出")
top_win.add(f2, text="数据查询")
top_win.add(f3, text="数据操作")
top_win.add(f4, text="密码管理")
top_win.add(f5, text="信息展示")
top_win.add(f6, text="系统相关")
top_win.pack(fill=X, pady=1)
'''----------------------------------设置中间表格-------------------------------------'''
m_win = Frame(self.root)
colname = {"A": "编号", "B": "姓名", "C": "班级", "D": "积分", "E": "星级", "F": "称号", "G": "成长值"}
ys = Scrollbar(m_win) # 创建滚动条
ys.pack(side=RIGHT, fill=Y)
# 建立 reeview
self.tree = Treeview(m_win,
columns=list(colname.keys()), # 设置列名称，不是列标题字段
show="headings", # “headings”表示不显示图标栏，"tree"表示显示图标栏
selectmode=BROWSE, # BROWSE：一次选择一项， EXTENDED:一次可以选择多项，NONE:无法用鼠标选择
yscrollcommand=ys.set # 关联滚动条对象
)
# 建立标题及列格式化
for key, val in colname.items():
# 设置表头字段
self.tree.heading(key, # 列名称，对应Treeview中columns参数值
text=val # 设置表头字段值
)
# 设置列格式
self.tree.column(key, # 列名称，对应Treeview中columns参数值
anchor=CENTER, # 本列内容对齐方式
stretch=1, # 默认是1， 当控件大小改变时栏宽将随着改变
minwidth=20, # 最小栏宽，默认是20像素
width=200, # 栏宽，默认为200像素
)
self.init_data()
self.tree.pack(fill=BOTH, expand=True)
ys.config(command=self.tree.yview)
m_win.pack(padx=5, fill=BOTH, expand=True)
'''******************表格右键菜单*************************'''
memu = Menu(self.tree, tearoff=False)
memu.add_command(label="增加积分", command=lambda: self.add_fen_win("加分", "one"))
memu.add_command(label="减少积分", command=lambda: self.add_fen_win("减分", "one"))
memu.add_command(label="信息修改", command=self.info_updata_win)
memu.add_command(label="积分记录导出", command=lambda: self.out_JFJL_excel("one"))
memu.add_command(label="选择删除", command=lambda: self.finded_delete("one"))
def myfun(event):
iid = self.tree.identify_row(event.y) # 获得tree项目iid
if iid:
self.tree.selection_set(iid) # 选中项目iid
memu.post(event.x_root, event.y_root) # 菜单弹出
self.tree.bind("", myfun)
'''---------------------------------------------下面状态栏-----------------------------------------'''
self.state_txt = StringVar()
state_lab = Label(self.root, textvariable=self.state_txt, relief="solid")
state_lab.pack(fill=X, padx=5)
self.state_txt.set("欢迎使用学生在校成长量化系统。 作者：ZK")
self.root.protocol("WM_DELETE_WINDOW", self.colseDate)
self.root.mainloop()
'''=========================================下面界面相关函数====================================================='''
'''##########################系统设置相关###############################'''
def zt_background(self): # 设置展台背景色
self.zt_bg = askcolor()[1]
messagebox.showinfo("成功", "展台背景设置成功，如果展台已打开，请重启。")
def zt_time(self): # 设置展台切换时间
tem_time = simpledialog.askinteger("展台切换时间", "请输入展台换时间,不能小于1（单位秒）。")
if tem_time is not None:
if tem_time < 1:
messagebox.showwarning("失败", "时不能小于1，请重新设置！")
else:
self.zt_t = tem_time * 1000
messagebox.showinfo("成功", "展台切换时间设置成功。")
def about_me(self): # 关于
messagebox.showinfo("关于", "2022年5月24日改版完成。n 作者：ZK ")
'''$$$$$$$$$$$$$$$$$$$显示展台$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$'''
def display_ZT(self):
def auto_fun(arr_kj, imgs, temstus=[]):
# [xj_tu, bh, xm, bj, jf, ch, cz, msg_var]
# ['XS0035', '海不大', '99.2', 67, '★★★', '无', 1, '']
def my_auto(arr_kj, imgs, temstus):
if len(temstus) == 0:
temstus = sorted(self.stusData, key=lambda stu: stu[3], reverse=True)
for i in range(10):
if len(temstus) != 0:
bh = temstus[0][0]
xm = temstus[0][1]
bj = temstus[0][2]
jf = temstus[0][3]
xj = temstus[0][4]
ch = temstus[0][5]
cz = temstus[0][6]
msg = temstus[0][7].replace("n", "")
# [xj_tu, bh, xm, bj, jf, ch, cz, msg_var]
arr_kj[i][0]["image"] = imgs[len(xj)]
arr_kj[i][0]["text"] = f"星级:{xj}"
arr_kj[i][1]["text"] = f"编号：{bh}"
arr_kj[i][2]["text"] = f"姓名：{xm}"
arr_kj[i][3]["text"] = f"班级：{bj}"
arr_kj[i][4]["text"] = f"积分：{jf}"
arr_kj[i][5]["text"] = f"称号：{ch}"
arr_kj[i][6]["text"] = f"成长：{cz}"
arr_kj[i][7].set(msg)
temstus.pop(0)
else:
arr_kj[i][0]["image"] = ""
arr_kj[i][0]["text"] = ""
arr_kj[i][1]["text"] = ""
arr_kj[i][2]["text"] = ""
arr_kj[i][3]["text"] = ""
arr_kj[i][4]["text"] = ""
arr_kj[i][5]["text"] = ""
arr_kj[i][6]["text"] = ""
arr_kj[i][7].set("")
self.ZT.after(self.zt_t, lambda: my_auto(arr_kj, imgs, temstus))
my_auto(arr_kj, imgs, temstus)
def close_fun():
self.ZT.destroy()
self.ZT = None
if self.ZT is None:
self.ZT = Toplevel()
self.ZT.title("成长量化展台")
self.ZT.resizable(True, True)
self.ZT.state("zoom")
self.ZT.rowconfigure(0, weight=1)
self.ZT.rowconfigure(1, weight=1)
self.ZT.protocol("WM_DELETE_WINDOW", close_fun)
for i in range(5):
self.ZT.columnconfigure(i, weight=1)
imgfiles = ["./imgs/000.png", "./imgs/001.png", "./imgs/002.png",
"./imgs/003.png", "./imgs/004.png", "./imgs/005.png",
"./imgs/006.png", "./imgs/007.png", "./imgs/008.png"]
imgs = [PhotoImage(file=item) for item in imgfiles]
arr_widget = []
for x in range(2):
for y in range(5):
f1 = Frame(self.ZT)
f1.grid(row=x, column=y, padx=2, pady=2, sticky=N+S+W+E)
for i in range(6):
f1.rowconfigure(i, weight=1)
for ii in range(2):
f1.columnconfigure(ii, weight=1)
xj_tu = Label(f1, text="星级", image=imgs[0], compound=TOP, anchor=CENTER, background=self.zt_bg)
xj_tu.grid(row=0, column=0, rowspan=2, columnspan=2, sticky=N+S+W+E)
bh = Label(f1, text="编号", anchor=W, font="楷体 12 bold", background=self.zt_bg, padding=15)
bh.grid(row=2, column=0, sticky=N+S+W+E)
xm = Label(f1, text="姓名", anchor=W, font="楷体 12 bold", background=self.zt_bg, padding=15)
xm.grid(row=2, column=1, sticky=N+S+W+E)
bj = Label(f1, text="班级", anchor=W, font="楷体 12 bold", background=self.zt_bg, padding=15)
bj.grid(row=3, column=0, sticky=N+S+W+E)
jf = Label(f1, text="积分", anchor=W, font="楷体 12 bold", background=self.zt_bg, padding=15)
jf.grid(row=3, column=1, sticky=N+S+W+E)
ch = Label(f1, text="称号", anchor=W, font="楷体 12 bold", background=self.zt_bg, padding=15)
ch.grid(row=4, column=0, sticky=N+S+W+E)
cz = Label(f1, text="成长", anchor=W, font="楷体 12 bold", background=self.zt_bg, padding=15)
cz.grid(row=4, column=1, sticky=N+S+W+E)
msg_var = StringVar()
msg = Message(f1, textvariable=msg_var, anchor=CENTER, justify=LEFT, width=200, padx=20, font="楷体 10", background=self.zt_bg)
msg.grid(row=5, column=0, columnspan=2, sticky=N+S+W+E)
arr_widget.append([xj_tu, bh, xm, bj, jf, ch, cz, msg_var])
auto_fun(arr_widget, imgs)
def close_ZT(self):
if self.ZT is not None:
self.ZT.destroy()
self.ZT = None
'''*******初始化添加数据（插入数据）*******'''
def init_data(self):
for item in self.stusData:
self.tree_insert(item[:-1])
'''*******显示所有学生(完成)*******'''
def all_stus(self):
self.get_data(self.main_table, "")
self.tree_delete_all()
self.init_data()
stu_sum = len(self.stusData)
self.state_txt.set(f"查询结果：{stu_sum}名学生")
'''*******查询学生数据*******'''
def find_stus(self, arg): # arg为数据库中字段
tem_txt = simpledialog.askstring("查询", "请输入查询内容：")
# STUDENTS表中 ID：学生编号 NAME：学生姓名 BJ：班级 JF：积分 CH:称号 CZ:成长 LAST_LY:最后一次理由
if tem_txt:
self.get_data(self.main_table, f"where {arg} like '%{tem_txt}%'")
self.tree_delete_all()
self.init_data()
stu_sum = len(self.stusData)
self.state_txt.set(f"查询结果：{stu_sum}名学生")
'''*******记录加减分情况*******'''
def jf_table_insert(self, jf, ly, md):
jf_arr = ""
for item in self.stusData:
tem_list = (item[0], item[1], f"{md}{jf}积分", ly, self.today_str)
jf_arr += f"{tem_list},"
jf_arr = jf_arr[:-1]
if jf_arr != "":
self.insert_data(self.jf_table, jf_arr)
'''*******查询结果加分减分*******'''
def add_fen_win(self, txt, one_or_all):
# JFJL表中 ID:学生编号 NAME：学生姓名 JF：加分 LY:理由 DATE:加分日期 LAST_LY:最后一次理由
def ok_fun(item):
add_f = combovar.get()
add_ly = jf_txt.get("1.0", END)
fh = ""
arg3 = ""
if txt == "加分":
fh = "+"
arg3 = "增加"
else:
fh = "-"
arg3 = "减少"
if one_or_all == "all":
if self.up_data(f"JF=JF{fh}{add_f}, LAST_LY='{self.today_str}日:{add_ly},{arg3}{add_f}分。'", self.findIF):
self.get_data(self.main_table, self.findIF)
self.tree_delete_all()
self.init_data()
self.jf_table_insert(add_f, add_ly, arg3)
t1.destroy()
messagebox.showinfo("成功", f"查询结果{txt}成功！")
else:
if self.up_data(f"JF=JF{fh}{add_f}, LAST_LY='{self.today_str}日:{add_ly},{arg3}{add_f}分。'", f"where ID='{item[0]}'"):
self.get_data(self.main_table, self.findIF)
self.tree_delete_all()
self.init_data()
tem_stu = (item[0], item[1], f"{arg3}{add_f}积分", add_ly, self.today_str)
self.insert_data(self.jf_table, f"{tem_stu}")
t1.destroy()
messagebox.showinfo("成功", f"编号为{item[0]}学生{txt}成功！")
if self.mima_login_win():
t1 = Toplevel()
t1.resizable(False, False)
item = ""
if one_or_all == "all":
t1.title(f"查询结果{txt}")
else:
iid = self.tree.selection()[0]
item = self.tree.item(iid, "values")
t1.title(f"编号{item[0]}{txt}")
jf_lab = Label(t1, text="分值:", anchor="center", width=10, relief=SUNKEN)
jf_lab.grid(row=0, column=0, padx=2, pady=5)
combovar = StringVar()
jf_combo = Combobox(t1, textvariable=combovar, values=("1", "2", "5", "10"), state="readonly")
jf_combo.current(0)
jf_combo.grid(row=0, column=1, padx=2)
jf_lab2 = Label(t1, text=f"{txt}理由：", anchor="center", width=10, relief=SUNKEN)
jf_lab2.grid(row=1, column=0, sticky=N+S)
jf_txt = Text(t1, width=10, height=8)
jf_txt.grid(row=1, column=1, sticky=N+S+W+E)
jf_bt1 = Button(t1, text="确定", command=lambda: ok_fun(item))
jf_bt1.grid(row=2, column=0, pady=5)
jf_bt2 = Button(t1, text="取消", command=t1.destroy)
jf_bt2.grid(row=2, column=1, pady=5)
t1.grab_set()
'''*******向表格中添加数据*******'''
def tree_insert(self, val):
# 插入内容
self.tree.insert("", # 图标位内容，可为空
index=END, # 插入位置，END为表格最后
values=val # 插入内容
)
'''*******删除表格中的数据*******'''
def tree_delete_all(self):
tree_sum = self.tree.get_children()
for iid in tree_sum:
self.tree.delete(iid)
return len(tree_sum)
'''*******查询结果删除*******'''
def finded_delete(self, find_or_one):
if self.mima_login_win():
tem_var = messagebox.askyesno("谨慎操作", "是否要继续删除！")
if tem_var:
if find_or_one == "find":
if self.del_data(self.main_table, self.findIF):
stu_sum = self.tree_delete_all()
self.state_txt.set(f" 成功删除{stu_sum}条学生信息数据！")
messagebox.showinfo("成功！", "查询结果删除成功！")
else:
messagebox.showerror("错误", "数据库删除失败!")
else:
iid = self.tree.selection()
stu = self.tree.item(iid, "values")
if self.del_data(self.main_table, f"where ID='{stu[0]}'"):
self.tree.delete(iid)
self.state_txt.set(f" 编号为{stu[0]}的学生数据删除成功！")
messagebox.showinfo("成功！", f"编号为{stu[0]}的学生数据删除成功！")
else:
messagebox.showerror("错误", "数据库删除失败!")
'''*******添加学生数据*******'''
def add_stu_win(self):
# STUDENTS表中 ID：学生编号 NAME：学生姓名 BJ：班级 JF：积分 CH:称号 CZ:成长 LAST_LY:最后一次理由
def ok_fun():
tem_stu = []
for val in arr_en:
tem_stu.append(val.get())
if tem_stu[0] == "" or tem_stu[1] == "" or tem_stu[2] == "" or tem_stu[3] == "" or tem_stu[4] == "" or tem_stu[5] == "":
messagebox.showwarning("警告", "字段不能为空，请检查！")
else:
if tem_stu[3].isdigit() and tem_stu[5].isdigit():
tem_stu[3] = int(tem_stu[3])
tem_stu[5] = int(tem_stu[5])
if self.get_data(self.main_table, f"where ID='{tem_stu[0]}'"):
messagebox.showwarning("警告", "编号重复，请重新确定编号！")
else:
tem_stu.append("")
if self.insert_data(self.main_table, f"{tuple(tem_stu)}"):
messagebox.showinfo("成功", "数据插入成功")
t1.destroy()
self.state_txt.set(f" 编号为{tem_stu[0]}的数据插入成功。")
else:
messagebox.showwarning("警告", "积分与成长字段都必须为整数！")
if self.mima_login_win():
t1 = Toplevel()
my_list = ["编号: ", "姓名：", "班级：", "积分：", "称号：", "成长："]
arr_en = []
for item in range(6):
tem_lab1 = Label(t1, text=my_list[item], width=20, anchor="center")
tem_lab1.grid(row=item, column=0)
arr_en.append(Entry(t1))
arr_en[-1].grid(row=item, column=1)
Button(t1, text="确定", command=ok_fun).grid(row=6, column=0, pady=5)
Button(t1, text="取消", command=t1.destroy).grid(row=6, column=1, pady=5)
t1.grab_set()
'''*************学生信息修改**************'''
def info_updata_win(self):
if self.mima_login_win():
iid = self.tree.selection()
# STUDENTS表中 ID：学生编号 NAME：学生姓名 BJ：班级 JF：积分 CH:称号 CZ:成长 LAST_LY:最后一次理由
def okfun():
tem_stu = [stu[0]]
for item in arr_en:
tem_stu.append(item.get())
if tem_stu[3].isdigit() and tem_stu[5].isdigit():
if self.up_data(f"NAME='{tem_stu[1]}',BJ='{tem_stu[2]}',JF={tem_stu[3]},CH='{tem_stu[4]}',CZ={tem_stu[5]}", f"where ID='{tem_stu[0]}'"):
self.get_data(self.main_table, self.findIF)
self.tree_delete_all()
self.init_data()
t1.destroy()
messagebox.showinfo("成功！", f"编号{stu[0]}学生数据更新成功!")
else:
t1.destroy()
messagebox.showwarning("失败！", f"编号{stu[0]}学生数据更新失败")
else:
t1.destroy()
messagebox.showwarning("更新失败！", "'积分'与'成长'字段只能是整数值")
if iid:
stu = self.tree.item(iid[0], "values")
stu = list(stu)
stu.pop(4)
t1 = Toplevel()
t1.title(f"编号{stu[0]}信息修改")
title = ["姓名：", "班级：", "积分：", "称号：", "成长："]
arr_en = []
for item in range(5):
bh_lab = Label(t1, text=title[item], width=20, anchor="center")
arr_en.append(Entry(t1))
bh_lab.grid(row=item, column=0)
arr_en[-1].grid(row=item, column=1)
arr_en[-1].insert(0, stu[item+1])
Button(t1, text="确定", command=okfun).grid(row=5, column=0, pady=10)
Button(t1, text="取消", command=t1.destroy).grid(row=5, column=1, pady=10)
t1.grab_set()
else:
messagebox.showwarning("警告", "请先选择一个学生！")
'''*********导入数据（完成）*********'''
def import_excel(self):
if self.mima_login_win():
fname = filedialog.askopenfilename(filetypes=[("excel", ".xls")])
try:
if fname:
wb = xlrd.open_workbook(fname)
sh = wb.sheet_by_name("model")
# STUDENTS表中 ID：学生编号 NAME：学生姓名 BJ：班级 JF：积分 CH:称号 CZ:成长 LAST_LY:最后一次理由
vas = ""
stu_sum = sh.nrows
for i in range(1, stu_sum):
temdata = sh.row_values(i)
temdata[3] = int(temdata[3])
temdata[5] = int(temdata[5])
temdata.append("")
temdata = tuple(temdata)
vas += f"{temdata},"
vas = vas[:-1]
if self.insert_data(self.main_table, vas):
if self.get_data(self.main_table, ""):
self.tree_delete_all()
self.init_data()
self.state_txt.set(f" 成功导入{stu_sum-1}条数据！")
messagebox.showinfo("成功", "成功导入数据")
else:
messagebox.showwarning("失败", "导入数据失败")
except:
messagebox.showwarning("失败", "请使用数据模板")
'''*********导出学生信息数据(查询结果)*********'''
def out_stu_excel(self):
if self.mima_login_win():
fname = filedialog.asksaveasfilename(filetypes=[("excel", ".xls")])
try:
if fname:
fname = f"{fname}.xls"
wb = xlwt.Workbook(encoding="utf-8")
sh = wb.add_sheet("导出数据")
# STUDENTS表中 ID：学生编号 NAME：学生姓名 BJ：班级 JF：积分 XJ:星级 CH:称号 CZ:成长
temdata = self.stusData[:]
temdata.insert(0, ["学生编号", "姓名", "班级", "积分", "星级", "称号", "成长值", "最近一次加分"])
xx = len(temdata)
for x in range(xx):
for y in range(8):
sh.write(x, y, temdata[x][y])
wb.save(fname)
self.state_txt.set(f" 成功导出{len(self.stusData)}条数据！")
messagebox.showinfo("成功", "查询结果导出成功！")
except:
messagebox.showwarning("警告", "查询结果导出失败！")
'''*********导出学生信息数据(完成)*********'''
def out_JFJL_excel(self, all_or_one):
if self.mima_login_win():
try:
if all_or_one == "all":
fname = filedialog.asksaveasfilename(filetypes=[("excel", ".xls")])
if fname:
fname = f"{fname}.xls"
wb = xlwt.Workbook(encoding="utf-8")
sh = wb.add_sheet("导出数据")
# JFJL 表中 ID:学生编号 NAME：学生姓名 JF：加分 LY:理由 DATE:加分日期
temdata = list(self.get_data(self.jf_table, ""))
temdata.insert(0, ["学生编号", "姓名", "积分情况", "理由", "日期"])
xx = len(temdata)
for x in range(xx):
for y in range(5):
sh.write(x, y, temdata[x][y])
wb.save(fname)
self.state_txt.set(f" 成功导出{len(temdata)-1}条积分记录数据！")
messagebox.showinfo("成功", "查询结果导出成功！")
else:
iid = self.tree.selection()
stu = self.tree.item(iid, "values")
temdata = self.get_data(self.jf_table, f"where ID='{stu[0]}'")
if temdata:
fname = filedialog.asksaveasfilename(filetypes=[("excel", ".xls")])
if fname:
fname = f"{fname}.xls"
wb = xlwt.Workbook(encoding="utf-8")
sh = wb.add_sheet("导出数据")
temdata = list(temdata)
temdata.insert(0, ["学生编号", "姓名", "积分情况", "理由", "日期"])
xx = len(temdata)
for x in range(xx):
for y in range(5):
sh.write(x, y, temdata[x][y])
wb.save(fname)
self.state_txt.set(f" 成功导出编号为{stu[0]}的学生积分记录{len(temdata)-1}条记录！")
messagebox.showinfo("成功", "查询结果导出成功！")
else:
messagebox.showwarning("提示", f"编号{stu[0]}学生不存在加分记录！")
except Exception as e:
messagebox.showwarning("警告", e)
'''*********操作时，密码登录*********'''
def mima_login_win(self):
if self.is_login:
return True
else:
temmima = simpledialog.askstring("密码", "请输入密码", show="*")
if isinstance(temmima, str):
temmima = self.computeMD5(temmima)
if temmima == self.sys_config[0]:
return True
else:
messagebox.showwarning("错误", "密码输入错误！")
return False
else:
return False
'''*********密码长时间登录*********'''
def mima_long_login_win(self):
temmima = simpledialog.askstring("密码", "请输入密码", show="*")
if isinstance(temmima, str):
temmima = self.computeMD5(temmima)
if temmima == self.sys_config[0]:
self.is_login = True
messagebox.showinfo("成功", "密码登录成功，请谨慎操作。")
else:
messagebox.showwarning("错误", "密码输入错误！")
'''************密码退出*************'''
def mima_out_login_win(self):
self.is_login = False
messagebox.showinfo("成功", "密码退出成功！")
'''************密码修改**************'''
def mima_updata_win(self):
def my_fun():
old_mm = t1_en1.get()
new_mm = t1_en2.get()
if new_mm == "" and len(new_mm) < 6:
messagebox.showwarning("警告", "密码不能为空 且 不能小于6位！")
else:
if self.computeMD5(old_mm) == self.sys_config[0]:
try:
new_md5 = self.computeMD5(new_mm)
self.cur.execute(f"update config_table set MIMA='{new_md5}'")
self.conn.commit()
self.sys_config[0] = new_md5
t1.destroy()
messagebox.showinfo("成功", "密码更新成功！")
except Exception as e:
self.conn.rollback()
t1.destroy()
messagebox.showwarning("错误", f"原因：{e}")
else:
messagebox.showwarning("失败", "原密码错误，请重新设置")
t1 = Toplevel()
t1_lab1 = Label(t1, text="原密码:", width=10, anchor=CENTER)
t1_en1 = Entry(t1, show="*")
t1_lab1.grid(row=0, column=0, padx=2, pady=2)
t1_en1.grid(row=0, column=1, padx=2, pady=2)
t1_lab2 = Label(t1, text="新密码:", width=10, anchor=CENTER)
t1_en2 = Entry(t1, show="*")
t1_lab2.grid(row=1, column=0, padx=2, pady=2)
t1_en2.grid(row=1, column=1, padx=2, pady=2)
btn1 = Button(t1, text="确定", command=my_fun)
btn2 = Button(t1, text="取消", command=t1.destroy)
btn1.grid(row=2, column=0, padx=2, pady=2)
btn2.grid(row=2, column=1, padx=2, pady=2)
'''----------------------------------------下面数据库相关操作函数------------------------------------------------'''
def openDate(self): # 打开数据库
try:
if os.path.exists(self.dbFile):
self.conn = sqlite3.connect(self.dbFile)
self.cur = self.conn.cursor()
else:
self.conn = sqlite3.connect(self.dbFile)
self.cur = self.conn.cursor()
# STUDENTS表中 ID：学生编号 NAME：学生姓名 BJ：班级 JF：积分 CH:称号 CZ:成长 LAST_LY:最后一次理由
self.cur.execute(f'''CREATE TABLE {self.main_table}
(
ID TEXT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
BJ TEXT NOT NULL,
JF INT NOT NULL,
CH TEXT ,
CZ INT NOT NULL,
LAST_LY TEXT
);''')
# JFJL表中 SYSID:系统编号 ID:学生编号 NAME：学生姓名 JF：加分 LY:理由 DATE:加分日期
self.cur.execute(f'''CREATE TABLE {self.jf_table}
(
ID TEXT NOT NULL,
NAME TEXT NOT NULL,
JF TEXT NOT NULL,
LY TEXT NOT NULL,
DATE TEXT NOT NULL
);''')
# config_table表中 MIAM：密码字段 TODAY :日期 KEY：注册码字段
self.cur.execute(f'''CREATE TABLE config_table
(
MIMA TEXT NOT NULL,
TODAY TEXT NOT NULL,
KEY TEXT NOT NULL
);''')
self.insert_data("config_table", f"('e10adc3949ba59abbe56e057f20f883e', '{self.today_str}', 'e10adc3949ba59abbe56e057f20f883e')")
except Error as e:
messagebox.showerror("错误", f"数据库打开错误：{e}")
def colseDate(self): # 关闭数据库 ,关闭窗口
if self.cur is not None:
self.cur.close()
if self.conn is not None:
self.conn.close()
self.root.destroy()
# 查询获得数据 table:表名 myif:条件
def get_data(self, table, myif):
try:
sql = f"select * from {table} {myif}"
self.cur.execute(sql)
tem_data = self.cur.fetchall()
if table == self.main_table:
self.findIF = myif # 记录查询条件
self.stusData = [list(item) for item in tem_data]
for item in self.stusData:
if item[3] > 2:
item.insert(4, "★" * math.floor(math.log(item[3], 3)))
else:
item.insert(4, "")
return self.stusData
else:
return tem_data
except Error as e:
messagebox.showerror("错误", f"数据查询失败：{e}")
return []
def del_data(self, table, myif): # 删除数据 table:表名 myif:条件
try:
sql = f"delete from {table} {myif}"
self.cur.execute(sql)
self.conn.commit()
return True
except Error as e:
self.conn.rollback()
messagebox.showerror("错误", f"数据删除失败：{e}")
return False
def insert_data(self, table, val): # 插入数据 val:为元组
sql = f"insert into {table} values {val}"
try:
self.cur.execute(sql)
self.conn.commit()
return True
except Error as e:
self.conn.rollback()
messagebox.showerror("错误", f"插入数据失败：{e}")
return False
def up_data(self, upDate, upWhere): # 更新数据 upDate需要更新内容 upWhere更新条件
try:
sql = f"update {self.main_table} set {upDate} {upWhere}"
self.cur.execute(sql)
self.conn.commit()
return True
except Error as e:
self.conn.rollback()
messagebox.showerror("错误", f"数据库更新失败：{e}")
return False
# 学生成长加分
# STUDENTS表中 ID：学生编号 NAME：学生姓名 BJ：班级 JF：积分 XJ:星级 CH:称号 CZ:成长
def auto_add_fen(self):
sys_config = list(self.get_data("config_table", "")[0])
if sys_config[1] != self.today_str:
try:
if self.up_data(f"JF=JF+CZ", ""):
self.cur.execute(f"update config_table set TODAY='{self.today_str}'")
self.conn.commit()
sys_config[1] = self.today_str
except Exception as e:
self.conn.rollback()
messagebox.showwarning("错误",f"原因：{e}")
return sys_config
'''=======================加密函数=============================='''
def computeMD5(self, message):
m = hashlib.md5()
m.update(message.encode(encoding="utf-8"))
return m.hexdigest()
if __name__ == '__main__':
my_main()
三、附件
1、imgs文件夹下内容：
2、 导入学生信息模板：
注：系统中使用到的密码为：123456。
