143 lines
7.2 KiB
Python
143 lines
7.2 KiB
Python
import uuid
|
|
import os
|
|
|
|
import pymysql
|
|
import requests
|
|
from qiniu import Auth, put_file
|
|
from concurrent.futures import ThreadPoolExecutor
|
|
|
|
bucket_name = 'peko-prod'
|
|
|
|
|
|
MYSQL_CONFIG = {
|
|
"host": "124.156.164.187",
|
|
"port": 3306,
|
|
"username": "root",
|
|
"password": "anan@dev##",
|
|
"db": "peko"
|
|
}
|
|
|
|
thread = None
|
|
|
|
def connect():
|
|
return pymysql.connect(
|
|
host=MYSQL_CONFIG['host'],
|
|
user=MYSQL_CONFIG['username'],
|
|
password=MYSQL_CONFIG['password'],
|
|
db=MYSQL_CONFIG['db'],
|
|
charset='utf8'
|
|
)
|
|
|
|
|
|
def select_list(sql):
|
|
conn = connect()
|
|
cursor = conn.cursor()
|
|
cursor.execute(sql)
|
|
results = cursor.fetchall()
|
|
fields = [field[0] for field in cursor.description]
|
|
result = [dict(zip(fields, result)) for result in results]
|
|
conn.close()
|
|
return result
|
|
|
|
|
|
def update(sql):
|
|
conn = connect()
|
|
cursor = conn.cursor()
|
|
cursor.execute(sql)
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
|
|
def async_parse(data, table_name):
|
|
# 启动线程执行
|
|
try:
|
|
thread.submit(parse, data, table_name)
|
|
except Exception as e:
|
|
print("thread submit error.", e)
|
|
|
|
|
|
def parse(data, table_name):
|
|
if data is None or len(data) == 0:
|
|
return
|
|
print(data)
|
|
for item in data:
|
|
for i in item:
|
|
val = item[i]
|
|
if val is not None and isinstance(val, str) and (val.startswith('http') or val.startswith('https')) and 'zhongjialx' in val:
|
|
filename = download(val)
|
|
if filename is not None:
|
|
result = upload(filename)
|
|
if result is not None:
|
|
sql = 'update ' + table_name + ' set ' + i + ' = \'' + result + '\' where ' + i + ' = \'' + val + '\';'
|
|
print(sql)
|
|
update(sql)
|
|
os.remove(filename)
|
|
|
|
|
|
def download(url):
|
|
filename = str(uuid.uuid4())
|
|
try:
|
|
response = requests.get(url)
|
|
if response.status_code == 200:
|
|
with open(filename, 'wb') as f:
|
|
f.write(response.content)
|
|
else:
|
|
filename = None
|
|
print('download failed, ', url)
|
|
except Exception as e:
|
|
print(e)
|
|
return filename
|
|
|
|
|
|
def qiniu_token(bucket_name):
|
|
q = Auth(access_key='7c9eC45DhG3qVtyRXuQe17SZfEmELLtGK2umzTc1',
|
|
secret_key='t3sAMF3u6kFXJ9PCs3YxRzsu-ZjB03vL3aUAOVpk')
|
|
token = q.upload_token(bucket_name)
|
|
return token
|
|
|
|
|
|
def upload(filename):
|
|
result = None
|
|
try:
|
|
token = qiniu_token(bucket_name)
|
|
ret, info = put_file(token, filename, filename)
|
|
print(ret)
|
|
result = 'https://image.hfighting.com/' + ret.get('key')
|
|
except Exception as e:
|
|
print(e)
|
|
return result
|
|
|
|
|
|
if __name__ == '__main__':
|
|
thread = ThreadPoolExecutor(max_workers=100)
|
|
async_parse(select_list("select pic_url, vgg_url, lucky_Gift_Svga_Url, gift_explain_url, view_url from gift where (pic_url like '%zhongjialx%' or vgg_url like '%zhongjialx%' or lucky_Gift_Svga_Url like '%zhongjialx%' or gift_explain_url like '%zhongjialx%' or view_url like '%zhongjialx%');"), 'gift')
|
|
async_parse(select_list("select pic from info_card where (pic like '%zhongjialx%');"), 'info_card')
|
|
async_parse(select_list("select android_url, ios_url from chat_bubble where (android_url like '%zhongjialx%' or ios_url like '%zhongjialx%');"), 'chat_bubble')
|
|
async_parse(select_list("select icon_pic from nameplate where (icon_pic like '%zhongjialx%');"), 'nameplate')
|
|
async_parse(select_list("select nameplate_image from user_nameplate where (nameplate_image like '%zhongjialx%');"), 'user_nameplate')
|
|
async_parse(select_list("select pic, effect from headwear where (pic like '%zhongjialx%' or effect like '%zhongjialx%');"), 'headwear')
|
|
async_parse(select_list("select user_voice, avatar from users where (user_voice like '%zhongjialx%' or avatar like '%zhongjialx%');"), 'users')
|
|
async_parse(select_list("select game_picture, game_icon, tag_icon from game_info where (game_picture like '%zhongjialx%' or game_icon like '%zhongjialx%' or tag_icon like '%zhongjialx%');"), 'game_info')
|
|
async_parse(select_list("select prize_img_url from activity_award where (prize_img_url like '%zhongjialx%');"), 'activity_award')
|
|
async_parse(select_list("select pic, effect, redirect_link, view_url from car_goods where (pic like '%zhongjialx%' or effect like '%zhongjialx%' or redirect_link like '%zhongjialx%' or view_url like '%zhongjialx%');"), 'car_goods')
|
|
async_parse(select_list("select alert_win_pic, skip_url from charge_activity where (alert_win_pic like '%zhongjialx%' or skip_url like '%zhongjialx%');"), 'charge_activity')
|
|
async_parse(select_list("select prize_img_url from draw_lottery_record where (prize_img_url like '%zhongjialx%');"), 'draw_lottery_record')
|
|
async_parse(select_list("select icon from family where (icon like '%zhongjialx%');"), 'family')
|
|
async_parse(select_list("select icon from family_group_chat where (icon like '%zhongjialx%');"), 'family_group_chat')
|
|
async_parse(select_list("select magic_icon, magic_svg_url, effect_svg_url from gift_magic where (magic_icon like '%zhongjialx%' or magic_svg_url like '%zhongjialx%' or effect_svg_url like '%zhongjialx%');"), 'gift_magic')
|
|
async_parse(select_list("select icon from hall_group_chat where (icon like '%zhongjialx%');"), 'hall_group_chat')
|
|
async_parse(select_list("select url, url1 from level_charm where (url like '%zhongjialx%' or url1 like '%zhongjialx%');"), 'level_charm')
|
|
async_parse(select_list("select url, url1 from level_experience where (url like '%zhongjialx%' or url1 like '%zhongjialx%');"), 'level_experience')
|
|
async_parse(select_list("select value, preview from noble_res where (value like '%zhongjialx%' or preview like '%zhongjialx%');"), 'noble_res')
|
|
async_parse(select_list("select zip_url, res_conf from noble_zip where (zip_url like '%zhongjialx%' or res_conf like '%zhongjialx%');"), 'noble_zip')
|
|
async_parse(select_list("select act_image from operation_act where (act_image like '%zhongjialx%');"), 'operation_act')
|
|
async_parse(select_list("select prize_img_url from prize where (prize_img_url like '%zhongjialx%');"), 'prize')
|
|
async_parse(select_list("select avatar, voice from user_doll where (avatar like '%zhongjialx%' or voice like '%zhongjialx%');"), 'user_doll')
|
|
async_parse(select_list("select magic_icon from user_magic_wall where (magic_icon like '%zhongjialx%');"), 'user_magic_wall')
|
|
async_parse(select_list("select voice_url from user_voice where (voice_url like '%zhongjialx%');"), 'user_voice')
|
|
async_parse(select_list("select pic_url from anchor_grade where (pic_url like '%zhongjialx%');"), 'anchor_grade')
|
|
async_parse(select_list("select url from anchor_level_experience where (url like '%zhongjialx%');"), 'anchor_level_experience')
|
|
async_parse(select_list("select badge, cardbg, zonebg, room_background from noble_users where (badge like '%zhongjialx%' or cardbg like '%zhongjialx%' or zonebg like '%zhongjialx%' or room_background like '%zhongjialx%');"), 'noble_users')
|
|
async_parse(select_list("select tag_pict, badge, avatar, back_pic from room where (tag_pict like '%zhongjialx%' or badge like '%zhongjialx%' or avatar like '%zhongjialx%' or back_pic like '%zhongjialx%');"), 'room')
|
|
thread.shutdown()
|