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