def process(self, data, history=False):
'''
数据存储
'''
# print(data)
data_, date_ = data[0], data[1]
data = eval(data_)
date = eval(date_)
# print(data)
# print(date)
df = pd.DataFrame(data)
origin_columns = list(df.columns)
df['date'] = date
origin_columns.insert(0, 'date')
# print(df.head(5))
# print(origin_columns)
df = df.reindex(columns=origin_columns)
# print(df.head(5))
if history == True:
'''
历史数据
'''
engine = DBSelector().get_engine('db_stock', 'qq')
try:
df.to_sql('bond_overview', con=engine, index_label='id',
if_exists='replace', dtype={'date': DATE})
except Exception as e:
print(e)
else:
print('pass')
else:
'''
当天数据
'''
last_data = df.iloc[-1]
# current_date = datetime.date
if last_data['date'] == datetime.date.today().strftime('%Y-%m-%d'):
# pass
insert_data = list(last_data.values)
insert_data = self.convert(insert_data)
join_str = ','.join(['%s']*21)
conn = DBSelector().get_mysql_conn('db_stock', 'qq')
cursor = conn.cursor()
cursor.execute(
'SELECT id FROM db_stock.bond_overview order by id desc limit 1')
idx = cursor.fetchone()
idx = idx[0]
# insert_sql = f'''insert into `bond_overview` (`id`,`date`, `price`,`volume`,`amount`,`count`,`avg_price`,`mid_price`,`avg_premium_rt`, `avg_ytm_rt`, `increase_val`, `increase_rt`, `turnover_rt`, `price_90`, `price_90_100`, `price_100_110`, `price_110_120`, `price_120_130`, `price_130`, `idx_price`, `idx_increase_rt`）value ({join_str})'''
insert_sql = f'''insert into `bond_overview` values ({join_str})'''
# print(insert_sql)
insert_data.insert(0, idx+1)
cursor.execute(insert_sql, insert_data)
conn.commit()
conn.close()
def notice(self):
buy,sell=self.run()
sub = '{}: 美元汇率{}'.format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M'),buy)
logger.info(sub)
# sendmail('',sub)
conn=DBSelector().get_mysql_conn('db_stock','qq')
cursor = conn.cursor()
cmd = 'insert into `usd_ratio` (`price`,`date`) VALUES ({},{!r})'.format(buy,datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
try:
cursor.execute(cmd)
conn.commit()
except Exception as e:
logger.error(e)
conn.rollback()
conn.close()