def insert_iplay_upload_game(infos):
"""
:param infos:
"""
conn = connections('Cursor')
cursor = conn.cursor()
try:
sql = """
INSERT IGNORE INTO iplay_upload_game(
apk_id,
start_date,
source,
msg
) VALUES (
%(apk_id)s,
%(now)s,
%(source)s,
%(msg)s
)
"""
cursor.execute(sql, infos)
conn.commit()
except Exception as e:
conn.rollback()
logger.debug("iplay_upload_game name: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
def update_label_and_pkg_img(info):
"""
把googleplay游戏的图片地址更新为七牛的图片
:param info:
:return:
"""
conn = connections('Cursor')
cursor = conn.cursor()
print info
try:
label_sql = """
UPDATE iplay_game_label_info
SET screen_shot_urls = %(screen_shot_urls)s
, icon_url = %(icon_url)s
where game_id = %(game_id)s;
"""
pkg_sql = """
UPDATE iplay_game_pkg_info
SET screen_shot_urls = %(screen_shot_urls)s
, icon_url = %(icon_url)s
where game_id = %(game_id)s;
"""
cursor.execute(label_sql, info)
cursor.execute(pkg_sql, info)
conn.commit()
except Exception as e:
conn.rollback()
logger.debug("update_label_and_pkg_img: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
def get_gg_apk_info():
gg_apk_info = {} # {game_id: {pkg_name:,game_name:,ver_code:,ver_name}}
conn = connections("GGCursor")
cursor = conn.cursor()
sql = """
SELECT pkg_name, game_name, ver_code, ver_name, game_id FROM
iplay_game_pkg_info WHERE source=4 AND is_max_version=1 AND enabled=1
"""
rows = None
try:
cursor.execute(sql)
rows = cursor.fetchall()
except Exception as e:
logger.debug("get_gg_apk_info error: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
for row in rows:
pkg_name = row[0]
game_name = row[1]
ver_code = int(row[2])
ver_name = row[3]
game_id = row[4]
info = {
'pkg_name': pkg_name,
'game_name': game_name,
'ver_code': ver_code,
'ver_name': ver_name
}
gg_apk_info[game_id] = info
return gg_apk_info
def get_uc_apk_info():
uc_apk_info = {} # {pkg_name: {game_name:,ver_code:,ver_name}}
conn = connections("UCApkInfoCursor")
cursor = conn.cursor()
sql = """
SELECT pkg_name, label, ver_code, ver_name FROM uc_apk_info WHERE data_source=4
"""
rows = None
try:
cursor.execute(sql)
rows = cursor.fetchall()
except Exception as e:
logger.debug("get_uc_apk_info error: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
for row in rows:
pkg_name = row[0]
game_name = row[1]
ver_code = int(row[2])
ver_name = row[3]
info = {
"game_name": game_name,
"ver_code": ver_code,
"ver_name": ver_name
}
uc_apk_info[pkg_name] = info
return uc_apk_info
def update_iplay_upload_game(infos):
"""
:param infos:
"""
conn = connections('Cursor')
cursor = conn.cursor()
try:
sql = """
UPDATE iplay_upload_game SET
end_date = %(now)s,
source = %(source)s,
is_finished = %(is_finished)s,
msg = %(msg)s,
apk_id = %(apk_id)s,
channel = %(channel)s
WHERE id = %(id)s
"""
cursor.execute(sql, infos)
conn.commit()
except Exception as e:
conn.rollback()
logger.debug("update_iplay_upload_game name: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
def insert_iplay_upload_game(infos):
"""
:param infos:
"""
conn = connections('Cursor')
cursor = conn.cursor()
try:
sql = """
INSERT IGNORE INTO iplay_upload_game(
apk_id,
start_date,
source,
msg
) VALUES (
%(apk_id)s,
%(now)s,
%(source)s,
%(msg)s
)
"""
cursor.execute(sql, infos)
conn.commit()
except Exception as e:
conn.rollback()
logger.debug("iplay_upload_game name: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
def update_iplay_upload_game_baidupan(infos):
"""
:param infos:百度盘中文件大小不一致,数据库更新
"""
conn = connections('Cursor')
cursor = conn.cursor()
try:
sql = """
UPDATE iplay_upload_game SET
end_date = %(now)s,
msg = %(msg)s,
is_finished = %(is_finished)s
WHERE id = %(id)s
"""
cursor.execute(sql, infos)
conn.commit()
except Exception as e:
conn.rollback()
logger.debug("update_iplay_upload_game_baidupan name: %s" %
str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
def update_iplay_upload_plugin(info):
"""
:param info:
"""
conn = connections('Cursor')
cursor = conn.cursor()
try:
sql = """
UPDATE iplay_upload_plugin SET
plugin_pkg_name = %(plugin_pkg_name)s,
plugin_ver_code = %(plugin_ver_code)s,
update_timestamp = %(update_timestamp)s,
is_finished = %(is_finished)s,
msg = %(msg)s
WHERE id = %(id)s
"""
cursor.execute(sql, info)
conn.commit()
except Exception as e:
conn.rollback()
logger.debug("update_iplay_upload_plugin: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
def get_gg_apk_info():
gg_apk_info = {} # {game_id: {pkg_name:,game_name:,ver_code:,ver_name}}
conn = connections("GGCursor")
cursor = conn.cursor()
sql = """
SELECT pkg_name, game_name, ver_code, ver_name, game_id FROM
iplay_game_pkg_info WHERE source=4 AND is_max_version=1 AND enabled=1
"""
rows = None
try:
cursor.execute(sql)
rows = cursor.fetchall()
except Exception as e:
logger.debug("get_gg_apk_info error: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
for row in rows:
pkg_name = row[0]
game_name = row[1]
ver_code = int(row[2])
ver_name = row[3]
game_id = row[4]
info = {
'pkg_name': pkg_name,
'game_name': game_name,
'ver_code': ver_code,
'ver_name': ver_name
}
gg_apk_info[game_id] = info
return gg_apk_info
def get_uc_apk_info():
uc_apk_info = {} # {pkg_name: {game_name:,ver_code:,ver_name}}
conn = connections("UCApkInfoCursor")
cursor = conn.cursor()
sql = """
SELECT pkg_name, label, ver_code, ver_name FROM uc_apk_info WHERE data_source=4
"""
rows = None
try:
cursor.execute(sql)
rows = cursor.fetchall()
except Exception as e:
logger.debug("get_uc_apk_info error: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
for row in rows:
pkg_name = row[0]
game_name = row[1]
ver_code = int(row[2])
ver_name = row[3]
info = {
"game_name": game_name,
"ver_code": ver_code,
"ver_name": ver_name
}
uc_apk_info[pkg_name] = info
return uc_apk_info
def update_label_and_pkg_img(info):
"""
把googleplay游戏的图片地址更新为七牛的图片
:param info:
:return:
"""
conn = connections('Cursor')
cursor = conn.cursor()
print info
try:
label_sql = """
UPDATE iplay_game_label_info
SET screen_shot_urls = %(screen_shot_urls)s
, icon_url = %(icon_url)s
where game_id = %(game_id)s;
"""
pkg_sql = """
UPDATE iplay_game_pkg_info
SET screen_shot_urls = %(screen_shot_urls)s
, icon_url = %(icon_url)s
where game_id = %(game_id)s;
"""
cursor.execute(label_sql, info)
cursor.execute(pkg_sql, info)
conn.commit()
except Exception as e:
conn.rollback()
logger.debug("update_label_and_pkg_img: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
def update_iplay_upload_plugin(info):
"""
:param info:
"""
conn = connections('Cursor')
cursor = conn.cursor()
try:
sql = """
UPDATE iplay_upload_plugin SET
plugin_pkg_name = %(plugin_pkg_name)s,
plugin_ver_code = %(plugin_ver_code)s,
update_timestamp = %(update_timestamp)s,
is_finished = %(is_finished)s,
msg = %(msg)s
WHERE id = %(id)s
"""
cursor.execute(sql, info)
conn.commit()
except Exception as e:
conn.rollback()
logger.debug("update_iplay_upload_plugin: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
def get_appmarket_apk_info():
appmarket_apk_info = {} # {apk_id: {pkg_name:,ver_code:,ver_name:}}
conn = connections("AppMarketCursor")
cursor = conn.cursor()
sql = """
SELECT pkg_name, ver_code, ver_name, id FROM
apk_info WHERE enabled=1
"""
rows = None
try:
cursor.execute(sql)
rows = cursor.fetchall()
except Exception as e:
logger.debug("get_appmarket_apk_info error: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
for row in rows:
pkg_name = row[0]
ver_code = int(row[1])
ver_name = row[2]
apk_id = row[3]
info = {
'pkg_name': pkg_name,
'ver_code': ver_code,
'ver_name': ver_name
}
appmarket_apk_info[apk_id] = info
return appmarket_apk_info
def get_appmarket_apk_info():
appmarket_apk_info = {} # {apk_id: {pkg_name:,ver_code:,ver_name:}}
conn = connections("AppMarketCursor")
cursor = conn.cursor()
sql = """
SELECT pkg_name, ver_code, ver_name, id FROM
apk_info WHERE enabled=1
"""
rows = None
try:
cursor.execute(sql)
rows = cursor.fetchall()
except Exception as e:
logger.debug("get_appmarket_apk_info error: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
for row in rows:
pkg_name = row[0]
ver_code = int(row[1])
ver_name = row[2]
apk_id = row[3]
info = {
'pkg_name': pkg_name,
'ver_code': ver_code,
'ver_name': ver_name
}
appmarket_apk_info[apk_id] = info
return appmarket_apk_info
def get_google_plugin_info(plugin_id):
"""
获取需要入库的googleplay插件
:param plugin_id:
:return:
"""
conn = connections('DictCursor')
cursor = conn.cursor()
rows = tuple()
try:
sql = """
SELECT target_pkg_name, target_ver_code, editor, id
FROM iplay_upload_plugin
WHERE id = %d
""" % plugin_id
cursor.execute(sql)
rows = cursor.fetchone()
except Exception as e:
logger.debug("get_google_plugin_info: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
return rows
def get_google_pkg_info(game_id):
"""
获取需要下载图片的googleplay游戏
:param game_id:
:return:
"""
conn = connections('DictCursor')
cursor = conn.cursor()
rows = tuple()
try:
sql = """
SELECT game_id, icon_url, screen_shot_urls
FROM iplay_game_pkg_info
# WHERE market_channel = "google"
WHERE enabled = 1
AND (icon_url like '%ggpht.com%' or icon_url like '%googleusercontent.com%')
AND game_id ='""" + game_id + """'"""
cursor.execute(sql)
rows = cursor.fetchall()
except Exception as e:
logger.debug("select_google_pkg_info: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
return rows
def update_iplay_upload_game(infos):
"""
:param infos:
"""
conn = connections('Cursor')
cursor = conn.cursor()
try:
sql = """
UPDATE iplay_upload_game SET
end_date = %(now)s,
source = %(source)s,
is_finished = %(is_finished)s,
msg = %(msg)s,
apk_id = %(apk_id)s,
channel = %(channel)s
WHERE id = %(id)s
"""
cursor.execute(sql, infos)
conn.commit()
except Exception as e:
conn.rollback()
logger.debug("update_iplay_upload_game name: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
def get_google_plugin_info(plugin_id):
"""
获取需要入库的googleplay插件
:param plugin_id:
:return:
"""
conn = connections('DictCursor')
cursor = conn.cursor()
rows = tuple()
try:
sql = """
SELECT target_pkg_name, target_ver_code, editor, id
FROM iplay_upload_plugin
WHERE id = %d
""" % plugin_id
cursor.execute(sql)
rows = cursor.fetchone()
except Exception as e:
logger.debug("get_google_plugin_info: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
return rows
def get_google_pkg_info(game_id):
"""
获取需要下载图片的googleplay游戏
:param game_id:
:return:
"""
conn = connections('DictCursor')
cursor = conn.cursor()
rows = tuple()
try:
sql = """
SELECT game_id, icon_url, screen_shot_urls
FROM iplay_game_pkg_info
# WHERE market_channel = "google"
WHERE enabled = 1
AND (icon_url like '%ggpht.com%' or icon_url like '%googleusercontent.com%')
AND game_id ='""" + game_id + """'"""
cursor.execute(sql)
rows = cursor.fetchall()
except Exception as e:
logger.debug("select_google_pkg_info: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
return rows
def get_page_list(channel):
conn = connections('Cursor')
cursor = conn.cursor()
sql = "SELECT id, lists FROM games_test.lists_page where channel = %s"
rows = None
try:
cursor.execute(sql, (channel,))
rows = cursor.fetchall()
except Exception as e:
logger.debug("get_crawler_info: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
return rows
def get_crawlers_info():
conn = connections('Cursor')
cursor = conn.cursor()
sql = "SELECT id, channel, name, details FROM crawler_info where id > 0 order by id;"
rows = None
try:
cursor.execute(sql)
rows = cursor.fetchall()
except Exception as e:
logger.debug("get_crawlers_info: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
return rows
def get_crawler_info(channel, name):
conn = connections('Cursor')
cursor = conn.cursor()
sql = "SELECT details FROM crawler_info where channel = %s and name = %s;"
rows = None
try:
cursor.execute(sql, (channel, name,))
rows = cursor.fetchone()
except Exception as e:
logger.debug("get_crawler_info: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
return rows
def get_iplay_upload_game(upload_id):
conn = connections('DictCursor')
cursor = conn.cursor()
sql = "SELECT * FROM forum.iplay_upload_game where id = %s"
rows = None
try:
cursor.execute(sql, (upload_id,))
rows = cursor.fetchone()
except Exception as e:
logger.debug("get_crawler_info: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
return rows
def get_iplay_upload_game(upload_id):
conn = connections('DictCursor')
cursor = conn.cursor()
sql = "SELECT * FROM forum.iplay_upload_game where id = %s"
rows = None
try:
cursor.execute(sql, (upload_id, ))
rows = cursor.fetchone()
except Exception as e:
logger.debug("get_crawler_info: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
return rows
def get_crawlers_info():
conn = connections('Cursor')
cursor = conn.cursor()
sql = "SELECT id, channel, name, details FROM crawler_info where id > 0 order by id;"
rows = None
try:
cursor.execute(sql)
rows = cursor.fetchall()
except Exception as e:
logger.debug("get_crawlers_info: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
return rows
def get_page_list(channel):
conn = connections('Cursor')
cursor = conn.cursor()
sql = "SELECT id, lists FROM games_test.lists_page where channel = %s"
rows = None
try:
cursor.execute(sql, (channel, ))
rows = cursor.fetchall()
except Exception as e:
logger.debug("get_crawler_info: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
return rows
def get_appmarket_need_check_data():
conn = connections("AppMarketCursor")
cursor = conn.cursor()
sql = """
SELECT pkg_name, detail_url, apk_id, label FROM app_info WHERE enabled=1
"""
rows = None
try:
cursor.execute(sql)
rows = cursor.fetchall()
except Exception as e:
logger.debug("get_appmarket_need_check_data error: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
return rows
def get_appmarket_need_check_data():
conn = connections("AppMarketCursor")
cursor = conn.cursor()
sql = """
SELECT pkg_name, detail_url, apk_id, label FROM app_info WHERE enabled=1
"""
rows = None
try:
cursor.execute(sql)
rows = cursor.fetchall()
except Exception as e:
logger.debug("get_appmarket_need_check_data error: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
return rows
def get_gg_need_check_data():
conn = connections("GGCursor")
cursor = conn.cursor()
sql = """
SELECT game_id, detail_url FROM iplay_game_label_info WHERE source=4 AND
enabled=1
"""
rows = None
try:
cursor.execute(sql)
rows = cursor.fetchall()
except Exception as e:
logger.debug("get_gg_need_check_data error: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
return rows
def get_gg_need_check_data():
conn = connections("GGCursor")
cursor = conn.cursor()
sql = """
SELECT game_id, detail_url FROM iplay_game_label_info WHERE source=4 AND
enabled=1
"""
rows = None
try:
cursor.execute(sql)
rows = cursor.fetchall()
except Exception as e:
logger.debug("get_gg_need_check_data error: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
return rows
def get_crawler_info(channel, name):
conn = connections('Cursor')
cursor = conn.cursor()
sql = "SELECT details FROM crawler_info where channel = %s and name = %s;"
rows = None
try:
cursor.execute(sql, (
channel,
name,
))
rows = cursor.fetchone()
except Exception as e:
logger.debug("get_crawler_info: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
return rows
def get_pkg_info(games):
conn = connections("GGCursor")
cursor = conn.cursor()
sql = """
SELECT game_id, market_channel, ver_name, file_size, download_url, min_sdk, update_desc, source FROM iplay_game_pkg_info WHERE is_max_version=1 and source in (3, 4) order by gg_download_week desc limit 100;
"""
rows = None
try:
cursor.execute(sql)
rows = cursor.fetchall()
except Exception as e:
logger.debug("get_gg_need_check_data error: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
for row in rows:
try:
game_id = row[0]
if game_id in games:
continue
print game_id
label_info = get_label_info(game_id)
market_channel = row[1]
ver_name = row[2]
file_size = '%.2fM' % (int(row[3]) / 1024.0 / 1024)
download_url = row[4]
min_sdk = ANDROID[str(int(row[5]))]
update_desc = row[6] if row[6] else ''
source = row[7]
#result = [market_channel, ver_name, file_size, download_url, min_sdk, update_desc]
label_info['market_channel'] = market_channel
label_info['ver_name'] = ver_name
label_info['file_size'] = file_size
label_info['download_url'] = download_url
label_info['min_sdk'] = min_sdk
label_info['update_desc'] = update_desc
label_info['source'] = source
insert_data(label_info)
except:
logger.debug("ERROR game_id: %s" % str(game_id))
def get_pkg_info(games):
conn = connections("GGCursor")
cursor = conn.cursor()
sql = """
SELECT game_id, market_channel, ver_name, file_size, download_url, min_sdk, update_desc, source FROM iplay_game_pkg_info WHERE is_max_version=1 and source in (3, 4) order by gg_download_week desc limit 100;
"""
rows = None
try:
cursor.execute(sql)
rows = cursor.fetchall()
except Exception as e:
logger.debug("get_gg_need_check_data error: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
for row in rows:
try:
game_id = row[0]
if game_id in games:
continue
print game_id
label_info = get_label_info(game_id)
market_channel = row[1]
ver_name = row[2]
file_size = '%.2fM' % (int(row[3])/1024.0/1024)
download_url = row[4]
min_sdk = ANDROID[str(int(row[5]))]
update_desc = row[6] if row[6] else ''
source = row[7]
#result = [market_channel, ver_name, file_size, download_url, min_sdk, update_desc]
label_info['market_channel'] = market_channel
label_info['ver_name'] = ver_name
label_info['file_size'] = file_size
label_info['download_url'] = download_url
label_info['min_sdk'] = min_sdk
label_info['update_desc'] = update_desc
label_info['source'] = source
insert_data(label_info)
except:
logger.debug("ERROR game_id: %s" % str(game_id))
def check_developer(developer):
"""
检查新增游戏厂商是否在厂商列表内
:param infos:
"""
conn = connections('Cursor')
cursor = conn.cursor()
try:
sql = """
INSERT INTO iplay_game_developer(developer) VALUES("%s") ON DUPLICATE KEY UPDATE developer = VALUES(developer);""" % developer
cursor.execute(sql)
conn.commit()
except Exception as e:
conn.rollback()
logger.debug("insert_developer name: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
def check_developer(developer):
"""
检查新增游戏厂商是否在厂商列表内
:param infos:
"""
conn = connections('Cursor')
cursor = conn.cursor()
try:
sql = """
INSERT INTO iplay_game_developer(developer) VALUES("%s") ON DUPLICATE KEY UPDATE developer = VALUES(developer);""" % developer
cursor.execute(sql)
conn.commit()
except Exception as e:
conn.rollback()
logger.debug("insert_developer name: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
def get_tianji():
conn = connections("45Cursor")
cursor = conn.cursor()
sql = """
SELECT game_id from iplay_tianji_data;
"""
rows = None
try:
cursor.execute(sql)
rows = cursor.fetchall()
except Exception as e:
logger.debug("get_gg_need_check_data error: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
games = {}
for row in rows:
game_id = row[0]
games[game_id] = ''
return games
def get_tianji():
conn = connections("45Cursor")
cursor = conn.cursor()
sql = """
SELECT game_id from iplay_tianji_data;
"""
rows = None
try:
cursor.execute(sql)
rows = cursor.fetchall()
except Exception as e:
logger.debug("get_gg_need_check_data error: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
games = {}
for row in rows:
game_id = row[0]
games[game_id] = ''
return games
def update_pkg_by_game_id(game_id):
"""
如果国内游戏选择下载渠道为百度盘,则删除其他渠道游戏信息
:param infos:
"""
conn = connections('Cursor')
cursor = conn.cursor()
try:
sql = """
UPDATE iplay_game_pkg_info SET
enabled = 0
WHERE game_id = %s
"""
cursor.execute(sql, (game_id, ))
conn.commit()
except Exception as e:
conn.rollback()
logger.debug("update_pkg_by_game_id ERROR: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
def update_pkg_by_game_id(game_id):
"""
如果国内游戏选择下载渠道为百度盘,则删除其他渠道游戏信息
:param infos:
"""
conn = connections('Cursor')
cursor = conn.cursor()
try:
sql = """
UPDATE iplay_game_pkg_info SET
enabled = 0
WHERE game_id = %s
"""
cursor.execute(sql, (game_id,))
conn.commit()
except Exception as e:
conn.rollback()
logger.debug("update_pkg_by_game_id ERROR: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
def get_channel_mapping():
"""
:return: channels {'1': 'GG官方', '2': '高通版'...}
"""
conn = connections('52DictCursor')
cursor = conn.cursor()
sql = "SELECT * FROM iplay_tool_channel_mapping"
rows = None
try:
cursor.execute(sql)
rows = cursor.fetchall()
except Exception as e:
logger.debug("get_channel_mapping: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
channels = {}
for row in rows:
channel_id = str(int(row['id']))
channel_name = row['name']
channels[channel_id] = channel_name
return channels
def get_game_developer():
"""
get developers. return .csv
"""
conn = connections("GGCursor")
cursor = conn.cursor()
sql = """
SELECT developer FROM iplay_game_developer
"""
rows = None
try:
cursor.execute(sql)
rows = cursor.fetchall()
except Exception as e:
logger.debug("get_game_developer error: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
with open('developer.csv', 'w') as files:
for row in rows:
developer = row[0]
files.write('%s\n' % developer)
def get_game_developer():
"""
get developers. return .csv
"""
conn = connections("GGCursor")
cursor = conn.cursor()
sql = """
SELECT developer FROM iplay_game_developer
"""
rows = None
try:
cursor.execute(sql)
rows = cursor.fetchall()
except Exception as e:
logger.debug("get_game_developer error: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
with open('developer.csv', 'w') as files:
for row in rows:
developer = row[0]
files.write('%s\n' % developer)
def update_iplay_upload_game_baidupan(infos):
"""
:param infos:百度盘中文件大小不一致,数据库更新
"""
conn = connections('Cursor')
cursor = conn.cursor()
try:
sql = """
UPDATE iplay_upload_game SET
end_date = %(now)s,
msg = %(msg)s,
is_finished = %(is_finished)s
WHERE id = %(id)s
"""
cursor.execute(sql, infos)
conn.commit()
except Exception as e:
conn.rollback()
logger.debug("update_iplay_upload_game_baidupan name: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
def get_channel_mapping():
"""
:return: channels {'1': 'GG官方', '2': '高通版'...}
"""
conn = connections('52DictCursor')
cursor = conn.cursor()
sql = "SELECT * FROM iplay_tool_channel_mapping"
rows = None
try:
cursor.execute(sql)
rows = cursor.fetchall()
except Exception as e:
logger.debug("get_channel_mapping: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
channels = {}
for row in rows:
channel_id = str(int(row['id']))
channel_name = row['name']
channels[channel_id] = channel_name
return channels
def insert_label_info(infos):
"""
数据来源(source),1 抓取数据 2 论坛数据 3 google play 数据 4 推广游戏入库
:param infos:
"""
conn = connections('Cursor')
cursor = conn.cursor()
try:
sql = """
INSERT INTO iplay_game_label_info(
game_id
, game_name
, screen_shot_urls
, icon_url
, detail_desc
, game_language
, save_timestamp
, min_apk_size
, max_apk_size
, min_ver_name
, max_ver_name
, source
, display_name
, update_timestamp
, origin_types
, developer
, save_user
, update_user
, enabled
, download_counts
, star_num
, tid
) VALUES (
%(game_id)s
, %(game_name)s
, %(screen_shot_urls)s
, %(icon_url)s
, %(detail_desc)s
, %(game_language)s
, %(now)s
, %(file_size)s
, %(file_size)s
, %(ver_name)s
, %(ver_name)s
, %(source)s
, %(game_name)s
, %(now)s
, %(origin_types)s
, %(developer)s
, %(save_user)s
, %(save_user)s
, %(enabled)s
, %(downloaded_cnts)s
, %(star_num)s
, 0
) ON DUPLICATE KEY UPDATE
enabled = VALUES(enabled)
, icon_url = VALUES(icon_url)
, update_timestamp = VALUES(update_timestamp)
, save_timestamp = VALUES(save_timestamp)
, save_user = VALUES(save_user)
"""
cursor.execute(sql, infos)
conn.commit()
except Exception as e:
conn.rollback()
logger.debug("insert_label_info name: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
def get_label_info(game_id):
conn = connections("GGCursor")
cursor = conn.cursor()
sql = """
SELECT game_id, display_name, icon_url, color_label, star_num, game_language, developer, detail_desc, short_desc, gg_download_cnt,origin_types, screen_shot_urls, from_unixtime(save_timestamp),detail_desc_html FROM iplay_game_label_info WHERE game_id='%s'
""" % game_id
rows = None
try:
cursor.execute(sql)
rows = cursor.fetchall()
except Exception as e:
logger.debug("get_gg_need_check_data error: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
for row in rows:
result = {}
game_id = row[0]
display_name = row[1].replace(': ', ':').split(' ')[0]
icon_url = row[2]
color_label = row[3]
colors = []
for color in color_label.split('\n'):
if color in COLOR_LABELS:
colors.append(COLOR_LABELS[color])
color_label = ','.join(colors)
language = row[5] if row[5] else ''
developer = row[6] if row[6] else ''
detail_desc = row[7]
short_desc = row[8]
gg_download_cnt = row[9] if row[9] else 0
origin_types = row[10]
types = []
for origin in origin_types.split('\n'):
if origin:
types.append(origin)
origin_types = ','.join(types)
screen_shot_urls = row[11].replace('\n', ',')
save_time = str(row[12]).split(' ')[0]
detail_desc_html = row[13]
star_num = get_star(gg_download_cnt)
if detail_desc_html:
detail_desc = detail_desc_html
'''
print 'game_id: %s' % game_id
print '\n'
print 'display_name: %s' % display_name
print '\n'
print 'icon_url: %s' % icon_url
print '\n'
print 'color_label: %s' % color_label
print '\n'
print 'language: %s' % language
print '\n'
print 'developer: %s' % developer
print '\n'
print 'star_num: %s' % str(star_num)
print '\n'
#print 'detail_desc: %s' % detail_desc
print '\n'
#print 'short_desc: %s' % short_desc
print '\n'
print 'gg_download_cnt: %s' % gg_download_cnt
print '\n'
print 'origin_types: %s' % [origin_types]
print '\n'
print 'screen_shot_urls: %s' % [screen_shot_urls]
print '\n'
print 'save_time: %s' % save_time
'''
result['display_name'] = display_name
result['icon_url'] = icon_url
result['color_label'] = color_label
result['language'] = language
result['developer'] = developer
result['star_num'] = star_num
result['detail_desc'] = detail_desc
result['short_desc'] = short_desc
result['origin_types'] = origin_types
result['screen_shot_urls'] = screen_shot_urls
result['save_time'] = save_time
result['game_id'] = game_id
#result = [display_name, icon_url, color_label, language, developer, star_num, detail_desc, short_desc, origin_types, screen_shot_urls, save_time, game_id]
return result
def insert_pkg_info(infos):
"""
download_url_type: 1普通, 2百度盘
数据来源(source): 1 抓取数据 2 论坛数据 3 google play 数据, GG官方 4 推广游戏入库
is_plugin_required: 0不需要, 1需要
:param infos:
"""
conn = connections('Cursor')
cursor = conn.cursor()
try:
sql = """
INSERT INTO iplay_game_pkg_info(
apk_id
, game_id
, market_channel
, game_name
, pkg_name
, ver_code
, ver_name
, file_size
, download_url
, game_desc
, downloaded_cnts
, game_language
, screen_shot_urls
, icon_url
, min_sdk
, download_url_type
, source
, signature_md5
, file_md5
, origin_types
, gpu_vender
, ver_code_by_gg
, update_desc
, file_type
, save_user
, update_user
, url4details
, save_timestamp
, update_timestamp
) VALUES (
%(apk_id)s
, %(game_id)s
, %(market_channel)s
, %(game_name)s
, %(pkg_name)s
, %(ver_code)s
, %(ver_name)s
, %(file_size)s
, %(download_url)s
, %(game_desc)s
, %(downloaded_cnts)s
, %(game_language)s
, %(screen_shot_urls)s
, %(icon_url)s
, %(min_sdk)s
, %(download_url_type)s
, %(source)s
, %(signature_md5)s
, %(file_md5)s
, %(origin_types)s
, %(gpu_vender)s
, %(ver_code_by_gg)s
, %(update_desc)s
, %(file_type)s
, %(save_user)s
, %(save_user)s
, %(url4details)s
, %(now)s
, %(now)s
) ON DUPLICATE KEY UPDATE
enabled = 1
, save_timestamp = VALUES(save_timestamp)
, update_timestamp = VALUES(update_timestamp)
, ver_code = VALUES (ver_code)
, ver_name = VALUES (ver_name)
, file_size = VALUES (file_size)
, download_url = VALUES (download_url)
, gpu_vender = VALUES (gpu_vender)
, save_user = VALUES(save_user)
"""
cursor.execute(sql, infos)
conn.commit()
except Exception as e:
conn.rollback()
logger.debug("insert_pkg_info name: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
def get_label_info(game_id):
conn = connections("GGCursor")
cursor = conn.cursor()
sql = """
SELECT game_id, display_name, icon_url, color_label, star_num, game_language, developer, detail_desc, short_desc, gg_download_cnt,origin_types, screen_shot_urls, from_unixtime(save_timestamp),detail_desc_html FROM iplay_game_label_info WHERE game_id='%s'
""" % game_id
rows = None
try:
cursor.execute(sql)
rows = cursor.fetchall()
except Exception as e:
logger.debug("get_gg_need_check_data error: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
for row in rows:
result = {}
game_id = row[0]
display_name = row[1].replace(': ', ':').split(' ')[0]
icon_url = row[2]
color_label = row[3]
colors = []
for color in color_label.split('\n'):
if color in COLOR_LABELS:
colors.append(COLOR_LABELS[color])
color_label = ','.join(colors)
language = row[5] if row[5] else ''
developer = row[6] if row[6] else ''
detail_desc = row[7]
short_desc = row[8]
gg_download_cnt = row[9] if row[9] else 0
origin_types = row[10]
types = []
for origin in origin_types.split('\n'):
if origin:
types.append(origin)
origin_types = ','.join(types)
screen_shot_urls = row[11].replace('\n', ',')
save_time = str(row[12]).split(' ')[0]
detail_desc_html = row[13]
star_num = get_star(gg_download_cnt)
if detail_desc_html:
detail_desc = detail_desc_html
'''
print 'game_id: %s' % game_id
print '\n'
print 'display_name: %s' % display_name
print '\n'
print 'icon_url: %s' % icon_url
print '\n'
print 'color_label: %s' % color_label
print '\n'
print 'language: %s' % language
print '\n'
print 'developer: %s' % developer
print '\n'
print 'star_num: %s' % str(star_num)
print '\n'
#print 'detail_desc: %s' % detail_desc
print '\n'
#print 'short_desc: %s' % short_desc
print '\n'
print 'gg_download_cnt: %s' % gg_download_cnt
print '\n'
print 'origin_types: %s' % [origin_types]
print '\n'
print 'screen_shot_urls: %s' % [screen_shot_urls]
print '\n'
print 'save_time: %s' % save_time
'''
result['display_name'] = display_name
result['icon_url'] = icon_url
result['color_label'] = color_label
result['language'] = language
result['developer'] = developer
result['star_num'] = star_num
result['detail_desc'] = detail_desc
result['short_desc'] = short_desc
result['origin_types'] = origin_types
result['screen_shot_urls'] = screen_shot_urls
result['save_time'] = save_time
result['game_id'] = game_id
#result = [display_name, icon_url, color_label, language, developer, star_num, detail_desc, short_desc, origin_types, screen_shot_urls, save_time, game_id]
return result
def insert_label_info(infos):
"""
数据来源(source),1 抓取数据 2 论坛数据 3 google play 数据 4 推广游戏入库
:param infos:
"""
conn = connections('Cursor')
cursor = conn.cursor()
try:
sql = """
INSERT INTO iplay_game_label_info(
game_id
, game_name
, screen_shot_urls
, icon_url
, detail_desc
, game_language
, save_timestamp
, min_apk_size
, max_apk_size
, min_ver_name
, max_ver_name
, source
, display_name
, update_timestamp
, origin_types
, developer
, save_user
, update_user
, enabled
, download_counts
, star_num
, tid
) VALUES (
%(game_id)s
, %(game_name)s
, %(screen_shot_urls)s
, %(icon_url)s
, %(detail_desc)s
, %(game_language)s
, %(now)s
, %(file_size)s
, %(file_size)s
, %(ver_name)s
, %(ver_name)s
, %(source)s
, %(game_name)s
, %(now)s
, %(origin_types)s
, %(developer)s
, %(save_user)s
, %(save_user)s
, %(enabled)s
, %(downloaded_cnts)s
, %(star_num)s
, 0
) ON DUPLICATE KEY UPDATE
enabled = VALUES(enabled)
, icon_url = VALUES(icon_url)
, update_timestamp = VALUES(update_timestamp)
, save_timestamp = VALUES(save_timestamp)
, save_user = VALUES(save_user)
"""
cursor.execute(sql, infos)
conn.commit()
except Exception as e:
conn.rollback()
logger.debug("insert_label_info name: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
def insert_pkg_info(infos):
"""
download_url_type: 1普通, 2百度盘
数据来源(source): 1 抓取数据 2 论坛数据 3 google play 数据, GG官方 4 推广游戏入库
is_plugin_required: 0不需要, 1需要
:param infos:
"""
conn = connections('Cursor')
cursor = conn.cursor()
try:
sql = """
INSERT INTO iplay_game_pkg_info(
apk_id
, game_id
, market_channel
, game_name
, pkg_name
, ver_code
, ver_name
, file_size
, download_url
, game_desc
, downloaded_cnts
, game_language
, screen_shot_urls
, icon_url
, min_sdk
, download_url_type
, source
, signature_md5
, file_md5
, origin_types
, gpu_vender
, ver_code_by_gg
, update_desc
, file_type
, save_user
, update_user
, url4details
, save_timestamp
, update_timestamp
) VALUES (
%(apk_id)s
, %(game_id)s
, %(market_channel)s
, %(game_name)s
, %(pkg_name)s
, %(ver_code)s
, %(ver_name)s
, %(file_size)s
, %(download_url)s
, %(game_desc)s
, %(downloaded_cnts)s
, %(game_language)s
, %(screen_shot_urls)s
, %(icon_url)s
, %(min_sdk)s
, %(download_url_type)s
, %(source)s
, %(signature_md5)s
, %(file_md5)s
, %(origin_types)s
, %(gpu_vender)s
, %(ver_code_by_gg)s
, %(update_desc)s
, %(file_type)s
, %(save_user)s
, %(save_user)s
, %(url4details)s
, %(now)s
, %(now)s
) ON DUPLICATE KEY UPDATE
enabled = 1
, save_timestamp = VALUES(save_timestamp)
, update_timestamp = VALUES(update_timestamp)
, ver_code = VALUES (ver_code)
, ver_name = VALUES (ver_name)
, file_size = VALUES (file_size)
, download_url = VALUES (download_url)
, gpu_vender = VALUES (gpu_vender)
, save_user = VALUES(save_user)
"""
cursor.execute(sql, infos)
conn.commit()
except Exception as e:
conn.rollback()
logger.debug("insert_pkg_info name: %s" % str(e.args))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
def insert_data(pkg_info):
conn = connections("45Cursor")
cursor = conn.cursor()
sql = """
INSERT INTO iplay_tianji_data(
display_name
, market_channel
, ver_name
, file_size
, download_url
, icon_url
, min_sdk
, perm
, star_num
, category
, language
, detail_desc
, update_desc
, install_desc
, screen_shot_urls
, developer
, color_label
, short_desc
, save_time
, game_id
, source
) VALUES (
%(display_name)s
, %(market_channel)s
, %(ver_name)s
, %(file_size)s
, %(download_url)s
, %(icon_url)s
, %(min_sdk)s
, ''
, %(star_num)s
, %(origin_types)s
, %(language)s
, %(detail_desc)s
, %(update_desc)s
, ''
, %(screen_shot_urls)s
, %(developer)s
, %(color_label)s
, %(short_desc)s
, %(save_time)s
, %(game_id)s
, %(source)s
) ON DUPLICATE KEY UPDATE
save_time = VALUES(save_time)
"""
# sql = """
# INSERT INTO iplay_tianji_data(market_channel,ver_name,file_size,download_url,sdk,update_desc,display_name,icon_url,color_label,lang,company,star,desc,short_desc,category,screen_shot_urls,save_time,perm,install_desc,game_id) VALUES (%(market_channel)s, %(ver_name)s, %(file_size)s, %(download_url)s, %(min_sdk)s, %(update_desc)s, %(display_name)s, %(icon_url)s, %(color_label)s, %(language)s, %(developer)s, %(star_num)s, %(detail_desc)s, %(short_desc)s, %(origin_types)s, %(screen_shot_urls)s, %(save_time)s, '', '', %(game_id)s ) ON DUPLICATE KEY UPDATE save_time = VALUES(save_time)
# """
try:
cursor.execute(sql, pkg_info)
conn.commit()
except Exception as e:
logger.debug("game_id: %s error: %s" % (pkg_info['game_id'] ,str(e.args)))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
def insert_data(pkg_info):
conn = connections("45Cursor")
cursor = conn.cursor()
sql = """
INSERT INTO iplay_tianji_data(
display_name
, market_channel
, ver_name
, file_size
, download_url
, icon_url
, min_sdk
, perm
, star_num
, category
, language
, detail_desc
, update_desc
, install_desc
, screen_shot_urls
, developer
, color_label
, short_desc
, save_time
, game_id
, source
) VALUES (
%(display_name)s
, %(market_channel)s
, %(ver_name)s
, %(file_size)s
, %(download_url)s
, %(icon_url)s
, %(min_sdk)s
, ''
, %(star_num)s
, %(origin_types)s
, %(language)s
, %(detail_desc)s
, %(update_desc)s
, ''
, %(screen_shot_urls)s
, %(developer)s
, %(color_label)s
, %(short_desc)s
, %(save_time)s
, %(game_id)s
, %(source)s
) ON DUPLICATE KEY UPDATE
save_time = VALUES(save_time)
"""
# sql = """
# INSERT INTO iplay_tianji_data(market_channel,ver_name,file_size,download_url,sdk,update_desc,display_name,icon_url,color_label,lang,company,star,desc,short_desc,category,screen_shot_urls,save_time,perm,install_desc,game_id) VALUES (%(market_channel)s, %(ver_name)s, %(file_size)s, %(download_url)s, %(min_sdk)s, %(update_desc)s, %(display_name)s, %(icon_url)s, %(color_label)s, %(language)s, %(developer)s, %(star_num)s, %(detail_desc)s, %(short_desc)s, %(origin_types)s, %(screen_shot_urls)s, %(save_time)s, '', '', %(game_id)s ) ON DUPLICATE KEY UPDATE save_time = VALUES(save_time)
# """
try:
cursor.execute(sql, pkg_info)
conn.commit()
except Exception as e:
logger.debug("game_id: %s error: %s" %
(pkg_info['game_id'], str(e.args)))
finally:
if cursor:
cursor.close()
if conn:
conn.close()
