def notify(self, today):
now = datetime.datetime.now()
if now.hour > NOTIFY_HOUR:
# 下午才会发通知
query_sql = '''select `基金代码`,`基金简称`,`实时价格`,`实时净值`,`溢价率`,`净值日期` from `{}` where `申购状态`='开放' and `申赎状态`='开放' and !(`实时价格`=1 and `涨跌幅`=0 and `成交额-万`=0) order by `溢价率` limit 10'''.format(
today)
cursor.execute(query_sql)
ret = cursor.fetchall()
html = '<div><table border="1">' \
'<tr><th>基金代码</th><th>基金简称</th><th>实时价格</th><th>实时净值</th><th>溢价率</th><th>净值日期</th></tr>'
for i in ret:
html += f'<tr><td>{i[0]}</td><td>{i[1]}</td><td>{i[2]}</td><td>{i[3]}</td><td>{i[4]}</td><td>{i[5]}</td></tr>'
html += '</table></div>'
query_sql = '''select `基金代码`,`基金简称`,`实时价格`,`实时净值`,`溢价率`,`净值日期` from `{}` where `申购状态`='开放' and `申赎状态`='开放' and `溢价率` is not null and !(`实时价格`=1 and `涨跌幅`=0 and `成交额-万`=0) order by `溢价率` desc limit 10'''.format(
today)
cursor.execute(query_sql)
ret = cursor.fetchall()
html += '<div><table border="1">' \
'<tr><th>基金代码</th><th>基金简称</th><th>实时价格</th><th>实时净值</th><th>溢价率</th><th>净值日期</th></tr>'
for i in ret:
html += f'<tr><td>{i[0]}</td><td>{i[1]}</td><td>{i[2]}</td><td>{i[3]}</td><td>{i[4]}</td><td>{i[5]}</td></tr>'
html += '</table></div>'
title = f'{today} 基金折溢价'
try:
send_from_aliyun(title, html, types='html')
except Exception as e:
logger.error(e)
logger.info('发送失败')
else:
logger.info('发送成功')
def predict_price():
session = requests.Session()
session.get(url=home_page, headers=home_headers)
r = session.get(url=xueqiu_url, headers=headers)
js_data = r.json()
quote = js_data.get('data', {}).get('quote')
quote['crawltime'] = datetime.datetime.now()
doc.insert_one(quote)
percent = quote.get('percent')
jsl_qdii, est_val_dt = qdii_info()
if jsl_qdii:
predict_v = round((1 + percent * 0.95 * 0.01) * jsl_qdii, 3)
logger.info(f'最新估值{predict_v}')
d = {'日期': today, '估值': predict_v}
client['db_stock']['huabaoyouqi_predict'].insert_one(d)
title = f'华宝估值{predict_v} 净值日期{est_val_dt[5:]}'
send_from_aliyun(title, '')
else:
notify('华宝油气获取估值失败')
def get_price():
session = requests.Session()
session.get(url=home_page, headers=home_headers)
r = session.get(url=url,
headers=headers)
js_data = r.json()
quote = js_data.get('data', {}).get('quote')
quote['crawltime'] = datetime.datetime.now()
doc.insert_one(quote)
percent = quote.get('percent')
ret = qdii_info()
if ret:
predict_v = round((1+percent*0.95*0.01)*ret,3)
logger.info(f'最新估值{predict_v}')
d={'日期':today,'估值':predict_v}
client['db_stock']['huabaoyouqi_predict'].insert_one(d)
title=f'{today}华宝估值{predict_v}'
send_from_aliyun(title,'')
else:
logger.error('获取估值失败')
def cb_info():
con = DB.get_engine('db_stock', 'qq')
df = pd.read_sql('tb_bond_jisilu', con=con)
df['grade'] = df['评级'].map(lambda x: map_rate(x))
df['可转债综合价格'] = df['可转债价格'] + df['溢价率'] * df['grade']
df = df.sort_values(by='可转债综合价格')
df = df[df['强赎日期'].isnull()]
df1 = df[['可转债代码', '可转债名称', '可转债综合价格', '可转债价格', '溢价率', '评级']].head(20)
df1 = df1.reset_index(drop=True)
df2=df1
df2['可转债名称']=df2['可转债名称'].map(lambda x:x.replace('转债',''))
send_content = df2.to_html(index=False, border=1, justify='center')
send_content=send_content.replace('class', 'cellspacing=\"0\" class')
title = '{} 可转债综合价格前20名'.format(datetime.datetime.now().strftime('%Y-%m-%d'))
conn2 = DB.get_engine('double_low_full', 'qq')
df1.to_sql(f'double_low_{today}', con=conn2, if_exists='replace')
try:
send_from_aliyun(title, send_content, types='html')
except Exception as e:
logger.error('报错了')
logger.error(e)
else:
logger.info('发送成功！')
def save_to_dataframe(self, data, index, choice, post_fix):
engine = self.DB.get_engine('db_zdt', 'qq')
data_len = len(data)
filename = os.path.join(
self.path, self.today + "_" + post_fix + ".xls")
if choice == 1:
for i in range(data_len):
data[i][choice] = data[i][choice]
df = pd.DataFrame(data, columns=index)
# 今日涨停
if choice == 1:
df['今天的日期'] = self.today
df.to_excel(filename, encoding='gbk')
try:
df.to_sql(self.today + post_fix, engine, if_exists='fail')
except Exception as e:
self.logger.info(e)
# 昨日涨停
if choice == 2:
df = df.set_index('序号')
formula = lambda x: round(x * 100, 3)
df['最大涨幅'] = df['最大涨幅'].map(formula)
df['最大跌幅'] = df['最大跌幅'].map(formula)
df['今日开盘涨幅'] = df['今日开盘涨幅'].map(formula)
df['昨日涨停强度'] = df['昨日涨停强度'].map(lambda x: round(x, 0))
df['今日涨停强度'] = df['今日涨停强度'].map(lambda x: round(x, 0))
try:
df.to_sql(self.today + post_fix, engine, if_exists='fail')
except Exception as e:
self.notify(f'{self.__class__} 出错')
self.logger.info(e)
avg = round(df['今日涨幅'].mean(), 2)
median = round(df['今日涨幅'].median(), 2)
min_v = round(df['今日涨幅'].min(), 2)
min_index = df['今日涨幅'].argmin()
min_percent_name = df.iloc[min_index]['名称']
current = datetime.datetime.now().strftime('%Y-%m-%d')
title = '昨涨停今天{}平均涨{}\n'.format(current, avg)
content = '<p>昨天涨停今天<font color="red">{}</font></p>' \
'<p>平均涨幅 <font color="red">{}</font></p>' \
'<p>涨幅中位数 <font color="red">{}</font></p>' \
'<p>涨幅最小 <font color="red">{}</font></p>' \
'<p>涨幅最小股 <font color="red">{}</font></p>'.format(current, avg, median, min_v,min_percent_name)
try:
send_from_aliyun(title, content, types='html')
except Exception as e:
print(e)
def read_data_source(today):
engine = DB.get_engine('db_jisilu','qq')
try:
df = pd.read_sql('tb_jsl_{}'.format(today),con=engine)
except Exception as e:
logger.error(e)
send_from_aliyun('代码{}出错\n读取表tb_jsl_{}失败'.format('bond_daily',today),'{}'.format(today))
return None
else:
return df
def download(self, url, data, retry=5):
for i in range(retry):
try:
r = requests.post(url, headers=self.headers, data=data)
if not r.text or r.status_code != 200:
continue
else:
return r
except Exception as e:
self.logger.info(e)
send_from_aliyun(title='jisilu可转债', content='异常信息>>>>{}'.format(e))
continue
return None
def save_to_dataframe(self, data, indexx, choice, post_fix):
engine = self.DB.get_engine('db_zdt', 'qq')
if not data:
exit()
data_len = len(data)
if choice == 1:
for i in range(data_len):
data[i][choice] = data[i][choice]
df = pd.DataFrame(data, columns=indexx)
filename = os.path.join(self.path,
self.today + "_" + post_fix + ".xls")
# 今日涨停
if choice == 1:
df['今天的日期'] = self.today
df.to_excel(filename, encoding='gbk')
try:
df.to_sql(self.today + post_fix, engine, if_exists='fail')
except Exception as e:
logger.info(e)
# 昨日涨停
if choice == 2:
df = df.set_index('序号')
df['最大涨幅'] = df['最大涨幅'].map(lambda x: round(x * 100, 3))
df['最大跌幅'] = df['最大跌幅'].map(lambda x: round(x * 100, 3))
df['今日开盘涨幅'] = df['今日开盘涨幅'].map(lambda x: round(x * 100, 3))
df['昨日涨停强度'] = df['昨日涨停强度'].map(lambda x: round(x, 0))
df['今日涨停强度'] = df['今日涨停强度'].map(lambda x: round(x, 0))
try:
df.to_sql(self.today + post_fix, engine, if_exists='fail')
except Exception as e:
logger.info(e)
avg = round(df['今日涨幅'].mean(), 2)
median = round(df['今日涨幅'].median(), 2)
min_v = round(df['今日涨幅'].min(), 2)
current = datetime.datetime.now().strftime('%Y-%m-%d')
title = '昨天涨停个股今天{}\n的平均涨幅{}\n'.format(current, avg)
content = '昨天涨停个股今天{}\n的平均涨幅{}\n涨幅中位数{}\n涨幅最小{}\n'.format(
current, avg, median, min_v)
try:
send_from_aliyun(title, content)
except Exception as e:
print(e)
def notice_me(self, today):
now = datetime.datetime.now()
if now.hour > NOTIFY_HOUR:
# 下午才会发通知
title = f'{today} 基金折溢价'
html = ''
html = self.combine_html(html, TODAY)
try:
send_from_aliyun(title, html, types='html')
except Exception as e:
self.logger.error(e)
self.logger.info('发送失败')
else:
self.logger.info('发送成功')
def main(send_mail=True):
for item in doc.find({'last_resp_date': {
'$gt': date
}}, {
'html': 0,
'resp': 0,
'content': 0
}):
del item['_id']
total_list.append(item)
df = pd.DataFrame(total_list)
df['createTime'] = pd.to_datetime(df['createTime'])
df = df.set_index('createTime', drop=True)
new_df = df.resample('W').count()
show_data = new_df[['creator']].iloc[:last_time:-1]
# print(show_data)
# 最大值与
max_index = new_df['creator'].idxmax().to_pydatetime().strftime('%Y-%m-%d')
max_v = new_df['creator'].max()
current = datetime.datetime.now().strftime('%Y-%m-%d')
title = f'jsl一周发帖数量分析 {current}'
percentage = np.round(
(show_data['creator'].values[:-1] - show_data['creator'].values[1:]) /
show_data['creator'].values[1:] * 100, 0)
content = '| 日期 | 贴数 | 环比 |\n'
# print(percentage)
percentage = np.append(percentage, np.nan)
start_index = 0
for index, item in show_data.iterrows():
# print(index,item['creator'])
py_date = index.to_pydatetime().strftime('%Y-%m-%d')
count = item['creator']
content += f'| {py_date} | {count} | {percentage[start_index]}% |\n'
start_index += 1
content += f'最大值发生在 {max_index}，贴数为 {max_v}\n'
logger.info(title)
logger.info(content)
if send_mail:
try:
send_from_aliyun(title, content)
except Exception as e:
logger.error(e)
def save_list_mongo(source_list):
current = datetime.datetime.now()
title = current.strftime('%Y-%m-%d') + '可转债周涨幅的前10和后10'
top10 = source_list[:10]
last10 = source_list[-10:]
top_str = ['{} : {}'.format(i[0], i[1]) for i in top10]
last_str = ['{} : {}'.format(i[0], i[1]) for i in last10[::-1]]
top_str = '\n'.join(top_str)
last_str = '\n'.join(last_str)
content = '跌幅前10:::\n' + last_str + '\n涨幅前10:::\n' + top_str
try:
send_from_aliyun(title, content)
except Exception as e:
logger.error(e)
d = dict(source_list)
d['updated'] = current
try:
db['db_parker']['kzz_weekly_raise'].insert(d)
except Exception as e:
logger.error(e)
def main():
result = db['db_parker']['jsl'].find({},{'html':0}).sort('_id',pymongo.DESCENDING).limit(MAX)
filter_result = []
for i in result:
createTime = i['createTime']
createTime = datetime.datetime.strptime(createTime,'%Y-%m-%d %H:%M')
if createTime >= last_day :
title = i['title']
creator = i['creator']
resp_count = len(i['resp'])
url = i['url']
d = {'title':title,'url':url,'resp_count':resp_count}
filter_result.append(d)
hot_list = list(sorted(filter_result,key=lambda x:x['resp_count'],reverse=True))[:10]
title,html = format_mail(hot_list)
try:
send_from_aliyun(title,html,types='html')
except Exception as e:
# logger.error(e)
print(e)
engine_daily = DB.get_engine('db_daily', 'qq')
engine = DB.get_engine('db_stock', 'qq')
jsl_df = pd.read_sql('tb_bond_jisilu', con=engine)
basic_df = pd.read_sql('tb_basic_info', con=engine, index_col='index')
price_df = pd.read_sql(today, con=engine_daily)
INFO = _json_data['mongo']['arm']
host = INFO['host']
port = INFO['port']
user = INFO['user']
password = INFO['password']
connect_uri = f'mongodb://{user}:{password}@{host}:{port}'
db = pymongo.MongoClient(connect_uri)
except Exception as e:
logger.error(e)
send_from_aliyun('读取数据库失败', '')
exit()
# 市值选择
def market_share(zg_df, price_df):
if len(zg_df) == 0:
return zg_df
for index in zg_df.index:
price = price_df[price_df['code'] == zg_df.loc[index]
['code']]['trade'].values[0]
ltgb = zg_df.loc[index]['outstanding']
total_gb = zg_df.loc[index]['totals']
lt = round(price * ltgb, 1) # 流通市值
zg_df.loc[index, 'outstanding_shizhi'] = lt
def find_zz_zg_diff():
current = datetime.date.today().strftime('%Y-%m-%d')
# current ='2019-10-18'
if ts.is_holiday(current):
logger.info('假期')
return
con = DB.get_mysql_conn('db_stock', 'qq')
cursor = con.cursor()
zz_than_zg = 'select count(*) from tb_bond_jisilu WHERE `正股涨跌幅`>=`可转债涨幅` and `正股涨跌幅`<=0'
minus_count_cmd = 'select count(*) from tb_bond_jisilu where `可转债涨幅`<0'
plug_count_cmd = 'select count(*) from tb_bond_jisilu where `可转债涨幅`>=0'
cursor.execute(zz_than_zg)
zz_than_zg_count = cursor.fetchone()[0]
# zz_than_zg_count_=zz_than_zg_count[0]
cursor.execute(minus_count_cmd)
minus_count = cursor.fetchone()[0]
cursor.execute(plug_count_cmd)
plug_count = cursor.fetchone()[0]
try:
search_sql = 'select `溢价率` from `tb_bond_jisilu`'
cursor.execute(search_sql)
except Exception as e:
logger.error(e)
else:
content = cursor.fetchall()
data = []
for item in content:
data.append(item[0])
np_data = np.array(data)
max_value = np.round(np_data.max(), 2)
min_value = np.round(np_data.min(), 2)
mean = np.round(np_data.mean(), 2)
median = np.round(np.median(np_data), 2)
count = len(np_data)
t_value = (current, float(mean), float(max_value), float(min_value),
float(median), count)
update_sql = 'insert into tb_bond_avg_yjl (Date,溢价率均值,溢价率最大值,溢价率最小值,溢价率中位数,转债数目) values (%s,%s,%s,%s,%s,%s)'
try:
cursor.execute(update_sql, t_value)
con.commit()
except Exception as e:
logger.error(e)
con.rollback()
else:
logger.info('update')
cal_query = 'select `可转债涨幅`,`可转债名称` from tb_bond_jisilu'
cursor.execute(cal_query)
cal_result = cursor.fetchall()
# cal_result_list=[]
result_dict = {}
for i in cal_result:
result_dict[i[1]] = i[0]
# cal_result_list.append(i[0])
sort_result = list(
sorted(result_dict.items(), key=lambda x: x[1], reverse=False))
fall_name = sort_result[0][0]
raise_name = sort_result[-1][0]
result_list = list(result_dict.values())
cal_result_np = np.array(result_list)
large_than_zero = len(cal_result_np[cal_result_np >= 0])
# small_than_zero = len(cal_result_np[cal_result_np<0])
total_len = len(cal_result_np)
raise_ratio = round(large_than_zero / total_len * 100, 2)
max_v = cal_result_np.max()
min_v = cal_result_np.min()
mean = round(cal_result_np.mean(), 2)
median = round(np.median(cal_result_np), 2)
ripple_ratio = round(cal_result_np.var(), 2)
title = '{}转债跌大于正股数：{}'.format(current, zz_than_zg_count)
content=f'<p>转债上涨比例：<font color="red">{raise_ratio}</font></p>' \
f'<p>转债跌>正股数: <font color="red">{zz_than_zg_count}</font></p>' \
f'可转债涨幅>=0： <font color="red">{plug_count}</font></p>' \
f'可转债涨幅<0： <font color="red">{minus_count}</font></p>' \
f'涨幅最大值：<font color="red">{max_v}</font> 属于<font color="red">{raise_name}</font></p>' \
f'涨幅最小值：<font color="red">{min_v}</font> 属于<font color="red">{fall_name}</font></p>' \
f'涨幅均值：<font color="red">{mean}</font></p>' \
f'涨幅中位数：<font color="red">{median}</font></p>' \
f'涨幅波动的方差：<font color="red">{ripple_ratio}</font></p>'
try:
send_from_aliyun(title, content, types='html')
except Exception as e:
logger.error(e)
else:
logger.info('发送成功')
logger.info(content)
# 写入数据库
insert_sql = 'insert into tb_bond_analysis (date,转债跌大于正股数量,可转债涨幅大于0,可转债涨幅小于0) values (%s,%s,%s,%s)'
try:
cursor.execute(insert_sql,
(current, zz_than_zg_count, plug_count, minus_count))
con.commit()
except Exception as e:
logger.error(e)
con.rollback()
else:
logger.info('入库成功')
def data_parse(self, bond_list, adjust_no_use):
cell_list = []
for item in bond_list:
cell_list.append(pd.Series(item.get('cell')))
df = pd.DataFrame(cell_list)
if adjust_no_use:
# 类型转换 部分含有%
df['premium_rt'] = df['premium_rt'].map(
lambda x: float(x.replace('%', '')))
df['price'] = df['price'].astype('float64')
df['convert_price'] = df['convert_price'].astype('float64')
df['premium_rt'] = df['premium_rt'].astype('float64')
df['redeem_price'] = df['redeem_price'].astype('float64')
def convert_float(x):
try:
ret_float = float(x)
except:
ret_float = None
return ret_float
def convert_percent(x):
try:
ret = float(x) * 100
except:
ret = None
return ret
def remove_percent(x):
try:
ret = x.replace(r'%', '')
ret = float(ret)
except Exception as e:
ret = None
return ret
df['put_convert_price'] = df['put_convert_price'].map(
convert_float)
df['sprice'] = df['sprice'].map(convert_float)
df['ration'] = df['ration'].map(convert_percent)
df['volume'] = df['volume'].map(convert_float)
df['convert_amt_ratio'] = df['convert_amt_ratio'].map(
remove_percent)
df['ration_rt'] = df['ration_rt'].map(convert_float)
df['increase_rt'] = df['increase_rt'].map(remove_percent)
df['sincrease_rt'] = df['sincrease_rt'].map(remove_percent)
rename_columns = {
'bond_id': '可转债代码',
'bond_nm': '可转债名称',
'price': '可转债价格',
'stock_nm': '正股名称',
'stock_cd': '正股代码',
'sprice': '正股现价',
'sincrease_rt': '正股涨跌幅',
'convert_price': '最新转股价',
'premium_rt': '溢价率',
'increase_rt': '可转债涨幅',
'put_convert_price': '回售触发价',
'convert_dt': '转股起始日',
'short_maturity_dt': '到期时间',
'volume': '成交额(万元)',
'redeem_price': '强赎价格',
'year_left': '剩余时间',
'next_put_dt': '回售起始日',
'rating_cd': '评级',
# 'issue_dt': '发行时间',
# 'redeem_tc': '强制赎回条款',
# 'adjust_tc': '下修条件',
'adjust_tip': '下修提示',
# 'put_tc': '回售',
'adj_cnt': '下调次数',
# 'ration':'已转股比例'
'convert_amt_ratio': '转债剩余占总市值比',
'curr_iss_amt': '剩余规模',
'orig_iss_amt': '发行规模',
'ration_rt': '股东配售率',
'redeem_flag': '发出强赎公告',
'redeem_dt': '强赎日期',
}
df = df.rename(columns=rename_columns)
df = df[list(rename_columns.values())]
df['更新日期'] = datetime.datetime.now().strftime('%Y-%m-%d %H:%M')
df = df.set_index('可转债代码', drop=True)
try:
df.to_sql('tb_jsl_{}'.format(self.date),
self.engine,
if_exists='replace',
dtype={'可转债代码': VARCHAR(10)})
engine2 = DB.get_engine('db_stock', self.remote)
df.to_sql('tb_bond_jisilu'.format(self.date),
engine2,
if_exists='replace',
dtype={'可转债代码': VARCHAR(10)})
except Exception as e:
self.logger.info(e)
send_from_aliyun(title='jisilu可转债', content='写入数据库出错')
def current_data(self, adjust_no_use=True):
post_data = {
'btype': 'C',
'listed': 'Y',
'rp': '50',
'is_search': 'N',
}
js = self.download(self.url, data=post_data)
if not js:
return None
ret = js.json()
bond_list = ret.get('rows', {})
cell_list = []
for item in bond_list:
cell_list.append(pd.Series(item.get('cell')))
df = pd.DataFrame(cell_list)
# 下面的数据暂时不需要
if adjust_no_use:
# del df['active_fl']
# del df['adq_rating']
# del df['list_dt']
# del df['left_put_year']
# del df['owned']
# del df['put_dt']
# del df['real_force_redeem_price']
# del df['redeem_dt']
# del df['apply_cd']
# del df['force_redeem']
# del df['stock_id']
# del df['full_price']
# del df['pre_bond_id']
# del df['ytm_rt']
# del df['ytm_rt_tax']
# del df['repo_cd']
# del df['last_time']
# del df['pinyin']
# del df['put_real_days']
# del df['price_tips']
# del df['btype']
# del df['repo_valid']
# del df['repo_valid_to']
# del df['repo_valid_from']
# del df['repo_discount_rt']
# del df['adjust_tc']
# del df['cpn_desc']
# del df['market']
# del df['stock_net_value']
# 类型转换 部分含有%
df['premium_rt'] = df['premium_rt'].map(lambda x: float(x.replace('%', '')))
df['price'] = df['price'].astype('float64')
df['convert_price'] = df['convert_price'].astype('float64')
df['premium_rt'] = df['premium_rt'].astype('float64')
df['redeem_price'] = df['redeem_price'].astype('float64')
def convert_float(x):
try:
ret_float = float(x)
except:
ret_float = None
return ret_float
def convert_percent(x):
try:
ret = float(x) * 100
except:
ret = None
return ret
def remove_percent(x):
try:
ret = x.replace(r'%', '')
ret = float(ret)
except Exception as e:
ret = None
return ret
df['put_convert_price'] = df['put_convert_price'].map(convert_float)
df['sprice'] = df['sprice'].map(convert_float)
df['ration'] = df['ration'].map(convert_percent)
df['volume'] = df['volume'].map(convert_float)
df['convert_amt_ratio'] = df['convert_amt_ratio'].map(remove_percent)
df['ration_rt'] = df['ration_rt'].map(convert_float)
df['increase_rt']=df['increase_rt'].map(remove_percent)
df['sincrease_rt']=df['sincrease_rt'].map(remove_percent)
rename_columns = {'bond_id': '可转债代码', 'bond_nm': '可转债名称', 'price': '可转债价格', 'stock_nm': '正股名称',
'stock_cd': '正股代码',
'sprice': '正股现价',
'sincrease_rt': '正股涨跌幅',
'convert_price': '最新转股价', 'premium_rt': '溢价率', 'increase_rt': '可转债涨幅',
'put_convert_price': '回售触发价', 'convert_dt': '转股起始日',
'short_maturity_dt': '到期时间', 'volume': '成交额(万元)',
'redeem_price': '强赎价格', 'year_left': '剩余时间',
'next_put_dt': '回售起始日', 'rating_cd': '评级',
# 'issue_dt': '发行时间',
# 'redeem_tc': '强制赎回条款',
# 'adjust_tc': '下修条件',
'adjust_tip': '下修提示',
# 'put_tc': '回售',
'adj_cnt': '下调次数',
# 'ration':'已转股比例'
'convert_amt_ratio': '转债剩余占总市值比',
'curr_iss_amt': '剩余规模', 'orig_iss_amt': '发行规模',
'ration_rt': '股东配售率',
}
df = df.rename(columns=rename_columns)
df = df[list(rename_columns.values())]
df['更新日期'] = datetime.datetime.now().strftime('%Y-%m-%d %H:%M')
# dfx = df[['可转债代码', '可转债名称', '可转债涨幅', '可转债价格', '正股名称', '正股代码',
# '正股涨跌幅', '正股现价', '最新转股价', '溢价率', '评级',
# '转股起始日', '回售起始日', '回售触发价', '剩余时间',
# '更新日期']]
df = df.set_index('可转债代码', drop=True)
try:
df.to_sql('tb_jsl_{}'.format(self.date), self.engine, if_exists='replace', dtype={'可转债代码': VARCHAR(10)})
engine2=DB.get_engine('db_stock',self.remote)
df.to_sql('tb_bond_jisilu'.format(self.date), engine2, if_exists='replace', dtype={'可转债代码': VARCHAR(10)})
except Exception as e:
self.logger.info(e)
send_from_aliyun(title='jisilu可转债',content='写入数据库出错')
