Example #1
0
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
Example #2
0
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
Example #3
0
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']
Example #4
0
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
    }
Example #5
0
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
Example #6
0
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']
Example #7
0
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, ""
Example #8
0
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
Example #9
0
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
Example #10
0
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, ""
Example #11
0
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
Example #12
0
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
Example #13
0
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, "修改密码成功"
Example #14
0
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
Example #15
0
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
Example #16
0
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
Example #17
0
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
Example #18
0
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
Example #19
0
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
Example #20
0
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
Example #21
0
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, ""
Example #22
0
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, ""
Example #23
0
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, "回答成功"
Example #24
0
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
Example #25
0
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
Example #26
0
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
Example #27
0
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
Example #28
0
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 '已取消下载'
Example #29
0
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