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]
}
}
