def DeleteContest(self,cid):
whereclause = ' cid = {} '.format(cid)
conn = ConnPool.connect()
cur = conn.cursor()
sql = getQueryDetailSQL('contest',' cstatus ',whereclause,'1=1')
cur.execute(sql)
r = cur.fetchone()
if r[0] != 0 :
return False
sql = getDeletSQL('cproblem', whereclause)
cur.execute(sql)
sql = getDeletSQL('contest',whereclause=whereclause)
cur.execute(sql)
sql = getDeletSQL('contest', whereclause)
cur.execute(sql)
cur.close()
return True
def main():
oj = 'HDU'
problemid = '1092'
# get pid
conn = ConnPool.connect()
cur = conn.cursor()
sql = 'SELECT * FROM problem WHERE ( originOJ LIKE "{}" and originProb LIKE "{}" )'.format(
oj, problemid)
cur.execute(sql)
rt = cur.fetchone()
# error
if rt is None: pass
d = dict()
if rt[0] is not None:
d['pid'] = rt[0]
if rt[1] is not None:
d['title'] = rt[1]
if rt[2] is not None:
d['source'] = rt[2]
if rt[3] is not None:
d['url'] = rt[3]
sql = 'SELECT * FROM problemdetail WHERE pid = {}'.format(d['pid'])
cur.execute(sql)
rt = cur.fetchone()
if rt[2] is not None:
d['description'] = Base64StrToUTF8Str(rt[2])
if rt[3] is not None:
d['input'] = Base64StrToUTF8Str(rt[3])
if rt[4] is not None:
d['output'] = Base64StrToUTF8Str(rt[4])
if rt[5] is not None:
d['sampleinput'] = Base64StrToUTF8Str(rt[5])
if rt[6] is not None:
d['sampleoutput'] = Base64StrToUTF8Str(rt[6])
if rt[7] is not None:
d['hint'] = Base64StrToUTF8Str(rt[7])
if rt[8] is not None:
d['author'] = Base64StrToUTF8Str(rt[8])
if rt[10] is not None:
d['updatetime'] = str(rt[10])
if rt[11] is not None:
d['memorylimit'] = rt[11]
if rt[12] is not None:
d['timelimit'] = rt[12]
if rt[13] is not None:
d['specialjudge'] = rt[13]
cur.close()
for key in d:
print(key + ' ---> ' + str(d[key]))
def main():
oj = 'HDU'
problemid = '1092'
# get pid
conn = ConnPool.connect()
cur = conn.cursor()
sql = 'SELECT * FROM problem WHERE ( originOJ LIKE "{}" and originProb LIKE "{}" )'.format(oj, problemid)
cur.execute(sql)
rt = cur.fetchone()
# error
if rt is None: pass
d = dict()
if rt[0] is not None:
d['pid'] = rt[0]
if rt[1] is not None:
d['title'] = rt[1]
if rt[2] is not None:
d['source'] = rt[2]
if rt[3] is not None:
d['url'] = rt[3]
sql = 'SELECT * FROM problemdetail WHERE pid = {}'.format(d['pid'])
cur.execute(sql)
rt = cur.fetchone()
if rt[2] is not None:
d['description'] = Base64StrToUTF8Str(rt[2])
if rt[3] is not None:
d['input'] = Base64StrToUTF8Str(rt[3])
if rt[4] is not None:
d['output'] = Base64StrToUTF8Str(rt[4])
if rt[5] is not None:
d['sampleinput'] = Base64StrToUTF8Str(rt[5])
if rt[6] is not None:
d['sampleoutput'] = Base64StrToUTF8Str(rt[6])
if rt[7] is not None:
d['hint'] = Base64StrToUTF8Str(rt[7])
if rt[8] is not None:
d['author'] = Base64StrToUTF8Str(rt[8])
if rt[10] is not None:
d['updatetime'] = str(rt[10])
if rt[11] is not None:
d['memorylimit'] = rt[11]
if rt[12] is not None:
d['timelimit'] = rt[12]
if rt[13] is not None:
d['specialjudge'] = rt[13]
cur.close()
for key in d:
print(key + ' ---> ' + str(d[key]))
def FetchAll(sql):
conn = ConnPool.connect()
cur = conn.cursor()
cur.execute(sql)
rs = cur.fetchall()
cur.close()
conn.close()
return rs
def FetchAll(sql):
conn = ConnPool.connect()
cur = conn.cursor()
cur.execute(sql)
rs = cur.fetchall()
cur.close()
conn.close()
return rs
def UpdateDatabaseContestStatus(self, cid, cstauts):
data = dict()
data['cstatus'] = cstauts
sql = getUpdateSQL('contest', data, ' cid = {} '.format(cid))
conn = ConnPool.connect()
cur = conn.cursor()
cur.execute(sql)
cur.close()
conn.close()
def ExeSQL(sql):
try :
conn = ConnPool.connect()
cur = conn.cursor()
cur.execute(sql)
cur.close()
conn.close()
except Exception as e :
print('sql exe Error {}'.format(str(e)))
print('sql: ',sql)
return False
return True
def ExeSQL(sql):
try:
conn = ConnPool.connect()
cur = conn.cursor()
cur.execute(sql)
cur.close()
conn.close()
except Exception as e:
print('sql exe Error {}'.format(str(e)))
print('sql: ', sql)
return False
return True
def UpdateProblem(self, cid, data):
# clear old problem in contest
whereclause = ' cid = {} '.format(cid)
sql = getDeletSQL('cproblem', whereclause)
conn = ConnPool.connect()
cur = conn.cursor()
cur.execute(sql)
log = ''
for item in data:
# get pid
whereclause = ' originOJ = "{originOJ}" and originProb = "{originProb}" '.format(**item)
sql = getQueryDetailSQL('problem', '*', whereclause, ' pid ')
num = cur.execute(sql)
if num == 0:
log += 'Error when add Problem OJ: {originOJ} Pid: {originProb}<br>'.format(**item)
log += '\n'
else:
rs = cur.fetchone()
pid = rs[0]
title = rs[1]
originOJ = rs[4]
originProb = rs[5]
# insert into cproblem
data = dict()
data['cid'] = cid
data['pid'] = pid
data['title'] = title
data['originOJ'] = originOJ
data['originProb'] = originProb
sql = getInserSQL('cproblem', data)
cur.execute(sql)
cur.close()
conn.close()
if len(log) == 0:
log += 'All problem add into contest {} successfully. <br>'.format(cid)
else:
log += 'some error happend.<br> May be some problem can\'t find this problem in database...<br>'
return log
def GetProblemID(orj, orid):
sql = 'SELECT problem.pid FROM problem WHERE ' \
'( problem.originOJ LIKE "{}" AND problem.originProb LIKE "{}" )'.format(orj, orid)
conn = ConnPool.connect()
cur = conn.cursor()
cur.execute(sql)
tp = cur.fetchall()
cur.close()
conn.close()
if tp.__len__() == 0:
return 0
else:
return tp[0][0]
def GetProblemID(orj, orid):
sql = 'SELECT problem.pid FROM problem WHERE ' \
'( problem.originOJ LIKE "{}" AND problem.originProb LIKE "{}" )'.format(orj, orid)
conn = ConnPool.connect()
cur = conn.cursor()
cur.execute(sql)
tp = cur.fetchall()
cur.close()
conn.close()
if tp.__len__() == 0:
return 0
else:
return tp[0][0]
def getOriginOJandProb(self,vpid):
sql = getQueryDetailSQL('problem','originOJ,originProb',' virtualProb="{}" and virtualOJ="{}"'.format(vpid,'BNUVJ'),' pid ')
#print('sql: ',sql)
conn = ConnPool.connect()
cur = conn.cursor()
cur.execute(sql)
rt = cur.fetchone()
cur.close()
conn.close()
return rt
def CreateNewContest(self, data):
sql = getInserSQL('contest', data)
print('exeSQL: Create Contest!! ', sql)
conn = ConnPool.connect()
cur = conn.cursor()
cur.execute(sql)
sql = LAST_INSERT_ID()
cur.execute(sql)
id = cur.fetchone()[0]
cur.close()
conn.close()
return id
def InsertStatusToDB(self, pid, oj, Prob, lang, code, cid,vj_username):
data = dict()
data['pid'] = pid
data['cid'] = cid
data['language'] = lang
data['originOJ'] = oj
data['originProb'] = Prob
data['source'] = UTF8StrToBase64Str(code)
data['username'] = self.current_user
data['uid'] = str(self.get_secure_cookie('uid').decode('utf-8'))
data['timesubmit'] = time.strftime('%Y-%m-%d %H:%M:%S')
data['isdisplay'] = 1
data['isopen'] = 1
data['status'] = 'Pending'
data['codelenth'] = str(len(code))
sql = getInserSQL('status', data)
conn = ConnPool.connect()
cur = conn.cursor()
cur.execute(sql)
''' create a pkl file'''
file = SID_DATA_FILE+'/sid_{}.pkl'
cur.execute(LAST_INSERT_ID())
sid = cur.fetchone()[0]
pkl = dict()
pkl['sid'] = sid
pkl['codelenth'] = data['codelenth']
pkl['originOJ'] = data['originOJ']
pkl['originProb'] = data['originProb']
pkl['language'] = data['language']
pkl['vj_username'] = vj_username
pkl['looplimit'] = 10
print('vj_username: '******'wb')
pickle.dump(pkl, fw)
cur.close()
conn.close()
print('status_sql: ', sql)
def CreateNewContest(self, data):
sql = getInserSQL('contest', data)
print('exeSQL: Create Contest!! ', sql)
conn = ConnPool.connect()
cur = conn.cursor()
cur.execute(sql)
sql = LAST_INSERT_ID()
cur.execute(sql)
id = cur.fetchone()[0]
cur.close()
conn.close()
return id
def InsertStatusToDB(self, pid, oj, Prob, lang, code, cid, vj_username):
data = dict()
data['pid'] = pid
data['cid'] = cid
data['language'] = lang
data['originOJ'] = oj
data['originProb'] = Prob
data['source'] = UTF8StrToBase64Str(code)
data['username'] = self.current_user
data['uid'] = str(self.get_secure_cookie('uid').decode('utf-8'))
data['timesubmit'] = time.strftime('%Y-%m-%d %H:%M:%S')
data['isdisplay'] = 1
data['isopen'] = 1
data['status'] = 'Pending'
data['codelenth'] = str(len(code))
sql = getInserSQL('status', data)
conn = ConnPool.connect()
cur = conn.cursor()
cur.execute(sql)
''' create a pkl file'''
file = SID_DATA_FILE + '/sid_{}.pkl'
cur.execute(LAST_INSERT_ID())
sid = cur.fetchone()[0]
pkl = dict()
pkl['sid'] = sid
pkl['codelenth'] = data['codelenth']
pkl['originOJ'] = data['originOJ']
pkl['originProb'] = data['originProb']
pkl['language'] = data['language']
pkl['vj_username'] = vj_username
pkl['looplimit'] = 10
print('vj_username: '******'wb')
pickle.dump(pkl, fw)
cur.close()
conn.close()
print('status_sql: ', sql)
def AddUser(d):
conn = ConnPool.connect()
cur = conn.cursor()
sql = checkUserExist(d['username'])
cur.execute(sql)
ret = cur.fetchone()
if ret[0] != 0:
return 0
d['password'] = SHA512(d['username'] + '@' + d['password'])
sql = getInserSQL('user', d)
ret = cur.execute(sql)
conn.commit()
cur.close()
conn.close()
return ret
def AddUser(d):
conn = ConnPool.connect()
cur = conn.cursor()
sql = checkUserExist(d['username'])
cur.execute(sql)
ret = cur.fetchone()
if ret[0] != 0:
return 0
d['password'] = SHA512(d['username']+'@'+d['password'])
sql = getInserSQL('user', d)
ret = cur.execute(sql)
conn.commit()
cur.close()
conn.close()
return ret
def checkPasswd(self, username, password):
conn = ConnPool.connect()
cur = conn.cursor()
sql = checkUserSQL(username, password)
print('exe: ', sql)
cur.execute(sql)
ans = cur.fetchall()
print(ans)
print(ans[0][0])
if ans[0][0] == 1:
sql = getUserUid(username)
cur.execute(sql)
uid = cur.fetchone()[0]
cur.close()
conn.close()
return uid
else:
return None
def checkPasswd(self, username, password):
conn = ConnPool.connect()
cur = conn.cursor()
sql = checkUserSQL(username, password)
print('exe: ', sql)
cur.execute(sql)
ans = cur.fetchall()
print(ans)
print(ans[0][0])
if ans[0][0] == 1:
sql = getUserUid(username)
cur.execute(sql)
uid = cur.fetchone()[0]
cur.close()
conn.close()
return uid
else:
return None
def getProblem(self, oj, problemid):
# get pid
conn = ConnPool.connect()
cur = conn.cursor()
sql = 'SELECT * FROM problem WHERE ( originOJ LIKE "{}" and originProb LIKE "{}" )'.format(
oj, problemid)
cur.execute(sql)
rt = cur.fetchone()
# error
if rt is None:
return None
d = dict()
d['originOJ'] = oj
d['originProb'] = problemid
if rt[0] is not None:
d['pid'] = rt[0]
if rt[1] is not None:
d['title'] = rt[1]
if rt[2] is not None:
d['source'] = rt[2]
if rt[3] is not None:
d['url'] = rt[3]
sql = 'SELECT * FROM problemdetail WHERE pid = {}'.format(d['pid'])
cur.execute(sql)
rt = cur.fetchone()
print(sql)
print(rt)
if rt[2] is not None:
d['description'] = Base64StrToUTF8Str(rt[2])
if rt[3] is not None:
d['input'] = Base64StrToUTF8Str(rt[3])
if rt[4] is not None:
d['output'] = Base64StrToUTF8Str(rt[4])
if rt[5] is not None:
d['sampleinput'] = Base64StrToUTF8Str(rt[5])
if rt[6] is not None:
d['sampleoutput'] = Base64StrToUTF8Str(rt[6])
if rt[7] is not None:
d['hint'] = Base64StrToUTF8Str(rt[7])
if rt[8] is not None:
d['author'] = Base64StrToUTF8Str(rt[8])
if rt[10] is not None:
d['updatetime'] = str(rt[10])
if rt[11] is not None:
d['memorylimit'] = rt[11]
if rt[12] is not None:
d['timelimit'] = rt[12]
if rt[13] is not None:
d['specialjudge'] = rt[13]
cur.close()
conn.close()
return d
cur = conn.cursor()
sql = checkUserSQL(username, password)
print('exe: ', sql)
cur.execute(sql)
ans = cur.fetchall()
print(ans)
print(ans[0][0])
if ans[0][0] == 1:
sql = getUserUid(username)
cur.execute(sql)
uid = cur.fetchone()[0]
cur.close()
conn.close()
return uid
else:
return None
if __name__ == '__main__':
username = '******'
password = '******'
# sql = 'select * from user WHERE username = %s and password = %s'%(username,password)
sql = 'select * from user'
print(sql)
conn = ConnPool.connect()
cur = conn.cursor()
cur.execute(sql)
conn.close()
print(cur.fetchall())
cur = conn.cursor()
sql = checkUserSQL(username, password)
print('exe: ', sql)
cur.execute(sql)
ans = cur.fetchall()
print(ans)
print(ans[0][0])
if ans[0][0] == 1:
sql = getUserUid(username)
cur.execute(sql)
uid = cur.fetchone()[0]
cur.close()
conn.close()
return uid
else:
return None
if __name__ == '__main__':
username = '******'
password = '******'
# sql = 'select * from user WHERE username = %s and password = %s'%(username,password)
sql = 'select * from user'
print(sql)
conn = ConnPool.connect()
cur = conn.cursor()
cur.execute(sql)
conn.close()
print(cur.fetchall())
def getProblem(self, oj, problemid):
# get pid
conn = ConnPool.connect()
cur = conn.cursor()
sql = 'SELECT * FROM problem WHERE ( originOJ LIKE "{}" and originProb LIKE "{}" )'.format(oj, problemid)
cur.execute(sql)
rt = cur.fetchone()
# error
if rt is None:
return None
d = dict()
d['originOJ'] = oj
d['originProb'] = problemid
if rt[0] is not None:
d['pid'] = rt[0]
if rt[1] is not None:
d['title'] = rt[1]
if rt[2] is not None:
d['source'] = rt[2]
if rt[3] is not None:
d['url'] = rt[3]
sql = 'SELECT * FROM problemdetail WHERE pid = {}'.format(d['pid'])
cur.execute(sql)
rt = cur.fetchone()
print(sql)
print(rt)
if rt[2] is not None:
d['description'] = Base64StrToUTF8Str(rt[2])
if rt[3] is not None:
d['input'] = Base64StrToUTF8Str(rt[3])
if rt[4] is not None:
d['output'] = Base64StrToUTF8Str(rt[4])
if rt[5] is not None:
d['sampleinput'] = Base64StrToUTF8Str(rt[5])
if rt[6] is not None:
d['sampleoutput'] = Base64StrToUTF8Str(rt[6])
if rt[7] is not None:
d['hint'] = Base64StrToUTF8Str(rt[7])
if rt[8] is not None:
d['author'] = Base64StrToUTF8Str(rt[8])
if rt[10] is not None:
d['updatetime'] = str(rt[10])
if rt[11] is not None:
d['memorylimit'] = rt[11]
if rt[12] is not None:
d['timelimit'] = rt[12]
if rt[13] is not None:
d['specialjudge'] = rt[13]
cur.close()
conn.close()
return d