def get_user_information(data): if KEY.ACCOUNT not in data: return None find_id = "select id from account where account = %d" user_id = dbhelper.execute_fetchone(find_id%(data[KEY.ACCOUNT])) sql = "select * from user where id = %d" try: res = dbhelper.execute_fetchone(sql%(user_id[0])) if res is None: return None else: user = {} user[KEY.ID] = res[0] user[KEY.NAME] = res[1] user[KEY.NICKNAME] = res[2] user[KEY.GENDER] = res[3] user[KEY.AGE] = res[4] user[KEY.PHONE] = res[5] user[KEY.LOCATION] = res[6] user[KEY.LONGITUDE] = float(res[7]) user[KEY.LATITUDE] = float(res[8]) user[KEY.OCCUPATION] = res[9] user[KEY.REPUTATION] = float(res[10]) #user[KEY.AVATAR] = res[11] user[KEY.IDENTITY_ID] = res[12] user[KEY.IS_VERIFY] = res[14] return user except: return None
def get_event_supporter(data): supporter_list = [] if KEY.ID not in data or KEY.EVENT_ID not in data: return support_list # 1. find supporters id in table 'support_relation' by launcher id and event id find_id_sql = "select supporter from support_relation where supportee = %d and event_id = %d" id_result = dbhelper.execute_fetchall(find_id_sql%(data[KEY.ID], data[KEY.EVENT_ID])) # 2. find accounts from table 'account' by ids find_account_sql = "select account from account where id = %d" if id_result is not None: for each_id in id_result: supporter_result = dbhelper.execute_fetchone(find_account_sql%(each_id[0])) print each_id supporter = {} supporter[KEY.ACCOUNT] = supporter_result[0] user_info = get_user_information(supporter) # get current location of supporter find_longitude_sql = "select longitude from support_relation where event_id = %d and supporter = %d" find_latitude_sql = "select latitude from support_relation where event_id = %d and supporter = %d" longitude_result = dbhelper.execute_fetchone(find_longitude_sql%(data[KEY.EVENT_ID], each_id[0])) latitude_result = dbhelper.execute_fetchone(find_latitude_sql%(data[KEY.EVENT_ID], each_id[0])) if user_info is not None: supporter.update(user_info) supporter[KEY.ACCOUNT] = supporter_result[0] supporter[KEY.LONGITUDE] = float(longitude_result[0]) supporter[KEY.LATITUDE] = float(latitude_result[0]) supporter_list.append(supporter) #supporter_list.append(supporter_result[0]) return supporter_list
def user_event_manage(data): if KEY.ID not in data or KEY.EVENT_ID not in data: return False if KEY.OPERATION not in data: return True if data[KEY.OPERATION] < 0 or data[KEY.OPERATION] > 2: return False sql = "select launcher from event where id = %d" launcher_id = None try: sql_result = dbhelper.execute_fetchone(sql%(data[KEY.EVENT_ID])) if sql_result is not None: launcher_id = sql_result[0] except: pass if launcher_id is None: return False if data[KEY.OPERATION] == 0: sql = "delete from support_relation where event_id = %d and supporter = %d"%(data[KEY.EVENT_ID], data[KEY.ID]) update_event_sql = "update event set support_number = support_number-1 where id = %d"%(data[KEY.EVENT_ID]) else: sql = "insert into support_relation (event_id, supportee, supporter, type, time) values (%d, %d, %d, %d, now())"%(data[KEY.EVENT_ID], launcher_id, data[KEY.ID], data[KEY.OPERATION]) update_event_sql = "update event set support_number = support_number+1 where id = %d"%(data[KEY.EVENT_ID]) count_supporter = "select count(*) from support_relation where event_id = %d and supportee = %d"%(data[KEY.EVENT_ID], launcher_id) help_max = "select help_max from event where id = %d and launcher = %d"%(data[KEY.EVENT_ID], launcher_id) state_sql = "select state from event where id = %d and launcher = %d"%(data[KEY.EVENT_ID], launcher_id) update_state = "update event set state = %d where id = %d" try: # update support_relation p = dbhelper.execute(sql) print "From user_event_manage_handler: attend result: %d"%p if p == 0: return False # update event information dbhelper.execute(update_event_sql) # Check if people are enough for helping supporters_result = dbhelper.execute_fetchone(count_supporter) help_max_result = dbhelper.execute_fetchone(help_max) state_result = dbhelper.execute_fetchone(state_sql) if supporters_result[0] == help_max_result[0] and state_result[0] == 0: # update event state to "supporter enough" dbhelper.execute(update_state%(2, data[KEY.EVENT_ID])) print "From db update help event:", supporters_result[0], help_max_result[0] elif supporters_result[0] < help_max_result[0] and state_result[0] == 2: # update event state to "ing" dbhelper.execute(update_state%(0, data[KEY.EVENT_ID])) except: return False # # trust and reputation compute here. # return True
def calculate_number_of_login_weekly(): result = [] result.append(calculate_number_of_login()) sql = "select count(*) from sign_in where to_days(now())-to_days(time) <= %d" try: for num in range(0, 6): temp1 = dbhelper.execute_fetchone(sql % (num))[0] temp2 = dbhelper.execute_fetchone(sql % (num + 1))[0] result.append(temp2 - temp1) return result except: return None
def evaluate_user(data): if KEY.ID not in data or KEY.USER_ACCOUNT not in data or KEY.EVENT_ID not in data: return False if KEY.ATTITUDE not in data or KEY.SKILL not in data or KEY.SATISFY not in data: return False # get a new average reputation value value = 0.0 value = data[KEY.ATTITUDE] + data[KEY.SKILL] + data[KEY.SATISFY] value /= 3.0 # get type of the event get_type = "select type from event where id = %d"%data[KEY.EVENT_ID] type_result = dbhelper.execute_fetchone(get_type) event_type = type_result[0] result = 0 # split user_account string into long array user_account = data[KEY.USER_ACCOUNT].split(",") # update each user's reputation in list for each_account in user_account: if (long(each_account) != 0): # get id by account find_user_id = "select id from account where account = %d" user_id = dbhelper.execute_fetchone(find_user_id%(long(each_account))) print "From evaluate_handler: %ld" % long(each_account) # update a record in table 'evaluation' sql = "replace into evaluation (event_id, `from`, `to`, value, time, comment) values (%d, %d, %d, %f, now(), '%s')" # get an updated reputation value from table 'evaluation' get_final_value = "select AVG(value) from evaluation where `to` = %d" # update evaluatee's reputation in table 'user' update_repu = "update user set reputation = %d where id = %d" # update love coins in table 'loving_bank' if event_type == 0: update_coin = "update loving_bank set coin = coin+1 where id = %d" if event_type == 1: update_coin = "update loving_bank set coin = coin+2 where id = %d" if event_type == 2: update_coin = "update loving_bank set coin = coin+3 where id = %d" try: a = dbhelper.execute(sql%(data[KEY.EVENT_ID], data[KEY.ID], user_id[0], value, data[KEY.ASSESS])) final_value = dbhelper.execute_fetchone(get_final_value%(user_id[0])) dbhelper.execute(update_repu%(final_value[0], user_id[0])) dbhelper.execute(update_coin%(data[KEY.EVENT_ID])) result += 1 except: pass return result
def get_event_information(data): if KEY.EVENT_ID not in data: return None event_info = None sql = "select * from event where id = %d" try: sql_result = dbhelper.execute_fetchone(sql % (data[KEY.EVENT_ID])) if sql_result is not None: event_info = {} event_info[KEY.EVENT_ID] = sql_result[0] event_info[KEY.LAUNCHER_ID] = sql_result[1] event_info[KEY.CONTENT] = sql_result[2] event_info[KEY.TYPE] = sql_result[3] event_info[KEY.TIME] = str(sql_result[4]) event_info[KEY.LONGITUDE] = float(sql_result[5]) event_info[KEY.LATITUDE] = float(sql_result[6]) event_info[KEY.STATE] = sql_result[7] event_info[KEY.FOLLOW_NUMBER] = sql_result[8] event_info[KEY.SUPPORT_NUMBER] = sql_result[9] event_info[KEY.GROUP_PTS] = float(sql_result[10]) user = {} user[KEY.ID] = event_info[KEY.LAUNCHER_ID] user = get_user_information(user) if user is not None: event_info[KEY.LAUNCHER] = user[KEY.NICKNAME] except: pass finally: return event_info
def get_user_information(data): if KEY.ID not in data: if KEY.PHONE not in data: return None else: sql = "select * from user where phone = %s"%(data[KEY.PHONE]) else: sql = "select * from user where id = %d"%(data[KEY.ID]) try: res = dbhelper.execute_fetchone(sql) if res is None: return None else: user = {} user[KEY.ID] = res[0] user[KEY.NAME] = res[1] user[KEY.NICKNAME] = res[2] user[KEY.GENDER] = res[3] user[KEY.AGE] = res[4] user[KEY.PHONE] = res[5] user[KEY.LOCATION] = res[6] user[KEY.LONGITUDE] = float(res[7]) user[KEY.LATITUDE] = float(res[8]) user[KEY.OCCUPATION] = res[9] user[KEY.REPUTATION] = float(res[10]) user[KEY.IDENTITY_ID] = res[12] user[KEY.IS_VERIFY] = res[14] return user except: return None
def get_event_information(data): if KEY.EVENT_ID not in data: return None event_info = None sql = "select * from event where id = %d" try: sql_result = dbhelper.execute_fetchone(sql%(data[KEY.EVENT_ID])) if sql_result is not None: event_info = {} event_info[KEY.EVENT_ID] = sql_result[0] event_info[KEY.LAUNCHER_ID] = sql_result[1] event_info[KEY.TITLE] = sql_result[2] event_info[KEY.CONTENT] = sql_result[3] event_info[KEY.TYPE] = sql_result[4] event_info[KEY.TIME] = str(sql_result[5]) event_info[KEY.LAST_TIME] = str(sql_result[6]) event_info[KEY.LONGITUDE] = float(sql_result[7]) event_info[KEY.LATITUDE] = float(sql_result[8]) event_info[KEY.STATE] = sql_result[9] event_info[KEY.FOLLOW_NUMBER] = sql_result[10] event_info[KEY.SUPPORT_NUMBER] = sql_result[11] event_info[KEY.GROUP_PTS] = float(sql_result[12]) event_info[KEY.DEMAND_NUMBER] = sql_result[13] event_info[KEY.LOVE_COIN] = sql_result[14] event_info[KEY.COMMENT] = sql_result[15] event_info[KEY.LOCATION] = sql_result[16] user = {} user[KEY.ID] = event_info[KEY.LAUNCHER_ID] user = get_user_information(user) if user is not None: event_info[KEY.LAUNCHER] = user[KEY.NICKNAME] except: pass finally: return event_info
def user_event_manage(data): if KEY.ID not in data or KEY.EVENT_ID not in data: return False if KEY.OPERATION not in data: return True if data[KEY.OPERATION] < 0 or data[KEY.OPERATION] > 2: return False sql = "select launcher from event where id = %d" launcher_id = None try: sql_result = dbhelper.execute_fetchone(sql%(data[KEY.EVENT_ID])) if sql_result is not None: launcher_id = sql_result[0] except: pass if launcher_id is None: return False if data[KEY.OPERATION] == 0: sql = "delete from support_relation where event_id = %d and supporter = %d"%(data[KEY.EVENT_ID], data[KEY.ID]) else: sql = "replace into support_relation (event_id, supportee, supporter, type, time) values (%d, %d, %d, %d, now())"%(data[KEY.EVENT_ID], launcher_id, data[KEY.ID], data[KEY.OPERATION]) try: dbhelper.execute(sql) except: return False # # trust and reputation compute here. # return True
def get_event_information(data): if KEY.EVENT_ID not in data: return None event_info = None sql = "select * from event where id = %d" try: sql_result = dbhelper.execute_fetchone(sql%(data[KEY.EVENT_ID])) if data[KEY.EVENT_ID] == 526: print "search for EVENT_ID = 526" if sql_result is not None: event_info = {} event_info[KEY.EVENT_ID] = sql_result[0] event_info[KEY.LAUNCHER_ID] = sql_result[1] event_info[KEY.CONTENT] = sql_result[2] event_info[KEY.TYPE] = sql_result[3] event_info[KEY.TIME] = str(sql_result[4]) event_info[KEY.LONGITUDE] = float(sql_result[5]) event_info[KEY.LATITUDE] = float(sql_result[6]) event_info[KEY.STATE] = sql_result[7] event_info[KEY.FOLLOW_NUMBER] = sql_result[8] event_info[KEY.SUPPORT_NUMBER] = sql_result[9] event_info[KEY.GROUP_PTS] = float(sql_result[10]) event_info[KEY.MAX_PEOPLE] = sql_result[11] user = {} user[KEY.ID] = event_info[KEY.LAUNCHER_ID] user = get_user_information(user) if user is not None: event_info[KEY.LAUNCHER] = user[KEY.NICKNAME] except: pass finally: return event_info
def user_event_manage(data): if KEY.ID not in data or KEY.EVENT_ID not in data: return False if KEY.OPERATION not in data: return True if data[KEY.OPERATION] < 0 or data[KEY.OPERATION] > 2: return False sql = "select launcher from event where id = %d" launcher_id = None try: sql_result = dbhelper.execute_fetchone(sql % (data[KEY.EVENT_ID])) if sql_result is not None: launcher_id = sql_result[0] except: pass if launcher_id is None: return False if data[KEY.OPERATION] == 0: sql = "delete from support_relation where event_id = %d and supporter = %d" % ( data[KEY.EVENT_ID], data[KEY.ID]) else: sql = "replace into support_relation (event_id, supportee, supporter, type, time) values (%d, %d, %d, %d, now())" % ( data[KEY.EVENT_ID], launcher_id, data[KEY.ID], data[KEY.OPERATION]) try: dbhelper.execute(sql) except: return False # # trust and reputation compute here. # return True
def calculate_number_of_login(): sql = "select count(*) from sign_in where to_days(time) = to_days(now())" try: result = dbhelper.execute_fetchone(sql)[0] return result except: return -1
def calculate_number_of_user(): sql = "select count(*) from user" try: result = dbhelper.execute_fetchone(sql)[0] return result except: return -1
def get_comment_info(data): if KEY.COMMENT_ID not in data: return None sql = "select event_id, author, content, time, parent_author from comment where id = %d" comment_info = None try: sql_result = dbhelper.execute_fetchone(sql%(data[KEY.COMMENT_ID])) if sql_result is not None: comment_info = {} comment_info[KEY.COMMENT_ID] = data[KEY.COMMENT_ID] comment_info[KEY.EVENT_ID] = sql_result[0] comment_info[KEY.AUTHOR_ID] = sql_result[1] comment_info[KEY.CONTENT] = sql_result[2] comment_info[KEY.TIME] = str(sql_result[3]) comment_info[KEY.PARENT_AUTHOR_ID] = sql_result[4] comment_info[KEY.AUTHOR] = None comment_info[KEY.PARENT_AUTHOR] = None user = {} user[KEY.ID] = comment_info[KEY.AUTHOR_ID] user = get_user_information(user) if user is not None: comment_info[KEY.AUTHOR] = user[KEY.NICKNAME] user = {} user[KEY.ID] = comment_info[KEY.PARENT_AUTHOR_ID] user = get_user_information(user) if user is not None: comment_info[KEY.PARENT_AUTHOR] = user[KEY.NICKNAME] except: pass finally: return comment_info
def get_user_information(data): if KEY.ID not in data: return None sql = "select * from user where id = %d" try: res = dbhelper.execute_fetchone(sql % (data[KEY.ID])) if res is None: return None else: user = {} user[KEY.ID] = res[0] user[KEY.NAME] = res[1] user[KEY.NICKNAME] = res[2] user[KEY.GENDER] = res[3] user[KEY.AGE] = res[4] user[KEY.PHONE] = res[5] user[KEY.LOCATION] = res[6] user[KEY.LONGITUDE] = float(res[7]) user[KEY.LATITUDE] = float(res[8]) user[KEY.OCCUPATION] = res[9] user[KEY.REPUTATION] = float(res[10]) user[KEY.IDENTITY_ID] = res[12] user[KEY.IS_VERIFY] = res[14] return user except: return None
def get_supporter_num_from_event(eventId): sql = "select count(*) from support_relation where event_id = %d" try: result = dbhelper.execute_fetchone(sql % (eventId))[0] return result except: return None
def get_love_coin(data): if KEY.ID not in data: return -1 sql = "select coin from loving_bank where user_id = %d"%data[KEY.ID] try: result = dbhelper.execute_fetchone(sql) return result[0] except: return -1
def get_user_id(data): if KEY.USER_ACCOUNT not in data: return -1 sql = "select id from account where account = %d"%data[KEY.USER_ACCOUNT] sql_result = dbhelper.execute_fetchone(sql) if sql_result is not None: return sql_result[0] else: return -1
def get_coin_from_event(eventId): sql = "select love_coin from event where id = %d" try: result = dbhelper.execute_fetchone(sql % (eventId))[0] return result except: return None
def get_db_avatar(data): if KEY.ID not in data: return "" sql = "select avatar from user where id = %d" try: result = dbhelper.execute_fetchone(sql%(data[KEY.ID])) print "From database - get db avatar: user id - '%d', avatar - '%s'"%(data[KEY.ID], result[0]) return result[0] except: return ""
def upload_health(data): if KEY.ID not in data: return False sql = "select * from health where user_id = %d"%data[KEY.ID] res = dbhelper.execute_fetchone(sql) if res is not None: return update_health_record(data) else: if health_record(data) > 0: return True return False
def get_salt(data): if KEY.ACCOUNT not in data: return None sql = "select salt from account where account = '%s'" try: res = dbhelper.execute_fetchone(sql%(data[KEY.ACCOUNT])) if res is None: return None else: return res[0] except: return None
def get_user_evaluate(data): if KEY.ID not in data or KEY.EVENT_ID not in data: return None evaluate = {} sql = "select value, comment from evaluation where event_id = %d and `to` = %d" sql_result = dbhelper.execute_fetchone(sql%(data[KEY.EVENT_ID], data[KEY.ID])) if sql_result is not None: evaluate[KEY.VALUE] = float(sql_result[0]) evaluate[KEY.COMMENT] = sql_result[1] return evaluate else: return None
def get_salt(data): if KEY.ACCOUNT not in data: return None sql = "select salt from account where account = '%s'" try: res = dbhelper.execute_fetchone(sql % (data[KEY.ACCOUNT])) if res is None: return None else: return res[0] except: return None
def is_sign_in(user_id): result = False sql = "select count(*) from sign_in where user_id = %d and to_days(time) = to_days(now())" try: sql_result = dbhelper.execute_fetchone(sql % (user_id))[0] if sql_result > 0: result = True else: result = False except: result = False finally: return result
def get_push_token(data): if data == []: return None sql = "select push_token from account where id = %d" token_list = [] for user_id in data: token_result = dbhelper.execute_fetchone(sql%(user_id)) if token_result is not None: token_list.append(token_result[0]) print "From database - get push token function: " print token_list return token_list
def is_sign_in(user_id): result = False sql = "select count(*) from sign_in where user_id = %d and to_days(time) = to_days(now())" try: sql_result = dbhelper.execute_fetchone(sql%(user_id))[0] if sql_result > 0: result = True else: result = False except: result = False finally: return result
def remove_static_relation(data): if KEY.ID not in data or KEY.USER_ACCOUNT not in data or KEY.TYPE not in data: return False find_user_id = "select id from account where account = %d" user_id = dbhelper.execute_fetchone(find_user_id%data[KEY.USER_ACCOUNT]) sql = "delete from static_relation where user_a = %d and user_b = %d and type = %d" try: n = dbhelper.execute(sql%(data[KEY.ID], user_id[0], data[KEY.TYPE])) if n > 0: return True else: return False except: return False
def get_events(data, get_event_id_list): event_id_list = get_event_id_list(data) event_list = [] sql = "select nickname from user where id = %d" for event_id in event_id_list: event_info = {} event_info[KEY.EVENT_ID] = event_id event_info = get_event_information(event_info) if event_info is not None: # find nickname of id launcher_nickname = dbhelper.execute_fetchone(sql%(event_info[KEY.LAUNCHER_ID])) event_info[KEY.LAUNCHER] = launcher_nickname[0] event_list.append(event_info) return event_list
def add_static_relation(data): if KEY.ID not in data or KEY.USER_ACCOUNT not in data or KEY.TYPE not in data: return False find_user_id = "select id from account where account = %d" user_id = dbhelper.execute_fetchone(find_user_id%data[KEY.USER_ACCOUNT]) #sql = "replace into static_relation (user_a, user_b, type, time) values (%d, %d, %d, now())" sql = "insert into static_relation (user_a, user_b, type, time) values (%d, %d, %d, now())" try: n = dbhelper.execute(sql%(data[KEY.ID], user_id[0], data[KEY.TYPE])) if n > 0: return True else: return False except: return False
def get_friend(data): if KEY.HOST_NAME not in data: return None event_info = None sql = "select host_name from event where guest_name = %s and host_name = %s" try: sql_result = dbhelper.execute_fetchone(sql%(data[KEY.GUEST_NAME],data[KEY.HOST_NAME])) if sql_result is not None: friend_info = {} friend_info[KEY.HOST_NAME] = sql_result[0] friend_info[KEY.GUEST_NAME] = sql_result[1] except: pass finally: return friend_info
def get_illness_record(record_id): sql = "select id, user_id, content, time from illness where id = %d" record = None try: sql_result = dbhelper.execute_fetchone(sql%(record_id)) if sql_result is not None: record = {} record[KEY.ILLNESS_ID] = sql_result[0] record[KEY.USER_ID] = sql_result[1] record[KEY.CONTENT] = sql_result[2] record[KEY.TIME] = str(sql_result[3]) except: record = None finally: return record
def get_illness_record(record_id): sql = "select id, user_id, content, time from illness where id = %d" record = None try: sql_result = dbhelper.execute_fetchone(sql % (record_id)) if sql_result is not None: record = {} record[KEY.ILLNESS_ID] = sql_result[0] record[KEY.USER_ID] = sql_result[1] record[KEY.CONTENT] = sql_result[2] record[KEY.TIME] = str(sql_result[3]) except: record = None finally: return record
def get_health_record(record_id): sql = "select id, user_id, type, value, time from health where id = %d" record = None try: sql_result = dbhelper.execute_fetchone(sql % (record_id)) if sql_result is not None: record = {} record[KEY.HEALTH_ID] = sql_result[0] record[KEY.USER_ID] = sql_result[1] record[KEY.TYPE] = sql_result[2] record[KEY.VALUE] = float(sql_result[3]) record[KEY.TIME] = str(sql_result[4]) except: record = None finally: return record
def get_user_loving_bank(data): if KEY.USER_ID not in data: return None sql = "select score_rank, love_coin from loving_bank where userid = %d" try: res = dbhelper.execute_fetchone(sql%(data[KEY.USER_ID])) if res is None: return None else: bank_info = {} bank_info[KEY.ID] = data[KEY.USER_ID] bank_info[KEY.SCORE] = res[0] bank_info[KEY.LOVE_COIN] = res[1] return bank_info except: return None
def get_user_current_location(data): location = {} if KEY.ID not in data: return location sql = "select longitude, latitude from user where id = %d"%(data[KEY.ID]) try: location_result = dbhelper.execute_fetchone(sql) location[KEY.LONGITUDE] = location_result[0] location[KEY.LATITUDE] = location_result[1] print "From database - get user current location:" print location return location except: return location
def get_sign(): if KEY.ID not in data: return None sql = "select * from sign where sign_name = %s" try: res = dbhelper.execute_fetchone(sql%(data[KEY.BANK_NAME])) if res is None: return None else: user = {} user[KEY.SIGN_ID] = res[0] user[KEY.SIGN_NAME] = res[1] user[KEY.SIGN_HISTORY] = res[2] user[KEY.SIGN_TIME] = res[3] return user except: return None
def get_bank(data): if KEY.ID not in data: return None sql = "select * from bank where bank_name = %s" try: res = dbhelper.execute_fetchone(sql%(data[KEY.BANK_NAME])) if res is None: return None else: user = {} user[KEY.BANK_ID] = res[0] user[KEY.BANK_NAME] = res[1] user[KEY.GRADE] = res[2] user[KEY.COIN] = res[3] return user except: return None
def get_question_information(data): if KEY.QUESTION_ID not in data: return None event_info = None sql = "select question_id from question where question_time = %s" try: sql_result = dbhelper.execute_fetchone(sql%(data[KEY.QUESTION_TIME])) if sql_result is not None: question_info = {} question_info[KEY.QUESTION_ID] = sql_result[0] question_info[QUESTION_CONTENT] = sql_result[1] question_info[QUESTION_TYPE] = sql_result[2] question_info[QUESTION_TIME] = sql_result[3] except: pass finally: return question_info
def get_answer(data): if KEY.ANSWER_ID not in data: return None event_info = None sql = "select answer_id from answer where question_id = %s" try: sql_result = dbhelper.execute_fetchone(sql%(data[KEY.QUESTION_ID])) if sql_result is not None: question_info = {} question_info[KEY.ANSWER_ID] = sql_result[0] question_info[ANSWER_CONTENT] = sql_result[1] question_info[ANSWER_TYPE] = sql_result[2] question_info[ANSWER_TIME] = sql_result[3] except: pass finally: return question_info
def get_all_event_list(data): event_id_list = [] sql = "select id from event where id = %d" if KEY.STATE in data: if data[KEY.STATE] == 0 or data[KEY.STATE] == 1: sql += " and state = %d"%data[KEY.STATE] if KEY.TYPE in data: if data[KEY.TYPE] >= 0 and data[KEY.TYPE] <= 2: sql += " and type = %d"%data[KEY.TYPE] sql += " order by time DESC" for event_id in data[KEY.EVENT_LIST]: result = dbhelper.execute_fetchone(sql%event_id) if result is not None: event_id_list.append(result[0]) event_id_list = event_id_list[::-1] print "From database - get all events function: id list:" print event_id_list return event_id_list
def validate_password(data): if KEY.ACCOUNT not in data or KEY.PASSWORD not in data or KEY.SALT not in data: return -1 sql = "select id, password from account where account = '%s' and salt = '%s'" user_id = -1 password = None try: res = dbhelper.execute_fetchone(sql % (data[KEY.ACCOUNT], data[KEY.SALT])) if res is not None: user_id = res[0] password = res[1] except: pass finally: if password is None or data[KEY.PASSWORD] is None: return -1 elif password == data[KEY.PASSWORD]: return user_id else: return -1
def user_consume_coin(): result = 0 sql = "select love_coin from event" try: sql_result = dbhelper.execute_fetchall(sql) for each_result in sql_result: result += each_result[0] except: return None sql_event_num = "select count(*) from event" try: event_num = dbhelper.execute_fetchone(sql_event_num)[0] except: return None if event_num == 0: return 0 else: return ('%.2f' % (result / event_num))
def get_comment_info(data): if KEY.COMMENT_ID not in data: return None sql = "select event_id, author, content, time from comment where id = %d" comment_info = None try: sql_result = dbhelper.execute_fetchone(sql % (data[KEY.COMMENT_ID])) if sql_result is not None: comment_info = {} comment_info[KEY.COMMENT_ID] = data[KEY.COMMENT_ID] comment_info[KEY.EVENT_ID] = sql_result[0] comment_info[KEY.AUTHOR_ID] = sql_result[1] comment_info[KEY.CONTENT] = sql_result[2] comment_info[KEY.TIME] = str(sql_result[3]) user = {} user[KEY.ID] = comment_info[KEY.AUTHOR_ID] user = get_user_information(user) if user is not None: comment_info[KEY.AUTHOR] = user[KEY.NICKNAME] except: pass finally: return comment_info