在学习之前先要了解sqlite游标的使用方法python使用sqlite3时游标的使用方法
继上篇博客Python实现学生信息管理系统后，我就觉得写的太复杂了，然后又是一通优化、优化、优化；
本次优化主要修改了：
1.使用游标的方法连接、增、删、改、查数据库；
2.一般二级菜单是不能直接退出程序的，所以去掉了二级菜单退出程序的功能；
3.增加了连表查询；
4.但是还有一点很不满意，就是每次退出后都退出到主菜单而不是当前菜单，这点还没改好，希望小伙伴能一起学习交流！
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
|
#-*- coding:utf-8 -*-import sqlite3#打开本地数据库用于存储用户信息cx = sqlite3.connect('student.db')#在该数据库下创建学生信息表cx.execute ('''CREATE TABLE StudentTable( ID INTEGER PRIMARY KEY AUTOINCREMENT, StuId INTEGER NOT NULL, NAME TEXT NOT NULL, CLASS INT NOT NULL);''')print "Table created successfully";#在该数据库下创建课程信息表cx.execute ('''CREATE TABLE CourseTable( ID INTEGER PRIMARY KEY AUTOINCREMENT, CourseId INT NOT NULL, Name TEXT NOT NULL, Teacher TEXT NOT NULL, Classroom TEXT NOT NULL, StartTime CHAR(11) NOT NULL, EndTime CHAR(11) NOT NULL);''')print "Table created successfully";#在该数据库下创建选课情况信息表cx.execute ('''CREATE TABLE XuankeTable( ID INTEGER PRIMARY KEY AUTOINCREMENT, StuId INT NOT NULL, CourseId INT NOT NULL);''')print "Table created successfully";#以上三个表创建完后，再次运行程序时，需要把三个建表代码注释掉，否则会提示:该表已存在。即建表只需建一次。def insert_stu():#录入学生信息 cu = cx.cursor() stu_id = input("请输入学生学号:") cu.execute("SELECT StuId from StudentTable where StuId = '%s';"%stu_id) row = cu.fetchone() if row: print "sorry,该学号已存在,请重新输入" else: stu_name = raw_input("请输入学生姓名:") stu_class = input("请输入学生班级:") sql1 = "INSERT INTO StudentTable(StuId,NAME,CLASS)" sql1 += " VALUES(%d,'%s',%d);"%(stu_id,stu_name,stu_class) cu.execute(sql1) cx.commit() print "恭喜你,学生录入成功!" cu.close()def xuanke():#学生选课 cu = cx.cursor() stu_id = input('请输入要选课的学生学号:') sql2 = "select StuId from StudentTable where StuId = %d;"%(stu_id) cu.execute(sql2) row = cu.fetchone() if row: sql3 = "select CourseId,Name,Teacher,Classroom, StartTime,EndTime from CourseTable" cu.execute(sql3) rows = cu.fetchall() for row in rows: print "CourseId = ", row[0] print "Name = ", row[1] print "Teacher = ", row[2] print "Classroom = ",row[3] print "StartTime = ",row[4] print "EndTime = ",row[5], "\n" cou_id = input("请输入要选的课程号:") sql0 = "select CourseId from CourseTable where CourseId =%d;"%(cou_id) cu.execute(sql0) row = cu.fetchone() if row: sql = "select StuId CourseId from XuankeTable " sql += "where CourseId = %d and StuId=%d;"%(cou_id,stu_id) cu.execute(sql) rows = cu.fetchone() if row: print "该课程已选,不能重复选课!" break else: sql3 = "insert into XuankeTable (StuId,CourseId) values (%d,%d)"%(stu_id,cou_id) cu.execute(sql3) cx.commit() print "恭喜你，选课成功!" else: print "sorry,该课程不存在!" else: print "sorry,没有该学生号" cu.close()def stu_id_search():#按照学生学号查询学生信息 cu = cx.cursor() search_stu_id = input("请输入要查询的学号:") sql4 = "SELECT ID,StuId,NAME, CLASS from StudentTable " sql4 += "where StuId= %d;" % (search_stu_id) cu.execute(sql4) row = cu.fetchone() if row: print print "您要查询的学生信息为:" print "ID = ", row[0] print "StuId = ", row[1] print "NAME = ", row[2] print "CLASS = ",row[3], "\n" else: print "sorry,没有该学生信息！" cu.close()def stu_id_cou():#按照学生学号查询该学生所选课程 cu = cx.cursor() stu_id = input("请输入要查询学生号:") sql5 = "select StuId from StudentTable where StuId = %d;"%(stu_id) cu.execute(sql5) row = cu.fetchone() if row : sql6 = "select A.*,B.*,C.* from XuankeTable A, CourseTable B, StudentTable C \ where A.StuId = %d and A.CourseId=B.CourseId and A.StuId=C.StuId"%(stu_id)#连表查询 cu.execute(sql6) rows = cu.fetchall() for row in rows: print "该学生所选课程为:" print "StuId=",row[1] print "CourseId=",row[2] print "Name = ", row[7] print "Teacher = ", row[8] print "Classroom = ",row[9] print "StartTime = " ,row[10] print "EndTime = ",row[11],"\n" print else: print "sorry,没有该学生选课信息!" cu.close()def cou_id_search(): #按照课程号查询课程信息 cu = cx.cursor() cou_id = input("请输入要查询的课程号:") sql7 = "select CourseId ,Name,Teacher,Classroom,StartTime,EndTime from CourseTable " sql7 += "where CourseId = %d;"%(cou_id) cu.execute(sql7) row = cu.fetchone() if row: print "您要查询的课程信息为:" print "CourseId = ",row[0] print "Name = ", row[1] print "Teacher = ", row[2] print "Classroom = ",row[3] print "StartTime = " ,row[4] print "EndTime = ",row[5],"\n" else: print "sorry,没有该课程信息!" cu.close()def cou_id_stu():#按照课程号查询选择该课程的学生列表 cu = cx.cursor() cou_id = input('请输入课程号:') sql8 = "select CourseId from XuankeTable where CourseId =%d;"%(cou_id) cu.execute(sql8) row = cu.fetchone() if row: sql9 = "select A.*,B.*,C.* from XuankeTable A, CourseTable B, StudentTable C \ where A.CourseId = %d and A.CourseId=B.CourseId and A.StuId=C.StuId"%(cou_id) cu.execute(sql9) rows = cu.fetchall() for row in rows: print print "选择该课程的学生为:" print "StuId = ", row[1] print "CourseId = ", row[2] print "NAME = ", row[14] print "CLASS = ",row[15],"\n" else: print "sorry,没有该课程信息!" cu.close()def menu(): print '1.进入学生信息系统(学生信息录入)' print '2.进入学生选课系统(学生选课操作)' print '3.进入学生选课信息系统(学生信息查询和选课情况查询)' print '4.退出程序'def student(): print '1.录入学生信息' print '2.返回主菜单'def Course(): print '1.开始选课' print '2.返回主菜单'def information(): print '1.按学号查询学生信息' print '2.按学号查看学生选课课程列表' print '3.按课程号查看课程信息' print '4.按课程号查看选课学生列表' print '5.返回主菜单'while True: menu() print x = raw_input('请输入您的选择菜单号:') if x == '1': #进入学生信息系统 student() stu = raw_input('您已进入学生录入系统,请再次输入选择菜单:') print if stu == '1': insert_stu() continue if stu == '2': continue else: print "输入的选项不存在，请重新输入！" continue if x == '2': #进入选课信息系统 Course() cou = raw_input('您已进入学生选课系统,请再次输入选择菜单:') print if cou == '1': xuanke() continue if cou == '2': continue else: print "输入的选项不存在，请重新输入！" continue if x == '3': #进入学生选课信息表 information() inf = raw_input('您已进入学生选课信息系统,请再次输入选择菜单:') print if inf == '1': stu_id_search() continue if inf == '2': stu_id_cou() continue if inf == '3': cou_id_search() continue if inf == '4': cou_id_stu() continue if inf == '5': continue else: print "输入的选项不存在，请重新输入！" continue if x == '4': print "谢谢使用！" exit() else: print "输入的选项不存在，请重新输入！" continue |
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持服务器之家。
原文链接：http://blog.csdn.net/MTbaby/article/details/60768948
