def get_paged_questions(page_count=10, last_qid=None, pre=False): # 获取问题列表 conn = yield async_connect() cur = conn.cursor() if not pre: # 前页 if not last_qid: sql = "SELECT q.qid, q.abstract, q.content, q.view_count, q.answer_count, u.username, t.tag_name FROM t_question q LEFT JOIN t_user u ON q.uid=u.uid LEFT JOIN t_tag t ON q.tid=t.tid ORDER BY qid DESC LIMIT %d;" % page_count else: sql = "SELECT q.qid, q.abstract, q.content, q.view_count, q.answer_count, u.username, t.tag_name FROM t_question q LEFT JOIN t_user u ON q.uid=u.uid LEFT JOIN t_tag t ON q.tid=t.tid WHERE qid<%d ORDER BY qid DESC LIMIT %d;" % ( last_qid, page_count) else: # 后页 if not last_qid: return [] else: sql = "SELECT q.qid, q.abstract, q.content, q.view_count, q.answer_count, u.username, t.tag_name FROM t_question q LEFT JOIN t_user u ON q.uid=u.uid LEFT JOIN t_tag t ON q.tid=t.tid WHERE qid>=%d ORDER BY qid DESC LIMIT %d;" % ( last_qid, page_count) try: yield cur.execute(sql) data = cur.fetchall() except Exception as e: data = [] finally: cur.close() conn.close() raise gen.Return(data)
def get_filtered_questions(name, user=None, tag=None): # 获取过滤问题列表 conn = yield async_connect() cur = conn.cursor() if name == 'newest': sql = "SELECT q.qid, q.abstract, q.content, q.view_count, q.answer_count, u.username, t.tag_name FROM t_question q LEFT JOIN t_user u ON q.uid=u.uid LEFT JOIN t_tag t ON q.tid=t.tid ORDER BY q.created_at DESC LIMIT 15;" elif name == 'hotest': sql = "SELECT q.qid, q.abstract, q.content, q.view_count, q.answer_count, u.username, t.tag_name FROM t_question q LEFT JOIN t_user u ON q.uid=u.uid LEFT JOIN t_tag t ON q.tid=t.tid ORDER BY answer_count DESC LIMIT 15;" elif name == 'under': sql = "SELECT q.qid, q.abstract, q.content, q.view_count, q.answer_count, u.username, t.tag_name FROM t_question q LEFT JOIN t_user u ON q.uid=u.uid LEFT JOIN t_tag t ON q.tid=t.tid WHERE q.status=0 ORDER BY q.created_at DESC LIMIT 15;" elif name == 'hasdone': sql = "SELECT q.qid, q.abstract, q.content, q.view_count, q.answer_count, u.username, t.tag_name FROM t_question q LEFT JOIN t_user u ON q.uid=u.uid LEFT JOIN t_tag t ON q.tid=t.tid WHERE q.status=1 ORDER BY q.created_at DESC LIMIT 15;" elif name == 'prefer' and user: sql = "SELECT q.qid, q.abstract, q.content, q.view_count, q.answer_count, u.username, t.tag_name FROM t_question q" sql += " LEFT JOIN t_user u ON q.uid=u.uid" sql += " LEFT JOIN t_tag t ON q.tid=t.tid" sql += " WHERE q.tid = (SELECT tid FROM t_question WHERE uid = (SELECT uid FROM t_user WHERE username = '******') GROUP BY tid ORDER BY COUNT(tid) DESC LIMIT 1)" % user sql += " ORDER BY q.created_at DESC LIMIT 15;" elif tag: sql = "SELECT q.qid, q.abstract, q.content, q.view_count, q.answer_count, u.username, t.tag_name FROM t_question q LEFT JOIN t_user u ON q.uid=u.uid LEFT JOIN t_tag t ON t.tid = q.tid WHERE q.tid=%d ORDER BY answer_count DESC LIMIT 15;" % tag else: raise gen.Return([]) try: yield cur.execute(sql) data = cur.fetchall() except Exception as e: data = [] finally: cur.close() conn.close() raise gen.Return(data)
def delete_answer_by_id(aid, qid, user): # 删除答案 conn = yield async_connect() cur = conn.cursor() sql = "DELETE FROM t_answer WHERE aid = %d AND qid = %d AND uid=(SELECT uid FROM t_user WHERE username='******');" % (aid, qid, user) try: result = yield cur.execute(sql) except Exception as e: result = 0 raise gen.Return(result)
def check_answers(qid): conn = yield async_connect() cur = conn.cursor() sql = "UPDATE t_answer SET has_read=1 WHERE qid=%d" % qid try: data = yield cur.execute(sql) except Exception as e: data = 0 finally: cur.close() conn.close() raise gen.Return(data)
def update_question_answer(qid): # 更新答案 conn = yield async_connect() cur = conn.cursor() sql = "UPDATE t_question SET answer_count = answer_count - 1 WHERE qid = %d;" % qid try: data = yield cur.execute(sql) except Exception as e: data = 0 finally: cur.close() conn.close() raise gen.Return(data)
def create_user(username, password): # 创建用户 conn = yield async_connect() cur = conn.cursor() sql = "INSERT INTO t_user(username, password) VALUES ('%s', '%s');" % (username, password) try: data = yield cur.execute(sql) except Exception as e: data = 0 finally: cur.close() conn.close() raise gen.Return(data)
def adopt_answer(aid, qid): # 采纳答案 conn = yield async_connect() cur = conn.cursor() sql = "UPDATE t_answer SET status=TRUE WHERE aid=%d AND qid=(SELECT qid FROM t_question WHERE qid=%d);" % (aid, qid) try: data = yield cur.execute(sql) except Exception as e: data = 0 finally: cur.close() conn.close() raise gen.Return(data)
def get_user_list(): # 获取用户列表 conn = yield async_connect() cur = conn.cursor() sql = "SELECT (@r:=@r+1) AS ranks, username, point FROM t_user u, (SELECT (@r:=0)) c WHERE u.group_type = 0 ORDER BY u.point DESC LIMIT 50;" try: yield cur.execute(sql) data = cur.fetchall() except Exception as e: data = [] finally: cur.close() conn.close() raise gen.Return(data)
def get_answers(qid): conn = yield async_connect() cur = conn.cursor() sql = "SELECT a.aid, a.status, a.created_at, a.updated_at, a.content, u.username FROM t_answer a LEFT JOIN t_user u ON u.uid=a.uid WHERE qid=%d ORDER BY a.created_at ASC;" % qid try: yield cur.execute(sql) data = cur.fetchall() except Exception as e: data = [] finally: cur.close() conn.close() raise gen.Return(data)
def get_answer_status(user): conn = yield async_connect() cur = conn.cursor() sql = "SELECT SUM(c) AS answer_count FROM (SELECT qid, COUNT(qid) AS c FROM t_answer WHERE has_read=0 GROUP BY qid) AS a WHERE a.qid IN (SELECT qid FROM t_question WHERE uid=(SELECT uid FROM t_user WHERE username='******'));" % user try: yield cur.execute(sql) data = cur.fetchone() except Exception as e: data = {} finally: cur.close() conn.close() raise gen.Return(data)
def get_adopted_count(qid, user): # 获取得分数据 conn = yield async_connect() cur = conn.cursor() sql = "SELECT adopted_count FROM t_question WHERE qid=%d AND uid=(SELECT uid FROM t_user WHERE username='******')" % (qid, user) try: yield cur.execute(sql) data = cur.fetchone() except Exception as e: data = {} finally: cur.close() conn.close() raise gen.Return(data)
def get_group_by_user(username): # 获取用户组 conn = yield async_connect() cur = conn.cursor() sql = "SELECT group_type FROM t_user WHERE username='******'" % username try: yield cur.execute(sql) data = cur.fetchone() except Exception as e: data = None finally: cur.close() conn.close() raise gen.Return(data)
def get_user_by_username(username): # 获取用户名 conn = yield async_connect() cur = conn.cursor() sql = "SELECT username, email, password FROM t_user WHERE username='******';" % username try: yield cur.execute(sql) data = cur.fetchone() except Exception as e: data = {} finally: cur.close() conn.close() raise gen.Return(data)
def get_all_tags(): # 获取标签名列表 conn = yield async_connect() cur = conn.sursor() sql = "SELECT tid, tag_name FROM t_tag ORDER BY tid;" try: yield cur.execute(sql) data = cur.fetchall() except Exception as e: data = [] finally: cur.close() conn.close() raise gen.Return(data)
def delete_question_by_id(qid, user): # 删除问题 conn = yield async_connect() cur = conn.cursor() sql = "DELETE FROM t_question WHERE qid = %d AND uid = (SELECT uid FROM t_user WHERE username='******')" % ( qid, user) try: data = yield cur.execute(sql) except Exception as e: data = 0 finally: cur.close() conn.close() raise gen.Return(data)
def get_question_by_qid(qid): # 获取问题详情 conn = yield async_connect() cur = conn.cursor() sql = "SELECT q.qid, q.abstract, q.content, q.view_count, q.answer_count, q.created_at, q.updated_at, u.username, t.tag_name FROM t_question AS q LEFT JOIN t_user as u ON u.uid=q.uid LEFT JOIN t_tag as t ON q.tid=t.tid WHERE qid=%d;" % qid try: yield cur.execute(sql) data = cur.fetchone() except Exception as e: data = {} finally: cur.close() conn.close() raise gen.Return(data)
def add_point(aid, qid, user): # 增加得分 conn = yield async_connect() cur = conn.cursor() sql1 = "UPDATE t_user SET point = point + 1 WHERE uid = (SELECT uid FROM t_answer WHERE aid=%d AND qid=%d);" % (aid, qid) sql2 = "UPDATE t_question SET status=TRUE, adopted_count = adopted_count + 1 WHERE qid = %d AND uid = (SELECT uid FROM t_user WHERE username='******');" % (qid, user) try: data1 = yield cur.execute(sql1) data2 = yield cur.execute(sql2) data = data1 and data2 except Exception as e: data = 0 finally: cur.close() conn.close() raise gen.Return(data)
def get_question_by_str(s): # 查询问题 conn = yield async_connect() cur = conn.cursor() sql = "SELECT q.qid, q.abstract, q.view_count, q.answer_count, q.created_at, q.updated_at, u.username, t.tag_name FROM t_question AS q " sql += "LEFT JOIN t_user as u ON u.uid=q.uid LEFT JOIN t_tag as t ON q.tid=t.tid WHERE abstract LIKE BINARY '%{}%' OR content LIKE BINARY '%{}%';".format( s, s) try: yield cur.execute(sql) data = cur.fetchall() except Exception as e: data = [] finally: cur.close() conn.close() raise gen.Return(data)
def get_user_by_str(s): # 查询用户 conn = yield async_connect() cur = conn.cursor() sql = "SELECT rank, username, point FROM" sql += " (SELECT (@r:=@r+1) AS rank, username, point FROM t_user u, (SELECT (@r:=0)) c WHERE u.group_type = 0 ORDER BY u.point DESC) d" sql += " WHERE username LIKE BINARY '%{}%';".format(s) try: yield cur.execute(sql) data = cur.fetchall() except Exception as e: data = [] finally: cur.close() conn.close() raise gen.Return(data)
def create_answer(qid, user, content): # 创建答案 conn = yield async_connect() cur = conn.cursor() if isinstance(content, str): content = escape_string(content) sql1 = "INSERT INTO t_answer (qid, uid, content) VALUES (%d, (SELECT uid FROM t_user WHERE username='******'), '%s');" % (qid, user, content) sql2 = "UPDATE t_question SET answer_count = answer_count + 1 WHERE qid=%d;" % qid try: data = yield cur.execute(sql1) yield cur.execute(sql2) except Exception as e: data = 0 finally: cur.close() conn.close() raise gen.Return(data)
def get_unread_answer(user): conn = yield async_connect() cur = conn.cursor() # sql = "SELECT q.qid, q.abstract, u.username FROM t_answer a LEFT JOIN t_question q ON a.qid = q.qid LEFT JOIN t_user u ON u.uid = a.uid WHERE q.uid = (SELECT uid FROM t_user WHERE username='******');" % user sql = "SELECT a.qid, a.answer_count, c.abstract FROM " sql += "(SELECT qid, COUNT(qid) AS answer_count FROM t_answer WHERE has_read=0 GROUP BY qid) AS a" sql += " LEFT JOIN t_question AS c ON c.qid = a.qid WHERE a.qid IN (SELECT b.qid FROM t_question AS b" sql += " WHERE uid=(SELECT uid FROM t_user WHERE username='******'));" % user try: yield cur.execute(sql) data = cur.fetchall() except Exception as e: data = [] finally: cur.close() conn.close() raise gen.Return(data)
def check_answers(qid,user): # 更新未读答案 conn = yield async_connect() cur = conn.cursor() sql = "SELECT u.username from t_user as u LEFT JOIN t_question as q on u.uid = q.uid where q.qid = %d" %qid try: yield cur.execute(sql) data = cur.fetchone() username = data["username"] if username == user: update_answers(qid) else: data = 0 except Exception as e: data = 0 finally: cur.close() conn.close() raise gen.Return(data)
def get_tag_list(): # 获取标签列表 conn = yield async_connect() cur = conn.cursor() sql = "SELECT d.tid, d.tag_name, SUM(d.qcount) question_count, SUM(d.ucount) user_count FROM (" sql += "SELECT tid, tag_name, COUNT(tid) qcount, uid, username, COUNT(uid) ucount FROM (" sql += "SELECT q.tid, t.tag_name, t.tid, u.username, u.uid FROM t_question q" sql += " LEFT JOIN t_tag t ON t.tid = q.tid" sql += " LEFT JOIN t_user u ON u.uid = q.uid) c" sql += " GROUP BY tid, uid) d GROUP BY d.tid ORDER BY question_count DESC;" try: yield cur.execute(sql) data = cur.fetchall() except Exception as e: data = [] finally: cur.close() conn.close() raise gen.Return(data)
def check_user_has_read(user, qid): # 获取未读信息 redis = redis_connect() redis.connect() conn = yield async_connect() cur = conn.cursor() has_read = yield gen.Task(redis.sismember, 'user:has:read:%d' % qid, user) if has_read: data = 0 raise gen.Return(data) redis.sadd('user:has:read:%d' % qid, user) sql = "UPDATE t_question SET view_count = view_count + 1 WHERE qid = %d" % qid try: data = yield cur.execute(sql) except Exception as e: data = 0 finally: cur.close() conn.close() raise gen.Return(data)
def create_question(tid, username, abstract, content): # 创建问题 conn = yield async_connect() cur = conn.cursor() if isinstance(abstract, str): abstract = escape_string(abstract) if isinstance(content, str): content = escape_string(content) sql1 = "INSERT INTO t_question (abstract, content, uid, tid) VALUES ('%s', '%s', (SELECT uid FROM t_user WHERE username='******'), %d);" % ( abstract, content, username, tid) sql2 = "SELECT LAST_INSERT_ID() as qid FROM t_question;" try: data = yield cur.execute(sql1) yield cur.execute(sql2) last_insert = cur.fetchone() except Exception as e: data = 0 last_insert = {} finally: cur.close() conn.close() raise gen.Return((data, last_insert.get('qid', None)))