Python简单数据库管理系统

90 阅读 0 评论 60 点赞

我是靠谱客的博主清秀高山，这篇文章主要介绍Python简单数据库管理系统，现在分享给大家，希望可以做个参考。

mysql数据库：

复制代码

create table student
(
sno varchar(10) primary key ,
sname varchar(20) not null unique,
ssex char(3) check(ssex in('男','女')),
sage smallint not null,
pwd varchar(20) not null
);
create table teacher
(
tno varchar(7) primary key,
tname varchar(20) not null unique,
tposition varchar(20) not null,
tsalary smallint not null,
pwd varchar(20) not null
);
create table course
(
cno varchar(4) primary key,
cname varchar(40) not null unique,
ccredit smallint not null,
tno varchar(7) not null,
foreign key (tno) references teacher(tno)
);
create table sc
(
sno varchar(10) not null,
cno varchar(4) not null,
grade smallint not null,
primary key(sno,cno),
foreign key (sno) references student(sno),
foreign key (cno) references course(cno)
);
create table syscontroler
(
kno varchar(10) primary key,
kname varchar(20) not null unique,
klevel enum('一','二','三')
);
insert into student(sname,ssex,sno, sage, pwd) values('李天明','男','2006100067',21,'123456');
insert into student(sname,ssex,sno, sage, pwd) values('陈小红','女','2006100068',19,'123456');
insert into student(sname,ssex,sno, sage, pwd) values('王明白','女','2006100069',18,'123456');
insert into student(sname,ssex,sno, sage, pwd) values('张小黑','男','2006100070',19,'123456');
insert into student(sname,ssex,sno, sage, pwd) values('林与','男','2006100071',21,'123456');
insert into teacher(tno,tname, tposition, tsalary, pwd) values('t001','李杰出','教授',8000,'123456');
insert into teacher(tno,tname, tposition, tsalary, pwd) values('t002','郑好','专任教师',5000,'123456');
insert into teacher(tno,tname, tposition, tsalary, pwd) values('t003','刘光明','教授',7000,'123456');
insert into teacher(tno,tname, tposition, tsalary, pwd) values('t004','何小兵','副教授',6000,'123456');
insert into teacher(tno,tname, tposition, tsalary, pwd) values('t005','黄小白','副教授',6500,'123456');
insert into teacher(tno,tname, tposition, tsalary, pwd) values('t006','林峰地','教授',7500,'123456');
insert into course values('6','高等数学',6,'t003');
insert into course values('2','计算机组成原理',3,'t004');
insert into course values('7','java语言',4,'t003');
insert into course values('5','操作系统',3,'t005');
insert into course values('1','数据库',2,'t001');
insert into course values('3','计算机网络',4,'t002');
insert into course values('4','数据结构',5,'t002');
insert into sc values('2006100067','1',92);
insert into sc values('2006100068','2',85);
insert into sc values('2006100069','4',88);
insert into sc values('2006100070','2',90);
insert into sc values('2006100071','5',80);
insert into syscontroler(kno,kname,klevel) values('t002','郑好','三');
insert into syscontroler(kno,kname,klevel) values('t001','李杰出','二');
insert into syscontroler(kno,kname,klevel) values('t003','刘光明','二');
insert into syscontroler(kno,kname,klevel) values('t006','林峰地','二');
insert into syscontroler(kno,kname,klevel) values('t004','何小兵','一');
insert into syscontroler(kno,kname,klevel) values('t005','黄小白','一');
insert into syscontroler(kno,kname,klevel) values('2006100067','李天明','一');
insert into syscontroler(kno,kname,klevel) values('2006100068','陈小红','一');
insert into syscontroler(kno,kname,klevel) values('2006100069','王明白','一');
create view courseinfo as select course.cno 课程编号,course.cname 课程名称,teacher.tname 任课老师,course.ccredit 课程学分,countnum 选修人数,
avg_grade 平均分,max_grade 最高分,min_grade 最低分 from teacher,
course left outer join (select cname, count(*) countnum,avg(grade) avg_grade,max(grade) max_grade,min(grade) min_grade from sc,course
where course.cno=sc.cno group by cname)a1 on (course.cname=a1.cname) where teacher.tno=course.tno;
create view studentinfo as select student.sno 学号,student.sname 姓名,(select sum(grade) from sc where sno=student.sno) 总分 ,
(select sum(ccredit) from course where cno in (select cno from sc where sno=student.sno)) 总学分 ,
(select max(grade) from sc where sc.sno in (student.sno)) 最高分,(select min(grade) from sc where sc.sno in (student.sno)) 最低分 from student;
create view teacherinfo1 as select tposition 职位,count(tno) 在任人数,avg(tsalary) 平均工资 from teacher group by tposition;
create view teacherinfo2 as select tno 教师工号,tname 教师名称,(select count(student.sno) from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and course.tno=teacher.tno) 授课学生总人数 from teacher;
Commit;

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
create table student
(
sno varchar(10) primary key ,
sname varchar(20) not null unique,
ssex char(3) check(ssex in('男','女')),
sage smallint not null,
pwd varchar(20) not null
);
create table teacher
(
tno varchar(7) primary key,
tname varchar(20) not null unique,
tposition varchar(20) not null,
tsalary smallint not null,
pwd varchar(20) not null
);
create table course
(
cno varchar(4) primary key,
cname varchar(40) not null unique,
ccredit smallint not null,
tno varchar(7) not null,
foreign key (tno) references teacher(tno)
);
create table sc
(
sno varchar(10) not null,
cno varchar(4) not null,
grade smallint not null,
primary key(sno,cno),
foreign key (sno) references student(sno),
foreign key (cno) references course(cno)
);
create table syscontroler
(
kno varchar(10) primary key,
kname varchar(20) not null unique,
klevel enum('一','二','三')
);
insert into student(sname,ssex,sno, sage, pwd) values('李天明','男','2006100067',21,'123456');
insert into student(sname,ssex,sno, sage, pwd) values('陈小红','女','2006100068',19,'123456');
insert into student(sname,ssex,sno, sage, pwd) values('王明白','女','2006100069',18,'123456');
insert into student(sname,ssex,sno, sage, pwd) values('张小黑','男','2006100070',19,'123456');
insert into student(sname,ssex,sno, sage, pwd) values('林与','男','2006100071',21,'123456');
insert into teacher(tno,tname, tposition, tsalary, pwd) values('t001','李杰出','教授',8000,'123456');
insert into teacher(tno,tname, tposition, tsalary, pwd) values('t002','郑好','专任教师',5000,'123456');
insert into teacher(tno,tname, tposition, tsalary, pwd) values('t003','刘光明','教授',7000,'123456');
insert into teacher(tno,tname, tposition, tsalary, pwd) values('t004','何小兵','副教授',6000,'123456');
insert into teacher(tno,tname, tposition, tsalary, pwd) values('t005','黄小白','副教授',6500,'123456');
insert into teacher(tno,tname, tposition, tsalary, pwd) values('t006','林峰地','教授',7500,'123456');
insert into course values('6','高等数学',6,'t003');
insert into course values('2','计算机组成原理',3,'t004');
insert into course values('7','java语言',4,'t003');
insert into course values('5','操作系统',3,'t005');
insert into course values('1','数据库',2,'t001');
insert into course values('3','计算机网络',4,'t002');
insert into course values('4','数据结构',5,'t002');
insert into sc values('2006100067','1',92);
insert into sc values('2006100068','2',85);
insert into sc values('2006100069','4',88);
insert into sc values('2006100070','2',90);
insert into sc values('2006100071','5',80);
insert into syscontroler(kno,kname,klevel) values('t002','郑好','三');
insert into syscontroler(kno,kname,klevel) values('t001','李杰出','二');
insert into syscontroler(kno,kname,klevel) values('t003','刘光明','二');
insert into syscontroler(kno,kname,klevel) values('t006','林峰地','二');
insert into syscontroler(kno,kname,klevel) values('t004','何小兵','一');
insert into syscontroler(kno,kname,klevel) values('t005','黄小白','一');
insert into syscontroler(kno,kname,klevel) values('2006100067','李天明','一');
insert into syscontroler(kno,kname,klevel) values('2006100068','陈小红','一');
insert into syscontroler(kno,kname,klevel) values('2006100069','王明白','一');
create view courseinfo as select course.cno 课程编号,course.cname 课程名称,teacher.tname 任课老师,course.ccredit 课程学分,countnum 选修人数,
avg_grade 平均分,max_grade 最高分,min_grade 最低分 from teacher,
course left outer join (select cname, count(*) countnum,avg(grade) avg_grade,max(grade) max_grade,min(grade) min_grade from sc,course
where course.cno=sc.cno group by cname)a1 on (course.cname=a1.cname) where teacher.tno=course.tno;
create view studentinfo as select student.sno 学号,student.sname 姓名,(select sum(grade) from sc where sno=student.sno) 总分 ,
(select sum(ccredit) from course where cno in (select cno from sc where sno=student.sno)) 总学分 ,
(select max(grade) from sc where sc.sno in (student.sno)) 最高分,(select min(grade) from sc where sc.sno in (student.sno)) 最低分 from student;
create view teacherinfo1 as select tposition 职位,count(tno) 在任人数,avg(tsalary) 平均工资 from teacher group by tposition;
create view teacherinfo2 as select tno 教师工号,tname 教师名称,(select count(student.sno) from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and course.tno=teacher.tno) 授课学生总人数 from teacher;
Commit;

