def put(self, stuNo): db = get_db() db.autocommit = False cur = get_db().cur args = parser_soc.parse_args() cur.execute("SELECT * FROM JoinStatus WHERE stuNo='%s'" % stuNo) society_now = [x[1] for x in cur.fetchall()] try: if args['societyNo']: # edit JoinStatus # ToDo: 不知道为什么单次出错不发生异常 for society in args['societyNo']: if int(society) not in society_now: cur.execute( "INSERT INTO JoinStatus(stuNo, societyNo, joinYear) VALUES('%s',%d, '%s');" % (stuNo, int(society), datetime.datetime.now().year)) for society in society_now: if int(society) not in args['societyNo']: cur.execute( "DELETE FROM JoinStatus WHERE stuNo=%s AND societyNo=%d;" % (stuNo, int(society))) else: cur.execute("DELETE FROM JoinStatus WHERE stuNo=%s;" % (stuNo)) db.commit() except Error as e: return {'errCode': -1, 'status': str(e)} return {'errCode': 0, 'status': 'OK'}, 200
def test_get_close_db(app): with app.app_context(): cur = get_db() assert cur is get_db() with pytest.raises(ReferenceError) as e: cur.execute('SELECT 1') assert 'weakly-referenced' in str(e.value)
def delete(self, classNo): # 删除 self.checkIfExist(classNo) db = get_db() cur = get_db().cur try: cur.execute("DELETE FROM Class WHERE classNo='%s';" % classNo) db.commit() except Error as e: return {'errCode': -1, 'status': str(e)} return {'errCode': 0, 'status': 'OK'}, 200
def delete(self, stuNo): # 删除 if not self.checkIfExist(stuNo): return {'errCode': -1, 'status': '操作的学生不存在'} db = get_db() cur = get_db().cur try: cur.execute("DELETE FROM JoinStatus WHERE stuNo='%s';" % stuNo) cur.execute("DELETE FROM Student WHERE stuNo='%s';" % stuNo) db.commit() except Error as e: return {'errCode': -1, 'status': str(e)} return {'errCode': 0, 'status': 'OK'}, 200
def post(self): args = parser_association.parse_args() db = get_db() cur = get_db().cur try: cur.execute("INSERT INTO Association(societyName,societyYear,societyLoc) VALUES('%s', '%d', '%s');" % ( args['societyName'], args['societyYear'], args['societyLoc'])) db.commit() except Error as e: return {'errCode': -1, 'status': str(e)} return {'errCode': 0, 'status': 'OK'}, 200
def post(self): db = get_db() args = parser.parse_args() cur = get_db().cur try: cur.execute('SELECT change_classNo(%s, %s)' % (args['old_No'], args['new_No'])) data = cur.fetchone() db.commit() except Error as e: return {'errCode': -1, 'status': str(e)} return {'errCode': 0, 'status': 'OK', 'data': data}, 200
def post(self): args = parser_dormitory.parse_args() db = get_db() cur = get_db().cur try: cur.execute( "INSERT INTO Dormitory(dormitoryNo, dormitoryName) VALUES('%s', '%s');" % (args['dormitoryNo'], args['dormitoryName'])) db.commit() except Error as e: return {'errCode': -1, 'status': str(e)} return {'errCode': 0, 'status': 'OK'}, 200
def delete(self, societyNo): # 删除 if not self.checkIfExist(societyNo): return {'errCode': -1, 'status': '操作的系不存在'} db = get_db() cur = get_db().cur try: cur.execute( "DELETE FROM Association WHERE societyNo='%s';" % societyNo) db.commit() except Error as e: return {'errCode': -1, 'status': str(e)} return {'errCode': 0, 'status': 'OK'}, 200
def delete(self, departNo): # 删除 if not self.checkIfExist(departNo): return {'errCode': -1, 'status': '操作的系不存在'} db = get_db() cur = get_db().cur try: cur.execute("DELETE FROM Department WHERE departNo='%s';" % departNo) db.commit() except Error as e: return {'errCode': -1, 'status': str(e)} return {'errCode': 0, 'status': 'OK'}, 200
def post(self): args = parser__Class.parse_args() db = get_db() cur = get_db().cur try: cur.execute( "INSERT INTO Class(classNo, className, classYear, departNo) VALUES('%s', '%s', %d, %d);" % (args['classNo'], args['className'], args['classYear'], args['departNo'])) db.commit() except Error as e: return {'errCode': -1, 'status': str(e)} return {'errCode': 0, 'status': 'OK'}, 200
def post(self): args = parser_student.parse_args() db = get_db() cur = get_db().cur try: print(args['classNo']) cur.execute("INSERT INTO Student(stuNo, stuName, stuAge, classNo) " "VALUES('%s', '%s', %d, '%s');" % (args['stuNo'], args['stuName'], args['stuAge'], args['classNo'])) db.commit() except Error as e: return {'errCode': -1, 'status': str(e)} return {'errCode': 0, 'status': 'OK'}, 200
def put(self, societyNo): db = get_db() cur = get_db().cur args = parser_associationItem.parse_args() if not self.checkIfExist(societyNo): return {'errCode': -1, 'status': '操作的系不存在'} try: cur.execute("UPDATE Association SET societyName = '%s',societyYear = '%d',societyLoc = '%s' WHERE societyNo='%s';" % ( args['societyName'], args['societyYear'], args['societyLoc'], societyNo)) db.commit() except Error as e: return {'errCode': -1, 'status': str(e)} return {'errCode': 0, 'status': 'OK'}, 200
def get(self): cur = get_db().cur cur.execute("SELECT * FROM NAME_SOCIETY;") res = {'errCode': 0, 'status': 'OK', 'data': [ {'societyNo': item[0], 'societyName': item[1], 'societyNum': item[2]} for item in cur.fetchall()]} return res
def put(self, dormitoryNo): db = get_db() cur = get_db().cur args = parser_dormitoryItem.parse_args() if not self.checkIfExist(dormitoryNo): return {'errCode': -1, 'status': '操作的系不存在'} try: cur.execute( "UPDATE Dormitory SET dormitoryName='%s' WHERE dormitoryNo='%s';" % (args['dormitoryName'], dormitoryNo)) db.commit() except Error as e: return {'errCode': -1, 'status': str(e)} return {'errCode': 0, 'status': 'OK'}, 200
def app(): db_fd, db_path = tempfile.mkstemp() app = create_app({ 'TESTING': True, 'DATABASE': db_path, }) with app.app_context(): init_db() get_db().execute(_data_sql, multi=True) yield app os.close(db_fd) os.unlink(db_path)
def get(self, stuNo): cur = get_db().cur cur.execute( "SELECT Student.stuNo,stuName,stuAge,Class.classNo,dormitoryNo,departName FROM Student,Class,Department WHERE Student.classNo=Class.classNo AND Class.departNo=Department.departNo AND stuNo='%s'" % stuNo) items = cur.fetchone() print(items) cur.execute("SELECT * FROM JoinStatus WHERE stuNo='%s'" % stuNo) society_list = [x[1] for x in cur.fetchall()] if not items: return {'errCode': -1, 'status': '请求条目不存在'} else: return { 'errCode': 0, 'status': 'OK', 'data': { 'stuNo': items[0], 'stuName': items[1], 'stuAge': items[2], 'classNo': items[3], 'dormitory': items[4], 'society': society_list, 'departName': items[5] } }
def checkIfExist(self, stuNo): # 查询是否存在 cur = get_db().cur cur.execute("SELECT * FROM Student WHERE stuNo='%s'" % stuNo) if (len(cur.fetchall()) < 1): return False else: return True
def checkIfExist(self, dormitoryNo): cur = get_db().cur cur.execute("SELECT * FROM Dormitory WHERE dormitoryNo='%s'" % dormitoryNo) if (len(cur.fetchall()) < 1): return False else: return True
def get(self): db = get_db() db.autocommit = True cur = get_db().cur try: cur.execute('CALL FIXNUM') cur.execute('SELECT * FROM tmp_table') data = cur.fetchall() message = "校准了%d个错误: " % (len(data)) for i in data: message += "%s系原人数%d现人数%d;" % (i[1], i[2], i[3]) #cur.execute('DROP TABLE IF EXISTS tmp_table') db.commit() except Error as e: return {'errCode': -1, 'status': str(e)} return {'errCode': 0, 'status': 'OK', 'data': message}, 200
def checkIfExist(self, departNo): cur = get_db().cur cur.execute("SELECT * FROM Department WHERE departNo='%s'" % departNo) if (len(cur.fetchall()) < 1): return False else: return True
def put(self, classNo): db = get_db() cur = get_db().cur args = parser_classItem.parse_args() self.checkIfExist(classNo) print(classNo) try: cur.execute( "UPDATE Class SET className='%s', classYear = %d, departNo = %d WHERE classNo='%s';" % (args['className'], args['classYear'], args['departNo'], classNo)) db.commit() except Error as e: return {'errCode': -1, 'status': str(e)} return {'errCode': 0, 'status': 'OK'}, 200
def checkIfExist(self, societyNo): # 查询是否存在 cur = get_db().cur cur.execute("SELECT * FROM Association WHERE societyNo='%s'" % societyNo) if(len(cur.fetchall()) < 1): return False else: return True
def put(self, stuNo): db = get_db() db.autocommit = False cur = get_db().cur args = parser_studentItem.parse_args() if not self.checkIfExist(stuNo): return {'errCode': -1, 'status': '操作的学生不存在'} try: cur.execute( "UPDATE Student SET stuName='%s'," "stuAge = %d," "classNo = '%s'" "WHERE stuNo='%s';" % (args['stuName'], args['stuAge'], args['classNo'], stuNo)) db.commit() except Error as e: db.rollback() return {'errCode': -1, 'status': str(e)} return {'errCode': 0, 'status': 'OK'}, 200
def get(self): cur = get_db().cur cur.execute("SELECT * FROM Dormitory;") res = { 'errCode': 0, 'status': 'OK', 'data': [{ 'dormitoryNo': item[0], 'dormitoryName': item[1] } for item in cur.fetchall()] } return res
def get(self, societyNo): cur = get_db().cur cur.execute("SELECT societyNo,societyName,societyYear,societyLoc FROM Association WHERE societyNo='%s'" % societyNo) items = cur.fetchone() if not items: return {'errCode': -1, 'status': '请求条目不存在'} else: return {'errCode': 0, 'status': 'OK', 'data': {'societyNo': items[0], 'societyName': items[1], 'societyYear': items[2], 'societyLoc': items[3]} }
def get(self): cur = get_db().cur cur.execute("SELECT stuNo,stuName,stuAge,classNo FROM Student;") res = { 'errCode': 0, 'status': 'OK', 'data': [{ 'stuNo': item[0], 'stuName': item[1], 'stuAge': item[2], 'classNo': item[3] } for item in cur.fetchall()] } return res
def get(self, dormitoryNo): cur = get_db().cur cur.execute("SELECT * FROM Dormitory WHERE dormitoryNo='%s'" % (dormitoryNo)) items = cur.fetchone() print(items) if not items: return {'errCode': -1, 'status': '请求条目不存在'} else: return { 'errCode': 0, 'status': 'OK', 'data': { 'dormitoryNo': items[0], 'dormitoryName': items[1] } }
def get(self): cur = get_db().cur cur.execute( "SELECT classNo, className, classYear, classNum, departName FROM Class, Department WHERE Class.departNo=Department.departNo" ) res = { 'errCode': 0, 'status': 'OK', 'data': [{ 'classNo': item[0], 'className': item[1], 'classYear': item[2], 'classNum': item[3], 'departName': item[4] } for item in cur.fetchall()] } return res
def get(self): cur = get_db().cur cur.execute( "SELECT departNo,departName,departOffice,departNum,dormitoryNo FROM Department;" ) res = { 'errCode': 0, 'status': 'OK', 'data': [{ 'departNo': item[0], 'departName': item[1], 'departOffice': item[2], 'departNum': item[3], 'dormitoryNo': item[4] } for item in cur.fetchall()] } return res
def get(self, departNo): cur = get_db().cur cur.execute( "SELECT departNo,departName,departOffice,departNum,dormitoryNo FROM Department WHERE departNo='%s'" % departNo) items = cur.fetchone() if not items: return {'errCode': -1, 'status': '请求条目不存在'} else: return { 'errCode': 0, 'status': 'OK', 'data': { 'departNo': items[0], 'departName': items[1], 'departOffice': items[2], 'departNum': items[3], 'dormitoryNo': items[4] } }