python實現學生信息管理系統

#-*- coding:utf-8 -*-
import sqlite3
#打開本地數據庫用于存儲用戶信息
conn = sqlite3.connect('student.db')

#在該數據庫下創建學生信息表
conn.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";

#在該數據庫下創建課程信息表
conn.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";

#在該數據庫下創建選課情況信息表
conn.execute ('''CREATE TABLE XuankeTable(
 ID INTEGER PRIMARY KEY AUTOINCREMENT,
 StuId INT NOT NULL,
 CourseId INT NOT NULL,
 StudentNAME TEXT NULL,
 StudenCourse TEXT NULL);''')
print "Table created successfully";

#以上三個表創建完后，再次運行程序時，需要把三個建表代碼注釋掉，否則會提示:該表已存在。即建表只需建一次。

def insert_stu(): #錄入學生信息
 conn = sqlite3.connect('student.db')
 stu_id = input("請輸入學生學號:")
 cursor = conn.execute("SELECT StuId from StudentTable where StuId = '%s';"%stu_id)
 conn.commit()
 for row in cursor:
 if stu_id == row[0]:
 print "sorry,該學號已存在,請重新輸入"
 break
 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)
 conn.execute(sql1)
 conn.commit()
 print "恭喜你,學生錄入成功!"

def xuanke(): #學生選課
 stu_id = input('請輸入要選課的學生學號:')
 sql2 = "select StuId from StudentTable where StuId = %d;"%(stu_id)
 cursor1 = conn.execute(sql2)
 for row in cursor1:
 if stu_id == row[0]:
 sql3 = "select CourseId,Name,Teacher,Classroom, StartTime,EndTime from CourseTable"
 cursor2 = conn.execute(sql3)
 for row in cursor2:
 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("請輸入要選的課程號:")
 sql = "select StuId from XuankeTable where CourseId = %d;"%(cou_id)
 cursor3= conn.execute(sql)
 for row in cursor3:
 if stu_id == row[0]:
 print "該課程已選,請重新輸入要選課程!"
 else:
 sql3 = "insert into XuankeTable (StuId,CourseId) values (%d,%d)"%(stu_id,cou_id)
 cursor4= conn.execute(sql3)
 conn.commit()
 print "恭喜你，選課成功!"
 break
 break
 break
 else:
 print "sorry,沒有該學生號"

def stu_id_search():#按照學生學號查詢學生信息
 conn = sqlite3.connect('student.db')
 search_stu_id = input("請輸入要查詢的學號:")
 sql4 = "SELECT StuId from StudentTable where StuId= %d;" % (search_stu_id)
 cursor1 = conn.execute(sql4)
 conn.commit()
 for row in cursor1:
 if search_stu_id == row[0]:
 sql10 = "select ID,StuId,NAME, CLASS from StudentTable where StuId = %d;"%(search_stu_id)
 cursor2 = conn.execute(sql10)
 conn.commit()
 for row in cursor2:
 print
 print "您要查詢的學生信息為:"
 print "ID = ", row[0]
 print "StuId = ", row[1]
 print "NAME = ", row[2]
 print "CLASS = ",row[3], "\n"
 break
 else:
 print "sorry,沒有該學生信息！"

def stu_id_cou(): #按照學生學號查詢該學生所選課程
 stu_id = input("請輸入要查詢學生號:")
 sql5 = "select StuId from StudentTable where StuId = %d;"%(stu_id)
 cursor = conn.execute(sql5)
 for row in cursor:
 if stu_id == row[0]:
 sql6 = "select CourseId from XuankeTable where StuId = %d;"%(stu_id)
 cursor = conn.execute(sql6)
 conn.commit()
 for row in cursor:
 print
 print "該學生所選課程號為:"
 print row
 print
 break
 else:
 print "sorry,沒有該學生選課信息!"

def cou_id_search(): #按照課程號查詢課程信息
 cou_id = input("請輸入要查詢的課程號:")
 sql7 = "select CourseId ,Name,Teacher,Classroom,StartTime,EndTime from CourseTable "
 sql7 += "where CourseId = %d;"%(cou_id)
 cursor1 = conn.execute(sql7)
 conn.commit()
 for row in cursor1:
 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"
 break
 else:
 print "sorry,沒有該課程信息!"

def cou_id_stu():#按照課程號查詢選擇該課程的學生列表
 cou_id = input('請輸入課程號:')
 sql8 = "select CourseId from XuankeTable where CourseId =%d;"%(cou_id)
 cursor1 = conn.execute(sql8)
 for row in cursor1:
 if cou_id == row[0]:
 sql9 = "select StuId from XuankeTable where CourseId =%d;"%(cou_id)
 cursor2 = conn.execute(sql9)
 conn.commit()
 for row in cursor2:
 print
 print "選擇該課程的學生為:"
 print row,"\n"
 break
 break
 else:
 print "sorry,沒有該課程信息!"

def menu():
 print '1.進入學生信息系統(學生信息錄入)'
 print '2.進入學生選課系統(學生選課操作)'
 print '3.進入學生選課信息系統(學生信息查詢和選課情況查詢)'
 print '4.退出程序'

def student():
 print '1.錄入學生信息'
 print '2.返回主菜單'
 print '3.退出程序'

def Course():
 print '1.開始選課'
 print '2.返回主菜單'
 print '3.退出程序'

def information():
 print '1.按學號查詢學生信息'
 print '2.按學號查看學生選課課程列表'
 print '3.按課程號查看課程信息'
 print '4.按課程號查看選課學生列表'
 print '5.返回主菜單'
 print '6.退出程序'

while True:
 menu()
 print
 x = raw_input('請輸入您的選擇菜單號:')
 if x == '1':
 #進入學生信息系統
 student()
 stu = raw_input('您已進入學生錄入系統,請再次輸入選擇菜單:')
 if stu == '1':
 insert_stu()
 continue
 if stu == '2':
 menu()
 continue
 if stu == '3':
 print "謝謝使用！"
 exit()
 continue
 else:
 print "輸入的選項不存在，請重新輸入！"
 continue

 if x == '2':
 #進入選課信息系統
 Course()
 cou = raw_input('您已進入學生選課系統,請再次輸入選擇菜單:')
 if cou == '1':
 xuanke()
 continue
 if cou == '2':
 menu()
 continue
 if cou == '3':
 print "謝謝使用！"
 exit()
 continue
 else:
 print "輸入的選項不存在，請重新輸入！"
 continue

 if x == '3':
 #進入學生選課信息表
 information()
 inf = raw_input('您已進入學生選課信息系統,請再次輸入選擇菜單:')
 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':
 menu()
 continue
 if inf == '6':
 print "謝謝使用！"
 exit()
 continue
 else:
 print "輸入的選項不存在，請重新輸入！"
 continue

 if x == '4':
 print "謝謝使用！"
 exit()
 else:
 print "輸入的選項不存在，請重新輸入！"
 continue