Python学生信息管理系统优化：基于SQLite游标与连表查询
发布时间：2026-10-13| 5人浏览过
本文对Python学生信息管理系统进行了深度优化，重点介绍了利用sqlite3游标进行数据库连接及增删改查操作的方法。新增连表查询功能，并调整了二级菜单逻辑以优化退出体验。文章附带完整代码示例，旨在帮助开发者提升系统性能与代码规范性。
在开始学习之前，有必要先掌握sqlite游标的基本用法，了解Python中sqlite3模块如何通过游标执行数据库操作。
继上一篇关于Python实现学生信息管理系统的博客之后，作者认为原有代码过于复杂，因此进行了一系列持续的优化与重构工作。
本次优化的核心修改点包括以下几个方面：
1. 采用游标方式实现数据库的连接、增加、删除、修改及查询操作； 2. 移除了二级菜单中直接退出程序的功能，因为通常二级菜单不应直接导致程序终止； 3. 增加了连表查询功能； 4. 目前仍存在一个不足之处：每次退出后均返回至主菜单，而非停留在当前菜单层级，该问题尚未解决，期待与各位小伙伴共同学习交流。
#-*- 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
以上就是本文的全部内容，希望对读者的学习有所帮助，同时也欢迎大家多多支持本站。
免责声明：本文内容仅供参考，不代表本站观点。转载或引用请注明出处。
