def task(): conn = source_info_pool.connection() cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) cursor.execute('''SELECT city_id, source, suggest AS suggestions, 1 AS select_index, 100 AS annotation, json_object('code', 0) AS error, label_batch, 0 AS is_new_type, sid FROM tmp_ota_loc WHERE suggest_type = 2;''') for line in cursor.fetchall(): source = line['source'] if source != 'ctrip': _n_line = copy.deepcopy(line) _n_line['suggestions'] = [eval(_n_line['suggestions'])] else: _n_line = copy.deepcopy(line) _n_line['suggestions'] = [_n_line['suggestions']] _n_line["suggestions"] = json.dumps(_n_line["suggestions"]) cursor.execute( '''REPLACE INTO hotel_suggestions_city (city_id, source, suggestions, select_index, annotation, error, label_batch, is_new_type, sid) VALUES (%(city_id)s, %(source)s, %(suggestions)s, %(select_index)s, %(annotation)s, %(error)s, %(label_batch)s, %(is_new_type)s, %(sid)s)''', _n_line) conn.commit() cursor.close() conn.close()
def task(): Common.MiojiSimilarCityDict.COUNTRY_KEYS.append('country_id') d = MiojiSimilarCityDict() conn = source_info_pool.connection() cursor = conn.cursor(cursor=DictCursor) cursor.execute('''SELECT source, sid, suggest, country_id, s_city FROM ota_location WHERE source = 'qyer' AND city_id = 'NULL';''') for line in cursor.fetchall(): matched_city = d.get_mioji_city_id((line['country_id'], line['s_city'].lower())) if not matched_city: continue elif len(matched_city) > 1: logger.info( "[country_id: {}][city: {}][url: {}][matched_keys: {}]".format(line['country_id'], line['s_city'], line['suggest'], matched_city)) else: update_city(line['source'], line['sid'], list(matched_city)[0]) cursor.close() conn.close()
def update_city(source, sid, cid): conn = source_info_pool.connection() cursor = conn.cursor() query_sql = '''UPDATE ota_location SET city_id = %s WHERE source = %s AND sid = %s;''' cursor.execute(query_sql, (cid, source, sid)) logger.debug("[update][source: {}][sid: {}][cid: {}]".format(source, sid, cid)) cursor.close() conn.close()
def update_sql(data): sql = '''INSERT INTO ota_location (source, sid_md5, sid, suggest_type, suggest, city_id, country_id, s_city, s_region, s_country, s_extra, label_batch, others_info) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);''' conn = source_info_pool.connection() cursor = conn.cursor() try: _res = cursor.executemany(sql, data) except Exception: print(sql) raise Exception() conn.commit() cursor.close() conn.close() logger.info("[total: {}][execute: {}]".format(len(data), _res))
def get_old_task_count(mioji_cids): conn = source_info_pool.connection() cursor = conn.cursor() if not mioji_cids: return 0 sql = '''SELECT count(*) FROM hotel_suggestions_city WHERE city_id IN ({}) AND source IN ('booking', 'ctrip', 'agoda', 'expedia', 'hotels', 'elong');'''.format( ','.join(mioji_cids)) cursor.execute(sql) _count = cursor.fetchone()[0] logger.debug("[mioji_cids: {}][task count: {}]".format(mioji_cids, _count)) cursor.close() conn.close() return _count
def get_tasks(): sql = '''SELECT others_info, city_id FROM ota_location_qyer_1226 WHERE source='qyer';''' conn = source_info_pool.connection() cursor = conn.cursor() cursor.execute(sql) for line in cursor.fetchall(): j_data = json.loads(line[0]) if 's_city_id' in j_data: if line[1] != 'NULL': print('prepare', (j_data['s_city_id'], line[1])) yield j_data['s_city_id'], line[1] cursor.close() conn.close()
def update_sql(data, source): sql = '''UPDATE ota_location SET suggest = %s WHERE source = '{}' AND sid = %s;'''.format(source) conn = source_info_pool.connection() cursor = conn.cursor() try: _res = cursor.executemany(sql, data) except Exception: print(sql) raise Exception() conn.commit() cursor.close() conn.close() logger.info("[source: {}][total: {}][execute: {}]".format( source, len(data), _res))
def get_old_task_info_set(mioji_cids): old_task_info_set = set() conn = source_info_pool.connection() cursor = conn.cursor() if not mioji_cids: return old_task_info_set sql = '''SELECT source,sid FROM hotel_suggestions_city_new WHERE city_id IN ({}) AND source IN ('booking', 'ctrip', 'agoda', 'expedia', 'hotels', 'elong');'''.format( ','.join(mioji_cids)) cursor.execute(sql) for source, sid in cursor.fetchall(): if sid: old_task_info_set.add((source, sid)) logger.debug("[mioji_cids: {}][task count: {}]".format( mioji_cids, len(old_task_info_set))) cursor.close() conn.close() return old_task_info_set