psmssql+Tkinter实现增删改查
一、介绍和概述
来源:代码借鉴了https://www.bbsmax.com/A/kmzL3WQBdG/
目的:数据库大作业——学生综合测评管理系统的前端部分
缺点:
1. message.showinfo提示操作成功时,教务管理界面的窗口会突然跳到操作窗口之上。
2. 对数据库进行删除和更新操作时有一定限制。
3. 数据库展示界面还没能很好适配。
4. 设计繁杂,代码不够简洁,且界面设计太过单一,很多组件没用上。
二、部分效果图展示:
三、完整代码
import tkinter import decimal import pymssql import datetime # decimal.__version__ from tkinter import * from tkinter import ttk from tkinter import messagebox # 获得当前日期 curTime = datetime.datetime.now() month = curTime.month if curTime.month >= 10 else '0' + str(curTime.month) day = curTime.day if curTime.day >= 10 else '0' + str(curTime.day) curDate = "%s%s%s" % (curTime.year, month, day) # 两个要用到的颜色 color1 = "#FFFFFF" # 纯白 color2 = "#09AAFF" # 蓝色 # 获取屏幕尺寸以计算布局参数,使窗口居屏幕中央 def clac_geo(Root, width, height): screenwidth = Root.winfo_screenwidth() # 屏幕宽 screenheight = Root.winfo_screenheight() # 屏幕高 # 前两项是尺寸,后两项是坐标 alignstr = '%dx%d+%d+%d' % (width, height, (screenwidth - width) / 2, (screenheight - height) / 2) return alignstr # 基准框模块 class BaseDesk: def __init__(self, master): self.root = master self.root.config(bg=color1) self.root.title('教务管理系统') self.alignStr = clac_geo(self.root, 380, 300) self.root.geometry(self.alignStr) self.R = Register(self.root) self.R.loginIn(self.root) class Register: def __init__(self, master): self.root = master # 基准框架 """以下三行需根据个人实际修改""" self.ip = 'localhost' # 填入个人ip地址 self.id = 'sa' # 填入个人使用的SqlServer的账号 self.pd = '123456' # 填入SqlServer账号对应密码 self.db = 'ActivityPoints' # 填入要进行操作的数据库名称 # 登录模块 def loginIn(self, master): # 账号密码输入框 self.labelFrame1 = LabelFrame(self.root, text='教务系统登录', font=('微软雅黑', 14), bg=color1, labelanchor="n") self.labelFrame1.pack(expand='yes') # 管理器为该组件分配的空间是自动扩展,在这里会使得labelFrame1居中 self.LabelUserName = Label(self.labelFrame1, bg=color1, text='账号') # 账号 self.LabelUserName.grid(row=1, column=0, padx=10, pady=10) self.LabelPassword = Label(self.labelFrame1, bg=color1, text='密码') # 密码 self.LabelPassword.grid(row=2, column=0, padx=10, pady=10) var1 = StringVar(self.labelFrame1, value=20181002116) # 设置变量默认值,这个是访问权限为0的 var2 = StringVar(self.labelFrame1, value=111111) # var1 = StringVar(self.labelFrame1, value=20181002988) # 设置变量默认值,这个是访问权限为1的 # var2 = StringVar(self.labelFrame1, value=123456) self.entryUserName = Entry(self.labelFrame1, textvariable=var1) # 账号输入框 self.entryUserName.grid(row=1, column=1, padx=10, pady=10) self.entryPassword = Entry(self.labelFrame1, textvariable=var2, show='*') # 密码输入框 self.entryPassword.grid(row=2, column=1, padx=10, pady=10) self.buttonEnter = Button(self.labelFrame1, text='登录', bg=color1, fg=color2, relief=GROOVE, width=10, height=1, command=self.conn) # 登录按钮 self.buttonEnter.grid(row=3, column=0, padx=10, pady=10, sticky=W) self.buttonQuit = Button(self.labelFrame1, text='退出', bg=color1, fg=color2, relief=GROOVE, width=10, height=1, command=self.root.quit) # 退出按钮 self.buttonQuit.grid(row=3, column=1, padx=10, pady=10, sticky=E) self.root.protocol('WM_DELETE_WINDOW', self.crossClose) # 将右上角的叉号绑定到crossClose函数上 # 点击叉号退出 def crossClose(self): res = messagebox.askokcancel('提示', '是否关闭窗口') if res: try: self.root1.destroy() # 如果选择了数据库还没退出,在关闭时需要一并destroy except Exception as e: print(e) self.root.destroy() self.root.quit() # 连接到学生数据库界面 def conn(self): try: self.connect = pymssql.connect(self.ip, self.id, self.pd, self.db) # 服务器名,账户,密码,数据库名 self.cursor = self.connect.cursor() sql = "select users.ID,users.password,users.level from users" # 查询用户名和密码的sql语句 self.cursor.execute(sql) self.usersInfo = self.cursor.fetchone() # 获取的第一条用户名和密码信息 self.userName = self.entryUserName.get() self.password = self.entryPassword.get() while self.usersInfo: if self.usersInfo[0] == self.userName and self.usersInfo[1] == self.password: # 逐条比对 self.userLevel = self.usersInfo[2] # 获取用户的等级 self.labelFrame1.destroy() # 登陆成功,labelFrame1摧毁 self.check() # 执行check函数,即跳转到登录成功的界面 break else: self.usersInfo = self.cursor.fetchone() else: # 账号或密码错误清空输入框 self.entryUserName.delete(0, END) self.entryPassword.delete(0, END) messagebox.showinfo(title='提示', message='账号或密码输入错误\n请重新输入?') self.cursor.close() self.connect.close() except Exception as e: print(e) messagebox.showinfo(title='提示', message='数据库连接失败!') # 数据库选择界面 def check(self): self.labelFrame2 = LabelFrame(self.root, text='功能选择', font=('微软雅黑', 14), labelanchor="n", bg="#FFFFFF") self.labelFrame2.pack(expand='yes') # 学生信息数据库按钮 buttonStudents = Button(self.labelFrame2, text='学生信息表', bg=color2, fg=color1, width=12, height=1, command=lambda: self.DBShow("students")) # 注意通过command传参的方式 buttonStudents.grid(row=1, column=0, padx=15, pady=5) # 综测信息数据库按钮 buttonCompre = Button(self.labelFrame2, text='综测信息表', bg=color2, fg=color1, width=12, height=1, command=lambda: self.DBShow("comprehensivie")) buttonCompre.grid(row=1, column=1, padx=15, pady=5) # 选课信息数据库按钮 buttonRecord = Button(self.labelFrame2, text='选课信息表', bg=color2, fg=color1, width=12, height=1, command=lambda: self.DBShow("record")) buttonRecord.grid(row=2, column=0, padx=15, pady=15) # 如果用户权限大于0,则拥有操作users表的权限 if eval(self.userLevel): # 用户信息数据库按钮 buttonUsers = Button(self.labelFrame2, text='用户信息表', bg=color2, fg=color1, width=12, height=1, command=lambda: self.DBShow("users")) buttonUsers.grid(row=2, column=1, padx=15, pady=15) # 欢迎信息 userLabel = Label(self.labelFrame2, bg=color1, text="您好,\n" + str(self.userName)) userLabel.grid(row=0, column=0, padx=5, pady=5, columnspan=2) # 修改密码按钮 buttonRevise = Button(self.labelFrame2, text='修改密码', width=10, height=1, bg=color1, fg=color2, relief=GROOVE, command=self.pdChange) buttonRevise.grid(row=3, column=0, padx=5, pady=15) # 退出登录 buttonQuit = Button(self.labelFrame2, text='安全退出', width=10, height=1, bg=color1, fg=color2, relief=GROOVE, command=self.quitUserAction) buttonQuit.grid(row=3, column=1, padx=5, pady=15) # 安全退出账号相应 def quitUserAction(self): # 退出账号时更新上次登录时间 sql = "update users set lastlogindate='%s' where ID='%s'" % (curDate, self.userName) self.connOperation(sql) self.labelFrame2.destroy() try: self.root1.destroy() except Exception as e: print(e) self.loginIn(self.root) # 执行TreeView的读取过程 def readTreeViewAction(self, rt, rowList): tv = ttk.Treeview(rt, height=18, show="headings", columns=self.columnsList) for c in self.columnsList: tv.column(c, width=100, anchor='center') # 表示列,不显示 tv.heading(c, text=c) # 显示表头 scrollBar1 = Scrollbar(rt, orient=VERTICAL, command=tv.yview) # 滚动条 tv.configure(yscrollcommand=scrollBar1.set) tv.grid(row=0, column=0, sticky=NSEW) scrollBar1.grid(row=0, column=1, sticky=NS) # 插入数据 for row in rowList: valueList = [row[i] for i in range(len(self.columnsList))] tv.insert('', 0, values=valueList) return tv # 选择后跳转的界面 def DBShow(self, tableName): self.tableName = tableName # 获取表名 # 连接数据库 self.connect = pymssql.connect(self.ip, self.id, self.pd, self.db) # 服务器名,账户,密码,数据库名 if self.connect: self.cursor = self.connect.cursor() # 获取列名 self.cursor.execute( "select COLUMN_NAME from information_schema.COLUMNS where table_name = '%s'" % self.tableName) self.columnsTmpList = self.cursor.fetchall() self.columnsList = [c[0].replace(',', '') for c in self.columnsTmpList] # 获取内容 selectAllSql = "select * from " + self.tableName self.cursor.execute(selectAllSql) self.rowList = self.cursor.fetchall() # 读取查询结果 # 表格框 self.root1 = Tk() # 初始框的声明 self.root1.config(bg=color1) self.root1.title('学生综合测评管理数据库系统') self.alignStr1 = clac_geo(self.root1, 820, 420) self.root1.geometry(self.alignStr1) print("判断root1是否存在", hasattr(self, 'root1')) treeView = self.readTreeViewAction(self.root1, self.rowList) # 增删改查的实现 operationsFrame = LabelFrame(self.root1) operationsFrame.grid(row=1, column=0, sticky=NS) # 判断等级是否为1 if eval(self.userLevel): bt_insert = Button(operationsFrame, text='插入', bg=color1, fg=color2, width=10, height=1, command=self.insert).grid(row=0, column=0) bt_delete = Button(operationsFrame, text='删除', bg=color1, fg=color2, width=10, height=1, command=self.delete).grid(row=0, column=1) bt_update = Button(operationsFrame, text='更新', bg=color1, fg=color2, width=10, height=1, command=self.update).grid(row=0, column=2) bt_select = Button(operationsFrame, text='查询', bg=color1, fg=color2, width=10, height=1, command=self.select).grid(row=0, column=3) bt_flush = Button(operationsFrame, text='刷新', bg=color1, fg=color2, width=10, height=1, command=self.flush).grid(row=0, column=4) bt_quit = Button(operationsFrame, text='退出', bg=color1, fg=color2, width=10, height=1, command=self.quitDBAction).grid(row=0, column=5) self.cursor.close() self.connect.close() self.root1.mainloop() def quitDBAction(self): self.cursor.close() self.connect.close() self.root1.destroy() # 刷新 def flush(self): self.root1.destroy() self.DBShow(self.tableName) # 执行非选择语句 def connOperation(self, sql): # 连接数据库 connect = pymssql.connect(self.ip, self.id, self.pd, self.db) # 服务器名,账户,密码,数据库名 cursor2 = connect.cursor() # 创建游标 if connect: print(sql) cursor2.execute(sql) connect.commit() messagebox.showinfo(title='提示', message='操作成功!') cursor2.close() connect.close() else: messagebox.showinfo(title='提示', message='操作失败!') # 执行选择语句 def connOperationSelect(self, sql): # 连接数据库 connect = pymssql.connect(self.ip, self.id, self.pd, self.db) # 服务器名,账户,密码,数据库名 cursor2 = connect.cursor() # 创建游标 if connect: print(sql) cursor2.execute(sql) resList = cursor2.fetchall() messagebox.showinfo(title='提示', message='操作成功!') cursor2.close() connect.close() return resList else: messagebox.showinfo(title='提示', message='操作失败!') # 增 def insert(self): window = Tk() window.title("插入") window.configure(bg=color1) window.geometry(clac_geo(window, 400, 400)) frame = Frame(window, bg=color1) frame.pack(side='top', expand='yes') # 创建关联字符变量 self.columnsVar = [] for cNum in range(len(self.columnsList)): exec('self.var' + str(cNum) + ' = ' + 'StringVar') self.columnsVar.append('self.var' + str(cNum)) # 创建标签组件 self.entryList = [] yValue = 0 for i in range(len(self.columnsList)): label = Label(frame, text=self.columnsList[i], bg=color1).grid(row=yValue, column=0, pady=10) # 创建文本框组件,同时设置关联的变量 exec("self.entryValue" + str(i) + " = Entry(frame, textvariable=eval(self.columnsVar[i]))") exec("self.entryValue" + str(i) + ".grid(row=yValue, column=1)") self.entryList.append("self.entryValue" + str(i)) yValue += 1 button_ok = Button(frame, text='插入', bg=color1, fg=color2, width=10, height=1, command=self.insertGo).grid(row=yValue, column=0, sticky=W) button_quit = Button(frame, text='退出', bg=color1, fg=color2, width=10, height=1, command=window.destroy).grid(row=yValue, column=1, sticky=E) def insertGo(self): for i in range(len(self.entryList)): self.entryList[i] = "'" + str(eval(self.entryList[i]).get()) + "'" values = ",".join(self.entryList) sql = "insert into %s values (%s)" % (self.tableName, values) self.connOperation(sql) # 删 def delete(self): window = Tk() window.title("删除") window.configure(bg=color1) window.geometry(clac_geo(window, 400, 150)) frame = Frame(window, bg=color1) frame.pack(side='top', expand='yes') # 创建关联字符变量 varId1 = StringVar # 创建标签组件 label = Label(frame, text="ID", bg=color1).grid(row=0, column=0) # 创建文本框组件,同时设置关联的变量 ID = Entry(frame, textvariable=varId1) ID.grid(row=0, column=1) button_ok = Button(frame, text='删除', bg=color1, fg=color2, width=10, height=1, command=lambda: self.deleteGo(ID.get())).grid(row=1, column=0, pady=20, sticky=W) button_quit = Button(frame, text='退出', bg=color1, fg=color2, width=10, height=1, command=window.destroy).grid(row=1, column=1, pady=20, sticky=E) def deleteGo(self, ID): sql = "delete from %s where ID = '%s'" % (self.tableName, ID) self.connOperation(sql) # 改 def update(self): window = Tk() window.title("更新") window.configure(bg=color1) window.geometry(clac_geo(window, 400, 400)) frame = Frame(window, bg=color1) frame.pack(side='top', expand='yes') # 创建关联字符变量 self.columnsVar = [] for cNum in range(len(self.columnsList)): exec('self.var' + str(cNum) + ' = ' + 'StringVar') self.columnsVar.append('self.var' + str(cNum)) # 创建标签组件 self.entryList = [] yValue = 0 for i in range(len(self.columnsList)): label = Label(frame, text=self.columnsList[i], bg=color1).grid(row=yValue, column=0, pady=10) # 创建文本框组件,同时设置关联的变量 exec("self.entryValue" + str(i) + " = Entry(frame, textvariable=eval(self.columnsVar[i]))") exec("self.entryValue" + str(i) + ".grid(row=yValue, column=1)") self.entryList.append("self.entryValue" + str(i)) yValue += 1 button_ok = Button(frame, text='更新', bg=color1, fg=color2, width=10, height=1, command=self.updateGo).grid(row=yValue, column=0, sticky=W) button_quit = Button(frame, text='退出', bg=color1, fg=color2, width=10, height=1, command=window.destroy).grid(row=yValue, column=1, sticky=E) def updateGo(self): itemsList = ['' for i in range(len(self.entryList))] for i in range(len(self.entryList)): itemsList[i] = eval(self.entryList[i]).get() tmpList = [] for i in range(len(self.columnsList)): if itemsList[i]: tmpList.append(self.columnsList[i] + "='" + itemsList[i] + "'") reviseItems = ",".join(tmpList) sql = "update %s set %s where ID='%s'" % (self.tableName, reviseItems, itemsList[0]) self.connOperation(sql) # 查 def select(self): window = Tk() window.title("查询") window.configure(bg=color1) window.geometry(clac_geo(window, 400, 400)) frame = Frame(window, bg=color1) frame.pack(side='top', expand='yes') # 创建关联字符变量 self.columnsVar = [] for cNum in range(len(self.columnsList)): exec('self.var' + str(cNum) + ' = ' + 'StringVar') self.columnsVar.append('self.var' + str(cNum)) # 创建标签组件 self.entryList = [] yValue = 0 for i in range(len(self.columnsList)): label = Label(frame, text=self.columnsList[i], bg=color1).grid(row=yValue, column=0, pady=10) # 创建文本框组件,同时设置关联的变量 exec("self.entryValue" + str(i) + " = Entry(frame, textvariable=eval(self.columnsVar[i]))") exec("self.entryValue" + str(i) + ".grid(row=yValue, column=1)") self.entryList.append("self.entryValue" + str(i)) yValue += 1 button_ok = Button(frame, text='查询', bg=color1, fg=color2, width=10, height=1, command=lambda: self.selectGo("='")).grid(row=yValue, column=0) button_ok = Button(frame, text='模糊查询', bg=color1, fg=color2, width=10, height=1, command=lambda: self.selectGo(" like '")).grid(row=yValue, column=1) button_quit = Button(frame, text='退出', bg=color1, fg=color2, width=10, height=1, command=window.destroy).grid(row=yValue, column=2) def selectGo(self, str1): itemsList = ['' for i in range(len(self.entryList))] for i in range(len(self.entryList)): print(eval(self.entryList[i]), type(eval(self.entryList[i]))) itemsList[i] = eval(self.entryList[i]).get() tmpList = [] for i in range(len(self.columnsList)): if itemsList[i]: tmpList.append(self.columnsList[i] + str1 + itemsList[i] + "' and ") reviseItems = "".join(tmpList) sql = "select * from %s where %s" % (self.tableName, reviseItems) sql = sql[:-4] resList = self.connOperationSelect(sql) if not resList: messagebox.showerror("警告", "查询结果为空!") return # 表格框 root2 = Tk() # 初始框的声明 root2.title('查询结果') root2.geometry(self.alignStr1) treeview2 = self.readTreeViewAction(root2, resList) root2.mainloop() # 改密码 def pdChange(self): window = Tk() # 初始框的声明 window.config(bg=color1) # 初始框的声明 window.geometry(clac_geo(window, 400, 200)) window.title('密码修改管理') labelFrame = LabelFrame(window, bg=color1) labelFrame.pack(side='top', expand='yes') pdLabel = Label(labelFrame, text='原密码:', bg=color1) pdLabel.grid(row=0, column=0, padx=10, pady=10) pdLabel = Label(labelFrame, text='新密码:', bg=color1) pdLabel.grid(row=1, column=0, padx=10, pady=10) pdLabel = Label(labelFrame, text='再次输入:', bg=color1) pdLabel.grid(row=2, column=0, padx=10, pady=10) var = StringVar var1 = StringVar var2 = StringVar self.pdEntry = Entry(labelFrame, show='*', textvariable=var) self.pdEntry.grid(row=0, column=1, padx=10, pady=10) self.pdEntry1 = Entry(labelFrame, show='*', textvariable=var1) self.pdEntry1.grid(row=1, column=1, padx=10, pady=10) self.pdEntry2 = Entry(labelFrame, show='*', textvariable=var2) self.pdEntry2.grid(row=2, column=1, padx=10, pady=10) buttonOK = Button(labelFrame, text='确定', bg=color1, fg=color2, width=10, height=1, command=self.ok) buttonOK.grid(row=3, column=0, padx=10, pady=10, sticky=W) buttonQuit = Button(labelFrame, text='退出', bg=color1, fg=color2, width=10, height=1, command=window.destroy) buttonQuit.grid(row=3, column=1, padx=10, pady=10, sticky=E) # 修改密码 def ok(self): if self.pdEntry1.get() and self.pdEntry1.get() == self.pdEntry2.get() and self.pdEntry.get() == self.password: sql = "update users set password='%s' where ID='%s'" % (str(self.pdEntry2.get()), self.userName) self.connOperation(sql) elif self.pdEntry.get() != self.password: messagebox.showinfo(title='提示', message='原密码错误!') elif not self.pdEntry1.get(): messagebox.showinfo(title='提示', message='新密码不能为空!') elif self.pdEntry1.get() != self.pdEntry2.get(): messagebox.showinfo(title='提示', message='两次输入不一致!') if __name__ == '__main__': root = Tk() BaseDesk(root) mainloop()