Python连接SqlServer+GUI嵌入式——学生管理系统1.0

学生管理系统1.0

1.建学生数据库

2.数据库嵌入高级语言（Python）

3.界面设计

 

简化思路：

1.先通过SqlServer2012建立学生数据库，包括账号、密码，姓名、选课等信息

2.运用Python的pymssql库与SqlServer建立连接再用tkinter库创建GUI界面

 

1）SqlServer建学生数据库

 1 create database student_Mis
 2 on
 3 (name = student_Data,
 4 filename='F:\SQL list\student_mis_data.mdf',
 5 size = 20,
 6 maxsize = 100,
 7 filegrowth = 10)
 8 log on 
 9 (name = student_Log,
10 filename = 'F:\SQL list\student_mis_log.ldf',
11 size = 5mb,
12 maxsize = 50mb,
13 filegrowth = 5mb)

2）在学生库内建表并插入数据

 1 create table Students
 2 (学号 char(10) primary key,
 3 密码 varchar(20) not null,
 4 姓名 char(20) not null,
 5 性别 char(5),
 6 出生日期 date
 7 )
 8 
 9 create table Course
10 (课程名 char(30) not null,
11 课程老师 char(20) not null,
12 课程号 char(10) primary key)
13 
14 create table Report 
15 (学号 char(10),
16 课程号 char(10),
17 成绩 int,
18 primary key(学号,课程号),
19 constraint Students_Report foreign key(学号) references Students,
20 constraint Couse_Report foreign key(课程号) references Course)
21 
22 
23 insert  
24 into Students
25 values
26 ('2018165201','12345678','李铭','男','2000-02-01'),
27 ('2018165202','12345678','刘晓鸣','男','2000-08-02'),
28 ('2018165203','12345678','李明','男','1999-07-09'),
29 ('2018165204','12345678','张鹰','女','1998-08-20'),
30 ('2018165205','12345678','刘竟静','女','1999-09-09'),
31 ('2018165206','12345678','刘成刚','男','2000-10-10'),
32 ('2018165207','12345678','王铭','男','2000-01-01'),
33 ('2018165118','12345678','右耳朵耗子','男','1998-08-25')
34 
35 insert 
36 into Course
37 values
38 ('数据库', '美美', '20185101'),
39 ('概率论', '杨晓东', '20185102'),
40 ('C语言', '黄晓明', '20185103'),
41 ('计算机基础', '徐明', '20185104')
42 
43 insert 
44 into Report
45 values 
46 ('2018165201','20185101', '90'),
47 ('2018165201','20185102', '80'),
48 ('2018165201','20185103', '99'),
49 ('2018165201','20185104', '88'),
50 
51 ('2018165202','20185101', '70'),
52 ('2018165202','20185102', '90'),
53 ('2018165202','20185103', '90'),
54 ('2018165202','20185104', '78'),
55 
56 ('2018165203','20185101', '89'),
57 ('2018165203','20185102', '85'),
58 ('2018165203','20185103', '90'),
59 ('2018165203','20185104', '67'),
60 
61 ('2018165204','20185101', '88'),
62 ('2018165204','20185102', '85'),
63 ('2018165204','20185103', '85'),
64 ('2018165204','20185104', '85'),
65 
66 ('2018165205','20185101', '90'),
67 ('2018165205','20185102', '90'),
68 ('2018165205','20185103', '90'),
69 ('2018165205','20185104', '90'),
70 
71 ('2018165206','20185101', '0'),
72 ('2018165206','20185102', '0'),
73 ('2018165206','20185103', '0'),
74 ('2018165206','20185104', '0'),
75 
76 ('2018165207','20185101', '0'),
77 ('2018165207','20185102', '0'),
78 ('2018165207','20185103', '0'),
79 ('2018165207','20185104', '0'),
80 ('2018165118','20185101', '100'),
81 ('2018165118','20185102', '100'),
82 ('2018165118','20185103', '100'),
83 ('2018165118','20185104', '100')

 

 3）Python建立连接并实现GUI界面

# ======================
#       imports
# ======================

from tkinter import *
from tkinter import ttk
import pymssql

