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_question_by_id(self,question_id): mysql = Mysql() try: mysql.connect_master() search_sql = "select A.Knowledge,B.TypeValue,A.QuesAbility,A.QuesDiff,A.QuesBody,A.QuesAnswer,A.QuesParse from(select Knowledge,QuesType,QuesAbility,QuesDiff,QuesBody,QuesAnswer,QuesParse from paper.paper where ID=%(question_id)d union all select Knowledge,QuesType,QuesAbility,QuesDiff,QuesBody,QuesAnswer,QuesParse from paper.cz_paper where ID=%(question_id)d)A left outer join (select TypeId,TypeValue from paper.questype)B on (A.QuesType=B.TypeId);" if 0 == mysql.query(search_sql,question_id = int(question_id)): return None else: question_set = mysql.fetch() except DBException as e: pass question_ability_dict ={'1':'了解和识记','2':'理解和掌握','3':'简单应用','4':'综合应用'} question_diff_dict = {'1':'容易','2':'较易','3':'一般','4':'较难','5':'困难'} question_dict = {} question_dict['knowledge'] = question_set[0] question_dict['type'] = question_set[1] question_dict['ability'] = question_ability_dict[question_set[2]] question_dict['diff'] = question_diff_dict[question_set[3]] question_dict['body'] = question_set[4] question_dict['answer'] = question_set[5] question_dict['parse'] = question_set[6] return question_dict
def _new_question(data): for i in range(1): mysql = Mysql() try: mysql.connect_master() search_sql = "select id,json,subject,type from entity_question_new where oldid = %(oldid)d;" if 0 == mysql.query(search_sql,oldid = int(data)): return None else: question_set = mysql.fetch() except DBException as e: break newid = question_set[0] question_json = question_set[1] question_subject = question_set[2] question_type = question_set[3] new_question_dict = {} new_question_dict['q_new_id'] = newid new_question_dict['new_question'],new_question_dict['blank_num'] = Business.q_json_parse(question_type,question_json) new_question_dict['subject'] = question_subject #print new_question_dict return new_question_dict
def evaluate_db(): ''' 从数据库中得到pre_cluster :param gt: :return: ''' feature_path = 'C:\\Users\\xiongraorao\\Desktop\\facescrub_feature.txt' _, y, img_path = load_feature(feature_path) pre_label = [] db = Mysql('192.168.1.11', 3306, 'root', '123456', 'face_reid') logger = Log('visualize', is_save=False) db.set_logger(logger) from tqdm import tqdm for img in tqdm(img_path): file_name = img.split('/')[-1] sql = 'select cluster_id from t_cluster2 where uri like {}'.format( "\"%" + file_name + "\"") label = db.select(sql) pre_label.append(label[0]) # evaluate assert len(y) == len( pre_label), 'gt and predict label have different length' score = cluster_eval(y, pre_label) print('evaluate score: ', score)
def add_data(self, data): now = int(time.time()) nowDate = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(now)) mysql = Mysql() row = mysql.select_row("select * from entry where entry_url=%s", *[data['url']]) # 记录是否存在 if row is None: # 不存在则插入 data = { "entry_title": data['title'], "entry_url": data['url'], "entry_content": data['content'], "add_time": now, "update_time": now, "add_date": nowDate, "update_date": nowDate, } insert_id = mysql.insert("entry", **data) print('\033[1;33m' + '【INSERT】' + '\033[0m') return insert_id else: # 存在则更新 keys = [data['title'], data['content'], now, nowDate, data['url']] row_count = mysql.update( "update entry set entry_title=%s,entry_content=%s,update_time=%s,update_date=%s,entry_times=entry_times+1 where entry_url=%s", *keys) print('\033[1;34m' + '【UPDATE】' + '\033[0m') return row_count
def connect(self): if not self.connection: self.connection = Mysql(host=self.host, db=self.db, user=self.user, password=self.password) self.connection.connect() return self.connection
def load_questions(): cnf = config.MYSQL db = Mysql(*cnf) sql = "SELECT id, question, answer, store_id from question" r = db.execute(sql) result = list(r.fetchall()) db.close() return result
def save_urls(): db = Mysql(**db_conf) scrawler = Scrawler(**web_conf) for page_num in range(1, 251): data = [['url', 'page_num', 'flag']] urls = scrawler.parse_urls(page_num) for url in urls: data.append([url, page_num, '0']) db.insert_ignore('urls', data) time.sleep(2)
def list_users(): page = int(request.args.get('page', "0")) np = int(request.args.get('numOfitems', "20")) db = Mysql() res = db.list_users(page=page, numOfitems=np) result = {"users": "{}".format(res), "count": len(res), "page": page} return result
def delete_users(id): db = Mysql() result = db.delete_users(id) result = {"message": "ok"} if result is None else result response = application.response_class(response=json.dumps(result), status=200, mimetype='application/json') return response
def update_users(id): j = request.get_json() db = Mysql() result = db.update_users(id, j) result = {"message": "ok"} if result is None else result response = application.response_class(response=json.dumps(result), status=200, mimetype='application/json') return response
class BoseModel(Model): conn = Mysql(host='127.0.0.1', unix_socket='/tmp/mysql.sock', user='******', passwd='123456', db='wanghong', charset='utf8')
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 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 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 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 find_code(city_name): all_cc = all_city_data() mysql = Mysql() for i in all_cc: city_name = i url = 'http://www.nmc.cn/f/rest/tempchart/' + all_cc[i]['code'] response = request.urlopen(url) page = response.read() page = bytes.decode(page) content = eval(page) for i in content: sql = "insert into tempchart(city,cur_date,max_temp,min_temp) value " + "('" + city_name + "','" + str( i['time']) + "','" + str(i['max_temp']) + "','" + str( i['min_temp']) + "')" mysql.insert(sql) print('城市:' + city_name + ',' + '日期:' + str(i['time']) + ',' + '最高温度:%s,最低温度:%s' % (i['max_temp'], i['min_temp']))
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 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 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 lista_equipos(cls): """ carga en un diccionario los nombres de los equipos de la base de datos, lay key el nombre el valor el id """ if len(cls.__equipos.keys()) == 0: bd = Mysql.conectar() bd.execute('SELECT nombre, id_equipo FROM equipo ORDER BY nombre') for equipo in bd.fetchall(): cls.__equipos[equipo['nombre']] = equipo['id_equipo'] return cls.__equipos
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 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_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 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 __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 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 parseListHtml(page, titleindex): next_page = {'page': page, 'title': titleindex} common.save_now_page(next_page) mysql = Mysql() s = '' if page > 1: s = '_' + repr(page) print(url.format(titles[titleindex], s)) try: response = requests.get(url.format(titles[titleindex], s), headers=headers, timeout=10) response.encoding = 'gb2312' if response.status_code == 200: soup = BeautifulSoup(response.text, 'html.parser') re_coms = soup.find_all('ul', attrs={'class': 'recom_list'}) articles = [] for re_com in re_coms: article = Article(re_com.a.string, re_com.find('span', attrs={'class': 'gd1'}).a.attrs['href']) article.author = 'OK学车' article.contentHead = parseContentHead(re_com.find('li', attrs={'class': 'recom_nr'}).text) article.type = types[titles[titleindex]] articles.append(article) parseArticle(articles) # 保存到数据库 mysql.insert_array(articles) mysql.close() # common.save_file(titles[titleIndex], '第{0}页'.format(page), repr(common.convert_to_dicts(articles))) sleep_time = random.randint(5, 10) print('休息', sleep_time, 's后再获取') time.sleep(sleep_time) parseListHtml(page + 1, titleindex) else: mysql.close() if titleindex + 1 < len(titles): parseListHtml(1, titleindex + 1) except Exception as e: print(traceback.format_exc()) print('网页获取失败:', e) mysql.close() sleep_time = random.randint(1, 5) print(repr(sleep_time), 's后重新获取') time.sleep(sleep_time) parseListHtml(page + 1, titleindex)
def get_condition_keys(self): where = "" if self.conditions: where = ' AND '.join("`%s`=%s" % (str(k), self.placeholder) for k in self.conditions) if self.extras: where = Mysql.merge_sql( where, ' AND '.join([i.replace('%', '%%') for i in self.extras])) return "WHERE %s" % (where, ) if where else ""
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 lista_equipos_sin_plantilla(cls): """ carga en un diccionario los equipos que no tienen jugadores (key: valor, nombre: id) """ if len(cls.__equipos_pendientes.keys()) == 0: bd = Mysql.conectar() bd.execute("select id_equipo, nombre " "from equipo " "where id_equipo not in (select distinct id_equipo from jugador) " "order by nombre") for equipo in bd.fetchall(): cls.__equipos_pendientes[equipo['nombre']] = equipo['id_equipo'] return cls.__equipos_pendientes
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 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 _new_question(data): for i in range(1): mysql = Mysql() try: mysql.connect_master() search_sql = "select id,json,subject,type from entity_question_new where oldid = %(oldid)d;" if 0 == mysql.query(search_sql, oldid=int(data)): return None else: question_set = mysql.fetch() except DBException as e: break newid = question_set[0] question_json = question_set[1] question_subject = question_set[2] question_type = question_set[3] new_question_dict = {} new_question_dict['q_new_id'] = newid new_question_dict['new_question'], new_question_dict[ 'blank_num'] = Business.q_json_parse(question_type, question_json) new_question_dict['subject'] = question_subject #print new_question_dict return new_question_dict
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 visualize(files_path, pre_lables): # 写入数据库 db = Mysql('192.168.1.11', 3306, 'root', '123456', 'face_reid') logger = Log('visualize', is_save=False) db.set_logger(logger) values = [[ a, b.replace('/home/xrr/datasets/', '/data/'), c, d ] for a, b, c, d in zip(pre_lables, files_path, ['2018-10-10 00:00:00'] * len(files_path), [1] * len(files_path))] sql = "truncate t_cluster2;insert into `t_cluster2` (`cluster_id`, `uri`, `timestamp`, `camera_id`) values {}".format( trans_sqlinsert(values)) print(sql[:100]) db.insert(sql) db.commit() print('visualize over')
def load(): configer = Configer('../config.ini') log_info = configer.get_configer('LOG','info') log_path = configer.get_configer('LOG','path') log_format = '%(asctime)s - %(name)s - %(levelname)s - %(filename)s - %(funcName)s - %(message)s' logger = Logger(info = log_info,path = log_path,format = log_format) LOG = logger.get_logger() ''' from mongo import Mongo mongo = Mongo() mongo.connect('resource') ''' from mysql import Mysql mysql = Mysql() mysql.connect_master() '''
def datainserter(): my = Mysql(cfg.DB_HOST, cfg.DB_USER, cfg.DB_PASSWORD, cfg.DB_NAME) while True: sqlq = database_q.get() # print(sqlq) try: my.query(sqlq) except Exception as e: try: my = Mysql(cfg.DB_HOST, cfg.DB_USER, cfg.DB_PASSWORD, cfg.DB_NAME) except Exception as e2: print(e) print("\nSQL:\n" + sqlq, end="\n\n") print(e, e2) else: my.query(sqlq)
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 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')
class User: def __init__(self): self.db = Mysql('127.0.0.1', 'root', 'root', 'svr-demo', 'utf8') def handle(self, action, param): if action == 'register': pass elif action == 'getuserinfo': uid = param['uid'] return 0, self.getuserinfo(uid) elif action == 'setuserinfo': pass else: pass def getuserinfo(self, uid): sql = "SELECT * FROM users WHERE uid=%d" % int(uid) result = self.db.fetchrow(sql) if result is None: return {} else: user = {'nick':result[1], 'email':result[2], 'summary':result[3]} return user
class test_db(unittest.TestCase): def testRowCount(self): self.mql = Mysql() self.mql.populate() self.failIf(self.mql.rowcount() != 5)
from mysql import Mysql reader = Mysql() writer = Mysql() reader.query('SELECT * FROM directories') data = reader.fetch() while data != dict(): id = str(data['id']) new_directory = data['directory'].replace('/home/albertyw/','/home/albertyw/Drive2/') query = "UPDATE directories SET directory = '"+new_directory+"' WHERE id='"+id+"';" print query writer.query(query) data = reader.fetch() reader.query('SELECT * FROM localfiles') data = reader.fetch() while data != dict(): id = str(data['id']) new_directory = data['filedirectory'].replace('/home/albertyw/','/home/albertyw/Drive2/') query = "UPDATE localfiles SET filedirectory = '"+new_directory+"' WHERE id='"+id+"';" print query writer.query(query) data = reader.fetch()
#! /usr/bin/env python #coding=utf-8 from mysql import Mysql import config db_config = config.getConfig('db') db = Mysql(db_config['host'], db_config['user'], db_config['passwd'], db_config['db'], db_config['charset']) # 初始化数据 db.query("INSERT INTO config (var, value) VALUES ('timeline_last_id', '0')") # 清空数据 #db.query('TRUNCATE TABLE follows') #db.query('TRUNCATE TABLE subs') #db.query('TRUNCATE TABLE url2uid') #db.query('TRUNCATE TABLE users') #db.query("UPDATE config SET value='0' WHERE var='timeline_last_id'")
def testRowCount(self): self.mql = Mysql() self.mql.populate() self.failIf(self.mql.rowcount() != 5)