def query_specified_student_abstract_info(self, sid):
"""
:param sid:
:return:
"""
try:
with database.cursor() as cursor:
sql = "SELECT * FROM student WHERE sid={}".format(sid)
cursor.execute(sql)
row = cursor.fetchall()
if len(row) == 0:
return None
row = row[0]
stu = Student()
stu.sid = row[0]
stu.sname = row[1]
stu.sex = row[2]
stu.age = row[3]
stu.year = row[4]
stu.gpa = row[5]
return stu
except Exception as e:
traceback.print_exc()
return None
def query_specified_student_enrolls(self, sid) -> list:
"""
查询某个学生的所选的所有课
:param sid:
:return:
"""
try:
enrolls = set()
with database.cursor() as cursor:
sql = "SELECT stu.sid,stu.sname,stu.sex,stu.age,stu.year,stu.gpa,m.dname,e.dname,e.cno,e.sectno,e.grade,s.pname,c.cname,c.dname " \
"FROM student stu,enroll e,section s,course c,major m " \
"WHERE stu.sid=m.sid AND stu.sid=e.sid AND e.dname=s.dname AND e.cno=s.cno AND e.sectno=s.sectno AND s.dname=c.dname AND s.cno=c.cno AND stu.sid=" + str(
sid)
result_set = cursor.execute(sql)
for row in result_set:
c = Course()
c.cname = row[13]
sect = Section()
sect.pname = row[11]
e = Enroll()
e.dname = row[7]
e.cno = row[8]
e.sectno = row[9]
e.grade = row[10]
e.section = sect
sect.course = c
enrolls.add(e)
return list(enrolls)
except Exception as e:
traceback.print_exc()
return []
def delete_specifide_section(self, section): """ 删除某门课的所有信息（级联） :param section: :return: """ try: with database.cursor() as cursor: sql = "DELETE FROM section WHERE dname=? and cno=? and sectno=?" result = cursor.execute(sql, section.dname, section.cno, section.sectno) return result.rowcount except Exception as e: traceback.print_exc() return 0
def delete_specified_student(self, sid):
"""
删除某个学生的全部信息（级联）
:param sid:
:return:
"""
try:
with database.cursor() as cursor:
sql = "DELETE FROM student WHERE sid=?"
result = cursor.execute(sql, sid)
return result.rowcount
except Exception as e:
traceback.print_exc()
return 0
def revise_password(self, username, password) -> int: """ 修改用户密码 :param username: 用户名 :param password: 新密码 :return: 0为失败，1为修改成功 """ try: with database.cursor() as cursor: sql = "UPDATE userinfo SET password=? WHERE username=?" result = cursor.execute(sql, password, username) return result.rowcount except Exception as e: traceback.print_exc() return 0
def update_specified_student_info(self, sid, field, value):
"""
更新某个学生的基本信息
:param sid:
:param field:
:param value:
:return:
"""
try:
with database.cursor() as cursor:
sql = "UPDATE student SET {}=? WHERE sid=?".format(field)
result = cursor.execute(sql, value, sid)
return result.rowcount
except Exception as e:
traceback.print_exc()
return 0
def add_enroll_to_specified_student(self, sid, grade, section) -> int:
"""
添加某个学生的某门选课
:param sid:
:param grade:
:param section:
:return:
"""
try:
with database.cursor() as cursor:
sql = "INSERT INTO enroll (sid,grade,dname,cno,sectno)VALUES(?,?,?,?,?)"
result = cursor.execute(sql, sid, grade, section.dname,
section.cno, section.sectno)
return result.rowcount
except Exception as e:
traceback.print_exc()
return 0
def delete_enroll_of_specified_student(self, sid, grade, enroll) -> int:
"""
删除某个学生的某门选课
:param sid:
:param grade:
:param enroll:
:return:
"""
try:
with database.cursor() as cursor:
sql = "DELETE FROM enroll WHERE sid=? AND grade=? AND dname=? AND cno=? AND sectno=?"
result = cursor.execute(sql, sid, grade, enroll.dname,
enroll.cno, enroll.sectno)
return result.rowcount
except Exception as e:
traceback.print_exc()
return 0
def query_specified_student_major(self, sid) -> list:
"""
查询某个学生的主修专业
:param sid:
:return:
"""
major = list()
try:
with database.cursor() as cursor:
sql = "SELECT m.dname,m.sid FROM major m WHERE m.sid={}".format(
sid)
for row in cursor.execute(sql):
m = Major()
m.sid = row[1]
m.dname = row[0]
major.append(m)
return major
except Exception as e:
traceback.print_exc()
return []
def login(self, username, password) -> int: """ 用户登陆 :param username:用户名 :param password:密码 :return:-1为密码错误，-2为用户名不存在，0为管理员权限，1为教师权限，2为学生权限 """ try: with database.cursor() as cursor: sql = r"SELECT password,auth FROM userinfo WHERE username=?" cursor.execute(sql, username) result = cursor.fetchall() if len(result) == 0 or len(result) != 1: return -2 # 用户名不存在 elif result[0].password != password: return -1 # 密码错误 else: return result[0].auth except Exception as e: traceback.print_exc() return -1
def query_all_student_abstract_info(self):
"""
查询所有学生的基本信息
:return:
"""
result = []
try:
with database.cursor() as cursor:
sql = "SELECT * FROM student"
for row in cursor.execute(sql):
stu = Student()
stu.sid = row[0]
stu.sname = row[1]
stu.sex = row[2]
stu.age = row[3]
stu.year = row[4]
stu.gpa = row[5]
result.append(stu)
return result
except Exception as e:
traceback.print_exc()
return None
def query_all_sections(self) -> list: """ 查询所有开的课程 :return: """ try: sections = list() with database.cursor() as cursor: sql = "SELECT s.sectno,s.dname,s.cno,c.cname,s.pname FROM section s,course c WHERE s.dname=c.dname AND s.cno=c.cno" for row in cursor.execute(sql): c = Course() s = Section() s.sectno = row[0] s.dname = row[1] s.cno = row[2] s.pname = row[4] c.cname = row[3] s.course = c sections.append(s) return sections except Exception as e: traceback.print_exc() return None
def query_ambiguous_student_abstract_info(self, sname):
"""
:param sname:
:return:
"""
result = []
try:
with database.cursor() as cursor:
sql = "SELECT * FROM student WHERE sname LIKE ? "
for row in cursor.execute(sql,
"%{}%".format(sname)).fetchall():
stu = Student()
stu.sid = row[0]
stu.sname = row[1]
stu.sex = row[2]
stu.age = row[3]
stu.year = row[4]
stu.gpa = row[5]
result.append(stu)
return result
except Exception as e:
traceback.print_exc()
return []