def get_systematics(question_id): mysql = Mysql() mysql.connect_master() query_sql = "select D.name as module_name,C.name as unit_name,B.name as topic_name from (select topic_id from link_question_topic where question_id=%(question_id)d)A left outer join (select id,name,unit_id from entity_topic)B on (A.topic_id=B.id) left outer join (select id,name,module_id from entity_unit)C on (B.unit_id=C.id) left outer join (select id,name from entity_module)D on (C.module_id=D.id);" try: if mysql.query(query_sql,question_id = int(question_id)): res = mysql.fetchall() systematics_list = [] for line in res: module = line[0] unit = line[1] topic = line[2] systematics_dict = {'module':module,'unit':unit,'topic':topic} systematics_list.append(systematics_dict) return systematics_list else: return False except DBException as e: LOG.error('get systematics error [%s]' % e) raise CKException('get systematics error')
def get_group_list(system_id): mysql = Mysql() mysql.connect_master() query_sql = "select A.id,A.name,B.num from (select id,name from entity_group where system_id=%(system_id)d or id=0)A left outer join (select question_group,count(1) as num from entity_question where upload_id=%(system_id)d group by question_group)B on (A.id=B.question_group);" try: if mysql.query(query_sql,system_id = system_id): res = mysql.fetchall() group_list = [] for line in res: group_id = line[0] group_name = line[1] question_num = int(line[2]) if line[2] else 0 group_dict = {'id':int(group_id),'name':group_name,'num':int(question_num)} group_list.append(group_dict) return group_list else: return False except DBException as e: LOG.error('check topic error [%s]' % e) raise CKException('check topic error')
def q_subject_list(): mysql = Mysql() mysql.connect_master() query_sql = "select distinct subject from entity_question_new where type is not null;" subject_list = [] try: if mysql.query(query_sql): subject_tuple = mysql.fetchall() for type in subject_tuple: tmp_tuple = (type[0]) subject_list.append(tmp_tuple) return subject_list else: return None except DBException as e: LOG.error('get subject error [%s]' % e) raise CkException('get subject error')
def q_mark_list(): mysql = Mysql() mysql.connect_master() query_sql = "select id,name from link_question_mark where enable=1;" mark_list = [] try: if mysql.query(query_sql): mark_tuple = mysql.fetchall() for mark in mark_tuple: tmp_tuple = (mark[0],mark[1]) mark_list.append(tmp_tuple) return mark_list else: return None except DBException as e: LOG.error('get mark error [%s]' % e) raise CkException('get mark error')
def get_group_list(system_id): mysql = Mysql() mysql.connect_master() query_sql = "select A.id,count(1) from entity_group A,entity_question B where (A.system_id=%(system_id)d or A.id=0) and A.enable=1 and B.upload_id=%(system_id)d and A.id=B.question_group group by B.question_group;" group_sql = "select id,name from entity_group where (system_id=%(system_id)d or id=0) order by create_time desc;" try: group_dict = OrderedDict() group_list = [] default_num = 0 mysql.query(group_sql, system_id=system_id) group_res = mysql.fetchall() mysql.query(query_sql, system_id=system_id) num_res = mysql.fetchall() for group in group_res: group_id = group[0] group_name = group[1] group_dict[group_id] = { 'id': int(group_id), 'name': group_name, 'num': 0 } for num in num_res: gid = num[0] num = num[1] group_dict[gid]['num'] = num for gid in group_dict: if 0 == gid: default_num = group_dict[gid]['num'] continue group_list.append(group_dict[gid]) return group_list, default_num except DBException as e: LOG.error('check topic error [%s]' % e) raise CKException('check topic error')
def get_group_list(system_id): mysql = Mysql() mysql.connect_master() query_sql = "select A.id,count(1) from entity_group A,entity_question B where (A.system_id=%(system_id)d or A.id=0) and A.enable=1 and B.upload_id=%(system_id)d and A.id=B.question_group group by B.question_group;" group_sql = "select id,name from entity_group where (system_id=%(system_id)d or id=0) order by create_time desc;" try: group_dict = OrderedDict() group_list = [] default_num = 0 mysql.query(group_sql,system_id = system_id) group_res = mysql.fetchall() mysql.query(query_sql,system_id = system_id) num_res = mysql.fetchall() for group in group_res: group_id = group[0] group_name = group[1] group_dict[group_id] = {'id':int(group_id),'name':group_name,'num':0} for num in num_res: gid = num[0] num = num[1] group_dict[gid]['num'] = num for gid in group_dict: if 0 == gid: default_num = group_dict[gid]['num'] continue group_list.append(group_dict[gid]) return group_list,default_num except DBException as e: LOG.error('check topic error [%s]' % e) raise CKException('check topic error')
def q_subject_filter(type,start,num): mysql = Mysql() mysql.connect_master() query_sql = "select oldid,subject from entity_question_new where subject = '%(type)s' limit %(start)d,%(num)d;" try: if mysql.query(query_sql,type = type,start = start,num = num): return mysql.fetchall() else: return None except DBException as e: LOG.error('filtet type error [%s]' % e) raise CkException('filter type error')
def q_subject_filter_num(type): mysql = Mysql() mysql.connect_master() query_sql = "select count(*) from entity_question_new where subject = '%(type)s';" try: if mysql.query(query_sql,type = type): return mysql.fetchall()[0][0] else: return None except DBException as e: LOG.error('filtet type error [%s]' % e) raise CkException('filter type error')
class Subscribe: def __init__(self): db_config = config.getConfig('db') weibo_config = config.getConfig('weibo') app_config = config.getConfig('app') self.db = Mysql(db_config['host'], db_config['user'], db_config['passwd'], db_config['db'], db_config['charset']) self.user = User() self.wb = Weibo(weibo_config['username'], weibo_config['password'], weibo_config['appkey']) self.config = app_config def add(self, gtalk, url): print 'add %s %s' % (gtalk, url) if not self.user.checkUrl(url): return '微博URL格式错误!' if url == 'http://weibo.com/u/2268327783': return '您不能订阅我哦~' # 查询已订阅条数 sql = "SELECT COUNT(uid) cnt FROM subs WHERE gtalk='%s'" % gtalk count = self.db.fetchone(sql) if count and int(count) >= int(self.config['sub_limit']): return '您最多只能订阅%d条哦!' % int(self.config['sub_limit']) # 查询是否已订阅 sql = "SELECT COUNT(uid) cnt FROM subs WHERE gtalk='%s' AND url='%s'" % (gtalk, url) count = self.db.fetchone(sql) if count and int(count) > 0: return '您已经订阅:%s!' % url # 查询url-uid对应表 (uid, nick) = self.user.getUidfromurl(self.db, url) if uid == -1: return '参数格式错误!' if uid == 0: return '账号不存在!' # 查询是否已关注 sql = "SELECT count FROM follows WHERE uid=%d" % uid count = self.db.fetchone(sql) if count and int(count) > 0: # 关注表+1 sql = "UPDATE follows SET count=count+1 WHERE uid=%d" % uid else: # 关注用户 self.wb.Follow(uid) # 插入关注表 sql = "INSERT INTO follows (uid, count) VALUES (%d, 1)" % uid self.db.query(sql) # 插入订阅表 sql = "INSERT INTO subs (gtalk, url, uid, time) VALUES ('%s', '%s', %d, %d)" % (gtalk, url, uid, int(time.time())) self.db.query(sql) # 更新用户表 sql = "REPLACE INTO users (uid, nick) VALUES (%d, '%s')" % (uid, nick) self.db.query(sql) return '%s 订阅成功!' % nick def delete(self, gtalk, url): print 'delete %s %s' % (gtalk, url) if not self.user.checkUrl(url): return '微博URL格式错误!' # 查询是否已订阅 sql = "SELECT COUNT(uid) cnt FROM subs WHERE gtalk='%s' AND url='%s'" % (gtalk, url) count = self.db.fetchone(sql) if count == None or int(count) == 0: return '您还未订阅:%s!' % url # 查询url-uid对应表 (uid, nick) = self.user.getUidfromurl(self.db, url) if uid == -1: return '参数格式错误!' if uid == 0: return '账号不存在!' # 查询是否已关注 sql = "SELECT count FROM follows WHERE uid=%d" % uid count = self.db.fetchone(sql) if count: if int(count) <= 1: # 删除关注表记录 sql = "DELETE FROM follows WHERE uid=%d" % uid # 取消用户 self.wb.Unfollow(uid) else: # 关注表-1 sql = "UPDATE follows SET count=count-1 WHERE uid=%d" % uid self.db.query(sql) # 删除订阅表记录 sql = "DELETE FROM subs WHERE gtalk='%s' AND url='%s'" % (gtalk, url) self.db.query(sql) return '删除成功!' def list(self, gtalk): print 'list %s' % gtalk # 查询订阅表 sql = "SELECT subs.url, subs.uid, users.nick FROM subs, users WHERE subs.uid=users.uid AND subs.gtalk='%s' ORDER BY subs.time ASC" % gtalk results = self.db.fetchall(sql) if results: retstr = "您订阅的用户有:\n" for (idx, (url, uid, nick)) in enumerate(results): retstr = "%s\n%d. %s(%s)" % (retstr, (idx+1), nick, url) retstr = "%s\n\n回复'delete #对应url#'删除指定订阅" % retstr else: retstr = '您还未订阅任何用户!' return retstr def default(self, gtalk): return "错误命令!\n\n命令:\nlist(订阅列表)\nadd http://weibo.com/songerzhou或add http://weibo.com/u/1671527551(订阅指定微博)\ndelete http://weibo.com/songerzhou或delete http://weibo.com/u/1671527551(取消订阅指定微博)"
def _old_question(data): for i in range(1): mysql = Mysql() try: mysql.connect_master() search_sql = "select id,question_body,question_options,question_answer,question_analysis,question_type,difficulty from entity_question_old where id = %(question_id)d;" if 0 == mysql.query(search_sql,question_id = int(data)): return None else: question_set = mysql.fetch() children_sql = "select id,question_body,question_options,question_answer,question_analysis,question_type,difficulty from entity_question_old where parent_question_id = %(parent_id)d order by id;" if 0 == mysql.query(children_sql,parent_id = int(data)): children_set = None else: children_set = mysql.fetchall() except DBException as e: break domain = "http://%s.okjiaoyu.cn/%s" question_body = question_set[1] question_option = question_set[2] question_answer = question_set[3] question_analysis = question_set[4] question_type = question_set[5] question_level = question_set[6] url_list = [] body_url = '' option_url = '' answer_url = '' analysis_url = '' if question_body is not None: body_bucket = question_body[0:2] body_url = domain % (body_bucket,question_body) url_list.append(body_url) if question_option is not None: option_bucket = question_option[0:2] option_url = domain % (option_bucket,question_option) url_list.append(option_url) if question_answer is not None: answer_bucket = question_answer[0:2] answer_url = domain % (answer_bucket,question_answer) url_list.append(answer_url) if question_analysis is not None: analysis_bucket = question_analysis[0:2] analysis_url = domain % (analysis_bucket,question_analysis) url_list.append(analysis_url) if children_set is not None: for children in children_set: for i in range(1,5): if children[i] is not None: children_bucket = children[i][0:2] children_url = domain % (children_bucket,children[i]) url_list.append(children_url) return {'url_list' : url_list,'type' : question_type,'level' : question_level,'q_old_id' : data}
def _old_question(data): for i in range(1): mysql = Mysql() try: mysql.connect_master() search_sql = "select id,question_body,question_options,question_answer,question_analysis,question_type,difficulty from entity_question_old where id = %(question_id)d;" if 0 == mysql.query(search_sql, question_id=int(data)): return None else: question_set = mysql.fetch() children_sql = "select id,question_body,question_options,question_answer,question_analysis,question_type,difficulty from entity_question_old where parent_question_id = %(parent_id)d order by id;" if 0 == mysql.query(children_sql, parent_id=int(data)): children_set = None else: children_set = mysql.fetchall() except DBException as e: break domain = "http://%s.okjiaoyu.cn/%s" question_body = question_set[1] question_option = question_set[2] question_answer = question_set[3] question_analysis = question_set[4] question_type = question_set[5] question_level = question_set[6] url_list = [] body_url = '' option_url = '' answer_url = '' analysis_url = '' if question_body is not None: body_bucket = question_body[0:2] body_url = domain % (body_bucket, question_body) url_list.append(body_url) if question_option is not None: option_bucket = question_option[0:2] option_url = domain % (option_bucket, question_option) url_list.append(option_url) if question_answer is not None: answer_bucket = question_answer[0:2] answer_url = domain % (answer_bucket, question_answer) url_list.append(answer_url) if question_analysis is not None: analysis_bucket = question_analysis[0:2] analysis_url = domain % (analysis_bucket, question_analysis) url_list.append(analysis_url) if children_set is not None: for children in children_set: for i in range(1, 5): if children[i] is not None: children_bucket = children[i][0:2] children_url = domain % (children_bucket, children[i]) url_list.append(children_url) return { 'url_list': url_list, 'type': question_type, 'level': question_level, 'q_old_id': data }