Python连接SqlServer+GUI嵌入式——学生管理系统1.0
zqh001 人气:7学生管理系统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界面
1 ------------------引用请说明出处！---------------------
2 ------------------右耳朵耗子 2019/11/19---------------
3 ---------------期末数据库大作业：管理系统1.0-------------
4 # ======================
5 # imports
6 # ======================
7 from tkinter import *
8 from tkinter import ttk
9 import pymssql
10 from tkinter import messagebox
11 class Basedesk():
12 """
13 基准框模块
14 """
15 def __init__(self, master):
16 self.root = master
17 self.root.config()
18 self.root.title('教务管理系统')
19 self.width = 380 # 界面宽
20 self.height = 300 # 界面高
21 # 获取屏幕尺寸以计算布局参数，使窗口居屏幕中央
22 self.screenwidth = self.root.winfo_screenwidth() # 屏幕宽
23 self.screenheight = self.root.winfo_screenheight() # 屏幕高
24 self.alignstr = '%dx%d+%d+%d' % (self.width, self.height, (self.screenwidth-self.width)/2, (self.screenheight-self.height)/2)
25 self.root.geometry(self.alignstr)
26 self.R = Register(self.root)
27 self.R.reigister(self.root)
28
29
30 class Register():
31
32 def __init__(self, master):
33 self.root = master
34 # 基准框架
35
36 """
37 登录模块
38 """
39 def reigister(self, master):
40 # 账号密码输入框
41 self.initface = LabelFrame(self.root, text='教务系统登录', font=('微软雅黑', 14))
42 self.initface.grid(padx=85, pady=30, )
43
44 self.people = Label(self.initface, text='账号') # 账号
45 self.people.grid(row=1, column=0, padx=10, pady=10)
46 self.password = Label(self.initface, text='密码') # 密码
47 self.password.grid(row=2, column=0, padx=10, pady=10)
48 self.var1 = StringVar
49 self.var2 = StringVar
50 self.entry_people = Entry(self.initface, textvariable=self.var1) # 账号输入框
51 self.entry_people.grid(row=1, column=1, padx=10, pady=10)
52 self.entry_password = Entry(self.initface, textvariable=self.var2, show='*') # 密码输入框
53 self.entry_password.grid(row=2, column=1, padx=10, pady=10)
54 self.button_into = Button(self.initface, text='登录', command=self.conn) # 登录按钮
55 self.button_into.grid(row=3, column=0, padx=20, pady=20)
56 self.button_into = Button(self.initface, text='退出', command=self.root.quit) # 退出按钮
57 self.button_into.grid(row=3, column=1, padx=20, pady=20)
58 def conn(self):
59 self.connect = pymssql.connect('192.168.253.13', 'sa', '8881978aa', 'student_Mis') # 服务器名，账户，密码，数据库名
60 self.cursor = self.connect.cursor()
61 if self.connect:
62 print('连接成功')
63 self.sql = "select Students.学号,Students.密码 from Students"
64
65 self.cursor.execute(self.sql)
66 self.result = self.cursor.fetchone()
67 self.man = self.entry_people.get()
68 # self.pd = self.entry_password.get()
69 while self.result:
70 print('%s|%s' % (self.result[0], self.result[1]))
71 if self.result[0] == self.entry_people.get() and self.result[1] == self.entry_password.get():
72 print('账号密码正确')
73
74 self.initface.destroy() # 销毁initface
75 self.check()
76 # Check(self.root)
77 break
78 else:
79 # 账号或密码错误清空输入框
80 self.entry_people.delete(0, END)
81 self.entry_password.delete(0, END)
82 messagebox.showinfo(title='提示', message='账号或密码输入错误\n请重新输入?')
83 break
84
85 self.cursor.close()
86 self.connect.close()
87
88 """
89 选择模块
90 """
91 def check(self):
92 self.frame_checkbutton = LabelFrame(self.root, text='功能选择', font=('微软雅黑', 14))
93 self.frame_checkbutton.grid(padx=60, pady=30)
94 # 查询成绩按钮
95
96 self.button_success = Button(self.frame_checkbutton, text='查询成绩', width=10, height=2, command=self.success)
97 self.button_success.grid(row=0, column=0, padx=20, pady=20)
98 # 修改密码按钮
99 self.button_revise = Button(self.frame_checkbutton, text='修改密码', width=10, height=2, command=self.revise)
100 self.button_revise.grid(row=0, column=1, padx=20, pady=20)
101
102 def success(self):
103 # 连接数据库
104 self.connect = pymssql.connect('192.168.253.13', 'sa', '8881978aa', 'student_Mis') # 服务器名，账户，密码，数据库名
105 if self.connect:
106 print('连接成功')
107 print(self.man)
108 # 查询语句
109 search_sql = "select 姓名,Students.学号,课程名,成绩 from Students, Report, Course " \
110 "where Students.学号=Report.学号 and Report.课程号=Course.课程号 and Students.学号=%s" % self.man
111
112 # 创建游标
113 self.cursor1 = self.connect.cursor()
114 self.cursor1.execute(search_sql)
115 self.row = self.cursor1.fetchone() # 读取查询结果
116
117 # 表格框
118 root = Tk() # 初始框的声明
119 root.geometry('500x400+100+100')
120 root.title('成绩查询系统')
121 columns = ("姓名", "学号", "课程", "成绩")
122 self.treeview = ttk.Treeview(root, height=18, show="headings", columns=columns)
123 self.treeview.column("姓名", width=150, anchor='center') # 表示列,不显示
124 self.treeview.column("学号", width=100, anchor='center')
125 self.treeview.column("课程", width=150, anchor='center')
126 self.treeview.column("成绩", width=100, anchor='center')
127
128 self.treeview.heading("姓名", text="姓名") # 显示表头
129 self.treeview.heading("学号", text="学号")
130 self.treeview.heading("课程", text="课程")
131 self.treeview.heading("成绩", text="成绩")
132 self.treeview.pack(side=LEFT, fill=BOTH)
133
134 # 插入数据
135 while self.row:
136 self.treeview.insert('', 0, values=(self.row[0], self.row[1], self.row[2], self.row[3]))
137 self.row = self.cursor1.fetchone() # 读取查询结果,
138
139 self.cursor1.close()
140 self.connect.close()
141 root.mainloop()
142
143
144 def revise(self):
145 self.window = Tk() # 初始框的声明
146 self.window.geometry('400x200+100+100')
147 self.window.title('密码修改管理')
148 self.frame_revise = LabelFrame(self.window)
149 self.frame_revise.grid(padx=60, pady=60)
150 self.label_revise = Label(self.frame_revise, text='新密码：')
151 self.label_revise.grid(row=0, column=0, padx=10, pady=10)
152 self.var3 = StringVar
153 self.entry_revise = Entry(self.frame_revise, textvariable=self.var3)
154 self.entry_revise.grid(row=0, column=1, padx=10, pady=10)
155 self.button_ok = Button(self.frame_revise, text='确定', command=self.ok)
156 self.button_ok.grid(row=1, column=0)
157 self.button_resive = Button(self.frame_revise, text='取消', command=self.resive)
158 self.button_resive.grid(row=1, column=1)
159 self.button_quit = Button(self.frame_revise, text='退出', command=self.window.destroy)
160 self.button_quit.grid(row=1, column=2)
161
162 def ok(self):
163 # 连接数据库
164 self.connect = pymssql.connect('192.168.253.13', 'sa', '8881978aa', 'student_Mis') # 服务器名，账户，密码，数据库名
165 self.cursor2 = self.connect.cursor() # 创建游标
166 sql_revise = "update Students set 密码=%s where 学号=%s" % (self.entry_revise.get(), self.man)
167
168 if self.connect:
169 print('连接成功')
170 print(self.man)
171 self.cursor2.execute(sql_revise)
172 self.connect.commit()
173 print(self.entry_revise.get())
174 messagebox.showinfo(title='提示', message='密码修改成功!')
175 self.cursor2.close()
176 self.connect.close()
177
178 def resive(self):
179 self.entry_revise.delete(0, END)
180
181 if __name__ == '__main__':
182 root = Tk()
183 Basedesk(root)
184 mainloop()
缺点：1.功能较少，仅查询成绩与修改个人密码。
2.界面设计较简陋。
3.数据库结构设计还不够合理
说明：主要运用Python的pymssql和tkinter库和类定义等方法做出这个嵌入式作业，将于空闲时间继续改进，设计出更合理的数据关系模式。
加载全部内容