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

  

posted @ 2020-07-03 00:13  丁曾强  阅读(479)  评论(0编辑  收藏  举报