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