def insert_lhb_detail_table(self, items):
"""
This method inserts a list of items into table cinema_info
Parameters
----------
items: list of cinema items to insert
Returns
-------
None
"""
for item in items:
row = (item['lhb_date'], item['stock_id'], item['stock_name'],
item['reason'].encode('utf-8'),
item['yyb_name'].encode('utf-8'), item['buy_or_sell'],
item['buy_or_sell_order'],
item['buy_value'] if item['buy_value'] != '-' else 0,
item['buy_value_percent']
if item['buy_value_percent'] != '-' else 0,
item['sell_value'] if item['sell_value'] != '-' else 0,
item['sell_value_percent'] if
item['sell_value_percent'] != '-' else 0, item['net_value'],
get_current_timestamp("Asia/Shanghai"))
try:
self.database_instance.insert_detail_info_dfcf(row)
except mysql.connector.errors.IntegrityError as e:
print(item)
self.logger.info(item)
self.logger.error(e)
def insert_data(self, insert_ddl, insert_data):
try:
self.cursor.execute(insert_ddl, insert_data)
self.connection.commit()
self.logger.info('Insertion succeed! Timestamp: {0} {1}'
.format("UTC+08:00", get_current_timestamp("Asia/Shanghai")))
return True
except mysql.connector.errors.OperationalError as err:
self.logger.exception('Insertion failed. Exception: {0}. Timestamp: {1} {2}'
.format(err, "UTC+08:00", get_current_timestamp("Asia/Shanghai")))
self.logger.info('====== Reconnet to mysql ======')
self.connect_db()
self.cursor.execute(insert_ddl, insert_data)
self.connection.commit()
self.logger.info('Insertion succeed! Timestamp: {0} {1}'
.format("UTC+08:00", get_current_timestamp("Asia/Shanghai")))
except mysql.connector.Error as err:
self.logger.exception('Insertion failed. Exception: {0}. Timestamp: {1} {2}'
.format(err, "UTC+08:00", get_current_timestamp("Asia/Shanghai")))
return False
def insert_lhb_detail_table(self, items):
"""
Parameters
----------
items: list of cinema items to insert
Returns
-------
None
"""
for item in items:
row = (item['stock_id'],
item['end_date'],
item['reason'].encode('utf-8'),
item['change_percent'],
item['buy_value'],
item['sell_value'],
item['net_value'],
get_current_timestamp("Asia/Shanghai"))
self.database_instance.insert_detail_info(row)
def insert_lhb_summary_table(self, items):
"""
This method inserts a list of items into table cinema_info
Parameters
----------
items: list of cinema items to insert
Returns
-------
None
"""
for item in items:
row = (item['stock_id'], item['end_date'],
item['reason'].encode('utf-8'), item['change_percent'],
item['buy_value'], item['sell_value'], item['net_value'],
get_current_timestamp("Asia/Shanghai"))
try:
self.database_instance.insert_summary_info(row)
except Exception as e:
self.logger.info(item)
self.logger.error(e)
def insert_lhb_summary_table(self, items):
"""
This method inserts a list of items into table cinema_info
Parameters
----------
items: list of cinema items to insert
Returns
-------
None
"""
for item in items:
row = (item['lhb_date'], item['stock_id'], item['stock_name'],
item['close_price'] if item['close_price'] != '' else -1,
decimal.Decimal("%.2f" % float(item['change_percent']))
if item['change_percent'] != '' else -10000,
item['lhb_net_value'] if item['lhb_net_value'] != '' else 0,
item['lhb_buy_value'] if item['lhb_buy_value'] != '' else 0,
item['lhb_sell_value'] if item['lhb_sell_value'] != '' else
0, item['lhb_total_value']
if item['lhb_total_value'] != '' else 0,
item['trade_amount'] if item['trade_amount'] != '' else -1,
decimal.Decimal("%.2f" % float(item['net_value_percent']))
if item['net_value_percent'] != '' else -1,
decimal.Decimal("%.2f" % float(item['total_value_percent']))
if item['total_value_percent'] != '' else -1,
decimal.Decimal("%.2f" % float(item['turnover_ratio']))
if item['turnover_ratio'] != '' else -1,
item['reason'].encode('utf-8'),
get_current_timestamp("Asia/Shanghai"))
try:
self.database_instance.insert_summary_info_dfcf(row)
except Exception as e:
print(item)
self.logger.info(item)
self.logger.error(e)
def parse(self, response):
"""
It's the method to pass the lhb detail info from response object
Parameters
----------
response
Returns
-------
"""
stock_id = re.search(r'stockcode=(\d+)&', response.url).group(1)
summary_tables = response.css('.tithd-s1.mt')
summary_table_list = []
reason_list = []
for summary_table in summary_tables:
# 抓上榜原因
reason = summary_table.css('p')[0].xpath('text()').extract_first()
reason_list.append(reason)
# 上榜原因下面的总成交数据
text = summary_table.css('i').xpath('text()').extract()
summary_table_list.append(
list(map(lambda x: re.sub('[^0-9\.]+', '', x), text)))
detail_tables = response.css('.table-s1.mt')
print(len(detail_tables))
detail_table_lists = []
i = 0
for detail_table in detail_tables:
trs = detail_tables.css('tr')
detail_tables_list = []
for tr in trs[2:7]:
item = LhbDetailItem()
td_list = tr.css('td').xpath('text()').extract()
# institution name
institution_name = tr.css('td.tl').css('a').xpath(
'text()').extract()
item['institution_name'] = institution_name
# trade detail url
trade_detail_url = tr.css('td.last').css(
'a::attr(href)').extract()
item['trade_detail_url'] = trade_detail_url
row_list = list(map(lambda x: x.encode('utf8'), td_list))[3:8]
numbers_list = list(
map(lambda x: re.sub('[^0-9\.\-]+', '', x.decode('utf8')),
row_list))
# 买入金额
item['buy_value'] = numbers_list[0]
# 买入金额/总成交额
item['buy_over_total_ratio'] = numbers_list[1]
# 卖出金额
item['sell_value'] = numbers_list[2]
# 卖出金额/总成交额
item['sell_over_total_ratio'] = numbers_list[3]
# 净买入金额
item['net_buy_value'] = numbers_list[4]
#上榜原因
item['reason'] = reason_list[i]
# 收盘价
item['close_price'] = summary_table_list[i][0]
# 涨跌幅
item['change_percent'] = summary_table_list[i][1]
# 总成交量
item['total_volume'] = summary_table_list[i][2]
# 总成交金额
item['total_amount'] = summary_table_list[i][3]
# 爬取时间
item['crawl_date'] = get_current_timestamp("Asia/Shanghai")
yield item
i += 1
def insert_lhb_table(self, items):
"""
This method inserts a list of items into table cinema_info
Parameters
----------
items: list of cinema items to insert
Returns
-------
None
"""
for item in items:
detail_row = (item['lhb_date'], item['stock_id'],
item['stock_name'], item['reason'].encode('utf-8'),
item['yyb_name'].encode('utf-8'),
item['buy_or_sell'], item['buy_or_sell_order'],
item['buy_value'] if item['buy_value'] != '-' else 0,
item['buy_value_percent']
if item['buy_value_percent'] != '-' else 0,
item['sell_value'] if item['sell_value'] != '-' else
0, item['sell_value_percent']
if item['sell_value_percent'] != '-' else 0,
item['net_value'],
get_current_timestamp("Asia/Shanghai"))
summary_item = item['summary_item']
summary_row = (
summary_item['lhb_date'], summary_item['stock_id'],
summary_item['stock_name'], summary_item['close_price']
if summary_item['close_price'] != '' else -1,
decimal.Decimal("%.2f" % float(summary_item['change_percent']))
if summary_item['change_percent'] != '' else -10000,
summary_item['lhb_net_value']
if summary_item['lhb_net_value'] != '' else 0,
summary_item['lhb_buy_value']
if summary_item['lhb_buy_value'] != '' else 0,
summary_item['lhb_sell_value']
if summary_item['lhb_sell_value'] != '' else 0,
summary_item['lhb_total_value']
if summary_item['lhb_total_value'] != '' else 0,
summary_item['trade_amount']
if summary_item['trade_amount'] != '' else -1,
decimal.Decimal("%.2f" %
float(summary_item['net_value_percent']))
if summary_item['net_value_percent'] != '' else -1,
decimal.Decimal("%.2f" %
float(summary_item['total_value_percent']))
if summary_item['total_value_percent'] != '' else -1,
decimal.Decimal("%.2f" % float(summary_item['turnover_ratio']))
if summary_item['turnover_ratio'] != '' else -1,
summary_item['reason'].encode('utf-8'),
get_current_timestamp("Asia/Shanghai"))
try:
if not self.database_instance.is_detail_info_existed(
item['stock_id'], item['lhb_date'], item['reason'],
item['yyb_name'], item['buy_or_sell'],
item['buy_or_sell_order']):
self.database_instance.insert_detail_info_dfcf(detail_row)
if not self.database_instance.is_summary_info_existed(
summary_item['stock_id'], summary_item['lhb_date'],
summary_item['reason']):
self.database_instance.insert_summary_info_dfcf(
summary_row)
except mysql.connector.errors.IntegrityError as e:
self.logger.info(item)
self.logger.error(e)