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()
