文件结构
main.py
import pyodbc
import 学生信息管理
import 系统用户管理
import 课程信息管理
import 选课信息管理
def showMenu():
print("1.学生信息管理")
#print("2.系统用户管理")
print("2.课程信息管理")
print("3.选课信息管理")
print("4.自定义SQL语句")
print("0.退出系统")
select = eval(input("操作:"))
return select
def customStudent():
conn = pyodbc.connect(STR) #
cur = conn.cursor()
SQL = input("请输入SQL语句:")
cur.execute("" + SQL + "")
data = cur.fetchall()
print(data)
conn.commit()
cur.close()
conn.close()
def main():
while True:
UID = input("请输入用户名:")
if UID == "0":
break
PWD = input("请输入密码:")
global STR
STR = 'DRIVER={SQL Server};SERVER=localhost;DATABASE=tushu;UID=' + UID + ';PWD=' + PWD + ''
while True:
try:
conn = pyodbc.connect(STR) #
conn.commit()
conn.close()
# 显示菜单
select = showMenu()
if select == 1:
学生信息管理.main(STR)
# elif select == 2:
# 系统用户管理.main(STR)
elif select == 2:
课程信息管理.main(STR)
elif select == 3:
选课信息管理.main(STR)
elif select == 4:
try:
customStudent()
except Exception as e:
print(e)
elif select == 0:
print("结束进程,即将退出")
break
else:
print("输入有误!请重新操作!")
continue
except Exception as e:
print("输入账户密码有误")
break
if __name__ == '__main__':
main()
学生信息管理.py
# coding:utf-8
import pyodbc
# coon = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=tushu;UID=sa;PWD=123456')
# cursor = coon.cursor()
#
# if cursor:
# print(1)
# cursor.execute("SELECT DISTINCT 读者号 FROM 借阅")
#
# rows =cursor.fetchall()
# print(rows)
# coon.commit()
def showMenu():
print("1.增加学生信息")
print("2.删除学生信息")
print("3.修改学生信息")
print("4.显示学生信息")
print("5.自定义SQL语句")
print("0.退出系统")
select = eval(input("操作:"))
return select
# 添加学生信息
def addStudent():
print("-----增加学生信息-----")
sno = input("学号:")
name = input("姓名:")
sex = input("性别:")
age = eval(input("年龄:"))
address = input("家庭住址:")
admission = input("入学时间:")
speciality = input("专业:")
banhao = input("班号:")
conn = pyodbc.connect(STR) #
cur = conn.cursor()
cur.execute("insert into Student (id,name,sex,age,address,admission,speciality,banhao)values (?,?,?,?,?,?,?,?)",
(sno, name, sex, age, address, admission, speciality, banhao))
conn.commit()
cur.close()
conn.close()
print("添加成功!")
showStudent()
# 展示学生信息
def showStudent():
# 连接数据库,进行查询操作
conn = pyodbc.connect(STR) #
cur = conn.cursor()
cur.execute("select * from Student")
data = cur.fetchall()
if len(data) > 0:
print("-----------学生信息------------")
print("序号\t\t姓名\t\t\t\t性别\t\t年龄\t\t家庭地址\t\t入学时间\t\t\t\t专业\t\t班号")
for i in range(len(data)):
print(data[i][0], '\t', data[i][1], '\t', data[i][2], '\t', data[i][3], '\t', data[i][4], '\t', data[i][5],
'\t', data[i][6], '\t', data[i][7])
print("------------------------------")
else:
print("----------信息表为空-----------")
cur.close()
conn.close()
# 删除学生信息
def delStudent():
print("---正在进行删除操作---")
print("-----当前学生信息------")
showStudent()
select = input("请输入要删除的学生序号:")
# 连接数据库,进行删除操作
conn = pyodbc.connect(STR) #
cur = conn.cursor()
cur.execute("delete from Student where id = ?", (str(select)))
conn.commit()
cur.close()
conn.close()
print("删除成功!")
showStudent()
def customStudent():
conn = pyodbc.connect(STR) #
cur = conn.cursor()
SQL = input("请输入SQL语句:")
cur.execute("" + SQL + "")
data = cur.fetchall()
print(data)
conn.commit()
cur.close()
conn.close()
showStudent()
# 修改学生信息
def reviseStudent():
print("-----正在进行修改操作-----")
showStudent()
num = input("请输入要修改的学生序号:")
print("1-修改姓名\n2-修改性别\n3-修改年龄\n4-修改家庭住址\n5-修改入学时间\n6-修改专业\n7-修改班号")
revisenum = eval(input("请输入要修改的信息序号:"))
newstr = input("请输入新的信息:")
# 连接数据库进行更新操作
conn = pyodbc.connect(STR) #
cur = conn.cursor()
if revisenum == 1:
cur.execute("update Student set name = ? where id = ?", (str(newstr), str(num)))
conn.commit()
cur.close()
conn.close()
print("修改成功!")
showStudent()
elif revisenum == 2:
cur.execute("update Student set sex = ? where id = ?", (str(newstr), str(num)))
conn.commit()
cur.close()
conn.close()
print("修改成功!")
showStudent()
elif revisenum == 3:
cur.execute("update Student set age = ? where id = ?", (eval(newstr), str(num)))
conn.commit()
cur.close()
conn.close()
print("修改成功!")
showStudent()
elif revisenum == 4:
cur.execute("update Student set address = ? where id = ?", (str(newstr), str(num)))
conn.commit()
cur.close()
conn.close()
print("修改成功!")
showStudent()
elif revisenum == 5:
cur.execute("update Student set admission = ? where id = ?", (str(newstr), str(num)))
conn.commit()
cur.close()
conn.close()
print("修改成功!")
showStudent()
elif revisenum == 6:
cur.execute("update Student set speciality = ? where id = ?", (str(newstr), str(num)))
conn.commit()
cur.close()
conn.close()
print("修改成功!")
showStudent()
elif revisenum == 7:
cur.execute("update Student set banhao = ? where id = ?", (str(newstr), str(num)))
conn.commit()
cur.close()
conn.close()
print("修改成功!")
showStudent()
else:
# 如果revisenum输入有误,就修改失败
print("修改失败!请输入正确的修改信息!")
# 主要运行函数
def main(str):
global STR
STR = str
print(STR)
while True:
# 连接数据库,如果数据库不存在,默认在当前路径下创建
conn = pyodbc.connect(STR)
# 获取游标
cur = conn.cursor()
# 创建表
try:
cur.execute("""
create table Student(
id char(4) primary key,
name char(10),
sex char(2),
age int,
address char(10),
admission date,
speciality char(10),
banhao char(4)
)
""")
# 提交事物
conn.commit()
# 关闭游标
cur.close()
# 关闭连接
conn.close()
except:
print("", end='')
# 显示菜单
select = showMenu()
try:
if select == 1:
addStudent()
elif select == 2:
delStudent()
elif select == 3:
reviseStudent()
elif select == 4:
showStudent()
elif select == 5:
customStudent()
elif select == 0:
# 退出系统
break
else:
print("输入有误!请重新操作!")
continue
except Exception as e:
print("输入有误!请重新输入!")
print(e)
if __name__ == '__main__':
UID = input("请输入用户名")
PWD = input("请输入密码")
STR = 'DRIVER={SQL Server};SERVER=localhost;DATABASE=tushu;UID=' + UID + ';PWD=' + PWD + ''
main(STR)
课程信息管理.py
# coding:utf-8
import pyodbc
# coon = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=tushu;UID=sa;PWD=123456')
# cursor = coon.cursor()
#
# if cursor:
# print(1)
# cursor.execute("SELECT DISTINCT 读者号 FROM 借阅")
#
# rows =cursor.fetchall()
# print(rows)
# coon.commit()
def showMenu():
print("1.增加课程信息")
print("2.删除课程信息")
print("3.修改课程信息")
print("4.显示课程信息")
print("5.自定义SQL语句")
print("0.退出系统")
select = eval(input("操作:"))
return select
# 添加学生信息
def addStudent():
print("-----增加课程信息-----")
sno = input("课程号:")
name = input("课程名:")
book = input("教材:")
admission = input("开课时间:")
conn = pyodbc.connect(STR) #
cur = conn.cursor()
cur.execute("insert into Course (id,name,book,admission)values (?,?,?,?)",
(sno, name, book,admission))
conn.commit()
cur.close()
conn.close()
print("添加成功!")
showStudent()
# 展示学生信息
def showStudent():
# 连接数据库,进行查询操作
conn = pyodbc.connect(STR) #
cur = conn.cursor()
cur.execute("select * from Course")
data = cur.fetchall()
if len(data) > 0:
print("-----------课程信息------------")
print("课程号\t\t课程名\t\t教材\t\t开课时间")
for i in range(len(data)):
print(data[i][0], '\t', data[i][1], '\t', data[i][2], '\t', data[i][3])
print("------------------------------")
else:
print("----------信息表为空-----------")
cur.close()
conn.close()
# 删除学生信息
def delStudent():
print("---正在进行删除操作---")
print("-----当前课程信息------")
showStudent()
select = input("请输入要删除的课程序号:")
# 连接数据库,进行删除操作
conn = pyodbc.connect(STR) #
cur = conn.cursor()
cur.execute("delete from Course where id = ?", (str(select)))
conn.commit()
cur.close()
conn.close()
print("删除成功!")
showStudent()
def customStudent():
conn = pyodbc.connect(STR) #
cur = conn.cursor()
SQL = input("请输入SQL语句:")
cur.execute("" + SQL + "")
data = cur.fetchall()
print(data)
conn.commit()
cur.close()
conn.close()
showStudent()
# 修改学生信息
def reviseStudent():
print("-----正在进行修改操作-----")
showStudent()
num = input("请输入要修改的课程序号:")
print("1-修改课程名\n2-修改教材\n3-修改开课时间")
revisenum = eval(input("请输入要修改的信息序号:"))
newstr = input("请输入新的信息:")
# 连接数据库进行更新操作
conn = pyodbc.connect(STR) #
cur = conn.cursor()
if revisenum == 1:
cur.execute("update Course set name = ? where id = ?", (str(newstr), str(num)))
conn.commit()
cur.close()
conn.close()
print("修改成功!")
showStudent()
elif revisenum == 2:
cur.execute("update Course set book = ? where id = ?", (str(newstr), str(num)))
conn.commit()
cur.close()
conn.close()
print("修改成功!")
showStudent()
elif revisenum == 3:
cur.execute("update Course set admission = ? where id = ?", (str(newstr), str(num)))
conn.commit()
cur.close()
conn.close()
print("修改成功!")
showStudent()
else:
# 如果revisenum输入有误,就修改失败
print("修改失败!请输入正确的修改信息!")
# 主要运行函数
def main(str):
global STR
STR = str
while True:
# 连接数据库,如果数据库不存在,默认在当前路径下创建
conn = pyodbc.connect(STR)
# 获取游标
cur = conn.cursor()
# 创建表
try:
cur.execute("""
create table Course(
id char(4) primary key,
name char(10),
book char(10),
admission date,
)
""")
# 提交事物
conn.commit()
# 关闭游标
cur.close()
# 关闭连接
conn.close()
except:
print("", end='')
# 显示菜单
select = showMenu()
try:
if select == 1:
addStudent()
elif select == 2:
delStudent()
elif select == 3:
reviseStudent()
elif select == 4:
showStudent()
elif select == 5:
customStudent()
elif select == 0:
# 退出系统
break
else:
print("输入有误!请重新操作!")
continue
except Exception as e:
print("输入有误!请重新输入!")
print(e)
if __name__ == '__main__':
UID = input("请输入用户名:")
PWD = input("请输入密码:")
STR = 'DRIVER={SQL Server};SERVER=localhost;DATABASE=tushu;UID=' + UID + ';PWD=' + PWD + ''
main(STR)
选课信息管理.py
# coding:utf-8
import pyodbc
# coon = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=tushu;UID=sa;PWD=123456')
# cursor = coon.cursor()
#
# if cursor:
# print(1)
# cursor.execute("SELECT DISTINCT 读者号 FROM 借阅")
#
# rows =cursor.fetchall()
# print(rows)
# coon.commit()
def showMenu():
print("1.增加选课信息")
print("2.删除选课信息")
print("3.修改选课信息")
print("4.显示选课信息")
print("5.自定义SQL语句")
print("0.退出系统")
select = eval(input("操作:"))
return select
# 添加学生信息
def addStudent():
print("-----增加学生信息-----")
sno = input("学号:")
cno = input("课程:")
score = input("成绩:")
try:
score = eval(score)
except:
score="null"
conn = pyodbc.connect(STR) #
cur = conn.cursor()
cur.execute("insert into Score (idStudent,idCourse,score)values (?,?,?)",
(sno, cno, score))
conn.commit()
cur.close()
conn.close()
print("添加成功!")
showStudent()
# 展示学生信息
def showStudent():
# 连接数据库,进行查询操作
conn = pyodbc.connect(STR) #
cur = conn.cursor()
cur.execute("select * from Score")
data = cur.fetchall()
if len(data) > 0:
print("-----------学生信息------------")
print("学号\t\t课程号\t\t成绩")
for i in range(len(data)):
print(data[i][0], '\t', data[i][1], '\t', data[i][2])
print("------------------------------")
else:
print("----------信息表为空-----------")
cur.close()
conn.close()
# 删除学生信息
def delStudent():
print("---正在进行删除操作---")
print("-----当前学生信息------")
showStudent()
select1 = input("请输入要删除的学生序号:")
select2 = input("请输入要删除的课程序号:")
# 连接数据库,进行删除操作
conn = pyodbc.connect(STR) #
cur = conn.cursor()
cur.execute("delete from Score where idStudent = ? and idCourse = ?", (str(select1),str(select2)))
conn.commit()
cur.close()
conn.close()
print("删除成功!")
showStudent()
def customStudent():
conn = pyodbc.connect(STR) #
cur = conn.cursor()
SQL = input("请输入SQL语句:")
cur.execute("" + SQL + "")
data = cur.fetchall()
print(data)
conn.commit()
cur.close()
conn.close()
showStudent()
# 修改学生信息
def reviseStudent():
print("-----正在进行修改操作-----")
showStudent()
num1 = input("请输入要修改的学生序号:")
num2 = input("请输入要修改的课程序号:")
print("1-修改学号\n2-修改课程号\n3-修改成绩")
revisenum = eval(input("请输入要修改的信息序号:"))
newstr = input("请输入新的信息:")
try:
newstr2 = eval(newstr)
except:
print("",end="")
# 连接数据库进行更新操作
conn = pyodbc.connect(STR) #
cur = conn.cursor()
if revisenum == 1:
cur.execute("update Score set idStudent = ? where idStudent = ? and idCourse = ?", (str(newstr), str(num1),str(num2)))
conn.commit()
cur.close()
conn.close()
print("修改成功!")
showStudent()
elif revisenum == 2:
cur.execute("update Score set idCourse = ? where idStudent = ? and idCourse = ?", (str(newstr), str(num1),str(num2)))
conn.commit()
cur.close()
conn.close()
print("修改成功!")
showStudent()
elif revisenum == 3:
cur.execute("update Score set score = ? where idStudent = ? and idCourse = ?", (str(newstr2), str(num1),str(num2)))
conn.commit()
cur.close()
conn.close()
print("修改成功!")
showStudent()
else:
# 如果revisenum输入有误,就修改失败
print("修改失败!请输入正确的修改信息!")
# 主要运行函数
def main(str):
global STR
STR = str
while True:
# 连接数据库,如果数据库不存在,默认在当前路径下创建
conn = pyodbc.connect(STR)
# 获取游标
cur = conn.cursor()
# 创建表
try:
cur.execute("""
create table Score(
idStudent char(4),
idCourse char(4),
score int,
primary key(idStudent,idCourse),
foreign key (idStudent) references Student(id),
foreign key (idCourse) references Course(id)
)
""")
# 提交事物
conn.commit()
# 关闭游标
cur.close()
# 关闭连接
conn.close()
except:
print("", end='')
# 显示菜单
select = showMenu()
try:
if select == 1:
addStudent()
elif select == 2:
delStudent()
elif select == 3:
reviseStudent()
elif select == 4:
showStudent()
elif select == 5:
customStudent()
elif select == 0:
# 退出系统
break
else:
print("输入有误!请重新操作!")
continue
except Exception as e:
print("输入有误!请重新输入!")
print(e)
if __name__ == '__main__':
UID = input("请输入用户名")
PWD = input("请输入密码")
STR = 'DRIVER={SQL Server};SERVER=localhost;DATABASE=tushu;UID=' + UID + ';PWD=' + PWD + ''
main(STR)