def update_lesson(data): con, cursor = dao.getConn() sql = 'UPDATE teacher set lesson = ? ,name = ? where id = ? ' cursor.execute(sql, (data[3], data[2], data[0])) con.commit() cursor.close() con.close()
def find_like(like, value, type): ''' 模糊查询 :param like: :param value: :param type: :return: ''' con, cursor = dao.getConn() value = '%%%%%' + value + '%%%%' if type == 1: if like == 'username': sql = " select id,username,classname from user where username like ? and type = 1" else: sql = " select id,username,classname from user where id like ? and type = 1" cursor.execute(sql, (value,)) else: if like == 'username': sql = " select id,name,classname,lesson from teacher where name like ? " else: sql = " select id,name,classname,lesson from teacher where id like ? " cursor.execute(sql, (value,)) students = cursor.fetchall() cursor.close() con.close() return students
def update_score(date): con, cursor = dao.getConn() sql = "UPDATE score set score = ? where id = ? and lesson = ?" cursor.execute(sql, (date[4], date[1], date[3])) con.commit() cursor.close() con.close()
def fina_all_teacher_info(): con, cursor = dao.getConn() sql = 'select * from teacher ' cursor.execute(sql) data = cursor.fetchall() cursor.close() con.close() return data
def find_all_lesson(): con, cursor = dao.getConn() sql = "select lesson,username,user.id,teacher.classname from teacher,user WHERE user.id = teacher.id" cursor.execute(sql) item = cursor.fetchall() cursor.close() con.close() return item
def delete_teacher(id, lesson): con, cursor = dao.getConn() delete_lesson(id, lesson) sql = ' delete from user where user.id = ? ' cursor.execute(sql, (id,)) con.commit() cursor.close() con.close()
def find_lessons_by_classname(classname): con, cursor = dao.getConn() sql = 'select lesson from teacher where classname = ?' cursor.execute(sql, (classname,)) data = cursor.fetchall() cursor.close() con.close() return data
def find_all_students(): con, cursor = dao.getConn() sql = 'select username,id,classname from user where type = 1' cursor.execute(sql) data = cursor.fetchall() cursor.close() con.close() return data
def find_students_by_classname(classname): con, cursor = dao.getConn() sql = 'select id from user where type =1 and classname = ?' cursor.execute(sql, (classname,)) item = cursor.fetchall() cursor.close() con.close() return item
def get_lesson_by_teacher_id(id): con, cursor = dao.getConn() sql = "select * from teacher where id=?" cursor.execute(sql, (id,)) item = cursor.fetchall() cursor.close() con.close() return item
def get_scores_by_lesson_and_classname(lesson, classname): con, cursor = dao.getConn() sql = "select username,score.id,classname,lesson,score from user,score where score.id = user.id" \ " and classname=? and lesson = ?" cursor.execute(sql, (classname, lesson)) item = cursor.fetchall() cursor.close() con.close() return item
def add_lesson(id, classname, lesson): con, cursor = dao.getConn() data = find_user_by_id(id) username = data[0] sql = "insert into teacher(id,classname,name,lesson) values (?,?,?,?)" cursor.execute(sql, (id, classname, username, lesson)) con.commit() cursor.close() con.close()
def insert_new_lesson(lesson, classname): students = find_students_by_classname(classname) con, cursor = dao.getConn() sql = 'insert into score (id,lesson,score) values (?,?,?)' for i in students: cursor.execute(sql, (i[0], lesson, '0')) con.commit() cursor.close() con.commit() con.close()
def delete_user(id): con, cursor = dao.getConn() sql = "delete from user where id = ?" cursor.execute(sql, (id,)) con.commit() sql = "delete from score where id = ?" cursor.execute(sql, (id,)) con.commit() cursor.close() con.close()
def delete_lesson(id, lesson): con, cursor = dao.getConn() sql = "delete from teacher where id = ? and lesson = ?" cursor.execute(sql, (id, lesson)) con.commit() classname = find_user_by_id(id)[2] students = find_students_by_classname(classname) for student in students: sql = 'delete from score where id = ? and lesson = ?' cursor.execute(sql, (student[0], lesson)) con.commit() cursor.close() con.close()
def get_score_by_id(id): ''' 根据ID查询成绩 :param id: :return: ''' con, cursor = dao.getConn() sql = "select lesson,score from user,score where user.id = score.id and user.id= ?" cursor.execute(sql, (id,)) item = cursor.fetchall() cursor.close() con.close() return item
def update_teacher(data): con, cursor = dao.getConn() update_lesson(data) sql = 'update user set username = ? where id = ? ' cursor.execute(sql, (data[2], data[0])) con.commit() students = find_students_by_classname(data[1]) for i in students: sql = 'update score set lesson =? where id = ? ' cursor.execute(sql, (data[3], i[0])) con.commit() cursor.close() con.close()
def find_user_by_id(id): """ 通过用户ID查询用户信息 :param id: :return: """ con, cursor = dao.getConn() sql = 'select * from user where id = ?' cursor.execute(sql, (id,)) item = cursor.fetchall() cursor.close() con.close() return item[0]
def find_all_user(): con, cursor = dao.getConn() sql = "select username,id,classname,type from user where type!=0" cursor.execute(sql) data = cursor.fetchall() for i in range(len(data)): data[i] = list(data[i]) if data[i][3] == 1: data[i][3] = '学生' else: data[i][3] = '教师' cursor.close() con.close() return data
def register(username, id, classname, password, type, lesson): con, cursor = dao.getConn() sql = "insert into user (username,id,classname,password,type) values (?,?,?,?,?)" cursor.execute(sql, (username, id, classname, password, type)) con.commit() lessons = find_lessons_by_classname(classname) init_Student(lessons, id) if int(type) == 2: sql = "insert into teacher(id,classname,name,lesson) values (?,?,?,?)" insert_new_lesson(lesson, classname) cursor.execute(sql, (id, classname, username, lesson)) con.commit() cursor.close() con.close()
def init_Student(lessons, id): ''' 当你加入一个学生 到新的班级的时候 就需要给对应的科目 赋 初始值 :param lessons: :param id: :return: ''' con, cursor = dao.getConn() for lesson in lessons: sql = 'insert into score(id,lesson,score) values (?,?,?)' cursor.execute(sql, (id, lesson[0], '0')) con.commit() con.commit() cursor.close() con.close()
def login(id, password, typeNumber): """ 登录函数 :param id: 用户id :param password: 用户密码 :param typeNumber: 用户类型 :return: """ con, cursor = dao.getConn() sql = "select * from user where user.id = ? and user.type = ?" cursor.execute(sql, (id, typeNumber)) data = cursor.fetchone() if str(type(data)) == "<class 'tuple'>": if data[3] == password: return 1, data else: return 0, data return 0, data
def update_student(data): update_score(data) con, cursor = dao.getConn() sql = 'update user set username = ? where id = ? ' cursor.execute(sql, (data[0], data[1])) con.commit()