# import uuid
# import _mssql
# import decimal

from tkinter import messagebox
class Basedesk():
    """
    基准框模块
    """
    def __init__(self, master):


        self.root = master
        self.root.config()
        self.root.title('教务管理系统')
        self.width = 380  # 界面宽
        self.height = 300   # 界面高
        # 获取屏幕尺寸以计算布局参数，使窗口居屏幕中央
        self.screenwidth = self.root.winfo_screenwidth()  # 屏幕宽
        self.screenheight = self.root.winfo_screenheight()  # 屏幕高
        self.alignstr = '%dx%d+%d+%d' % (self.width, self.height, (self.screenwidth-self.width)/2, (self.screenheight-self.height)/2)
        self.root.geometry(self.alignstr)
        self.R = Register(self.root)
        self.R.reigister(self.root)


class Register():

    def __init__(self, master):
        self.root = master
        # 基准框架 """以下三行需根据个人实际修改"""
        self.ip = ' 填入个人ip地址 '
        self.id = ' 填入个人使用的SqlServer的账号 '
        self.pd = ' 填入SqlServer账号对应密码 '

    """
        登录模块
    """
    def reigister(self, master):
        # 账号密码输入框
        self.initface = LabelFrame(self.root, text='教务系统登录', font=('微软雅黑', 14))
        self.initface.grid(padx=85, pady=30, )

        self.people = Label(self.initface, text='账号')  # 账号
        self.people.grid(row=1, column=0, padx=10, pady=10)
        self.password = Label(self.initface, text='密码')   # 密码
        self.password.grid(row=2, column=0, padx=10, pady=10)
        self.var1 = StringVar
        self.var2 = StringVar
        self.entry_people = Entry(self.initface, textvariable=self.var1)   # 账号输入框
        self.entry_people.grid(row=1, column=1, padx=10, pady=10)
        self.entry_password = Entry(self.initface, textvariable=self.var2, show='*')    # 密码输入框
        self.entry_password.grid(row=2, column=1, padx=10, pady=10)
        self.button_into = Button(self.initface, text='登录', command=self.conn)   # 登录按钮
        self.button_into.grid(row=3, column=0, padx=20, pady=20)
        self.button_into = Button(self.initface, text='退出', command=self.root.quit)  # 退出按钮
        self.button_into.grid(row=3, column=1, padx=20, pady=20)
    def conn(self):
        self.connect = pymssql.connect(self.ip, self.id, self.pd,  'student_Mis1')  # 服务器名，账户，密码，数据库名
        self.cursor = self.connect.cursor()
        if self.connect:
            print('连接成功')
        self.sql = "select Students.学号,Students.密码 from Students"

        self.cursor.execute(self.sql)
        self.result = self.cursor.fetchone()
        self.man = self.entry_people.get()
        # self.pd = self.entry_password.get()
        while self.result:
            print('%s|%s' % (self.result[0], self.result[1]))

            if self.result[0] == self.entry_people.get() and self.result[1] == self.entry_password.get():
                print('账号密码正确')

                self.initface.destroy()  #  销毁initface
                self.check()

                break

            else:

                self.result = self.cursor.fetchone()
        else:
            # 账号或密码错误清空输入框
            self.entry_people.delete(0, END)
            self.entry_password.delete(0, END)
            messagebox.showinfo(title='提示', message='账号或密码输入错误\n请重新输入?')
            # break


        self.cursor.close()
        self.connect.close()

    """
        选择模块
    """
    def check(self):
        self.frame_checkbutton = LabelFrame(self.root, text='功能选择', font=('微软雅黑', 14))
        self.frame_checkbutton.grid(padx=60, pady=30)
        # 查询成绩按钮

        self.button_success = Button(self.frame_checkbutton, text='查询成绩', width=10, height=2, command=self.success)
        self.button_success.grid(row=0, column=0, padx=20, pady=20)
        # 修改密码按钮
        self.button_revise = Button(self.frame_checkbutton, text='修改密码', width=10, height=2, command=self.revise)
        self.button_revise.grid(row=0, column=1, padx=20, pady=20)

    def success(self):

        # 连接数据库
        self.connect = pymssql.connect(self.ip, self.id, self.pd,  'student_Mis1')  # 服务器名，账户，密码，数据库名
        if self.connect:
            print('连接成功')
            print(self.man)
            # 查询语句
            search_sql = "select  convert(nvarchar(20), 姓名) ,Students.学号,convert(nvarchar(20), 课程名) ,成绩 from  Students, Report, Course " \
                         "where Students.学号=Report.学号 and Report.课程号=Course.课程号 and Students.学号=%s" % self.man

            # 创建游标
            self.cursor1 = self.connect.cursor()
            self.cursor1.execute(search_sql)
            self.row = self.cursor1.fetchone()  # 读取查询结果

            # 表格框
            root = Tk()   # 初始框的声明
            root.geometry('500x400+100+100')
            root.title('成绩查询系统')
            columns = ("姓名", "学号", "课程", "成绩")
            self.treeview = ttk.Treeview(root, height=18, show="headings", columns=columns)
            self.treeview.column("姓名", width=150, anchor='center')  # 表示列,不显示
            self.treeview.column("学号", width=100, anchor='center')
            self.treeview.column("课程", width=150, anchor='center')
            self.treeview.column("成绩", width=100, anchor='center')

            self.treeview.heading("姓名", text="姓名")  # 显示表头
            self.treeview.heading("学号", text="学号")
            self.treeview.heading("课程", text="课程")
            self.treeview.heading("成绩", text="成绩")
            self.treeview.pack(side=LEFT, fill=BOTH)

            # 插入数据
            while self.row:
                self.treeview.insert('', 0, values=(self.row[0], self.row[1], self.row[2], self.row[3]))
                self.row = self.cursor1.fetchone()  # 读取查询结果,

            self.cursor1.close()
            self.connect.close()
            root.mainloop()


    def revise(self):
        self.window = Tk()  # 初始框的声明
        self.window.geometry('400x200+100+100')
        self.window.title('密码修改管理')
        self.frame_revise = LabelFrame(self.window)
        self.frame_revise.grid(padx=60, pady=60)
        self.label_revise = Label(self.frame_revise, text='新密码：')
        self.label_revise.grid(row=0, column=0, padx=10, pady=10)
        self.var3 = StringVar
        self.entry_revise = Entry(self.frame_revise, textvariable=self.var3)
        self.entry_revise.grid(row=0, column=1, padx=10, pady=10)
        self.button_ok = Button(self.frame_revise, text='确定', command=self.ok)
        self.button_ok.grid(row=1, column=0)
        self.button_resive = Button(self.frame_revise, text='取消', command=self.resive)
        self.button_resive.grid(row=1, column=1)
        self.button_quit = Button(self.frame_revise, text='退出', command=self.window.destroy)
        self.button_quit.grid(row=1, column=2)

    def ok(self):
        # 连接数据库
        self.connect = pymssql.connect(self.ip, self.id, self.pd, 'student_Mis1')  # 服务器名，账户，密码，数据库名
        self.cursor2 = self.connect.cursor()  # 创建游标
        sql_revise = "update Students set 密码=%s where 学号=%s" % (self.entry_revise.get(), self.man)

        if self.connect:
            print('连接成功')
            print(self.man)
            self.cursor2.execute(sql_revise)
            self.connect.commit()
            print(self.entry_revise.get())
            messagebox.showinfo(title='提示', message='密码修改成功!')
            self.cursor2.close()
            self.connect.close()

    def resive(self):
        self.entry_revise.delete(0, END)

if __name__ == '__main__':
    root = Tk()
    Basedesk(root)
    mainloop()

效果图：

1)登录界面

 

 

2）功能选择

 

 

3）成绩查询

 

 

4）密码修改

 

 

 

缺点：1.功能较少，仅查询成绩与修改个人密码。

　　　2.界面设计较简陋。

 　　   3.数据模式设计还不够完整合理

说明：主要运用Python的pymssql和tkinter库和类定义等方法做出这个嵌入式作业，将于空闲时间继续改进，设计出更合理的数据关系模式。