def query_followers(username): """ 请求关注/粉丝数量 :param username: 用户名 :return: """ db_manager = DBManager() cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor) sql = "select count(*) as follows from `%s` where username='******' and relation_type=0" % ("tb_relation", username) cursor_0.execute(sql) result = cursor_0.fetchone() cursor_0.close() if result is None: follows_num = 0 else: follows_num = result['follows'] cursor_1 = db_manager.conn_r.cursor(cursorclass=DictCursor) sql1 = "select count(*) as fans from `%s` where other_username='******' and relation_type=0" % ("tb_relation", username) cursor_1.execute(sql1) result1 = cursor_1.fetchone() cursor_1.close() if result1 is None: fans_num = 0 else: fans_num = result1['fans'] db_manager.close() return True, follows_num, fans_num
def feedback(username, content, feed_time): """ 意见反馈 :param username: 用户名 :param content: 反馈内容 :param feed_time: 提交反馈的时间 :return: """ db_manager = DBManager() props = dict() props['username'] = username props['content'] = content props['feed_time'] = feed_time sql = forEachPlusInsertProps("tb_feedback", props) try: cursor_0 = db_manager.conn_r.cursor() cursor_0.execute(sql) db_manager.conn_r.commit() cursor_0.close() except Exception: db_manager.conn_r.rollback() cursor_0.close() db_manager.close() raise Exception return False return True
def get_latest_id(username, is_new=False): """ 根据用户名获取tb_account中对应主键ID :param username: 用户名 :param is_new: 是否是注册用户 :return: """ db_manager = DBManager() if not is_new: sql0 = "select id from `%s` where username='******'" % ("tb_account", username) cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor0.execute(sql0) result0 = cursor0.fetchone() cursor0.close() if result0: return result0['id'] sql1 = "SELECT Auto_increment FROM information_schema.tables WHERE table_schema = 'Question_Answer_Platform' " \ "and table_name='%s'" % "tb_account" cursor1 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor1.execute(sql1) result1 = cursor0.fetchone() cursor1.close() db_manager.close() return result1['Auto_increment']
def query_user_points_detail(username): """ 请求用户积分详情 :param username: 用户名 :return: """ db_manager = DBManager() # 拿到模板数据 sql0 = "select *from `%s` where username='******'" % ("tb_user", username) cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor0.execute(sql0) result0 = cursor0.fetchone() cursor0.close() if result0 is None or len(result0) == 0: return False, None identifier = result0['identifier'] if identifier == 0: score_rule_list = [1, 2, 6, 7] elif identifier == 1: score_rule_list = [3, 4, 6, 7] cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor) sql = "select user_operation_type, user_operation_desc, point_value from `%s` as u right " \ "join `%s` as s on u.point_type=s.user_operation_type and username='******'" % ("tb_user_points", "tb_score_rule_template", username) msg = "[in query_user_points_detail] sql=" + sql logging.info(msg) cursor_0.execute(sql) result = cursor_0.fetchall() cursor_0.close() db_manager.close() if result is None: return False, None data = [] total_points = 0 for item in result: tmp = dict() if item['point_value'] is None and item['user_operation_type'] in score_rule_list: tmp['point_type'] = item['user_operation_type'] tmp['point_desc'] = item['user_operation_desc'] tmp['point_value'] = 0 data.append(tmp) elif item['point_value'] is not None: tmp['point_type'] = item['user_operation_type'] tmp['point_desc'] = item['user_operation_desc'] tmp['point_value'] = item['point_value'] total_points += tmp['point_value'] data.append(tmp) return True, { "total_points": total_points, "point_detail": data }
def about_us(username): """ 关于我们 :param username: 用户名 :return: """ db_manager = DBManager() cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor) sql = "select content from `%s`" % "tb_about_us_template" cursor_0.execute(sql) result = cursor_0.fetchone() cursor_0.close() db_manager.close() if result is None: return False, "" return True, result
def get_latest_id(table_name): """ 根据用户名获取数据表对应主键ID :param table_name: 数据表名 :return: """ db_manager = DBManager() sql1 = "SELECT Auto_increment FROM information_schema.tables WHERE table_schema = 'Question_Answer_Platform' " \ "and table_name='%s'" % table_name msg = "[in get_latest_id(dbQuestion)] sql1=" + sql1 logging.info(msg) cursor1 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor1.execute(sql1) result1 = cursor1.fetchone() cursor1.close() db_manager.close() return result1['Auto_increment']
def valid_email(email_address): """ 验证邮箱是否唯一 :param email_address: 邮箱地址 :return: """ db_manager = DBManager() cursor_0 = db_manager.conn_r.cursor() # 判断邮箱地址是否存在 sql = "select * from `%s` where username='******'" % ("tb_account", email_address) cursor_0.execute(sql) result = cursor_0.fetchone() cursor_0.close() db_manager.close() if result: return False, "邮箱已被注册" return True, ""
def query_collection_question_list(username): """ 请求用户收藏问题列表 :param username: 用户名 :return: """ db_manager = DBManager() sql = "select tc.question_id, tq.question_content, tq.question_pic_url, tq.question_sound_url, (select count(*) from `tb_answer` where " \ "question_id=tc.question_id) as counts from `tb_question_collection` as tc inner join `tb_question` as tq on " \ "tc.question_id=tq.question_id and tc.collecter_username='******';" % username cursor = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor.execute(sql) results = cursor.fetchall() cursor.close() db_manager.close() if results is None or len(results) == 0: return False, None return True, results
def reset_user_password(user_name, email): """ 用户重置密码 :param user_name: 用户名 :param email: 邮箱 :return: """ recipient = email subject = "reset password" # 重新生成8位长度的密码 from tool.util import random_str new_password = random_str() content = "您的新密码: " + new_password + "\n请在24小时内修改默认密码!" print recipient, subject, content if recipient and subject and content: from lib.email import mailnotify mbres = mailnotify.send_notifymail(recipient, subject, content) if not mbres: return False, "发送失败" else: return False, "请求参数不正确" # 邮件发送成功 # 更新用户密码, 添加用户重置记录 db_manager = DBManager() password = hashlib.sha224(new_password).hexdigest() sql0 = "update `%s` set password='******' where username='******'" % ("tb_account", password, user_name) cursor0 = db_manager.conn_r.cursor() try: cursor0.execute(sql0) db_manager.conn_r.commit() cursor0.close() except Exception: db_manager.conn_r.rollback() cursor0.close() db_manager.close() raise Exception return False, "密码更新失败" sql1 = "insert into `%s` (username, reset_time) values ('%s', '%s')" % ("tb_reset", user_name, time_now_str()) cursor1 = db_manager.conn_r.cursor() try: cursor1.execute(sql1) db_manager.conn_r.commit() cursor1.close() except Exception: db_manager.conn_r.rollback() cursor1.close() db_manager.close() raise Exception return False, "添加重置记录失败" db_manager.close() is_success = True result = dict() result['new_password'] = new_password return is_success, result
def connect_question(username, question_id): """ 收藏问题 :param username: 用户名 :param question_id: 问题ID :return: """ db_manager = DBManager() # 问题是否存在 sql0 = "select *from `%s` where question_id=%s" % ("tb_question", question_id) cursor0 = db_manager.conn_r.cursor() cursor0.execute(sql0) result0 = cursor0.fetchone() cursor0.close() if result0 is None: return False, "问题不存在" # 查询是否收藏过该问题 sql0 = "select *from `%s` where collecter_username='******' and question_id=%s" % ("tb_question_collection", username, question_id) cursor0 = db_manager.conn_r.cursor() cursor0.execute(sql0) result0 = cursor0.fetchone() cursor0.close() if result0 is not None: return False, "已收藏过该问题" sql = "insert into `%s` (question_id, collecter_username, collect_time) values (%s, '%s', '%s')" % ("tb_question_collection", question_id, username, time_now_str()) msg = "[in connect_question] sql=" + sql logging.info(msg) cursor = db_manager.conn_r.cursor() try: cursor.execute(sql) db_manager.conn_r.commit() except Exception: db_manager.conn_r.rollback() raise Exception db_manager.close() return True, ""
def sign_up(username, sign_time): """ 每日签到 :param username: 用户名 :param sign_time: 签到时间 :return: """ db_manager = DBManager() # 验证用户当天是否已经签到过 sql0 = "select *from `%s` where username='******' and to_days(sign_time)=to_days('%s')" % ("tb_sign", username, string_toDatetime(sign_time)) cursor0 = db_manager.conn_r.cursor() cursor0.execute(sql0) result0 = cursor0.fetchone() cursor0.close() if result0 is not None: return False, "今天已签到过", None props = dict() props['username'] = username props['sign_time'] = sign_time sql1 = forEachPlusInsertProps("tb_sign", props) try: cursor1 = db_manager.conn_r.cursor() cursor1.execute(sql1) db_manager.conn_r.commit() cursor1.close() except Exception: db_manager.conn_r.rollback() cursor1.close() db_manager.close() raise Exception return False, "签到失败", None # 获取系统赠送的积分 user_operation_type = 6 sql2 = "select score_points from `%s` where user_operation_type=%s" % ("tb_score_rule_template", user_operation_type) cursor2 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor2.execute(sql2) result2 = cursor2.fetchone() point_value = result2['score_points'] sql_0 = "insert into `%s` (username, point_type, point_value) values ('%s', %s, %s) ON DUPLICATE KEY UPDATE" \ " point_value=point_value+VALUES(point_value)" % ("tb_user_points", username, user_operation_type, point_value) try: cursor2 = db_manager.conn_r.cursor() cursor2.execute(sql_0) db_manager.conn_r.commit() cursor2.close() except Exception: db_manager.conn_r.rollback() cursor2.close() db_manager.close() raise Exception return False, "签到失败", None db_manager.close() msg0 = "[in sign_up] sql_0=" + sql_0 logging.info(msg0) return True, "签到成功", point_value
class DataProcedure(): def __init__(self): self.cm = ConfigManager() self.db = DBManager() def __del__(self): self.db.close() def run(self): sql = self.get_sql() queue_data = self.cm.get_config('taskqueue')['data'] queue_back = self.cm.get_config('taskqueue')['backup'] r = redis.Redis( host=self.cm.get_config('redis')['host'], port=self.cm.get_config('redis')['port'] ) if not r: print 'Redis服务未启动' else: print 'Redis服务正常' # 处理当前任务 cur_task = r.rpoplpush(queue_data, queue_back) while cur_task is not None: # print cur_task is_success, rows = self.db.save(sql, eval(cur_task)) if is_success: # 提交成功, 清空备份队列 r.delete(queue_back) cur_task = r.rpoplpush(queue_data, queue_back) print '队列中没有要处理的任务' def get_sql(self): table_name = '%sresult' % self.cm.get_config('table')[0]['song']['prefix'] sql_data_save = ("INSERT INTO %s " % table_name) + "(`sid`, `author`, `sname`, `counts`, `durl`) " \ "VALUES (%s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE " \ "counts=counts+1;" return sql_data_save
def change_password(username, old_password, new_password): """ 修改密码 :param username: 用户名 :param old_password: 旧密码 :param new_password: 新密码 :return: """ db_manager = DBManager() cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor) # 判断用户是否合法 sql = "select password from `%s` where username='******'" % ("tb_account", username) cursor_0.execute(sql) result = cursor_0.fetchone() cursor_0.close() if result is None: return False, "用户不存在" tmp_password = result['password'] password = hashlib.sha224(old_password).hexdigest() if tmp_password != password: return False, "原密码错误" props = dict() prere = dict() props['password'] = hashlib.sha224(new_password).hexdigest() prere['username'] = username sql0 = forEachUpdateProps("tb_account", props, prere) try: cursor0 = db_manager.conn_r.cursor() cursor0.execute(sql0) db_manager.conn_r.commit() except Exception: db_manager.conn_r.rollback() cursor0.close() db_manager.close() raise Exception db_manager.close() return True, "修改密码成功"
def modify_personal_information(username, props=None, options=None): """ 更新个人信息 :param username: 用户名 :param props: 更新的数据域 :return: """ if len(props) > 0: is_avatar = False assert isinstance(props, dict) if options: assert isinstance(options, dict) is_avatar = True props.update(options) update_str = FormatUpdateStr(props) sql0 = "update `%s` set %s where username='******'" % ("tb_user", update_str, username) msg = "[in modify_personal_information] sql0=" + sql0 logging.info(msg) try: db_manager = DBManager() cursor_0 = db_manager.conn_r.cursor() cursor_0.execute(sql0) db_manager.conn_r.commit() cursor_0.close() except Exception: db_manager.conn_r.rollback() cursor_0.close() db_manager.close() raise Exception return False, None db_manager.close() if is_avatar: return True, options return True, None elif options: return True, options
def follow_other(username, other_username): """ 关注某人 :param username: 用户名 :param other_username: 其他的用户名 :return: """ db_manager = DBManager() # 校验两者关系 sql0 = "select *from `%s` where (username='******' and other_username='******' and relation_type=0) or (username='******' " \ "and other_username='******' and relation_type=1)" % ("tb_relation", username, other_username, other_username, username) msg = "[in follow_other] sql0=" + sql0 logging.info(msg) cursor0 = db_manager.conn_r.cursor() result0 = cursor0.execute(sql0) cursor0.close() logging.info(result0) if result0 is not None and result0 != 0: return False props = dict() props['username'] = username props['other_username'] = other_username props['relation_type'] = 0 sql = forEachPlusInsertProps("tb_relation", props) try: cursor_0 = db_manager.conn_r.cursor() cursor_0.execute(sql) db_manager.conn_r.commit() cursor_0.close() except Exception: db_manager.conn_r.rollback() cursor_0.close() db_manager.close() raise Exception return False # 插入反向关系 props = dict() props['username'] = other_username props['other_username'] = username props['relation_type'] = 1 sql = forEachPlusInsertProps("tb_relation", props) try: cursor_0 = db_manager.conn_r.cursor() cursor_0.execute(sql) db_manager.conn_r.commit() cursor_0.close() except Exception: db_manager.conn_r.rollback() cursor_0.close() db_manager.close() raise Exception return False db_manager.close() return True
def query_collect_question_list(username, cur_page, page_size): """ 请求用户收藏的问题列表 :param username: 用户名 :param cur_page: 当前数据分页 :param page_size: 每页显示数据条数 :return: """ db_manager = DBManager() question_list = [] # 获取总页数 sql_0 = "select count(*) as counts from `%s` where collecter_username='******'" % ("tb_question_collection", username) cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor_0.execute(sql_0) tmp = cursor_0.fetchone() cursor_0.close() if tmp is None or len(tmp) == 0: return True, question_list, 0 counts = tmp['counts'] sql0 = "select *from `%s` where collecter_username='******' order by collect_time limit %s,%s" % \ ("tb_question_collection", username, (cur_page - 1) * page_size, page_size) cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor0.execute(sql0) results = cursor0.fetchall() cursor0.close() for t in results: # 拿到用户信息 tmp = dict() tmp_username = t['collecter_username'] sql0 = "select avatar_url, nickname from `%s` where username='******'" % ("tb_user", tmp_username) cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor0.execute(sql0) result0 = cursor0.fetchone() cursor0.close() tmp['avatar_url'] = result0['avatar_url'] tmp['nickname'] = result0['nickname'] # 拿到问题信息 tmp_question_id = t['question_id'] sql0 = "select *from `%s` where question_id=%s" % ("tb_question", tmp_question_id) cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor0.execute(sql0) result0 = cursor0.fetchone() cursor0.close() if result0 is None: continue tmp.update(result0) cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) sql0 = "select count(*) as counts from `%s` where question_id=%s" % ("tb_answer", tmp_question_id) cursor0.execute(sql0) result0 = cursor0.fetchone() cursor0.close() counter = 0 if result0: counter = result0['counts'] tmp['has_collect'] = 1 tmp['answer_counts'] = counter question_list.append(tmp) db_manager.close() return True, question_list, counts
def register(username, password, grade, identifier, nickname, subject, serial_number, options=None): """ 用户注册 (用户获取登录账户的唯一途径) 1. 学生注册: username, password, grade为必填项, invitation_code为选填项 2. 教师注册: username, password, grade, subject, serial_number为必填项 :param username: 用户名 (需要检查唯一性) :param password: 密码 :param grade: 年级 :param identifier: 用户类型 (0: 学生 1: 教师) :param subject: 科目 (教师才有该选项) :param serial_number: 教师证 (教师才有) :param options: 可变参数 (由user_type决定) :return: """ db_manager = DBManager() cursor_0 = db_manager.conn_r.cursor() # 判断用户名是否存在 sql = "select * from `%s` where username='******'" % ("tb_account", username) cursor_0.execute(sql) result = cursor_0.fetchone() cursor_0.close() if result: db_manager.close() return False # 键值对 prop_dict = dict() prop_dict['username'] = username prop_dict['identifier'] = identifier prop_dict['grade'] = grade prop_dict['nickname'] = nickname if subject is not None: prop_dict['subject'] = subject if serial_number is not None: prop_dict['serial_number'] = serial_number if options: assert isinstance(options, dict) prop_dict.update(options) insert_sql = forEachPlusInsertProps('tb_user', prop_dict) msg0 = "[in register] insert_sql=" + insert_sql logging.info(msg0) cursor1 = db_manager.conn_r.cursor() try: cursor1.execute(insert_sql) db_manager.conn_r.commit() cursor1.close() except Exception: db_manager.conn_r.rollback() cursor1.close() db_manager.close() raise Exception password = hashlib.sha224(password).hexdigest() sql0 = "insert into `%s` (%s, %s) values ('%s', '%s')" % ("tb_account", "username", "password", username, password) msg = "[in register] sql=" + sql0 logging.info(msg) cursor0 = db_manager.conn_r.cursor() try: cursor0.execute(sql0) db_manager.conn_r.commit() cursor0.close() except Exception: db_manager.conn_r.rollback() cursor0.close() db_manager.close() db_manager.close() return True
def query_user_question_detail(username, question_id): """ 请求用户的问题详情 :param username: 用户名 :param question_id: 问题ID :return: """ db_manager = DBManager() sql0 = "SELECT tq.question_id, question_username, avatar_url, nickname, question_grade, question_subject, " \ "question_content, question_pic_url, question_sound_url, question_time, question_status, question_score from `tb_question` as tq inner join `tb_user` as tu on " \ "tq.question_username=tu.username and tq.question_id=%s and tq.question_username='******';" % (question_id, username) cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor0.execute(sql0) result0 = cursor0.fetchone() cursor0.close() # 没有该问题 if result0 is None or len(result0) == 0: return False, None data = dict() answer_list = [] # 用户是否收藏过该问题 has_collect = 0 sql0 = "select *from `%s` where collecter_username='******' and question_id=%s" % ("tb_question_collection", username, question_id) cursor0 = db_manager.conn_r.cursor() cursor0.execute(sql0) result_110 = cursor0.fetchone() cursor0.close() if result_110: has_collect = 1 result0['has_collect'] = has_collect sql0 = "select avatar_url, nickname from `%s` where username='******'" % ("tb_user", username) cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor0.execute(sql0) result_150 = cursor0.fetchone() cursor0.close() cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) sql0 = "select count(*) as counts from `%s` where question_id=%s" % ("tb_answer", question_id) cursor0.execute(sql0) result_1000 = cursor0.fetchone() cursor0.close() counter = 0 if result_1000: counter = result_1000['counts'] result0['answer_counts'] = counter result0['avatar_url'] = result_150['avatar_url'] result0['nickname'] = result_150['nickname'] data['question_info'] = result0 data['answers_info'] = answer_list # 找出回答列表(根据回答时间排序) sql1 = "select *from `tb_answer` where question_id=%s order by answer_time" % question_id msg0 = "[in query_user_question_detail] sql1=" + sql1 logging.info(msg0) cursor1 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor1.execute(sql1) answers = cursor1.fetchall() cursor1.close() # 没有回答 if answers is None or len(answers) == 0: return True, data for a_answer in answers: t_username = a_answer['answer_username'] # 添加回答信息 tmp_dict = a_answer.copy() # 拿到回答者信息 sql0 = "select avatar_url, nickname from `%s` where username='******'" % ("tb_user", t_username) cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor0.execute(sql0) result0 = cursor0.fetchone() cursor0.close() tmp_dict['avatar_url'] = result0['avatar_url'] tmp_dict['nickname'] = result0['nickname'] # 是否有追问 sql2 = "select *from (select *from `tb_ask` where be_asked_username='******' and original_question_id=%s and answer_id=%s) as ta left join `tb_answer` as tb on ta.ask_question_id=" \ "tb.question_id and ta.be_asked_username=tb.answer_username" % (t_username, question_id, a_answer['answer_id']) cursor2 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor2.execute(sql2) results = cursor2.fetchall() cursor2.close() if results is None or len(results) == 0: # 没有追问 tmp_dict['counts'] = 1 answer_list.append(tmp_dict) continue counts = 0 ask_question_id = 0 for t in results: tmp_ask_question_id = t['ask_question_id'] if ask_question_id != tmp_ask_question_id: counts += 1 ask_question_id = tmp_ask_question_id # 是否有回答 if t['question_id']: counts += 1 tmp_dict['counts'] = counts answer_list.append(tmp_dict) db_manager.close() return True, data
def query_user_question_list(username, cur_page, page_size): """ 查询用户问题列表 :param username: 用户名 :return: """ db_manager = DBManager() sql = "select *from `%s` where username='******'" % ("tb_user", username) cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor_0.execute(sql) result = cursor_0.fetchone() cursor_0.close() question_list = [] if result is None or len(result) == 0: return False, None, None identifier = result['identifier'] grade = result['grade'] if identifier == 0: # 获取总页数 sql_0 = "select count(*) as counts from `%s` where question_grade=%s" % ("tb_question", grade) cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor_0.execute(sql_0) tmp = cursor_0.fetchone() cursor_0.close() if tmp is None or len(tmp) == 0: return True, question_list, 0 counts = tmp['counts'] sql1 = "select *from `%s` where question_grade=%s order by question_time desc limit %s, %s" % \ ("tb_question", grade, (cur_page - 1) * page_size, page_size) elif identifier == 1: subject = result['subject'] # 获取总页数 sql_0 = "select count(*) as counts from `%s` where question_grade=%s and question_subject=%s" % \ ("tb_question", grade, subject) cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor_0.execute(sql_0) tmp = cursor_0.fetchone() cursor_0.close() if tmp is None or len(tmp) == 0: return True, question_list, 0 counts = tmp['counts'] sql1 = "select *from `%s` where question_grade=%s and question_subject=%s order by question_time desc limit %s, %s" % \ ("tb_question", grade, subject, (cur_page - 1) * page_size, page_size) msg0 = "[in query_user_question_list] sql1=" + sql1 logging.info(msg0) cursor1 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor1.execute(sql1) result1 = cursor1.fetchall() cursor1.close() if result1: for item in result1: # 拿到用户信息 tmp = dict() tmp_username = item['question_username'] question_id = item['question_id'] sql0 = "select avatar_url, nickname from `%s` where username='******'" % ("tb_user", tmp_username) cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor0.execute(sql0) result0 = cursor0.fetchone() cursor0.close() tmp['avatar_url'] = result0['avatar_url'] tmp['nickname'] = result0['nickname'] tmp.update(item) # 用户是否收藏过该问题 has_collect = 0 sql0 = "select *from `%s` where collecter_username='******' and question_id=%s" % ("tb_question_collection", username, question_id) cursor0 = db_manager.conn_r.cursor() cursor0.execute(sql0) result_119 = cursor0.fetchone() cursor0.close() if result_119: has_collect = 1 tmp['has_collect'] = has_collect cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) sql0 = "select count(*) as counts from `%s` where question_id='%s'" % ("tb_answer", question_id) cursor0.execute(sql0) result0 = cursor0.fetchone() cursor0.close() counter = 0 if result0: counter = result0['counts'] tmp['answer_counts'] = counter question_list.append(tmp) db_manager.close() return True, question_list, counts
def query_all_information(username, identifier): """ 请求我的页面数据 :param username: 用户名 :param identifier: 身份标志 :return: """ db_manager = DBManager() data = {} # 学生 if identifier == 0: # 拿到称号 # 拿到总条数 sql = "select count(*) as counts from `%s` where question_username='******'" % ("tb_question", username) cursor = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor.execute(sql) result = cursor.fetchone() cursor.close() sums = 0 if result: sums = result['counts'] # 拿到已解决的问题数 sql = "select count(*) as counts from `%s` where question_username='******' and question_status=%s" % \ ("tb_question", username, 1) cursor = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor.execute(sql) result = cursor.fetchone() cursor.close() solved_num = 0 if result: solved_num = result['counts'] # 拿到总问题数/已解决的问题数 data['question_info'] = { "total_questions": sums, "solved_questions": solved_num } # 教师 elif identifier == 1: # 拿到称号 # 拿到总回答数 sql = "select count(question_id) as counts from `tb_answer` where answer_username='******' and question_id not in (select ask_question_id from " \ "`tb_ask` where be_asked_username='******')" % (username, username) cursor = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor.execute(sql) result = cursor.fetchone() cursor.close() sums = 0 if result: sums = result['counts'] # 拿到没有被采纳的回答列表 query_str = FormatCondition(props={ "answer_username": username, "is_accepted": 0 }) sql = "select count(question_id) as counts from `tb_answer` where %s and question_id not in (select ask_question_id from " \ "`tb_ask` where be_asked_username='******')" % (query_str, username) cursor = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor.execute(sql) result = cursor.fetchone() cursor.close() counts = 0 if result: counts = result['counts'] # 拿到总回答数/被采纳的回答数 data['answer_info'] = { "total_answers": sums, "accepted_answers": sums - counts } # 拿到总学分 cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor) sql = "select user_operation_type, user_operation_desc, point_value from `%s` as u inner " \ "join `%s` as s on u.point_type=s.user_operation_type and username='******'" % ("tb_user_points", "tb_score_rule_template", username) msg = "[in query_user_points_detail] sql=" + sql logging.info(msg) cursor_0.execute(sql) result = cursor_0.fetchall() cursor_0.close() if result is None: return False, None total_points = 0 for item in result: total_points += item['point_value'] # 拿到称号模板数据 sql = "select *from `%s` where level_type=%s order by level" % ("tb_level_rule_template", identifier) cursor = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor.execute(sql) user_level_templates = cursor.fetchall() cursor.close() # 默认的数据 user_level = 0 level_desc = user_level_templates[0]['level_desc'] for t in user_level_templates: level_section = safe_str_to_list(t['level_section']) if sums in xrange(level_section[0], level_section[1]): user_level = t['level'] level_desc = t['level_desc'] break data['user_info'] = { "user_level": user_level, "level_desc": level_desc, "total_points": total_points } # 拿到关注/粉丝数 cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor) sql = "select count(*) as follows from `%s` where username='******' and relation_type=0" % ("tb_relation", username) cursor_0.execute(sql) result = cursor_0.fetchone() cursor_0.close() if result is None: follows_num = 0 else: follows_num = result['follows'] cursor_1 = db_manager.conn_r.cursor(cursorclass=DictCursor) sql1 = "select count(*) as fans from `%s` where other_username='******' and relation_type=0" % ("tb_relation", username) cursor_1.execute(sql1) result1 = cursor_1.fetchone() cursor_1.close() if result1 is None: fans_num = 0 else: fans_num = result1['fans'] data['relation_info'] = { "follows_num": follows_num, "fans_num": fans_num } db_manager.close() return True, data
def ask_question(username, answer_id, ask_content, original_question_id, be_asked_username, options=None): """ 用户追问 :param username: 用户名 :param answer_id: 回答的ID :param ask_content: 追问内容 :param original_question_id: 原问题ID :param be_asked_username: 被追问的用户 :return: """ db_manager = DBManager() # 问题是否已经被解决 sql_0 = "select question_status from `%s` where question_id=%s" % ("tb_question", original_question_id) cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor_0.execute(sql_0) result_0 = cursor_0.fetchone() cursor_0.close() if result_0 is None: return False, "问题不存在" question_status = result_0['question_status'] # 问题已经被解决 if question_status == 1: return False, "问题已经被解决" # 检验被提问的用户是否回答过该问题 sql_0 = "select *from `tb_answer` where answer_username='******' and question_id=%s" % (be_asked_username, original_question_id) cursor_0 = db_manager.conn_r.cursor() cursor_0.execute(sql_0) result_0 = cursor_0.fetchone() cursor_0.close() if result_0 is None or len(result_0) == 0: return False, "没有回答该问题" # 拿到追问顺序ask_order sql_1 = "select max(ask_order) as ask_order0 from `tb_ask` where be_asked_username='******' and original_question_id=" \ "%s" % (be_asked_username, original_question_id) cursor_1 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor_1.execute(sql_1) result_1 = cursor_1.fetchone() cursor_1.close() if result_1 is None or len(result_1) == 0: ask_order = 0 else: ask_order = result_1['ask_order0'] if result_1['ask_order0'] else 0 # 添加一条追问记录 props = dict() props['ask_content'] = ask_content props['ask_time'] = time_now_str() props['original_question_id'] = original_question_id props['be_asked_username'] = be_asked_username props['ask_order'] = ask_order + 1 props['answer_id'] = answer_id if options: assert isinstance(options, dict) props.update(options) insert_sql = forEachPlusInsertProps("tb_ask", props) msg0 = "[in ask_question] insert_sql=" + insert_sql logging.info(msg0) cursor_2 = db_manager.conn_r.cursor() try: cursor_2.execute(insert_sql) db_manager.conn_r.commit() cursor_2.close() except Exception: db_manager.conn_r.rollback() cursor_2.close() db_manager.close() raise Exception db_manager.close() return True, ""
def adopt_answer(username, question_id, answer_id, answer_username): """ 采纳回答 :param username: 用户名 :param question_id: 原问题ID :param answer_id: 回答的ID :param answer_username: 回答者的用户名 :return: """ db_manager = DBManager() # 判断用户是否有权限采纳答案 sql0 = "select *from `%s` where username='******'" % ("tb_user", username) cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor0.execute(sql0) result0 = cursor0.fetchone() cursor0.close() if result0 is None: return False, "用户不存在" identifier = result0['identifier'] if identifier != 0: return False, "没有采纳权限" # 问题是否已经被解决 sql_0 = "select question_status, question_score from `%s` where question_id=%s" % ("tb_question", question_id) cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor_0.execute(sql_0) result_0 = cursor_0.fetchone() cursor_0.close() if result_0 is None: return False, "问题不存在" question_status = result_0['question_status'] question_score = result_0['question_score'] # 问题已经被解决 if question_status == 1: return False, "问题已经被解决" # 关闭问题 update_prop = dict() update_prop['question_status'] = 1 update_sql_0 = FormatUpdateStr(update_prop) sql0 = "update `%s` set %s where question_id=%s" % ("tb_question", update_sql_0, question_id) cursor0 = db_manager.conn_r.cursor() try: cursor0.execute(sql0) db_manager.conn_r.commit() cursor0.close() except Exception: db_manager.conn_r.rollback() cursor0.close() db_manager.close() raise Exception return False # 更新回答的状态 update_prop = dict() update_prop['is_accepted'] = 1 update_sql_0 = FormatUpdateStr(update_prop) sql0 = "update `%s` set %s where answer_id=%s" % ("tb_answer", update_sql_0, answer_id) cursor0 = db_manager.conn_r.cursor() try: cursor0.execute(sql0) db_manager.conn_r.commit() cursor0.close() except Exception: db_manager.conn_r.rollback() cursor0.close() db_manager.close() raise Exception return False # 更新教师积分 user_operation_type = 3 sql2 = "select score_points from `%s` where user_operation_type=%s" % ("tb_score_rule_template", user_operation_type) cursor2 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor2.execute(sql2) result2 = cursor2.fetchone() cursor2.close() point_value = result2['score_points'] + question_score sql_0 = "insert into `%s` (username, point_type, point_value) values ('%s', %s, %s) ON DUPLICATE KEY UPDATE" \ " point_value=point_value+VALUES(point_value)" % ("tb_user_points", answer_username, user_operation_type, point_value) msg_120 = "[in adopt_answer fresh_user_points] sql_0=" + sql_0 logging.info(msg_120) cursor2 = db_manager.conn_r.cursor() try: cursor2.execute(sql_0) db_manager.conn_r.commit() cursor2.close() except Exception: db_manager.conn_r.rollback() cursor2.close() db_manager.close() raise Exception return False db_manager.close() return True, ""
def answer_question(username, question_id, answer_content, is_original_answer, options=None): """ 回答问题 :param username: 用户名 :param question_id: 问题ID :param answer_content: 回答内容 :param is_original_answer: 是否是原回答(1: 原回答 0: 追答) :return: """ db_manager = DBManager() # 判断用户是否有权限回答问题 sql0 = "select *from `%s` where username='******'" % ("tb_user", username) cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor0.execute(sql0) result0 = cursor0.fetchone() cursor0.close() if result0 is None: return False, "用户不存在" identifier = result0['identifier'] if identifier != 1: return False, "没有回答权限" original_question_id = question_id # 如果是追答 if is_original_answer == 0: # 拿到问题原始ID sql_0 = "select original_question_id from `%s` where ask_question_id=%s" % ("tb_ask", question_id) cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor_0.execute(sql_0) result_0 = cursor_0.fetchone() cursor_0.close() if result_0 is None: return False, "问题不存在" original_question_id = result_0['original_question_id'] # 问题是否已经被解决 sql_0 = "select question_status from `%s` where question_id=%s" % ("tb_question", original_question_id) cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor_0.execute(sql_0) result_0 = cursor_0.fetchone() cursor_0.close() if result_0 is None: return False, "问题不存在" question_status = result_0['question_status'] # 问题已经被解决 if question_status == 1: return False, "问题已经被解决" props = dict() props['answer_username'] = username props['answer_time'] = time_now_str() props['question_id'] = question_id props['is_accepted'] = 0 props['answer_content'] = answer_content if options: assert isinstance(options, dict) props.update(options) sql = forEachPlusInsertProps("tb_answer", props) msg = "[in answer_question] sql=" + sql logging.info(msg) cursor = db_manager.conn_r.cursor() try: cursor.execute(sql) db_manager.conn_r.commit() cursor.close() except Exception: db_manager.conn_r.rollback() cursor.close() db_manager.close() raise Exception # 更新用户积分 (只有原始问题的回答加分, 追答不加分) if is_original_answer == 1: user_operation_type = 4 sql2 = "select score_points from `%s` where user_operation_type=%s" % ("tb_score_rule_template", user_operation_type) cursor2 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor2.execute(sql2) result2 = cursor2.fetchone() point_value = result2['score_points'] sql_0 = "insert into `%s` (username, point_type, point_value) values ('%s', %s, %s) ON DUPLICATE KEY UPDATE" \ " point_value=point_value+VALUES(point_value)" % ("tb_user_points", username, user_operation_type, point_value) msg_110 = "[in answer_question refresh user points] sql_0=" + sql_0 logging.info(msg_110) try: cursor2 = db_manager.conn_r.cursor() cursor2.execute(sql_0) db_manager.conn_r.commit() cursor2.close() except Exception: db_manager.conn_r.rollback() cursor2.close() db_manager.close() raise Exception return False db_manager.close() return True, "回答成功"
def query_ask_and_answer_page(answer_id): """ 获取某个问题的追问追答列表 :param answer_id: 回答的ID :return: """ db_manager = DBManager() sql0 = "select *from `tb_answer` where answer_id=%s" % answer_id cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor0.execute(sql0) answer = cursor0.fetchone() cursor0.close() if answer is None or len(answer) == 0: return False, "没有该回答" # 拿到问题信息 question_id = answer['question_id'] sql0 = "select *from `tb_question` where question_id=%s" % question_id cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor0.execute(sql0) result0 = cursor0.fetchone() cursor0.close() data = dict() answer_list = [] question_username = result0['question_username'] # 用户是否收藏过该问题 has_collect = 0 sql0 = "select *from `%s` where collecter_username='******' and question_id=%s" % ("tb_question_collection", question_username, question_id) cursor0 = db_manager.conn_r.cursor() cursor0.execute(sql0) result_112 = cursor0.fetchone() cursor0.close() if result_112: has_collect = 1 result0['has_collect'] = has_collect # 拿到提问者的信息 sql0 = "select avatar_url, nickname from `%s` where username='******'" % ("tb_user", question_username) cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor0.execute(sql0) result_150 = cursor0.fetchone() cursor0.close() result0['avatar_url'] = result_150['avatar_url'] result0['nickname'] = result_150['nickname'] data['question_info'] = result0 data['answers_info'] = answer_list t_username = answer['answer_username'] # 添加回答信息 tmp_dict = answer.copy() # 拿到回答者信息 sql0 = "select avatar_url, nickname from `%s` where username='******'" % ("tb_user", t_username) cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor0.execute(sql0) result0 = cursor0.fetchone() cursor0.close() tmp_dict['avatar_url'] = result0['avatar_url'] tmp_dict['nickname'] = result0['nickname'] # 原始问题的回答 tmp_dict['type'] = 0 answer_list.append(tmp_dict) # 是否有追问 sql2 = "select *from (select *from `tb_ask` where be_asked_username='******' and original_question_id=%s and answer_id=%s) as ta left join `tb_answer` as tb on ta.ask_question_id=" \ "tb.question_id and ta.be_asked_username=tb.answer_username " % (t_username, question_id, answer_id) cursor2 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor2.execute(sql2) results = cursor2.fetchall() cursor2.close() if results is None or len(results) == 0: return True, data # 去重 ask_question_id = 0 need_sort_list = [] # 记录需要排序的数据段 start_index = 1 end_index = 1 for t in results: tmp_ask_dict = dict() tmp_ask_question_id = t['ask_question_id'] # logging.info("ZZZ###") # msg = str(tmp_ask_question_id) + "," + str(t['ask_question_id']) + ", " + str(start_index) + "," + str(end_index) # logging.info(msg) # 拿到追问者的信息 if ask_question_id != tmp_ask_question_id: # 更新游标 ask_question_id = tmp_ask_question_id # 记录需要排序的数据区间 if end_index - start_index >= 1: t_list = [start_index, end_index] need_sort_list.append(t_list) end_index += 1 start_index = end_index # 拿到提问者的信息 tmp_ask_dict['avatar_url'] = result_150['avatar_url'] tmp_ask_dict['nickname'] = result_150['nickname'] # 追问 tmp_ask_dict['type'] = 1 old_keys = ['answer_id', 'be_asked_username', 'ask_time', 'ask_question_id', 'ask_content', 'ask_pic_url', 'ask_sound_url'] new_keys = ["answer_id", "answer_username", "answer_time", "question_id", "answer_content", "answer_pic_url", "answer_sound_url"] tmp_ask_dict['is_accepted'] = 0 copy_dict_by_keys_with_new_keys(old_keys, new_keys, t, tmp_ask_dict) answer_list.append(tmp_ask_dict) else: # 处理多个追答的情形 end_index += 1 if t['question_id'] is None: if end_index - start_index >= 1: need_sort_list.append([start_index, end_index]) start_index = end_index else: tmp_answer_dict = dict() # 拿到回答者的信息 tmp_username = t['answer_username'] sql0 = "select avatar_url, nickname from `%s` where username='******'" % ("tb_user", tmp_username) cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor0.execute(sql0) result0 = cursor0.fetchone() cursor0.close() tmp_answer_dict['avatar_url'] = result0['avatar_url'] tmp_answer_dict['nickname'] = result0['nickname'] # 追答 tmp_answer_dict['type'] = 2 copy_dict_by_keys(['answer_username', 'answer_time', 'question_id', 'is_accepted', 'answer_content', 'answer_pic_url', 'answer_sound_url', 'answer_id'], t, tmp_answer_dict) answer_list.append(tmp_answer_dict) if end_index - start_index >= 1: need_sort_list.append([start_index, end_index]) # logging.info('########start') # logging.info(answer_list) # 进行数据排序 (维度选择answer_time) if need_sort_list and answer_list: # logging.info('to sort') logging.info(need_sort_list) for v in need_sort_list: # logging.info("hhhh") # logging.info(v) for i in xrange(v[0], v[1]): for j in xrange(v[0], v[1]-i): if answer_list[j]['answer_time'] > answer_list[j+1]['answer_time']: # test swap # logging.info("before swap####") t_data = answer_list[j+1] logging.info(t_data) answer_list[j+1] = answer_list[j] answer_list[j] = t_data logging.info(answer_list[j+1]) # logging.info('########end') logging.info(answer_list) db_manager.close() return True, data
def query_user_question_or_answer_list(username, identifier, is_part, cur_page, page_size): """ 请求用户的问题列表或者回答列表 :param username: 用户名 :param identifier: 身份标志 (0: 学生 1: 教师) :param is_part: 按照条件搜索 (学生: 问题完成数 教师: 回答采纳数) :param cur_page: 当前数据分页 :param page_size: 每页显示数据条数 :return: """ db_manager = DBManager() order_key = 'question_time' table_name = 'tb_question' question_list = [] counts = 0 # 学生 if identifier == 0: # 全部搜索 if is_part == 0: query_str = FormatCondition(props={ "question_username": username }) # 部分搜索 elif is_part == 1: query_str = FormatCondition(props={ "question_username": username, "question_status": 1 }) # 拿到总条数 sql = "select count(*) as counts from `%s` where %s" % (table_name, query_str) cursor = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor.execute(sql) result = cursor.fetchone() cursor.close() if result is None or len(result) == 0: return True, question_list, 0 counts = result['counts'] sql0 = "select *from `%s` where %s order by %s limit %s,%s" % \ (table_name, query_str, order_key, (cur_page - 1) * page_size, page_size) # 教师 elif identifier == 1: # 全部搜索 if is_part == 0: query_str = FormatCondition(props={ "answer_username": username }) # 部分搜索 elif is_part == 1: query_str = FormatCondition(props={ "answer_username": username, "is_accepted": 1 }) # 拿到总条数 sql = "select count(question_id) as counts from `tb_answer` where %s and question_id not in (select ask_question_id from " \ "`tb_ask` where be_asked_username='******')" % (query_str, username) cursor = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor.execute(sql) result = cursor.fetchone() cursor.close() if result is None or len(result) == 0: return True, question_list, 0 counts = result['counts'] sql0 = "select *from `%s` where question_id in (select question_id from `tb_answer` where " \ "%s and question_id not in (select ask_question_id from `tb_ask` where be_asked_username='******')) order by " \ "%s limit %s,%s" % (table_name, query_str, username, order_key, (cur_page - 1) * page_size, page_size) cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor0.execute(sql0) results = cursor0.fetchall() cursor0.close() for t in results: # 拿到用户信息 tmp = dict() tmp_username = t['question_username'] tmp_question_id = t['question_id'] sql0 = "select avatar_url, nickname from `%s` where username='******'" % ("tb_user", tmp_username) cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor0.execute(sql0) result0 = cursor0.fetchone() cursor0.close() tmp['avatar_url'] = result0['avatar_url'] tmp['nickname'] = result0['nickname'] # 用户是否收藏过该问题 has_collect = 0 sql0 = "select *from `%s` where collecter_username='******' and question_id=%s" % ("tb_question_collection", username, tmp_question_id) cursor0 = db_manager.conn_r.cursor() cursor0.execute(sql0) result_120 = cursor0.fetchone() cursor0.close() if result_120: has_collect = 1 tmp['has_collect'] = has_collect cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) sql0 = "select count(*) as counts from `%s` where question_id=%s" % ("tb_answer", tmp_question_id) cursor0.execute(sql0) result0 = cursor0.fetchone() cursor0.close() counter = 0 if result0: counter = result0['counts'] tmp['answer_counts'] = counter question_list.append(tmp) db_manager.close() return True, question_list, counts
def post_question(username, grade, subject, question_content, question_score=0, options=None): """ 用户提问 :param username: 用户名 :param grade: 年级 :param subject: 学科 :param question_content: 问题内容 :param question_score: 悬赏积分 :param options: 可变字段 :return: """ db_manager = DBManager() # 判断用户是否有权限提问 sql0 = "select *from `%s` where username='******'" % ("tb_user", username) cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor0.execute(sql0) result0 = cursor0.fetchone() cursor0.close() if result0 is None: return False identifier = result0['identifier'] if identifier != 0: return False # 客户端提交的问题内容为空 if question_content is None or question_content == '': # 获取随机问题内容 (随机数的上限取决于后期配置的模板表的大小) # 拿到模板表的大小 template_size = 0 sql0 = "select count(*) as counts from `%s`" % "tb_question_content_template" cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor0.execute(sql0) result0 = cursor0.fetchone() cursor0.close() if result0: template_size = result0['counts'] if template_size < 1: return False r_index = randint(1, template_size) sql0 = "select *from `%s` where content_id=%s" % ("tb_question_content_template", r_index) cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor0.execute(sql0) result0 = cursor0.fetchone() cursor0.close() if result0: question_content = result0['content_value'] # 拿到插入的数据 props = dict() props['question_username'] = username props['question_content'] = question_content props['question_score'] = question_score props['question_grade'] = grade props['question_subject'] = subject props['question_time'] = time_now_str() props['question_status'] = 0 if options: assert isinstance(options, dict) props.update(options) sql = forEachPlusInsertProps("tb_question", props) msg = "[in post_question] sql=" + sql logging.info(msg) cursor = db_manager.conn_r.cursor() try: cursor.execute(sql) db_manager.conn_r.commit() except Exception: db_manager.conn_r.rollback() raise Exception # 提问成功, 更新积分 # 更新用户积分 user_operation_type = 2 sql2 = "select score_points from `%s` where user_operation_type=%s" % ("tb_score_rule_template", user_operation_type) cursor2 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor2.execute(sql2) result2 = cursor2.fetchone() point_value = result2['score_points'] - question_score if point_value >= 0: sql_0 = "insert into `%s` (username, point_type, point_value) values ('%s', %s, %s) ON DUPLICATE KEY UPDATE" \ " point_value=point_value+VALUES(point_value)" % ("tb_user_points", username, user_operation_type, point_value) else: # 查询是否存在记录 sql_110 = "select *from `%s` where username='******' and point_type=%s" % ("tb_user_points", username, user_operation_type) cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor_0.execute(sql_110) result_110 = cursor_0.fetchone() cursor_0.close() if result_110: point_value += result_110['point_value'] sql_0 = "update `%s` set point_value=%s where username='******' and point_type=%s" % \ ("tb_user_points", point_value, username, user_operation_type) else: sql_0 = "insert into `%s` (username, point_type, point_value) values ('%s', %s, %s)" % \ ("tb_user_points", username, user_operation_type, point_value) msg_110 = "[in post_question refresh user points] sql_0=" + sql_0 logging.info(msg_110) try: cursor2 = db_manager.conn_r.cursor() cursor2.execute(sql_0) db_manager.conn_r.commit() cursor2.close() except Exception: db_manager.conn_r.rollback() cursor2.close() db_manager.close() raise Exception return False db_manager.close() return True
def login(username, password, identifier): """ 用户登录 (学生, 教师登录入口) :param username: 用户名 :param password: 密码 :return: """ password = hashlib.sha224(password).hexdigest() db_manager = DBManager() sql = "select *from `tb_account` as ta inner join `tb_user` as tu on ta.username=tu.username and ta.username='******'" \ " and ta.password='******' and tu.identifier=%s" % (username, password, identifier) cursor_0 = db_manager.conn_r.cursor() cursor_0.execute(sql) result = cursor_0.fetchone() cursor_0.close() is_ok = False data = None if result: # 是否为新用户登录 is_new_user = False sql = "select *from `%s` where username='******'" % ("tb_user_log", username) cursor_0 = db_manager.conn_r.cursor() cursor_0.execute(sql) result_0 = cursor_0.fetchone() cursor_0.close() if result_0 is None: is_new_user = True cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) sql0 = "select *from `%s` where username='******'" % ("tb_user", username) cursor0.execute(sql0) result0 = cursor0.fetchone() cursor0.close() is_ok = True data = result0 # 拿到签到数据 sql1 = "select *from `%s` where username='******' and to_days(sign_time)=to_days(now())" % ("tb_sign", username) cursor1 = db_manager.conn_r.cursor() cursor1.execute(sql1) result1 = cursor1.fetchone() cursor1.close() has_sign_today = 0 if result1 is not None: has_sign_today = 1 data['has_sign_today'] = has_sign_today # 如果是新用户登录, 更新积分 if is_new_user: user_operation_type = 1 sql2 = "select score_points from `%s` where user_operation_type=%s" % ("tb_score_rule_template", user_operation_type) cursor2 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor2.execute(sql2) result2 = cursor2.fetchone() point_value = result2['score_points'] sql_0 = "insert into `%s` (username, point_type, point_value) values ('%s', %s, %s) ON DUPLICATE KEY UPDATE" \ " point_value=point_value+VALUES(point_value)" % ("tb_user_points", username, user_operation_type, point_value) try: cursor2 = db_manager.conn_r.cursor() cursor2.execute(sql_0) db_manager.conn_r.commit() cursor2.close() except Exception: db_manager.conn_r.rollback() cursor2.close() db_manager.close() raise Exception # 记录用户登录日志 sql_0 = "insert into `%s` (username, login_time) values ('%s', '%s') ON DUPLICATE KEY UPDATE" \ " login_time=login_time" % ("tb_user_log", username, time_now_str()) cursor1 = db_manager.conn_r.cursor() try: cursor1.execute(sql_0) db_manager.conn_r.commit() cursor1.close() except Exception: db_manager.conn_r.rollback() cursor1.close() db_manager.close() raise Exception db_manager.close() return is_ok, data
class Baidu(): def __init__(self): self.cm = ConfigManager() self.db = DBManager() def __del__(self): self.db.close() def get_song_nums(self, *args, **kwargs): """ 获取实际歌曲数,存在同歌曲名,不同sid的情形,故需要去重 :param args: 查询的集合域 :param kwargs: 过滤条件,可以取值歌手,歌名或者其他 :return: """ table = '%sresult' % self.cm.get_config('table')[0]['song']['prefix'] category = ', '.join(args) filter_key = kwargs.keys()[0] filter_value = kwargs.get(filter_key) sql_search = ('SELECT COUNT(DISTINCT %s) FROM %s ' % (category, table)) + 'WHERE %s = \'%s\'' % (filter_key, filter_value) data = self.db.query(sql_search) return data[0][0] def searchBySinger(self, singer): """ 根据歌曲名称查询 优先搜索数据库, 若找到, 直接返回该数据 提示用户是否仍要继续下载 否则, 联网搜索, 并将新数据存入数据库 :param singer: 歌手 :return: """ table = '%sresult' % self.cm.get_config('table')[0]['song']['prefix'] sql_search = ('SELECT sname, durl FROM %s ' % table) + 'WHERE author = \'%s\'' % singer data = self.db.query(sql_search) size = self.get_song_nums('sname', author=singer) print('数据库目前收录%d首' % size) print '分别有:' for l in data: print(l[0]) print('是否开始下载?(y/n)') choice = raw_input() if choice == 'y': base_dir = self.cm.get_config('dir')['path'] download.download_with_singer(data, base_dir, singer, size) else: print '已取消下载' def searchBySname(self, song, singer=None): """ 提供对外调用的接口, 接受歌曲名作为参数 :param song: 歌曲名 :param singer: 歌手名(选项) :return: """ table = '%sresult' % self.cm.get_config('table')[0]['song']['prefix'] sql_search = ('SELECT author, durl FROM %s ' % table) + 'WHERE sname = \'%s\'' % song data = self.db.query(sql_search) size = self.get_song_nums('author', sname=song) print('数据库目前收录%d首' % size) print '分别有:' for l in data: print(l[0]) print('是否开始下载?(y/n)') choice = raw_input() if choice == 'y': base_dir = self.cm.get_config('dir')['path'] download.download_with_sname(song, data, base_dir) else: print '已取消下载'
def search_question(username, question_content, cur_page, page_size, grade=None, subject=None): """ 搜索问题 (按照问题内容搜索) :param username: 用户名 :param question_content: 问题内容 :param grade: 年级 :param subject: 科目 :return: """ db_manager = DBManager() question_list = [] condition_props = dict() if grade is not None: condition_props['question_grade'] = grade if subject is not None: condition_props['question_subject'] = subject # 默认搜索文字 con_str = FormatCondition(condition_props) # 拿到总条数 signal = False if con_str is not None and len(con_str) > 0: signal = True sql_1 = "select count(*) as counts from `%s` where %s and question_content like '%s%s%s'" % \ ("tb_question", con_str, '%', question_content, '%') else: sql_1 = "select count(*) as counts from `%s` where question_content like '%s%s%s'" % \ ("tb_question", '%', question_content, '%') msg0 = "[in search_question] sql_1=" + sql_1 logging.info(msg0) cursor_1 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor_1.execute(sql_1) tmp = cursor_1.fetchone() cursor_1.close() if tmp is None or len(tmp) == 0: return True, question_list, 0 counts = tmp['counts'] if signal: sql = "select *from `%s` where %s and question_content like '%s%s%s' order by question_time desc limit %s,%s " % \ ("tb_question", con_str, '%', question_content, '%', (cur_page - 1) * page_size, page_size) else: sql = "select *from `%s` where question_content like '%s%s%s' order by question_time desc limit %s,%s " % \ ("tb_question", '%', question_content, '%', (cur_page - 1) * page_size, page_size) msg = '[in search_question] sql=' + sql logging.info(msg) cursor_0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor_0.execute(sql) result = cursor_0.fetchall() cursor_0.close() logging.info(result) if result: for item in result: tmp = item.copy() question_id = item['question_id'] tmp_username = item['question_username'] # 拿到用户信息 sql0 = "select avatar_url, nickname from `%s` where username='******'" % ("tb_user", tmp_username) cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) cursor0.execute(sql0) result0 = cursor0.fetchone() cursor0.close() tmp['avatar_url'] = result0['avatar_url'] tmp['nickname'] = result0['nickname'] # 用户是否收藏过该问题 has_collect = 0 sql0 = "select *from `%s` where collecter_username='******' and question_id=%s" % ("tb_question_collection", username, question_id) cursor0 = db_manager.conn_r.cursor() cursor0.execute(sql0) result_121 = cursor0.fetchone() cursor0.close() if result_121: has_collect = 1 tmp['has_collect'] = has_collect cursor0 = db_manager.conn_r.cursor(cursorclass=DictCursor) sql0 = "select count(*) as counts from `%s` where question_id=%s" % ("tb_answer", question_id) cursor0.execute(sql0) result0 = cursor0.fetchone() cursor0.close() counter = 0 if result0: counter = result0['counts'] tmp['answer_counts'] = counter question_list.append(tmp) db_manager.close() return True, question_list, counts