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 connect_master(self): if self.connect_flag: self.cur.close() self.conn.close() self.connect_flag = False configer = Configer() host = configer.get_configer('MYSQL', 'host') port = configer.get_configer('MYSQL', 'port') user = configer.get_configer('MYSQL', 'user') passwd = configer.get_configer('MYSQL', 'passwd') db = configer.get_configer('MYSQL', 'db') charset = configer.get_configer('MYSQL', 'charset') try: self.conn = MySQLdb.connect(host=host, port=int(port), user=user, passwd=passwd, db=db, charset=charset) self.cur = self.conn.cursor() self.connect_flag = True except MySQLdb.Error, e: self.status = self.status_enum.CONN_ERR msg = 'connect failed' LOG.error('Error:%s' % str(e)) raise DBException(msg)
def connect_master(self): if self.connect_flag: self.cur.close() self.conn.close() self.connect_flag = False configer = Configer() host = configer.get_configer('MYSQL','host') port = configer.get_configer('MYSQL','port') user = configer.get_configer('MYSQL','user') passwd = configer.get_configer('MYSQL','passwd') db = configer.get_configer('MYSQL','db') charset = configer.get_configer('MYSQL','charset') try: self.conn = MySQLdb.connect( host = host, port = int(port), user = user, passwd = passwd, db = db, charset = charset) self.cur = self.conn.cursor() self.connect_flag = True except MySQLdb.Error,e: self.status = self.status_enum.CONN_ERR msg = 'connect failed' LOG.error('Error:%s' % str(e)) raise DBException(msg)
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 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 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 select_collection(self,collection): try: self.collection = self.db[collection] except ServerSelectionTimeoutError as e: LOG.error('mongo select failed [%s]' % e) raise DBException('mongo select failed')
def select_collection(self, collection): try: self.collection = self.db[collection] except ServerSelectionTimeoutError as e: LOG.error('mongo select failed [%s]' % e) raise DBException('mongo select failed')
def query(self,sql,**kwds): try: self.sql = sql % kwds LOG.info('execute SQL[%s]' % (self.sql)) self.cur.execute(self.sql) except MySQLdb.Warning,w: LOG.warn('Warning:%s' % str(w)) self.status = self.status_enum.QUERY_WAR
def query(self, sql, **kwds): try: self.sql = sql % kwds LOG.info('execute SQL[%s]' % (self.sql)) self.cur.execute(self.sql) except MySQLdb.Warning, w: LOG.warn('Warning:%s' % str(w)) self.status = self.status_enum.QUERY_WAR
def leave_func(self, code): if 0 == code: LOG.debug('%s(%s) ...' % (self.__class__.__name__, json.dumps(self.request.arguments, ensure_ascii=False))) else: LOG.debug('%s(%s) %s' % (self.__class__.__name__, json.dumps(self.request.arguments, ensure_ascii=False), error_process(code))) self.write(error_process(code))
def connect(self,db): configer = Configer() mongo_host = configer.get_configer('MONGO','host') mongo_port = int(configer.get_configer('MONGO','port')) try: self.client = MongoClient(host = mongo_host,port = mongo_port) self.db = self.client[db] except ConnectionFailure,e: LOG.error('mongo connect failed [%s]' % e) raise DBException('mongo connect failed')
def _upload_qiniu(self, ori_url): from gl import LOG LOG.info('Original Image Url [%s]' % ori_url) if not self.img_url_exp.match(ori_url): suffix = ori_url[ori_url.rfind('.'):] qiniu_file_name = md5(ori_url).hexdigest() + suffix LOG.info('Open Refer Imgage[%s]' % ori_url) request = urllib2.Request(ori_url) response = urllib2.urlopen(request) img_data = response.read() #LOG.info('img data [%s]' % img_data) qiniu = QiniuWrap() res = qiniu.upload_data('qdimg', qiniu_file_name, img_data) if not res: qiniu_url = self.qiniu_prefix % ('qdimg', qiniu_file_name) LOG.info('[%s] local [%s] successful' % (ori_url, qiniu_url)) return qiniu_url else: LOG.error('upload qiniu error [%s]' % res) return None
def _upload_qiniu(self,ori_url): from gl import LOG LOG.info('Original Image Url [%s]' % ori_url) if not self.img_url_exp.match(ori_url): suffix = ori_url[ori_url.rfind('.'):] qiniu_file_name = md5(ori_url).hexdigest() + suffix LOG.info('Open Refer Imgage[%s]' % ori_url) request = urllib2.Request(ori_url) response = urllib2.urlopen(request) img_data = response.read() #LOG.info('img data [%s]' % img_data) qiniu = QiniuWrap() res = qiniu.upload_data('qdimg',qiniu_file_name,img_data) if not res: qiniu_url = self.qiniu_prefix % ('qdimg',qiniu_file_name) LOG.info('[%s] local [%s] successful' % (ori_url,qiniu_url)) return qiniu_url else: LOG.error('upload qiniu error [%s]' % res) return None
def get(self): enter_func(self) if not set(['doc_id']).issubset(self.request.arguments.keys()): return leave_func(self, 1) doc_id = self.request.arguments['doc_id'][0] if 'uid' in self.request.arguments.keys(): uid = self.request.arguments['uid'][0] LOG.info('data_statistics: %s' % {'uid': uid, 'doc_id': doc_id, 'func': self.__class__.__name__}) url = 'http://wenku.baidu.com/api/interface/download?doc_id=%s&token=%s&host=%s' % (doc_id, generate_token(), host) LOG.info(url) result = error_process(0) result['url'] = url leave_func(self, 0) return self.write(result)
def connect(self, db): configer = Configer() mongo_host = configer.get_configer('MONGO', 'host') mongo_port = int(configer.get_configer('MONGO', 'port')) try: self.client = MongoClient(host=mongo_host, port=mongo_port) self.db = self.client[db] except ConnectionFailure, e: LOG.error('mongo connect failed [%s]' % e) raise DBException('mongo connect failed')
def get(self): enter_func(self) if not set(['word']).issubset(self.request.arguments.keys()): return leave_func(self, 1) if 'page_num' in self.request.arguments.keys() and self.request.arguments['page_num'][0]: page_num = int(self.request.arguments['page_num'][0]) else: page_num = 1 if 'page_size' in self.request.arguments.keys() and self.request.arguments['page_size'][0]: page_size = int(self.request.arguments['page_size'][0]) else: page_size = 10 keyword = self.request.arguments['word'][0] if 'uid' in self.request.arguments.keys() and self.request.arguments['uid'][0]: uid = int(self.request.arguments['uid'][0]) LOG.info('data_statistics: %s' % {'uid': uid, 'keyword': keyword, 'func': self.__class__.__name__}) if not keyword or page_num < 1 or page_size < 1: return leave_func(self, 1) url = 'http://wenku.baidu.com/api/interface/search?%s' % urllib.urlencode({ 'word': keyword, 'pn': page_num, 'rn': page_size, 'token': generate_token(), 'host': host }) LOG.info(url) docs = json.loads(urllib2.urlopen(url).read().decode('raw_unicode_escape')) LOG.info(docs) if 0 != docs['status']['code']: LOG.error(docs['status']) return leave_func(self, 100) ret = dict(error_process(0).items() + docs['data'].items()) if 'jsonp' in self.request.arguments.keys(): jsonp = self.request.arguments['jsonp'][0] leave_func(self, 0) return self.write('%s(%s)' % (jsonp, json.dumps(ret, ensure_ascii=False))) leave_func(self, 0) return self.write(json.dumps(ret, ensure_ascii=False))
def get(self): enter_func(self) if not set(['id']).issubset(self.request.arguments.keys()): return leave_func(self, 1) id = int(self.request.arguments['id'][0]) mysql_handle = Mysql().get_handle() mysql_cursor = mysql_handle.cursor(MySQLdb.cursors.DictCursor) sql = 'UPDATE entity_question SET state = "DISABLED" WHERE id = %d' % id LOG.info('mysql> %s' % sql) mysql_cursor.execute(sql) mysql_handle.commit() mysql_cursor.close() mysql_handle.close() leave_func(self, 0) return self.write(error_process(0))
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 chapter_id_exist(chapter_id): mysql = Mysql() mysql.connect_master() query_sql = "select 1 from entity_teaching_chapter where id='%(chapter_id)d';" try: if mysql.query(query_sql, chapter_id=int(chapter_id)): return True else: return False except DBException as e: LOG.error('check chapter error [%s]' % e) raise CKException('check chapter error')
def group_id_exist(group_id): mysql = Mysql() mysql.connect_master() query_sql = "select 1 from entity_group where id = '%(group_id)d';" try: if mysql.query(query_sql,group_id = int(group_id)): return True else: return False except DBException as e: LOG.error('check topic error [%s]' % e) raise CKException('check topic error')
def chapter_id_exist(chapter_id): mysql = Mysql() mysql.connect_master() query_sql = "select 1 from entity_teaching_chapter where id='%(chapter_id)d';" try: if mysql.query(query_sql,chapter_id = int(chapter_id)): return True else: return False except DBException as e: LOG.error('check chapter error [%s]' % e) raise CKException('check chapter error')
def subprocess(self, cmd, callback): ioloop = tornado.ioloop.IOLoop.instance() args = shlex.split(cmd) LOG.debug(args) pipe = subprocess.Popen(args, stdout = subprocess.PIPE, close_fds = True) fd = pipe.stdout.fileno() result = [] def recv(*args): data = pipe.stdout.readline() if data: result.append(data) elif pipe.poll() is not None: ioloop.remove_handler(fd) callback(''.join(result)) ioloop.add_handler(fd, recv, ioloop.READ)
def update_json_by_id(oldid,json): mysql = Mysql() mysql.connect_master() query_sql = "update entity_question_new set json='%(question_json)s' where oldid=%(oldid)d;" try: if mysql.query(query_sql,oldid = int(oldid),question_json = json): return True else: return False except DBException as e: LOG.error('update json error [%s]' % e) raise CkException('update json error')
def is_type(type_id): mysql = Mysql() mysql.connect_master() query_sql = "select name from entity_question_type where type_id = %(type_id)d and enable = 1;" try: if mysql.query(query_sql,type_id = int(type_id)): return mysql.fetch()[0] else: return False except DBException as e: LOG.error('check type error [%s]' % e) raise CkException('check type error')
def is_seriess(seriess_id): mysql = Mysql() mysql.connect_master() query_sql = "select 1 from entity_seriess where id = %(seriess_id)d;" try: if mysql.query(query_sql,seriess_id = int(seriess_id)): return True else: return False except DBException as e: LOG.error('check seriess error [%s]' % e) raise CkException('check seriess error')
def is_topic(topic_id): mysql = Mysql() mysql.connect_master() query_sql = "select 1 from entity_topic where id = %(topic_id)d;" try: if mysql.query(query_sql,topic_id = int(topic_id)): return True else: return False except DBException as e: LOG.error('check topic error [%s]' % e) raise CKException('check topic error')
def subprocess(self, cmd, callback): ioloop = tornado.ioloop.IOLoop.instance() args = shlex.split(cmd) LOG.debug(args) pipe = subprocess.Popen(args, stdout=subprocess.PIPE, close_fds=True) fd = pipe.stdout.fileno() result = [] def recv(*args): data = pipe.stdout.readline() if data: result.append(data) elif pipe.poll() is not None: ioloop.remove_handler(fd) callback(''.join(result)) ioloop.add_handler(fd, recv, ioloop.READ)
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_mark(oldid,newid,mark): mysql = Mysql() mysql.connect_master() query_sql = "insert into entity_question_mark (oldid,newid,mark,mark_time) values (%(oldid)d,%(newid)d,%(mark)d,now());" try: if mysql.query(query_sql,oldid = oldid,newid = newid,mark = mark): return 'success' else: return None except DBException as e: LOG.error('mark error [%s]' % e) raise CkException('mark 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')
def verify(username,oldid,newid,verify): mysql = Mysql() mysql.connect_master() query_sql = "insert into entity_verify (username,oldid,newid,state) values ('%(username)s',%(oldid)d,%(newid)d,%(verify)d);" try: if mysql.query(query_sql,username = username,oldid = int(oldid),newid = int(newid),verify = int(verify)): return mysql.get_last_id() else: return None except DBException as e: LOG.error('add mark error [%s]' % e) raise CkException('add mark error')
def get_json_by_id(oldid): mysql = Mysql() mysql.connect_master() query_sql = "select json from entity_question_new where oldid=%(oldid)d;" try: if mysql.query(query_sql,oldid = int(oldid)): json = mysql.fetch()[0] return json else: return False except DBException as e: LOG.error('get json error [%s]' % e) raise CkException('get json error')
def add_mark(name): mysql = Mysql() mysql.connect_master() query_sql = "insert into link_question_mark (name,mark_time) values ('%(name)s',now());" try: if mysql.query(query_sql,name = name): return mysql.get_last_id() else: return None except DBException as e: LOG.error('add mark error [%s]' % e) raise CkException('add mark error')
def get(self): enter_func(self) if not set(['doc_id']).issubset(self.request.arguments.keys()): return leave_func(self, 1) doc_id = self.request.arguments['doc_id'][0] if 'uid' in self.request.arguments.keys(): uid = self.request.arguments['uid'][0] LOG.info('data_statistics: %s' % { 'uid': uid, 'doc_id': doc_id, 'func': self.__class__.__name__ }) url = 'http://wenku.baidu.com/api/interface/download?doc_id=%s&token=%s&host=%s' % ( doc_id, generate_token(), host) LOG.info(url) result = error_process(0) result['url'] = url leave_func(self, 0) return self.write(result)
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 add_user(username,password): password = Base.md5(password) mysql = Mysql() mysql.connect_master() query_sql = "insert into verify_user (username,password) values ('%(username)s','%(password)s');" try: if mysql.query(query_sql,username = username,password = password): return mysql.get_last_id() else: return None except DBException as e: LOG.error('add user error [%s]' % e) raise CkException('add user error')
def get(self): enter_func(self) if not set(['url']).issubset(self.request.arguments.keys()): _leave_func(self, 1, {}) return url = urllib.quote(urllib.quote(self.request.arguments['url'][0])) cmds = [] cmds.append(self.gen_cmd(url, 'ChromelessView&Embed=1', '6', '2')) cmds.append(self.gen_cmd(url, 'SlideShowView', '1', '0')) cmds.append(self.gen_cmd(url, 'ReadingView', '0', '0')) cmds.append(self.gen_cmd(url, 'ReadingView', '0', '4')) for cmd in cmds: LOG.info(cmd) stdoutput = yield gen.Task(self.subprocess, cmd) LOG.debug(stdoutput) if json.loads(stdoutput)['Error']: _leave_func(self, 2, {}) return _leave_func(self, 0, {})
def check_user(username,password): password = Base.md5(password) mysql = Mysql() mysql.connect_master() query_sql = "select password from verify_user where username='******';" try: if mysql.query(query_sql,username = username): pwd = mysql.fetch()[0] if password == pwd: return True else: return False except DBException as e: LOG.error('check user error [%s]' % e) raise CkException('check user error')
def get(self): enter_func(self) params = {'token': generate_token(), 'host': host, 'zone': 8} if 'zone' in self.request.arguments.keys( ) and self.request.arguments['zone'][0].isdigit(): params['zone'] = int(self.request.arguments['zone'][0]) redis_handle = redis.Redis(Configer().get_configer('REDIS', 'host')) if redis_handle.exists('json_class:%d' % params['zone']): leave_func(self, 0) return self.write( redis_handle.get('json_class:%d' % params['zone'])) url = 'http://wenku.baidu.com/api/interface/getclass?%s' % urllib.urlencode( params) LOG.info(url) ret = json.loads( urllib2.urlopen(url).read().decode('raw_unicode_escape')) LOG.info(ret) if 0 != ret['status']['code']: LOG.error('baidu library interface error: %s' % ret['status']) return leave_func(self, 100) result = error_process(0) result['data'] = ret['data'] result = json.dumps(result, sort_keys=True, ensure_ascii=False) redis_handle.set('json_class:%d' % params['zone'], result) leave_func(self, 0) return self.write(result)
def get(self): enter_func(self) if not set(['subject', 'grade', 'version']).issubset(self.request.arguments.keys()): return leave_func(self, 1) params = { 'subject': self.request.arguments['subject'][0], 'grade': self.request.arguments['grade'][0], 'version': self.request.arguments['version'][0] } if 'unit' in self.request.arguments.keys(): params['Unite'] = self.request.arguments['unit'][0] if 'lesson' in self.request.arguments.keys(): params['Lesson'] = self.request.arguments['lesson'][0] if 'word' in self.request.arguments.keys(): params['word'] = self.request.arguments['word'][0] if 'stype' in self.request.arguments.keys(): params['stype'] = self.request.arguments['stype'][0] if 'order' in self.request.arguments.keys(): params['order'] = self.request.arguments['order'][0] if 'page_num' in self.request.arguments.keys(): params['pn'] = self.request.arguments['page_num'][0] else: params['pn'] = 1 if 'page_size' in self.request.arguments.keys(): params['rn'] = self.request.arguments['page_size'][0] params['token'] = generate_token() params['host'] = host url = 'http://wenku.baidu.com/api/interface/getsubject?%s' % urllib.urlencode(params) LOG.info(url) ret = json.loads(urllib2.urlopen(url).read().decode('raw_unicode_escape')) LOG.info(ret) if 0 != ret['status']['code']: LOG.error('baidu library interface error: %s' % ret['status']) return leave_func(self, 100) ret = dict(error_process(0).items() + ret['data'].items()) leave_func(self, 0) return self.write(json.dumps(ret, ensure_ascii=False))