def __insert_manufacture2mysql(self, manufacturer):
cursor = self.database.cursor()
have_data = self.__select_manufacture(manufacturer)
if have_data:
Log.d('UPDATE公司 ' + manufacturer.name)
update_sql = 'UPDATE manufacturer_en SET code = %s , name = %s ,known_for = %s , description = %s , source_url = %s WHERE id = %s'
cursor.execute(update_sql,
(self.handle_opt_data(manufacturer.code),
self.handle_opt_data(manufacturer.name),
self.handle_opt_data(manufacturer.known_for),
self.handle_opt_data(manufacturer.description),
self.handle_opt_data(manufacturer.source_url),
int(self.handle_opt_data(manufacturer.id))))
self.database.commit()
else:
Log.d('insert公司 ' + manufacturer.name)
sql = 'INSERT INTO manufacturer_en (id,code,name,known_for,description,source_url) VALUES (%s,%s,%s,%s,%s,%s)'
cursor.execute(sql,
(int(self.handle_opt_data(manufacturer.id)),
self.handle_opt_data(manufacturer.code),
self.handle_opt_data(manufacturer.name),
self.handle_opt_data(manufacturer.known_for),
self.handle_opt_data(manufacturer.description),
self.handle_opt_data(manufacturer.source_url)))
self.database.commit()
cursor.close()
def get_comm_link_content(comm_link, content_list):
from CitizenWikiRobot.translate_util import TransLateUtil
translate_util = TransLateUtil()
for sub_content in content_list:
if sub_content is None or isinstance(sub_content, NavigableString):
continue
name = sub_content.name
if name == 'p' or name == 'h2':
comm_content = CommLinkContent()
if sub_content.text:
if name == 'h2':
comm_content.content_data = sub_content.text
comm_content.content_type = "title"
if name == 'p':
comm_content.content_data = sub_content.text
comm_content.machine_translate_data = translate_util.translate(sub_content.text)
Log.d(comm_content.machine_translate_data)
comm_content.content_type = "content"
comm_content.data_index = len(comm_link.content)
comm_link.content.append(comm_content)
# Log.d(comm_content.content_data)
if name == 'a':
comm_content_image = CommLinkContent()
comm_content_image.content_type = "image"
comm_content_image.content_data = sub_content.get('data-source_url')
comm_content_image.data_index = len(comm_link.content)
comm_link.content.append(comm_content_image)
def insert_comm_link(self, comm_link):
cursor = self.database.cursor()
# query
select_sql = "SELECT id FROM comm_link WHERE url = %s "
cursor.execute(select_sql, comm_link.url)
if cursor.rowcount > 0:
Log.d("update comm_link ")
Log.d(comm_link.title)
# 有数据 update
data = cursor.fetchone()
m_id = data[0]
comm_link.id = m_id
update_sql = 'UPDATE comm_link SET url = %s , background = %s ,title = %s , type = %s WHERE id = %s'
cursor.execute(update_sql, (comm_link.url, comm_link.background,
comm_link.title, comm_link.type, m_id))
else:
# 无数据 insert
Log.d("insert comm_link ")
Log.d(comm_link.title)
insert_sql = "INSERT INTO comm_link (url , background , title , type) VALUE (%s , %s , %s, %s)"
cursor.execute(insert_sql, (comm_link.url, comm_link.background,
comm_link.title, comm_link.type))
comm_link.id = cursor.lastrowid
self.database.commit()
cursor.close()
self.__insert_comm_link_content(comm_link)
def __insert_ship_equipment(self, ship_equipment):
# 还是先查询 更新 插入
cursor = self.database.cursor()
Log.d("插入装备 " + ship_equipment.type)
cursor.execute(ShipEquipment.get_insert_sql(),
(self.handle_opt_data(ship_equipment.type),
self.handle_opt_data(ship_equipment.name),
self.handle_opt_data(ship_equipment.mounts),
self.handle_opt_data(ship_equipment.component_size),
self.handle_opt_data(ship_equipment.size),
self.handle_opt_data(ship_equipment.details),
self.handle_opt_data(ship_equipment.quantity),
self.handle_opt_data(ship_equipment.manufacturer),
self.handle_opt_data(ship_equipment.component_class),
int(self.handle_opt_data(ship_equipment.ship_id))))
self.database.commit()
cursor.close()
def get_ships():
page = 1
ship_json = get_ship_json(page)
html = ship_json.get('data').get('html')
totalrows = ship_json.get('data').get('totalrows')
rowcount = ship_json.get('data').get('rowcount')
init_ship(html)
need_req_time = float(totalrows) / rowcount
if need_req_time != 0:
need_req_time += 1
for page in range(2, 2 + int(need_req_time)):
print "正在获取第 " + str(page) + "页数据"
ship_json = get_ship_json(page)
html = ship_json.get('data').get('html')
init_ship(html)
mysql_helper = MysqlHelper()
# 插入公司
Log.d('开始将公司插入数据库')
mysql_helper.insert_manufacturer(manufacturer_list)
def __insert_comm_link_content(self, comm_link):
cursor = self.database.cursor()
select_sql = "SELECT id FROM comm_link_content WHERE comm_link_id = %s "
cursor.execute(select_sql, comm_link.id)
count = cursor.rowcount
if count != len(comm_link.content):
Log.d("链接内容与数据库不符合 删除重新插入! ")
# 删除旧的
delete_sql = "DELETE FROM comm_link_content WHERE comm_link_id = %s"
cursor.execute(delete_sql, comm_link.id)
for content in comm_link.content:
select_item = "SELECT id FROM comm_link_content WHERE comm_link_id = %s AND data_index = %s"
cursor.execute(select_item, (comm_link.id, content.data_index))
if cursor.rowcount <= 0:
Log.d("未查询到链接内容 插入--")
insert_item_sql = "INSERT INTO comm_link_content (data_index , comm_link_id , content_type , content_data , machine_translate_data , human_translate_data) VALUE (%s , %s , %s, %s, %s, %s )"
cursor.execute(
insert_item_sql,
(content.data_index, comm_link.id, content.content_type,
content.content_data, content.machine_translate_data,
content.human_translate_data))
else:
Log.d("查询到链接内容 更新--")
update_item_sql = "UPDATE comm_link_content SET content_type = %s , content_data = %s ,machine_translate_data = %s , human_translate_data = %s WHERE comm_link_id = %s AND data_index = %s"
cursor.execute(update_item_sql,
(content.content_type, content.content_data,
content.machine_translate_data,
content.human_translate_data, comm_link.id,
content.data_index))
self.database.commit()
cursor.close()
def init_road_map():
url = 'https://robertsspaceindustries.com/api/roadmap/v1/boards/1'
road_header = {
'x-rsi-token': '6ec0661bc4216ad2b6017727e59349e7',
'accept': 'application/json',
'accept-encoding': 'gzip, deflate, br',
'user-agent': 'Mozilla/5.0 Chrome/66.0.3359.139 Mobile Safari/537.36',
'accept-language': 'zh-CN,zh;q=0.9,en;q=0.8'
}
road_map_req = requests.get(url=url, headers=road_header)
road_map_json = json.loads(road_map_req.content)
data = road_map_json['data']
road_map_releases_json = data['releases']
# "category_id": 6 ships and vehicle
# "category_id": 7 weapon and items
# "category_id": 3 characters
# "category_id": 4 locations
# "category_id": 2 game_play
# "category_id": 5 AI
# "category_id": 1 Core Tech
Log.d(road_map_releases_json)
def insert_vehicle(self, vehicle):
# 更新载具
self.__insert_vehicle2mysql(vehicle)
# 更新载具url
self.__insert_img(vehicle)
# 更新载具类型
self.__insert_ship_type(vehicle)
# 更新载具装备
# 先删除旧的装备
cursor = self.database.cursor()
delete_sql = "DELETE FROM ship_equipment_en WHERE ship_id = %s "
cursor.execute(delete_sql, vehicle.id)
Log.d("id : " + str(vehicle.id))
Log.d("name : " + str(vehicle.name))
Log.d("删除了 : " + str(cursor.rowcount))
self.database.commit()
cursor.close()
for equip in vehicle.ship_equipment_list:
self.__insert_ship_equipment(equip)
def init_with_data_json(self):
self.id = self.data_json.get('id')
self.production_status = self.data_json.get('production_status')
self.production_note = self.data_json.get('production_note')
self.length = self.data_json.get('length')
self.beam = self.data_json.get('beam')
self.height = self.data_json.get('height')
self.size = self.data_json.get('size')
self.mass = self.data_json.get('mass')
self.type = self.data_json.get('type')
self.cargocapacity = self.data_json.get('cargocapacity')
self.min_crew = self.data_json.get('min_crew')
self.max_crew = self.data_json.get('max_crew')
self.scm_speed = self.data_json.get('scm_speed')
self.afterburner_speed = self.data_json.get('afterburner_speed')
self.pitch_max = self.data_json.get('pitch_max')
self.yaw_max = self.data_json.get('yaw_max')
self.roll_max = self.data_json.get('roll_max')
self.x_axis_acceleration = self.data_json.get('x_axis_acceleration')
self.y_axis_acceleration = self.data_json.get('y_axis_acceleration')
self.z_axis_acceleration = self.data_json.get('z_axis_acceleration')
self.chassis_id = self.data_json.get('chassis_id')
self.time_modified = self.data_json.get('time_modified')
self.name = self.data_json.get('name')
self.focus = self.data_json.get('focus')
self.description = self.data_json.get('description')
media_list = self.data_json.get('media')
if len(media_list) > 0:
media = media_list[0]
images = media.get('images')
if images is not None:
self.store_large = images.get('store_large')
Log.d('Loading Ship ' + self.name)
if self.focus is not None:
if self.focus not in Vehicle.ship_type_list:
Vehicle.ship_type_list.append(self.focus)
Log.d('Ship Type ' + self.focus)
from CitizenWikiRobot.StaticField import StaticField
if self.name not in StaticField.SHIP_NAME_MAP:
Log.d('Do not have ' + self.name + ' translate')
manufacturer_json = self.data_json.get('manufacturer')
manufacturer = Manufacturer()
manufacturer.id = manufacturer_json.get('id')
manufacturer.code = manufacturer_json.get('code')
manufacturer.name = manufacturer_json.get('name')
manufacturer.known_for = manufacturer_json.get('known_for')
manufacturer.description = manufacturer_json.get('description')
media_list = manufacturer_json.get('media')
if media_list is not None and len(media_list) > 0:
manufacturer_media = media_list[0]
if manufacturer_media is not None:
manufacturer.source_url = manufacturer_media.get('source_url')
from CitizenWikiRobot.ship_robot import append_manufacturer
append_manufacturer(manufacturer)
self.manufacturer_code = manufacturer.code
self.init_ship_equipments('RSIAvionic', 'radar')
self.init_ship_equipments('RSIAvionic', 'computers')
self.init_ship_equipments('RSIPropulsion', 'fuel_intakes')
self.init_ship_equipments('RSIPropulsion', 'fuel_tanks')
self.init_ship_equipments('RSIPropulsion', 'quantum_drives')
self.init_ship_equipments('RSIPropulsion', 'jump_modules')
self.init_ship_equipments('RSIPropulsion', 'quantum_fuel_tanks')
self.init_ship_equipments('RSIThruster', 'main_thrusters')
self.init_ship_equipments('RSIThruster', 'maneuvering_thrusters')
self.init_ship_equipments('RSIModular', 'power_plants')
self.init_ship_equipments('RSIModular', 'coolers')
self.init_ship_equipments('RSIModular', 'shield_generators')
self.init_ship_equipments('RSIWeapon', 'weapons')
self.init_ship_equipments('RSIWeapon', 'turrets')
self.init_ship_equipments('RSIWeapon', 'missiles')
self.init_ship_equipments('RSIWeapon', 'utility_items')
def get_galactic_guide():
mysql_helper = MysqlHelper()
s = requests.Session()
url = 'https://robertsspaceindustries.com/api/hub/getCommlinkItems'
road_header = {
'Connection': 'close',
'x-rsi-token': '6ec0661bc4216ad2b6017727e59349e7',
'accept': 'application/json',
'accept-encoding': 'gzip, deflate, br',
'user-agent': 'Mozilla/5.0 Chrome/66.0.3359.139 Mobile Safari/537.36',
'accept-language': 'zh-CN,zh;q=0.9,en;q=0.8'
}
for page in range(0, 6):
galactic_guide_req = ''
pamas = {"channel": "spectrum-dispatch", "page": str(page), "series": "galactic-guide", "sort": "publish_new"}
while galactic_guide_req == '':
try:
galactic_guide_req = s.post(url=url, data=pamas, headers=road_header)
except:
print("Connection refused by the server..")
print("Let me sleep for 5 seconds")
print("ZZzzzz...")
time.sleep(5)
print("Was a nice sleep, now let me continue...")
continue
galactic_guide_json = json.loads(galactic_guide_req.content)
data = galactic_guide_json['data']
guide_soup = bs4.BeautifulSoup(data, "lxml")
for link in guide_soup.findAll('a'):
guide_url = link.get('href')
if guide_url is None:
continue
icon_origin_data = link.find('div', class_='background').get('style')
start_index = icon_origin_data.find('(')
end_index = icon_origin_data.find(')')
background = icon_origin_data[start_index + +2:end_index - 1]
title_tag = link.find('div', class_='title trans-opacity trans-03s')
title = title_tag.string
comm_link = CommLink()
comm_link.type = 'galactic_guide'
comm_link.url = guide_url
comm_link.title = title
comm_link.background = background
link_page = ''
while link_page == '':
try:
link_page = requests.get(StaticField.BASE_URL + guide_url)
except:
print("Connection refused by the server..")
print("Let me sleep for 5 seconds")
print("ZZzzzz...")
time.sleep(5)
print("Was a nice sleep, now let me continue...")
continue
link_soup = bs4.BeautifulSoup(link_page.content, "lxml")
content_block1_tag = link_soup.find('div', class_='content-block1 rsi-markup')
content_tag = content_block1_tag.find('div', class_='content')
first_image_tag = link_soup.find('div', class_='content clearfix')
if first_image_tag is not None:
image_tag = first_image_tag.find('img')
if image_tag is not None:
first_image_comm_content = CommLinkContent()
first_image_comm_content.content_type = "image"
first_image_comm_content.content_data = image_tag.get('src')
first_image_comm_content.data_index = 0
# Log.d(first_image_comm_content.content_data)
comm_link.content.append(first_image_comm_content)
content = content_tag.find_all('div', class_='variant-block')
if len(content) > 0:
for div in content:
get_comm_link_content(comm_link, div.children)
else:
get_comm_link_content(comm_link, content_tag.children)
mysql_helper.insert_comm_link(comm_link)
Log.d(title)
from CitizenWikiRobot.MysqlHelper import MysqlHelper
import os
from CitizenWikiRobot.Log import Log
import json
if __name__ == '__main__':
my = MysqlHelper()
jsonFile = open(os.curdir + "/priceJson", 'r')
jsonStr = json.loads(jsonFile.read())
for ship_price in jsonStr:
name = ship_price.get('name')
price = ship_price.get('msrp')
price_final = price[1:]
Log.d(price_final)
my.update_ship_price(name, price_final)
def __insert_vehicle2mysql(self, vehicle):
cursor = self.database.cursor()
have_data = self.__select_vehicle(vehicle)
if have_data:
Log.d('Update Ship' + vehicle.name)
sql = Vehicle.get_update_sql()
rows = cursor.execute(
sql,
(self.handle_opt_data(vehicle.production_status),
self.handle_opt_data(vehicle.production_note),
self.handle_opt_data(
vehicle.length), self.handle_opt_data(
vehicle.beam), self.handle_opt_data(vehicle.height),
self.handle_opt_data(
vehicle.size), self.handle_opt_data(
vehicle.mass), self.handle_opt_data(vehicle.type),
self.handle_opt_data(vehicle.cargocapacity),
self.handle_opt_data(
vehicle.min_crew), self.handle_opt_data(vehicle.max_crew),
self.handle_opt_data(vehicle.scm_speed),
self.handle_opt_data(vehicle.afterburner_speed),
self.handle_opt_data(
vehicle.pitch_max), self.handle_opt_data(vehicle.yaw_max),
self.handle_opt_data(vehicle.roll_max),
self.handle_opt_data(vehicle.x_axis_acceleration),
self.handle_opt_data(vehicle.y_axis_acceleration),
self.handle_opt_data(vehicle.z_axis_acceleration),
self.handle_opt_data(vehicle.manufacturer_code),
self.handle_opt_data(vehicle.chassis_id),
self.handle_opt_data(
vehicle.time_modified), self.handle_opt_data(
vehicle.name), self.handle_opt_data(vehicle.focus),
self.handle_opt_data(
vehicle.description), self.handle_opt_data(vehicle.url),
self.handle_opt_data(vehicle.store_large),
int(self.handle_opt_data(vehicle.id))))
self.database.commit()
else:
Log.d('Insert Ship' + vehicle.name)
sql = Vehicle.get_insert_sql()
rows = cursor.execute(
sql,
(int(self.handle_opt_data(vehicle.id)),
self.handle_opt_data(vehicle.production_status),
self.handle_opt_data(vehicle.production_note),
self.handle_opt_data(
vehicle.length), self.handle_opt_data(
vehicle.beam), self.handle_opt_data(vehicle.height),
self.handle_opt_data(
vehicle.size), self.handle_opt_data(
vehicle.mass), self.handle_opt_data(vehicle.type),
self.handle_opt_data(vehicle.cargocapacity),
self.handle_opt_data(
vehicle.min_crew), self.handle_opt_data(vehicle.max_crew),
self.handle_opt_data(vehicle.scm_speed),
self.handle_opt_data(vehicle.afterburner_speed),
self.handle_opt_data(
vehicle.pitch_max), self.handle_opt_data(vehicle.yaw_max),
self.handle_opt_data(vehicle.roll_max),
self.handle_opt_data(vehicle.x_axis_acceleration),
self.handle_opt_data(vehicle.y_axis_acceleration),
self.handle_opt_data(vehicle.z_axis_acceleration),
self.handle_opt_data(vehicle.manufacturer_code),
self.handle_opt_data(vehicle.chassis_id),
self.handle_opt_data(
vehicle.time_modified), self.handle_opt_data(
vehicle.name), self.handle_opt_data(vehicle.focus),
self.handle_opt_data(
vehicle.description), self.handle_opt_data(vehicle.url),
self.handle_opt_data(vehicle.store_large)))
self.database.commit()
vehicle.id = cursor.lastrowid
cursor.close()