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;
第一次搞没什么经验，用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")

• « 上一篇：Python中url的编码以及解码
• » 下一篇：Flink 常见面试题