Python Mysql.fetchall方法代码示例

# 需要导入模块: from mysql import Mysql [as 别名]
# 或者: from mysql.Mysql import fetchall [as 别名]
	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')

# 需要导入模块: from mysql import Mysql [as 别名]
# 或者: from mysql.Mysql import fetchall [as 别名]
	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')

# 需要导入模块: from mysql import Mysql [as 别名]
# 或者: from mysql.Mysql import fetchall [as 别名]
	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')

# 需要导入模块: from mysql import Mysql [as 别名]
# 或者: from mysql.Mysql import fetchall [as 别名]
	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')

# 需要导入模块: from mysql import Mysql [as 别名]
# 或者: from mysql.Mysql import fetchall [as 别名]
	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')

# 需要导入模块: from mysql import Mysql [as 别名]
# 或者: from mysql.Mysql import fetchall [as 别名]
	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')

# 需要导入模块: from mysql import Mysql [as 别名]
# 或者: from mysql.Mysql import fetchall [as 别名]
	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')

# 需要导入模块: from mysql import Mysql [as 别名]
# 或者: from mysql.Mysql import fetchall [as 别名]

#.........这里部分代码省略.........
        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（取消订阅指定微博）"

# 需要导入模块: from mysql import Mysql [as 别名]
# 或者: from mysql.Mysql import fetchall [as 别名]
	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}