第一次搞没什么经验，用navicat把mysql数据库里的表导出后，再重新导入新创建的.db文件中

python主要通过sqlite3

复制代码

import sqlite3
def opendb():
# 打开数据库
conn = sqlite3.connect('C://D-drive-103625//Ubantu//datebase_e.db')
cur = conn.execute(" ")
return cur, conn
def show_student_db():
# 打印学生信息表
print("--------------------学生信息表--------------------")
hel = opendb()
cur = hel[1].cursor()
cur.execute("SELECT * FROM student")
res = cur.fetchall()
for line in res:
print(line)
cur.close()
def show_course_db():
# 打印课程信息表
print("--------------------课程信息表--------------------")
hel = opendb()
cur = hel[1].cursor()
cur.execute("SELECT * FROM course")
res = cur.fetchall()
for line in res:
print(line)
cur.close()
def show_teacher_db():
# 打印教师信息表
print("--------------------教师信息表--------------------")
hel = opendb()
cur = hel[1].cursor()
cur.execute("SELECT * FROM teacher")
res = cur.fetchall()
for line in res:
print(line)
cur.close()
def show_sc_db():
# 打印成绩信息表
print("--------------------成绩信息表--------------------")
hel = opendb()
cur = hel[1].cursor()
cur.execute("SELECT * FROM sc")
res = cur.fetchall()
for line in res:
print(line)
cur.close()
def show_syscontroler_db():
# 打印管理员信息表
print("--------------------管理员信息表--------------------")
hel = opendb()
cur = hel[1].cursor()
cur.execute("SELECT * FROM syscontroler")
res = cur.fetchall()
for line in res:
print(line)
cur.close()
def input_info(n):
# 插入新数据
while n == 1:
# 插入学生数据
sno = input("请输入学号:")
sname = input("请输入姓名:")
ssex = input("请输入性别:")
sage = input("请输入年龄:")
pwd = input("请输入简介：")
return sno, sname, ssex, sage, pwd
while n == 2:
# 插入教师数据
tno = input("请输入教师编号:")
tname = input("请输入姓名:")
tposition = input("请输入职称:")
tsalary = input("请输入每月薪资:")
pwd = input("请输入简介：")
return tno, tname, tposition, tsalary, pwd
while n == 3:
# 插入课程数据
cno = input("请输入课程编号:")
cname = input("请输入课程名:")
ccredit = input("请输入学分:")
tno = input("请输入授课教师编号:")
return cno, cname, ccredit, tno
while n == 4:
# 插入成绩数据
sno = input("请输入学生学号:")
cno = input("请输入课程编号:")
grade = input("请输入成绩:")
return sno, cno, grade
while n == 5:
# 插入管理员信息
kno = input("请输入学生学号:")
kname = input("请输入课程编号:")
klevel = input("请输入成绩:")
return kno, kname, klevel
#
print("数据成功插入！")
def add_to_db(n):
# 向表中添加新的数据
print("--------------------欢迎使用添加数据功能--------------------")
while n == 1:
# 添加学生数据
print("--------------------请输入需要添加的数据--------------------")
in_data = input_info(1)
hel = opendb()
hel[1].execute("insert into student(sname,ssex,sno, sage, pwd) values(?,?,?,?,?)",
(in_data[0], in_data[1], in_data[2], in_data[3]), in_data[4])
hel[1].commit()
print("--------------------数据添加成功！--------------------")
show_student_db()
hel[1].close()
while n == 2:
# 添加教师数据
print("--------------------请输入需要添加的数据--------------------")
in_data = input_info(2)
hel = opendb()
hel[1].execute("insert into teacher(tno,tname, tposition, tsalary, pwd) values (?,?,?,?,?)",
(in_data[0], in_data[1], in_data[2], in_data[3]), in_data[4])
hel[1].commit()
print("--------------------数据添加成功！--------------------")
show_teacher_db()
hel[1].close()
while n == 3:
# 添加课程数据
print("--------------------请输入需要添加的数据--------------------")
in_data = input_info(3)
hel = opendb()
hel[1].execute("insert into course values (?,?,?,?)",
(in_data[0], in_data[1], in_data[2], in_data[3]))
hel[1].commit()
print("--------------------数据添加成功！--------------------")
show_course_db()
hel[1].close()
while n == 4:
# 添加成绩数据
print("--------------------请输入需要添加的数据--------------------")
in_data = input_info(4)
hel = opendb()
hel[1].execute("insert into sc values(?,?,?,?)",
(in_data[0], in_data[1], in_data[2], in_data[3]))
hel[1].commit()
print("--------------------数据添加成功！--------------------")
show_sc_db()
hel[1].close()
while n == 5:
# 添加管理员信息
print("--------------------请输入需要添加的数据--------------------")
in_data = input_info(5)
hel = opendb()
hel[1].execute("insert into syscontroler(kno,kname,klevel) values(?,?,?)",
(in_data[0], in_data[1], in_data[2]))
hel[1].commit()
print("--------------------数据添加成功！--------------------")
show_syscontroler_db()
hel[1].close()
def del_db(n):
# 删除表中的某项数据
print("--------------------欢迎使用删除数据功能--------------------")
while n == 1:
print("--------------------删除学生信息--------------------")
del_choice = input("请输入想要删除的学号")
hel = opendb()
# 返回游标conn
hel[1].excute("delete from student where sno =" + del_choice)
hel[1].commint()
print("--------------------数据删除成功！--------------------")
show_student_db()
hel[1].close
while n == 2:
print("--------------------删除教师信息--------------------")
del_choice = input("请输入想要删除的教职工号")
hel = opendb()
# 返回游标conn
hel[1].excute("delete from teacher where tno =" + del_choice)
hel[1].commint()
print("--------------------数据删除成功！--------------------")
show_teacher_db()
hel[1].close
while n == 3:
print("--------------------删除课程信息--------------------")
del_choice = input("请输入想要删除的课程号")
hel = opendb()
# 返回游标conn
hel[1].excute("delete from course where cno =" + del_choice)
hel[1].commint()
print("--------------------数据删除成功！--------------------")
show_course_db()
hel[1].close
while n == 4:
print("--------------------删除成绩信息--------------------")
del_choice = input("请输入想要删除成绩的学生的学号")
hel = opendb()
# 返回游标conn
hel[1].excute("delete from sc where sno =" + del_choice)
hel[1].commint()
print("--------------------数据删除成功！--------------------")
show_sc_db()
hel[1].close
while n == 5:
print("--------------------删除管理员信息--------------------")
del_choice = input("请输入想要删除的账号")
hel = opendb()
# 返回游标conn
hel[1].excute("delete from syscontroler where kno =" + del_choice)
hel[1].commint()
print("--------------------数据删除成功！--------------------")
show_syscontroler_db()
hel[1].close
def alter_db(n):
# 修改表中已存在的数据
print("--------------------欢迎使用修改数据功能--------------------")
while n == 1:
print("--------------------学生数据修改--------------------")
alter_choice = input("请输入想要修改学生的学号")
hel = opendb()
person = input_info(1)
hel[1].execute("update student set sno=?,sname=?,ssex=?,sage=?,pwd=? where sno = " + alter_choice,
(person[0], person[1], person[2], person[3]))
hel[1].commint()
show_student_db()
hel[1].close()
while n == 2:
print("--------------------教师数据修改--------------------")
alter_choice = input("请输入想要修改的教师的职工号")
hel = opendb()
person = input_info(2)
hel[1].execute(
"update teacher set tno=?,tname=?,tposition=?,tsalary=?,pwd=? where tno = " + alter_choice,
(person[0], person[1], person[2], person[3]))
hel[1].commint()
show_student_db()
hel[1].close()
while n == 3:
print("--------------------课程数据修改--------------------")
alter_choice = input("请输入想要修改课程的课程号")
hel = opendb()
person = input_info(3)
hel[1].execute("update course set cno=?,cname=?,ccredit=?,tno=? where cno = " + alter_choice,
(person[0], person[1], person[2], person[3]))
hel[1].commint()
show_student_db()
hel[1].close()
while n == 4:
print("--------------------成绩数据修改--------------------")
alter_choice = input("请输入想要修改成绩的学生学号")
hel = opendb()
person = input_info(4)
hel[1].execute("update sc set sno=?,cno=?,grade=? where sno = " + alter_choice,
(person[0], person[1], person[2], person[3]))
hel[1].commint()
show_student_db()
hel[1].close()
while n == 5:
print("--------------------成绩数据修改--------------------")
alter_choice = input("请输入想要修改成绩的学生学号")
hel = opendb()
person = input_info(5)
hel[1].execute("update syscontroler set kno=?,kname=?,klevel=? where kno = " + alter_choice,
(person[0], person[1], person[2], person[3]))
hel[1].commint()
show_student_db()
hel[1].close()
def search_db(n):
# 查询单行数据
print("--------------------欢迎使用查询数据功能--------------------")
while n == 1:
print("--------------------学生数据查询--------------------")
search_choice = input("请输入要查询学生的学号")
hel = opendb()
cur = hel[1].cursor()
cur.execute("select * from student where sno=" + search_choice)
hel[1].commit()
print("--------------------你所查找的数据如下--------------------")
res = cur.fetchall()
for line in res:
print(line)
cur.close()
hel[1].close()
while n == 2:
print("--------------------教师数据查询--------------------")
search_choice = input("请输入要查询教师的职工号")
hel = opendb()
cur = hel[1].cursor()
cur.execute("select * from teacher where tno=" + search_choice)
hel[1].commit()
print("--------------------你所查找的数据如下--------------------")
res = cur.fetchall()
for line in res:
print(line)
cur.close()
hel[1].close()
while n == 3:
print("--------------------课程数据查询--------------------")
search_choice = input("请输入要查询课程的课程号")
hel = opendb()
cur = hel[1].cursor()
cur.execute("select * from course where cno=" + search_choice)
hel[1].commit()
print("--------------------你所查找的数据如下--------------------")
res = cur.fetchall()
for line in res:
print(line)
cur.close()
hel[1].close()
while n == 4:
print("--------------------成绩数据查询--------------------")
search_choice = input("请输入要查询学生成绩的学号")
hel = opendb()
cur = hel[1].cursor()
cur.execute("select * from sc where sno=" + search_choice)
hel[1].commit()
print("--------------------你所查找的数据如下--------------------")
res = cur.fetchall()
for line in res:
print(line)
cur.close()
hel[1].close()
while n == 5:
print("--------------------管理员数据查询--------------------")
search_choice = input("请输入要查询管理员帐号")
hel = opendb()
cur = hel[1].cursor()
cur.execute("select * from syscontroler where kno=" + search_choice)
hel[1].commit()
print("--------------------你所查找的数据如下--------------------")
res = cur.fetchall()
for line in res:
print(line)
cur.close()
hel[1].close()
def del_table(n):
# 删表
hel = opendb()
cur = hel[1].cursor()
while n == 1:
# 删除学生表
cur.execute("drop table student")
show_student_db()
while n == 2:
# 删除教师表
cur.execute("drop table teacher")
show_teacher_db()
while n == 3:
# 删除课程表
cur.execute("drop table course")
show_course_db()
while n == 4:
# 删除成绩表
cur.execute("drop table sc")
show_sc_db()
while n == 5:
# 删除管理员表
cur.execute("drop table syscontroler")
show_syscontroler_db()
if __name__ == "__main__":
flag1 = 1
# 主菜单循环条件
while flag1 == 1:
# 初级菜单
print("--------------------欢迎使用信息管理系统--------------------")
choice_show1 = """
请选择操作：
（添加）
---A
（删除）
---B
（修改）
---C
（查询）
---D
（查看现有数据）---E
（删除整个表）
---F
"""
choice1 = input(choice_show1)
if choice1 == "A":
flag2 = 1
# 次级菜单循环条件
while flag2 == 1:
# 次级菜单
choice_show2 = """
请选择您的操作对象：
学生
---A
教师
---B
课程
---C
成绩
---D
管理员信息
---E
"""
choice2 = input(choice_show2)
if choice2 == "A":
add_to_db(1)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "B":
add_to_db(2)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "C":
add_to_db(3)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "D":
add_to_db(4)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "E":
add_to_db(5)
flag2 = int(input("是否继续（0 or 1）？"))
else:
print("输入错误！")
flag1 = int(input("是否继续（0 or 1）？"))
elif choice1 == 'B':
flag2 = 1
while flag2 == 1:
choice_show2 = """
请选择您的操作对象：
学生
---A
教师
---B
课程
---C
成绩
---D
管理员信息
---E
"""
choice2 = input(choice_show2)
if choice2 == "A":
del_db(1)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "B":
del_db(2)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "C":
del_db(3)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "D":
del_db(4)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "E":
del_db(5)
flag2 = int(input("是否继续（0 or 1）？"))
else:
print("输入错误！")
flag1 = int(input("是否继续（0 or 1）？"))
elif choice1 == 'C':
flag2 = 1
while flag2 == 1:
choice_show2 = """
请选择您的操作对象：
学生
---A
教师
---B
课程
---C
成绩
---D
管理员信息
---E
"""
choice2 = input(choice_show2)
if choice2 == "A":
alter_db(1)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "B":
alter_db(2)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "C":
alter_db(3)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "D":
alter_db(4)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "E":
alter_db(5)
flag2 = int(input("是否继续（0 or 1）？"))
else:
print("输入错误！")
flag1 = int(input("是否继续（0 or 1）？"))
elif choice1 == 'D':
flag2 = 1
while flag2 == 1:
choice_show2 = """
请选择您的操作对象：
学生
---A
教师
---B
课程
---C
成绩
---D
管理员信息
---E
"""
choice2 = input(choice_show2)
if choice2 == "A":
search_db(1)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "B":
search_db(2)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "C":
search_db(3)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "D":
search_db(4)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "E":
search_db(5)
flag2 = int(input("是否继续（0 or 1）？"))
else:
print("输入错误！")
flag1 = int(input("是否继续（0 or 1）？"))
elif choice1 == 'E':
flag2 = 1
while flag2 == 1:
choice_show2 = """
请选择您的操作对象：
学生
---A
教师
---B
课程
---C
成绩
---D
管理员信息
---E
"""
choice2 = input(choice_show2)
if choice2 == "A":
show_student_db()
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "B":
show_teacher_db()
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "C":
show_course_db()
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "D":
show_sc_db()
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "E":
show_syscontroler_db()
flag2 = int(input("是否继续（0 or 1）？"))
else:
print("输入错误！")
flag1 = int(input("是否继续（0 or 1）？"))
elif choice1 == 'F':
flag2 = 1
while flag2 == 1:
choice_show2 = """
请选择您的操作对象：
学生
---A
教师
---B
课程
---C
成绩
---D
管理员信息
---E
"""
choice2 = input(choice_show2)
if choice2 == "A":
del_table(1)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "B":
del_table(2)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "C":
del_table(3)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "D":
del_table(4)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "E":
del_table(5)
flag2 = int(input("是否继续（0 or 1）？"))
else:
print("输入错误！")
flag1 = int(input("是否继续（0 or 1）？"))
else:
print("输入错误！请输入：A|B|C|D|E|F")

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
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
import sqlite3
def opendb():
# 打开数据库
conn = sqlite3.connect('C://D-drive-103625//Ubantu//datebase_e.db')
cur = conn.execute(" ")
return cur, conn
def show_student_db():
# 打印学生信息表
print("--------------------学生信息表--------------------")
hel = opendb()
cur = hel[1].cursor()
cur.execute("SELECT * FROM student")
res = cur.fetchall()
for line in res:
print(line)
cur.close()
def show_course_db():
# 打印课程信息表
print("--------------------课程信息表--------------------")
hel = opendb()
cur = hel[1].cursor()
cur.execute("SELECT * FROM course")
res = cur.fetchall()
for line in res:
print(line)
cur.close()
def show_teacher_db():
# 打印教师信息表
print("--------------------教师信息表--------------------")
hel = opendb()
cur = hel[1].cursor()
cur.execute("SELECT * FROM teacher")
res = cur.fetchall()
for line in res:
print(line)
cur.close()
def show_sc_db():
# 打印成绩信息表
print("--------------------成绩信息表--------------------")
hel = opendb()
cur = hel[1].cursor()
cur.execute("SELECT * FROM sc")
res = cur.fetchall()
for line in res:
print(line)
cur.close()
def show_syscontroler_db():
# 打印管理员信息表
print("--------------------管理员信息表--------------------")
hel = opendb()
cur = hel[1].cursor()
cur.execute("SELECT * FROM syscontroler")
res = cur.fetchall()
for line in res:
print(line)
cur.close()
def input_info(n):
# 插入新数据
while n == 1:
# 插入学生数据
sno = input("请输入学号:")
sname = input("请输入姓名:")
ssex = input("请输入性别:")
sage = input("请输入年龄:")
pwd = input("请输入简介：")
return sno, sname, ssex, sage, pwd
while n == 2:
# 插入教师数据
tno = input("请输入教师编号:")
tname = input("请输入姓名:")
tposition = input("请输入职称:")
tsalary = input("请输入每月薪资:")
pwd = input("请输入简介：")
return tno, tname, tposition, tsalary, pwd
while n == 3:
# 插入课程数据
cno = input("请输入课程编号:")
cname = input("请输入课程名:")
ccredit = input("请输入学分:")
tno = input("请输入授课教师编号:")
return cno, cname, ccredit, tno
while n == 4:
# 插入成绩数据
sno = input("请输入学生学号:")
cno = input("请输入课程编号:")
grade = input("请输入成绩:")
return sno, cno, grade
while n == 5:
# 插入管理员信息
kno = input("请输入学生学号:")
kname = input("请输入课程编号:")
klevel = input("请输入成绩:")
return kno, kname, klevel
#
print("数据成功插入！")
def add_to_db(n):
# 向表中添加新的数据
print("--------------------欢迎使用添加数据功能--------------------")
while n == 1:
# 添加学生数据
print("--------------------请输入需要添加的数据--------------------")
in_data = input_info(1)
hel = opendb()
hel[1].execute("insert into student(sname,ssex,sno, sage, pwd) values(?,?,?,?,?)",
(in_data[0], in_data[1], in_data[2], in_data[3]), in_data[4])
hel[1].commit()
print("--------------------数据添加成功！--------------------")
show_student_db()
hel[1].close()
while n == 2:
# 添加教师数据
print("--------------------请输入需要添加的数据--------------------")
in_data = input_info(2)
hel = opendb()
hel[1].execute("insert into teacher(tno,tname, tposition, tsalary, pwd) values (?,?,?,?,?)",
(in_data[0], in_data[1], in_data[2], in_data[3]), in_data[4])
hel[1].commit()
print("--------------------数据添加成功！--------------------")
show_teacher_db()
hel[1].close()
while n == 3:
# 添加课程数据
print("--------------------请输入需要添加的数据--------------------")
in_data = input_info(3)
hel = opendb()
hel[1].execute("insert into course values (?,?,?,?)",
(in_data[0], in_data[1], in_data[2], in_data[3]))
hel[1].commit()
print("--------------------数据添加成功！--------------------")
show_course_db()
hel[1].close()
while n == 4:
# 添加成绩数据
print("--------------------请输入需要添加的数据--------------------")
in_data = input_info(4)
hel = opendb()
hel[1].execute("insert into sc values(?,?,?,?)",
(in_data[0], in_data[1], in_data[2], in_data[3]))
hel[1].commit()
print("--------------------数据添加成功！--------------------")
show_sc_db()
hel[1].close()
while n == 5:
# 添加管理员信息
print("--------------------请输入需要添加的数据--------------------")
in_data = input_info(5)
hel = opendb()
hel[1].execute("insert into syscontroler(kno,kname,klevel) values(?,?,?)",
(in_data[0], in_data[1], in_data[2]))
hel[1].commit()
print("--------------------数据添加成功！--------------------")
show_syscontroler_db()
hel[1].close()
def del_db(n):
# 删除表中的某项数据
print("--------------------欢迎使用删除数据功能--------------------")
while n == 1:
print("--------------------删除学生信息--------------------")
del_choice = input("请输入想要删除的学号")
hel = opendb()
# 返回游标conn
hel[1].excute("delete from student where sno =" + del_choice)
hel[1].commint()
print("--------------------数据删除成功！--------------------")
show_student_db()
hel[1].close
while n == 2:
print("--------------------删除教师信息--------------------")
del_choice = input("请输入想要删除的教职工号")
hel = opendb()
# 返回游标conn
hel[1].excute("delete from teacher where tno =" + del_choice)
hel[1].commint()
print("--------------------数据删除成功！--------------------")
show_teacher_db()
hel[1].close
while n == 3:
print("--------------------删除课程信息--------------------")
del_choice = input("请输入想要删除的课程号")
hel = opendb()
# 返回游标conn
hel[1].excute("delete from course where cno =" + del_choice)
hel[1].commint()
print("--------------------数据删除成功！--------------------")
show_course_db()
hel[1].close
while n == 4:
print("--------------------删除成绩信息--------------------")
del_choice = input("请输入想要删除成绩的学生的学号")
hel = opendb()
# 返回游标conn
hel[1].excute("delete from sc where sno =" + del_choice)
hel[1].commint()
print("--------------------数据删除成功！--------------------")
show_sc_db()
hel[1].close
while n == 5:
print("--------------------删除管理员信息--------------------")
del_choice = input("请输入想要删除的账号")
hel = opendb()
# 返回游标conn
hel[1].excute("delete from syscontroler where kno =" + del_choice)
hel[1].commint()
print("--------------------数据删除成功！--------------------")
show_syscontroler_db()
hel[1].close
def alter_db(n):
# 修改表中已存在的数据
print("--------------------欢迎使用修改数据功能--------------------")
while n == 1:
print("--------------------学生数据修改--------------------")
alter_choice = input("请输入想要修改学生的学号")
hel = opendb()
person = input_info(1)
hel[1].execute("update student set sno=?,sname=?,ssex=?,sage=?,pwd=? where sno = " + alter_choice,
(person[0], person[1], person[2], person[3]))
hel[1].commint()
show_student_db()
hel[1].close()
while n == 2:
print("--------------------教师数据修改--------------------")
alter_choice = input("请输入想要修改的教师的职工号")
hel = opendb()
person = input_info(2)
hel[1].execute(
"update teacher set tno=?,tname=?,tposition=?,tsalary=?,pwd=? where tno = " + alter_choice,
(person[0], person[1], person[2], person[3]))
hel[1].commint()
show_student_db()
hel[1].close()
while n == 3:
print("--------------------课程数据修改--------------------")
alter_choice = input("请输入想要修改课程的课程号")
hel = opendb()
person = input_info(3)
hel[1].execute("update course set cno=?,cname=?,ccredit=?,tno=? where cno = " + alter_choice,
(person[0], person[1], person[2], person[3]))
hel[1].commint()
show_student_db()
hel[1].close()
while n == 4:
print("--------------------成绩数据修改--------------------")
alter_choice = input("请输入想要修改成绩的学生学号")
hel = opendb()
person = input_info(4)
hel[1].execute("update sc set sno=?,cno=?,grade=? where sno = " + alter_choice,
(person[0], person[1], person[2], person[3]))
hel[1].commint()
show_student_db()
hel[1].close()
while n == 5:
print("--------------------成绩数据修改--------------------")
alter_choice = input("请输入想要修改成绩的学生学号")
hel = opendb()
person = input_info(5)
hel[1].execute("update syscontroler set kno=?,kname=?,klevel=? where kno = " + alter_choice,
(person[0], person[1], person[2], person[3]))
hel[1].commint()
show_student_db()
hel[1].close()
def search_db(n):
# 查询单行数据
print("--------------------欢迎使用查询数据功能--------------------")
while n == 1:
print("--------------------学生数据查询--------------------")
search_choice = input("请输入要查询学生的学号")
hel = opendb()
cur = hel[1].cursor()
cur.execute("select * from student where sno=" + search_choice)
hel[1].commit()
print("--------------------你所查找的数据如下--------------------")
res = cur.fetchall()
for line in res:
print(line)
cur.close()
hel[1].close()
while n == 2:
print("--------------------教师数据查询--------------------")
search_choice = input("请输入要查询教师的职工号")
hel = opendb()
cur = hel[1].cursor()
cur.execute("select * from teacher where tno=" + search_choice)
hel[1].commit()
print("--------------------你所查找的数据如下--------------------")
res = cur.fetchall()
for line in res:
print(line)
cur.close()
hel[1].close()
while n == 3:
print("--------------------课程数据查询--------------------")
search_choice = input("请输入要查询课程的课程号")
hel = opendb()
cur = hel[1].cursor()
cur.execute("select * from course where cno=" + search_choice)
hel[1].commit()
print("--------------------你所查找的数据如下--------------------")
res = cur.fetchall()
for line in res:
print(line)
cur.close()
hel[1].close()
while n == 4:
print("--------------------成绩数据查询--------------------")
search_choice = input("请输入要查询学生成绩的学号")
hel = opendb()
cur = hel[1].cursor()
cur.execute("select * from sc where sno=" + search_choice)
hel[1].commit()
print("--------------------你所查找的数据如下--------------------")
res = cur.fetchall()
for line in res:
print(line)
cur.close()
hel[1].close()
while n == 5:
print("--------------------管理员数据查询--------------------")
search_choice = input("请输入要查询管理员帐号")
hel = opendb()
cur = hel[1].cursor()
cur.execute("select * from syscontroler where kno=" + search_choice)
hel[1].commit()
print("--------------------你所查找的数据如下--------------------")
res = cur.fetchall()
for line in res:
print(line)
cur.close()
hel[1].close()
def del_table(n):
# 删表
hel = opendb()
cur = hel[1].cursor()
while n == 1:
# 删除学生表
cur.execute("drop table student")
show_student_db()
while n == 2:
# 删除教师表
cur.execute("drop table teacher")
show_teacher_db()
while n == 3:
# 删除课程表
cur.execute("drop table course")
show_course_db()
while n == 4:
# 删除成绩表
cur.execute("drop table sc")
show_sc_db()
while n == 5:
# 删除管理员表
cur.execute("drop table syscontroler")
show_syscontroler_db()
if __name__ == "__main__":
flag1 = 1
# 主菜单循环条件
while flag1 == 1:
# 初级菜单
print("--------------------欢迎使用信息管理系统--------------------")
choice_show1 = """
请选择操作：
（添加）
---A
（删除）
---B
（修改）
---C
（查询）
---D
（查看现有数据）---E
（删除整个表）
---F
"""
choice1 = input(choice_show1)
if choice1 == "A":
flag2 = 1
# 次级菜单循环条件
while flag2 == 1:
# 次级菜单
choice_show2 = """
请选择您的操作对象：
学生
---A
教师
---B
课程
---C
成绩
---D
管理员信息
---E
"""
choice2 = input(choice_show2)
if choice2 == "A":
add_to_db(1)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "B":
add_to_db(2)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "C":
add_to_db(3)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "D":
add_to_db(4)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "E":
add_to_db(5)
flag2 = int(input("是否继续（0 or 1）？"))
else:
print("输入错误！")
flag1 = int(input("是否继续（0 or 1）？"))
elif choice1 == 'B':
flag2 = 1
while flag2 == 1:
choice_show2 = """
请选择您的操作对象：
学生
---A
教师
---B
课程
---C
成绩
---D
管理员信息
---E
"""
choice2 = input(choice_show2)
if choice2 == "A":
del_db(1)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "B":
del_db(2)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "C":
del_db(3)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "D":
del_db(4)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "E":
del_db(5)
flag2 = int(input("是否继续（0 or 1）？"))
else:
print("输入错误！")
flag1 = int(input("是否继续（0 or 1）？"))
elif choice1 == 'C':
flag2 = 1
while flag2 == 1:
choice_show2 = """
请选择您的操作对象：
学生
---A
教师
---B
课程
---C
成绩
---D
管理员信息
---E
"""
choice2 = input(choice_show2)
if choice2 == "A":
alter_db(1)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "B":
alter_db(2)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "C":
alter_db(3)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "D":
alter_db(4)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "E":
alter_db(5)
flag2 = int(input("是否继续（0 or 1）？"))
else:
print("输入错误！")
flag1 = int(input("是否继续（0 or 1）？"))
elif choice1 == 'D':
flag2 = 1
while flag2 == 1:
choice_show2 = """
请选择您的操作对象：
学生
---A
教师
---B
课程
---C
成绩
---D
管理员信息
---E
"""
choice2 = input(choice_show2)
if choice2 == "A":
search_db(1)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "B":
search_db(2)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "C":
search_db(3)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "D":
search_db(4)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "E":
search_db(5)
flag2 = int(input("是否继续（0 or 1）？"))
else:
print("输入错误！")
flag1 = int(input("是否继续（0 or 1）？"))
elif choice1 == 'E':
flag2 = 1
while flag2 == 1:
choice_show2 = """
请选择您的操作对象：
学生
---A
教师
---B
课程
---C
成绩
---D
管理员信息
---E
"""
choice2 = input(choice_show2)
if choice2 == "A":
show_student_db()
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "B":
show_teacher_db()
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "C":
show_course_db()
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "D":
show_sc_db()
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "E":
show_syscontroler_db()
flag2 = int(input("是否继续（0 or 1）？"))
else:
print("输入错误！")
flag1 = int(input("是否继续（0 or 1）？"))
elif choice1 == 'F':
flag2 = 1
while flag2 == 1:
choice_show2 = """
请选择您的操作对象：
学生
---A
教师
---B
课程
---C
成绩
---D
管理员信息
---E
"""
choice2 = input(choice_show2)
if choice2 == "A":
del_table(1)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "B":
del_table(2)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "C":
del_table(3)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "D":
del_table(4)
flag2 = int(input("是否继续（0 or 1）？"))
elif choice2 == "E":
del_table(5)
flag2 = int(input("是否继续（0 or 1）？"))
else:
print("输入错误！")
flag1 = int(input("是否继续（0 or 1）？"))
else:
print("输入错误！请输入：A|B|C|D|E|F")