def sql_upload_slice(tno, uno, submit_time): db = connect_db() cursor = db.cursor() tno = int(tno) uno = int(uno) sql = "SELECT FNO FROM FILE F WHERE F.TNO = %s " % tno cursor.execute(sql) fno = int(cursor.fetchone()[0]) sql = "UPDATE PARTICIPATION_TASK SET STATUS='pending',SUBMIT_TIME='%s' WHERE FNO=%d AND UNO=%d" % ( submit_time, fno, uno) #print sql cursor.execute(sql) sql = "SELECT FSNO FROM PARTICIPATION_TASK WHERE FNO=%d AND UNO=%d" % (fno, uno) cursor.execute(sql) res = cursor.fetchall() for i in res: sql = "UPDATE FILE_SLICE SET RECEIVE=RECEIVE+1 WHERE FNO=%d AND FSNO=%d" % ( fno, i[0]) #print sql cursor.execute(sql) db.commit() db.close()
def load_taskinfo(tno): db = connect_db() cursor = db.cursor() json = {} sql = "SELECT UNO,TITLE,DESCRIPTION,PAYMENT,DEADLINE,EXAMPLE_NAME FROM TASK T, SCHOLAR_OWN_TASK S,FILE F WHERE T.TNO = S.TNO AND T.TNO = F.TNO AND S.TNO = %s" % tno cursor.execute(sql) res = cursor.fetchone() json['uno'] = res[0] json['title'] = res[1] json['description'] = res[2] json['payment'] = res[3] json['deadline'] = res[4] json['example_name'] = res[5] sql = "SELECT COUNT(FSNO) FROM FILE F, FILE_SLICE S WHERE F.FNO = S.FNO AND F.TNO = %s GROUP BY F.FNO" % tno #print sql cursor.execute(sql) res = cursor.fetchone() json['num'] = res[0] db.close() return json
def add_task_to_db(task=None): db = connect_db() cursor = db.cursor() #print task.payment sql = "INSERT INTO TASK(TITLE,DESCRIPTION,OPENTIME,DEADLINE,PAYMENT,TYPE,STAGE) VALUES\ ('%s','%s','%s','%s','%s','TASK','OPEN')" % \ (task.title,task.description,task.opentime,task.deadline,task.payment) #print "sql:",sql cursor.execute(sql) cursor.execute("SELECT MAX(TNO) FROM TASK") tno = cursor.fetchone()[0] #print tno cursor.execute("SELECT UNO FROM USERINFO WHERE UNAME = '%s'" % task.owner) uno = cursor.fetchone()[0] #print uno sql = "INSERT INTO SCHOLAR_OWN_TASK(UNO,TNO,ACCESS) VALUES(%d,%d,'owner')" % (uno,tno) cursor.execute(sql) db.commit() db.close() return tno
def check_legibility(sno,uno): # whether volunteer meets requirements sno = int(sno) db = connect_db() cursor = db.cursor() cursor.execute("SELECT MINAGE,MAXAGE,SURVEY_RESTRICT,GENDER_RESTRICT FROM SURVEY WHERE SNO = %d" % sno) tup = cursor.fetchone() min_age,max_age,sr,gr = tup[0:5] cursor.execute("SELECT AGE,GENDER,USERTYPE FROM USERINFO WHERE UNO = %d" % uno) age,gender,usertype = cursor.fetchone()[0:4] cursor.execute("SELECT SNO FROM PARTICIPATION WHERE UNO = %d AND SNO = %d" % (uno,sno)) has_dup = cursor.fetchone() errtext = "" if has_dup: errtext = "你已经参与过调研" return False,errtext if age < min_age or age > max_age: errtext += "年龄不符合要求;" if (gr == u'仅女性' and gender == u'Male') or (gr == u'仅男性' and gender == u'Female'): errtext += "性别不符合要求;" if sr == u'仅学者' and usertype != u'Scholar': errtext += "身份不符合要求;" db.close() if errtext != "": return False,errtext else: return True,""
def grant_scholar(uno, pwd, cursor, db): cursor.execute("CREATE USER scholar_%d IDENTIFIED by '%s'" % (uno, pwd)) cursor.execute( "GRANT SELECT,DELETE,UPDATE,INSERT ON ScholarDB.* TO scholar_%d" % uno) cursor.execute("CREATE DATABASE WorkSpace_%d" % uno) cursor.execute( "GRANT SELECT,DELETE,UPDATE,INSERT ON WorkSpace_%d.* to scholar_%d" % (uno, uno)) db.commit() db2 = connect_db('root', 'dbpjdbpj', 'WorkSpace_%d' % uno) cursor2 = db2.cursor() cursor2.execute("create table PRIV_QUESTION(\ QNO INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,\ TITLE VARCHAR(200) NOT NULL,\ SUPPLEMENT VARCHAR(200),\ SUPPLEMENT_TYPE VARCHAR(45),\ TYPE VARCHAR(45) NOT NULL,\ INPUTTYPE VARCHAR(45) NOT NULL\ )") cursor2.execute("create table PRIV_CHOICE(\ CNO INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,\ PLACE INTEGER NOT NULL,\ QNO INTEGER NOT NULL,\ CONTENT VARCHAR(200)\ )") db2.commit() db2.close()
def search(uno=None, subject=None, datatype=None, type=None, pattern=None, order=None, isDesc=None, user='******', pwd=123456): db = connect_db() cursor = db.cursor() get_scholar_list_from_db(uno, subject, datatype, type, order, user, pwd) sql = "SELECT TYPE,NO,TITLE,DESCRIPTION,PAYMENT,OPENTIME FROM LIST WHERE TITLE LIKE '%" + pattern + "%'" #print "isDesc:",isDesc if isDesc == '1': sql += "OR DESCRIPTION LIKE '%" + pattern + "%'" sql += "ORDER BY " + order #print sql cursor.execute(sql) list_res = cursor.fetchall() res = load_json(list_res) db.commit() db.close() return res
def load_from_db(self,sno): db = connect_db() cursor = db.cursor() cursor.execute('SELECT TITLE,DESCRIPTION,OPENTIME,PAYMENT FROM SURVEY WHERE SNO = %d' % sno) self.title,self.description,self.opentime,self.payment = cursor.fetchone()[0:4] cursor.execute('SELECT PUBLICITY FROM PUBLICITY_SURVEY WHERE SNO = %d' % sno) self.publicity = cursor.fetchone()[0] db.close()
def get_money(UNO): uno = int(UNO) db = connect_db() cursor = db.cursor() sql = 'SELECT MONEY FROM USERINFO WHERE UNO = %d' % uno cursor.execute(sql) money = cursor.fetchone()[0] db.close() return money
def load_brief_summary(sno): sno = int(sno) db = connect_db() cursor = db.cursor() cursor.execute("SELECT TITLE,DESCRIPTION,PAYMENT,INST FROM SURVEY,SCHOLAR_OWN_SURVEY,SCHOLAR WHERE " "SURVEY.SNO = SCHOLAR_OWN_SURVEY.SNO AND SCHOLAR_OWN_SURVEY.UNO = SCHOLAR.UNO AND ACCESS = 'owner' " "AND SURVEY.SNO = %d" %sno) l = cursor.fetchone() return {'title':l[0],'description':l[1],'payment':l[2],'inst':l[3]}
def get_scholar_list_from_db(uno=None, subject=None, datatype=None, publicity=None, type=None, order=None, user='******', pwd='123456', onlyforme=True): db = connect_db() cursor = db.cursor() cursor.execute( "SELECT COUNT(*) FROM information_schema.views WHERE table_schema = 'ScholarDB' AND table_name = 'LIST' " ) l = cursor.fetchall() #print l if (l[0][0] == 1): cursor.execute("DROP VIEW LIST") sql = """CREATE VIEW LIST (NO, TYPE, TITLE, DESCRIPTION, PAYMENT, OPENTIME) AS """ if type == 'SURVEY': sql += add_survey_to_scholar_list(uno, subject, user, pwd, onlyforme, publicity) if type == 'TASK': sql += add_task_to_scholar_list(uno, datatype, user, pwd, onlyforme, publicity) if type == "BOTH": sql += add_survey_to_scholar_list( uno, subject, user, pwd, onlyforme, publicity) + " UNION " + add_task_to_scholar_list( uno, datatype, user, pwd, onlyforme, publicity) #print sql cursor.execute(sql) sql = "SELECT TYPE,NO,TITLE,DESCRIPTION,PAYMENT,OPENTIME FROM LIST ORDER BY " + order cursor.execute(sql) list_res = cursor.fetchall() #print list_res res = load_json(list_res) db.commit() db.close() return res
def inform_privacy(sno): sno = int(sno) db = connect_db() cursor = db.cursor() cursor.execute("SELECT WHAT FROM PRIVACY WHERE SNO = %d" % sno) l = cursor.fetchall() ret = [] for tup in l: ret.append(tup[0]) db.close() return ret
def login_mail(MAIL, PWD): db = connect_db() cursor = db.cursor() sql = 'SELECT PWD FROM USERINFO WHERE MAIL = "%s"' % MAIL cursor.execute(sql) l = cursor.fetchall() if not l or l[0][0] != PWD: db.close() return (False, '用户名或者密码不正确') else: db.close() return (True, 'Success')
def get_basic_info(MAIL): db = connect_db() cursor = db.cursor() sql = 'SELECT UNAME,UNO,USERTYPE FROM USERINFO WHERE MAIL = "%s"' % MAIL cursor.execute(sql) try: username, uno, usertype = cursor.fetchall()[0][0:3] db.close() return username, uno, usertype except KeyError: db.close() #print "Not found" return ''
def register(UNAME, PWD, MAIL, AGE, GENDER, NATION, CITY, INST, TTYPE, USERTYPE, MONEY=0): AGE = int(AGE) db = connect_db() cursor = db.cursor() # 邮箱是否已注册? sql = 'SELECT MAIL FROM USERINFO WHERE MAIL ="%s"' % MAIL cursor.execute(sql) if cursor.fetchall(): db.close() return (False, "Mail already registered") sql = 'SELECT MAX(UNO) FROM USERINFO' cursor.execute(sql) max_uno = cursor.fetchall()[0][0] # return ((1000L,),) try: new_uno = max_uno + 1 except TypeError: new_uno = 1000 sql1 = "INSERT INTO USERINFO(UNO,UNAME,MAIL,AGE,USERTYPE,GENDER,NATION,CITY,MONEY,PWD)" \ "VALUES(%d,'%s','%s',%d,'%s','%s','%s','%s',%d,'%s')" % ( new_uno, UNAME, MAIL, AGE, USERTYPE, GENDER, NATION, CITY, 0, PWD) if USERTYPE == 'Scholar': sql2 = "INSERT INTO SCHOLAR(UNO,TYPE,INST,RATE) VALUES(%d,'%s','%s',%d)" % ( new_uno, TTYPE, INST, 1) elif USERTYPE == 'Volunteer': sql2 = "INSERT INTO VOLUNTEER(UNO,CRED) VALUES(%d,%d)" % (new_uno, 100) else: raise ValueError('Unknown usertype: %s' % USERTYPE) #print sql1, sql2 cursor.execute(sql1) #print 1 cursor.execute(sql2) #print 2 # update #if USERTYPE == 'Scholar': # grant_scholar(new_uno,PWD,cursor,db) db.commit() db.close() return True, 'Success'
def load_questions(sno): sno = int(sno) db = connect_db() cursor = db.cursor() sq = SurveyQuestions() cursor.execute("SELECT QNO,TITLE,SUPPLEMENT,SUPPLEMENT_TYPE,TYPE FROM QUESTION WHERE SNO = %d ORDER BY SNO" % sno) tups = cursor.fetchall() if not tups: return None for tup in tups: tup2 = [] if tup[4].startswith("qsc"): cursor.execute("SELECT CONTENT FROM CHOICE WHERE QNO = %d ORDER BY PLACE" % tup[0]) tup2 = cursor.fetchall() sq.append(tup,tup2) return sq
def send_index(max_num, num, tno, uno): db = connect_db() cursor = db.cursor() max_num = int(max_num) num = int(num) uno = int(uno) sql = "SELECT SEND FROM FILE F, FILE_SLICE S WHERE F.FNO = S.FNO AND F.TNO = %s " % tno cursor.execute(sql) res = cursor.fetchall() for i in range(0, max_num): j = i i = (i + 1) % max_num if res[i][0] < res[j][0]: break index = [] for k in range(0, num): index.append(i) i = (i + 1) % max_num sql = "SELECT FNO FROM FILE F WHERE F.TNO = %s " % tno cursor.execute(sql) fno = int(cursor.fetchone()[0]) for k in index: sql = "UPDATE FILE_SLICE,FILE SET SEND = SEND + 1 WHERE FILE_SLICE.FNO = FILE.FNO AND FILE.TNO = %s AND FSNO = %d" % ( tno, k) cursor.execute(sql) sql = "INSERT INTO PARTICIPATION_TASK(FNO,FSNO,UNO,STATUS) VALUES (%d,%d,%d,'unload')" % ( fno, k, uno) cursor.execute(sql) #print "fno",fno #print "tno",tno db.commit() db.close() #print index return index
def load_json(list_res): db = connect_db() cursor = db.cursor() res = [] for tup in list_res: try: dict = { "type": tup[0], "no": tup[1], "title": tup[2], "description": tup[3], "payment": tup[4], "opentime": tup[5] } if tup[0] == 'SURVEY': sql = "SELECT MINAGE,MAXAGE,GENDER_RESTRICT,SURVEY_RESTRICT FROM SURVEY WHERE SNO= %d" % tup[ 1] cursor.execute(sql) l = cursor.fetchall() dict["min_age"] = l[0][0] dict["max_age"] = l[0][1] dict["gender_restrict"] = l[0][2] dict["survey_restrict"] = l[0][3] sql = "SELECT WHAT FROM SURVEY_SUBJECT WHERE SNO = %d" % tup[1] cursor.execute(sql) l = cursor.fetchall() dict["subject1"] = l[0][0] if len(l) >= 1 else "" dict["subject2"] = l[1][0] if len(l) >= 2 else "" dict["subject3"] = l[2][0] if len(l) >= 3 else "" else: sql = "SELECT DATATYPE FROM FILE WHERE TNO =%d" % tup[1] cursor.execute(sql) l = cursor.fetchall() dict["datatype"] = l[0][0] res.append(dict) except Exception: #print 'error at sql_list' pass db.commit() db.close() return res
def add_file_to_db(rawdata,example,tno,datatype,num): db = connect_db() cursor = db.cursor() sql = "INSERT INTO FILE(FNAME,EXAMPLE_NAME,TNO,DATATYPE) VALUES" \ "('%s','%s',%d,'%s')" % (rawdata,example,tno,datatype) print sql cursor.execute(sql) cursor.execute("SELECT MAX(FNO) FROM FILE") fno = cursor.fetchone()[0] for i in range(0,num): sql = "INSERT INTO FILE_SLICE(FNO,FSNO,SEND,RECEIVE) VALUES" \ "(%d,%d,'0','0')" % (fno,i) #print "test____",sql cursor.execute(sql) db.commit() db.close()
def add_survey_to_db(title, detail, questions, user='******', pwd='dbpjdbpj'): # args type - SurveyTitle, SurveyDetail, SurveyQuestion db = connect_db() cursor = db.cursor() # title sql = "INSERT INTO SURVEY(SNO,TITLE,DESCRIPTION,MINAGE,MAXAGE,GENDER_RESTRICT,SURVEY_RESTRICT,PAYMENT,STAGE,OPENTIME,TYPE,MAXNEED) VALUES" \ "(NULL,'%s','%s',%d,%d,'%s','%s',%d,'OPEN','%s','SURVEY',%d)" % ( title.title, title.description, detail.min_age, detail.max_age, detail.gender_restrict, detail.survey_restrict, detail.payment, detail.opentime,detail.maxneed) cursor.execute(sql) cursor.execute("SELECT MAX(SNO) FROM SURVEY") sno = cursor.fetchall()[0][0] cursor.execute("SELECT UNO FROM USERINFO WHERE UNAME = '%s'" % detail.owner) uno = cursor.fetchall()[0][0] sql = "INSERT INTO SCHOLAR_OWN_SURVEY(UNO,SNO,ACCESS) VALUES(%d,%d,'owner')" % (uno, sno) cursor.execute(sql) for subject in title.subject: sql = "INSERT INTO SURVEY_SUBJECT(SNO,WHAT) VALUES(%d,'%s')" % (sno, subject) cursor.execute(sql) for privacy in detail.privacy: sql = "INSERT INTO PRIVACY(SNO,WHAT) VALUES (%d,'%s')" % (sno, privacy) cursor.execute(sql) for question in questions.question_list: sql = "INSERT INTO QUESTION(QNO,SNO,TITLE,SUPPLEMENT,SUPPLEMENT_TYPE,TYPE) VALUES" \ "(NULL,%d,'%s','%s','%s','%s')" % (sno, question.title, question.supplement, question.supplement_type, question.type + " " + question.input_type) cursor.execute(sql) if question.type == 'qsc': cursor.execute("SELECT MAX(QNO) FROM QUESTION") qno = cursor.fetchall()[0][0] for i, choice in enumerate(question.choice): sql = "INSERT INTO CHOICE(CNO,PLACE,QNO,CONTENT) VALUES(NULL,%d,%d,'%s')" % (i, qno, choice) cursor.execute(sql) db.commit() db.close()
def load_from_db(self,tno): db = connect_db() cursor = db.cursor() cursor.execute('SELECT TITLE,DESCRIPTION,OPENTIME,PAYMENT FROM TASK WHERE TNO = %d' % tno) self.title, self.description, self.opentime, self.payment = cursor.fetchone()[0:4] db.close()
def load_json(list_res): db = connect_db() cursor = db.cursor() res = [] for tup in list_res: dict = { "type": tup[0], "no": tup[1], "title": tup[2], "description": tup[3], "payment": tup[4], "opentime": tup[5] } if tup[0] == 'SURVEY': sql = "SELECT MINAGE,MAXAGE,GENDER_RESTRICT,SURVEY_RESTRICT FROM SURVEY WHERE SNO= %d" % tup[ 1] cursor.execute(sql) l = cursor.fetchall() dict["min_age"] = l[0][0] dict["max_age"] = l[0][1] dict["gender_restrict"] = l[0][2] dict["survey_restrict"] = l[0][3] sql = "SELECT COUNT(*) FROM PARTICIPATION WHERE SNO = %d" % tup[1] cursor.execute(sql) dict["num"] = cursor.fetchone()[0] #added by AuCson 0428: PUBLICITY: sql = "SELECT PUBLICITY FROM PUBLICITY_SURVEY WHERE SNO = %d" % tup[ 1] cursor.execute(sql) try: dict["publicity"] = cursor.fetchone()[0] except TypeError: dict['publicity'] = '' #end add sql = "SELECT WHAT FROM SURVEY_SUBJECT WHERE SNO = %d" % tup[1] cursor.execute(sql) l = cursor.fetchall() #print l dict["subject1"] = l[0][0] if len(l) >= 1 else "" dict["subject2"] = l[1][0] if len(l) >= 2 else "" dict["subject3"] = l[2][0] if len(l) >= 3 else "" else: #added by Aucson 0428 sql = "SELECT PUBLICITY FROM PUBLICITY_TASK WHERE TNO = %d" % tup[1] cursor.execute(sql) try: dict["publicity"] = cursor.fetchone()[0] except TypeError: dict['publicity'] = '' #end add sql = "SELECT DATATYPE,SUM(SEND),SUM(RECEIVE),COUNT(FSNO) FROM FILE F,FILE_SLICE S WHERE TNO =%d AND F.FNO=S.FNO GROUP BY F.FNO" % tup[ 1] #print sql cursor.execute(sql) l = cursor.fetchall() if l: dict["datatype"] = l[0][0] dict["num"] = l[0][1] dict["now"] = l[0][2] dict["slice"] = l[0][3] res.append(dict) db.commit() db.close() return res