亲宝软件园·资讯

展开

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库和类定义等方法做出这个嵌入式作业,将于空闲时间继续改进,设计出更合理的数据关系模式。

加载全部内容

相关教程
猜你喜欢
用户评论