def loginUserInsert(*args): #OK
with configDB.configLocalDB() as cur:
print("loginUserAdd")
cur.execute(
"insert into user_add values('%s','%s',%s,false) on conflict(id) do \
update set passwd=excluded.passwd, rights=excluded.rights, is_uploaded=excluded.is_uploaded"
% args)
def speechRecordInsertSecond(*args):
with configDB.configLocalDB() as cur:
print("speechRecordInsertSecond")
cur.execute(
"insert into speech_record (spe_id, stu_id, signin_second) values(%s,%s,true) \
on conflict(spe_id,stu_id) do update \
set signin_second=excluded.signin_second" % args)
def loginUserDelete(*names): #OK
with configDB.configLocalDB() as cur:
print("loginUserDelete")
if (names == None):
print("empty input")
for name in names:
cur.execute("delete from user_add where id = %s ", (name, ))
def examRecordDelete(*ids):
with configDB.configLocalDB() as cur:
print("examRecordDelete")
for exam_id, stu_id in ids:
cur.execute(
"delete from exam_record where exam_id=%s and stu_id=%s" %
(exam_id, stu_id))
def examStudentAppend(stu_id, stu_name, stu_class):
with configDB.configLocalDB() as cur:
cur.execute(
"insert into exam_student (id,name,class) values({0},'{1}',{2})\
on conflict (id) do update set id=excluded.id, \
name=excluded.name, class=excluded.class".format(
stu_id, stu_name, stu_class))
def registerStudentDelete(*ids): #OK
with configDB.configLocalDB() as cur:
print("registerStudentDelete")
if (ids == None):
print("empty input")
for id in ids:
cur.execute("delete from register_student where id=%s" % (id, ))
def speechSpeechInsert(*args):
with configDB.configLocalDB() as cur:
print("speechSpeechInsert")
cur.execute(
"insert into speech values(nextval('seq_spe'), '%s','%s')" % args)
cur.execute("select lastval() from speech")
spe_id_local = cur.fetchone()[0]
return spe_id_local
def getKeysExamExamLocal():
with configDB.configLocalDB() as cur:
cur.execute("select id from exam")
results = numpy.array(cur.fetchall())
if len(results) == 0:
return ()
keys = results[:, 0]
return tuple(keys)
def loginUserDownload(): #OK
with configDB.configLocalDB() as curLocal:
with configDB.configServerDB() as curServer:
print("loginUserDownload")
curServer.execute('''select * from "user" ''')
rows = curServer.fetchall()
curLocal.execute('''delete from "user"''')
for row in rows:
curLocal.execute('''insert into "user" values %s ''', (row, ))
def examRecordGetAttribute(exam_id, stu_id):
with configDB.configLocalDB() as cur:
cur.execute(
'select is_appended from exam_record where exam_id={0} and stu_id={1}'
.format(exam_id, stu_id))
result = cur.fetchone()
if result == None: return "Empty"
elif result[0] == True: return "Appended"
else: return "Normal"
def registerStudentInsert(
*args): #stu_id, stu_name, stu_class, finger, face, is_ic
with configDB.configLocalDB() as cur: #OK
print("registerStudentInsert")
cur.execute(
"insert into register_student(id, name, class, finger, face, is_ic, is_uploaded)\
values(%s,'%s',%s,%s,%s,%s,false) \
on conflict(id) do update set name=excluded.name, class=excluded.class,\
finger=excluded.finger, face=excluded.face, is_ic=excluded.is_ic, \
is_uploaded=excluded.is_uploaded" % args)
def loginUserLogin(*args): #OK
with configDB.configLocalDB() as cur:
print("loginUserLogin")
cur.execute(
'''select rights from "user" where id=%s and passwd ='%s' ''' %
args)
result = cur.fetchone()
if result == None:
return 0
else:
return result[0]
def registerStudentUploadFiles(*stu_ids):
with configDB.configLocalDB() as cur:
with configFTP() as ftp:
print("registerStudentUploadFiles")
for stu_id in stu_ids:
cur.execute("select finger, face from register_student where id =%s",(stu_id,))
fingerCnt, faceCnt = cur.fetchone()
#上传stu_id学生的txt文件.该学生有fingerCnt个txt文件,local端目录是...,server端目录是...
uploadGroupFiles(ftp,stu_id,".txt",fingerCnt,config.localRegisterStudentDir, config.serverStudentDir)
#上传stu_id学生的jpg文件.该学生有fingerCnt个jpg文件,local端目录是...,server端目录是...
uploadGroupFiles(ftp,stu_id,".jpg",faceCnt,config.localRegisterStudentDir, config.serverStudentDir)
def examRecordUploadFiles(*id_groups):
with configDB.configLocalDB() as cur:
with configFTP() as ftp:
print("examRecordUploadFiles")
for exam_id,stu_id in id_groups:
local_dir = config.localExamRecordDir + '/' + str(exam_id)
server_dir = config.serverExamRecordDir + '/' +str(exam_id)
if (server_dir in ftp.nlst(config.serverExamRecordDir)) == False :
ftp.mkd(server_dir)
cur.execute("select finger, face from exam_record where exam_id=%s and stu_id=%s",(exam_id, stu_id))
fingerCnt, faceCnt = cur.fetchone()
uploadGroupFiles(ftp, stu_id,".txt",fingerCnt,local_dir,server_dir)
uploadGroupFiles(ftp, stu_id,".jpg",faceCnt,local_dir,server_dir)
def examMemberDownload(*exam_ids):
with configDB.configLocalDB() as curLocal:
with configDB.configServerDB() as curServer:
print("examMemberDownload")
for exam_id in exam_ids:
curServer.execute(
"select * from exam_member where exam_id=%s" % (exam_id, ))
rows = curServer.fetchall()
for row in rows:
curLocal.execute(
"insert into exam_member values %s on conflict(exam_id, stu_id)\
do update set exam_id=excluded.exam_id, stu_id=excluded.stu_id",
(row, ))
curLocal.execute(
"insert into exam_record(exam_id,stu_id) values %s\
on conflict(exam_id,stu_id) do nothing", (row, ))
def examExamDownload(*exam_ids):
with configDB.configLocalDB() as curLocal:
with configDB.configServerDB() as curServer:
print("examExamDownload")
for exam_id in exam_ids:
dir = config.localExamRecordDir + '/{0}'.format(exam_id)
print(dir)
if os.path.exists(dir) == False:
os.mkdir(dir)
curServer.execute("select * from exam where id=%s" %
(exam_id, ))
row = curServer.fetchone()
curLocal.execute(
"insert into exam values %s on conflict(id) do update set \
name=excluded.name, location=excluded.location, teacher=excluded.teacher"
% (row, ))
def examRecordInsert(exam_id, stu_id, finger, sim_finger, face, sim_face,
is_ic, is_appended, is_matched):
with configDB.configLocalDB() as cur:
print("examRecordInsert")
cur.execute("insert into exam_record(exam_id, stu_id,\
finger, sim_finger, face, sim_face,\
is_ic, is_appended,is_matched,\
is_uploaded )\
values( %s, %s, %s, %s, %s, %s,%s, %s, %s,false)\
on conflict(exam_id, stu_id) do update set\
finger=excluded.finger, sim_finger=excluded.sim_finger,\
face=excluded.face, sim_face=excluded.sim_face,\
is_ic=excluded.is_ic, is_appended=excluded.is_appended, \
is_matched=excluded.is_matched,\
is_uploaded=excluded.is_uploaded " %
(exam_id, stu_id, finger, sim_finger, face, sim_face,
is_ic, is_appended, is_matched))
def examStudentDownload(*exam_ids):
with configDB.configLocalDB() as curLocal:
with configDB.configServerDB() as curServer:
print("examStudentDownload")
for exam_id in exam_ids:
configFTP.examStudentDownloadFiles(exam_id)
curServer.execute("select id, name, class, finger, face \
from student, exam_member \
where id=stu_id \
and exam_id =%s" % (exam_id, ))
rows = curServer.fetchall()
for row in rows:
curLocal.execute(
"insert into exam_student values %s \
on conflict(id) do update set\
name=excluded.name, class=excluded.class,\
finger=excluded.finger, face=excluded.face", (row, ))
def loginUserUpload(*user_ids): #OK
with configDB.configLocalDB() as curLocal:
with configDB.configServerDB() as curServer:
print("loginUserUpload")
if (user_ids == None):
print("empty input")
for user_id in user_ids:
curLocal.execute(
"select id, passwd, rights from user_add where id = %s",
(user_id, ))
row = curLocal.fetchone()
print(row)
curServer.execute(
'''insert into "user" values %s on conflict(id)\
do update set passwd=excluded.passwd ,rights=excluded.rights ''', (row, ))
curLocal.execute(
"update user_add set is_uploaded=true where id=%s" %
(user_id, ))
def registerStudentUpload(*ids):
with configDB.configLocalDB() as curLocal:
with configDB.configServerDB() as curServer:
print("registerStudentUpload")
if (ids == None):
print("empty input")
for id in ids:
configFTP.registerStudentUploadFiles(id)
curLocal.execute(
"select id, name, class, face, finger, is_ic \
from register_student where id=%s " % (id, ))
row = curLocal.fetchone()
curServer.execute(
"insert into student values %s on conflict(id) do update set\
name=excluded.name, class=excluded.class, face=excluded.face, \
finger=excluded.finger, is_ic=excluded.is_ic,time=excluded.time", (row, ))
curLocal.execute(
"update register_student set is_uploaded=true where id=%s"
% (id, ))
def speechSpeechUpload(*spe_id_locals):
with configDB.configLocalDB() as curLocal:
with configDB.configServerDB() as curServer:
print("speechSpeechUpload")
for spe_id_local in spe_id_locals:
curLocal.execute(
"select name, location from speech where id=%s",
(spe_id_local, ))
row = curLocal.fetchone()
curServer.execute(
"insert into speech values \
(nextval('seq_spe'),%s,%s,to_char(current_timestamp,'YYYY-MM-DD HH:MI'))",
row)
curServer.execute("select lastval() from speech")
spe_id_server = curServer.fetchone()[0]
curLocal.execute("insert into speech_id_relation values(%s,%s)\
on conflict(id_local, id_server) do nothing" %
(spe_id_local, spe_id_server))
curLocal.execute(
"update speech set is_uploaded=true where id={0}".format(
spe_id_local))
def examRecordUpload(*ids):
with configDB.configLocalDB() as curLocal:
with configDB.configServerDB() as curServer:
print("examRecordUpload")
for exam_id, stu_id in ids:
configFTP.examRecordUploadFiles((exam_id, stu_id), )
curLocal.execute(
"select exam_id, stu_id, finger, sim_finger, face, sim_face,\
is_ic, is_appended, is_matched \
from exam_record\
where exam_id=%s and stu_id=%s" % (exam_id, stu_id))
rows = curLocal.fetchall()
for row in rows:
curServer.execute(
"insert into exam_record(exam_id, stu_id,\
finger, sim_finger, face, sim_face, \
is_ic, is_appended, is_matched)\
values(%s,%s,%s,%s,%s,%s,%s,%s,%s)\
on conflict(exam_id, stu_id) do update set\
finger=excluded.finger, sim_finger=excluded.sim_finger,\
face=excluded.face, sim_face=excluded.sim_face,\
is_ic=excluded.is_ic, is_appended=excluded.is_appended,\
is_matched=excluded.is_matched,\
time=excluded.time" % row)
curLocal.execute("update exam_record set is_uploaded=true\
where exam_id=%s and stu_id=%s" % (exam_id, stu_id))
curLocal.execute(
"select stu_id from exam_record where is_appended = true")
ids = curLocal.fetchall()
for id in ids:
print(id)
curLocal.execute(
"select name,class from exam_student where id = %s" %
(id[0], ))
stu_name, stu_class = curLocal.fetchone()
curServer.execute(
"insert into student (id,name,class) values(%s,'%s',%s)\
on conflict(id) do update set name=excluded.name,class=excluded.class"
% (id[0], stu_name, stu_class))
def speechRecordUpload(*spe_id_locals):
with configDB.configLocalDB() as curLocal:
with configDB.configServerDB() as curServer:
print("speechRecordUpload")
print(spe_id_locals)
for spe_id_local in spe_id_locals:
curLocal.execute(
"select id_server from speech_id_relation where id_local=%s"
% (spe_id_local, ))
spe_id_server = curLocal.fetchone()[0]
curLocal.execute(
"select stu_id from speech_record where signin_first=true and signin_second=true\
and spe_id=%s" % (spe_id_local, ))
stu_ids = curLocal.fetchall()
for stu_id in stu_ids:
curServer.execute(
"insert into speech_record values(%s,%s) \
on conflict(spe_id,stu_id) do nothing", (spe_id_server, stu_id[0]))
curLocal.execute(
"update speech_record set is_uploaded=true where spe_id=%s and stu_id=%s"
% (spe_id_local, stu_id[0]))
curLocal.execute(
"update speech set is_uploaded=true where id = %s" %
(spe_id_local, ))
def registerStudentSelect(view, *args): #OK
with configDB.configLocalDB() as cur:
print("registerStudentSelect")
cur.execute("select id from register_student where %s" % sql)
return cur.fetchone()
def loginUserSelect(view, filter): #OK
with configDB.configLocalDB() as cur:
print("loginUserSelect")
cur.execute("select id from user_add where %s" % filter)
return cur.fetchone()
def examMemberDelete(*exam_ids):
with configDB.configLocalDB() as cur:
print("examMemberDelete")
for exam_id in exam_ids:
cur.execute("delete from exam_member where exam_id=%s" %
(exam_id, ))
def examExamShow():
with configDB.configLocalDB() as curLocal:
print("examMemberShow")
curLocal.execute("select id from exam ")
rows = curLocal.fetchall()
return rows
def speechRecordInsertFirst(*args):
with configDB.configLocalDB() as cur:
print("speechRecordInsertFirst")
cur.execute(
"insert into speech_record(spe_id, stu_id, signin_first) values (%s,%s,true) on conflict(spe_id, stu_id) \
do update set signin_first=excluded.signin_first" % args)
def speechSpeechDelete(*spe_id_locals):
with configDB.configLocalDB() as cur:
for spe_id_local in spe_id_locals:
cur.execute("delete from speech where id =%s", (spe_id_local, ))
def loginUserShow():
with configDB.configLocalDB() as curLocal:
print("loginUserShow")
curLocal.execute('''select * from "user" ''')
rows = curLocal.fetchall()
return rows
