def cb_info():
con = get_engine('db_stock', 'local')
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['强赎标志'] != 'Y']
df1 = df[['可转债代码', '可转债名称', '可转债综合价格', '可转债价格', '溢价率', '评级']].head(20)
df1 = df1.reset_index(drop=True)
send_content = df1.to_html()
# send_content=send_content+'\n\n默认每周一开盘前发送一次，如果有其他需求请回复。\n'
# send_content = send_content
title = '{} 可转债综合价格前10名'.format(
datetime.datetime.now().strftime('%Y-%m-%d'))
conn2 = get_engine('double_low_full')
df1.to_sql(f'double_low_{today}', con=conn2, if_exists='replace')
try:
send_aliyun(title, send_content, QQ_MAIL, types='html')
except Exception as e:
logger.error('报错了')
logger.error(e)
else:
logger.info('发送成功！')
def zt_location(date):
'''
:help: 分析涨停的区域分布
:param date:日期格式 20180404
:return:
'''
engine_zdt = get_engine('db_zdt')
engine_basic = get_engine('db_stock')
df = pd.read_sql(date + 'zdt', engine_zdt, index_col='index')
df_basic = pd.read_sql('tb_basic_info', engine_basic, index_col='index')
result = {}
for code in df['代码'].values:
try:
area = df_basic[df_basic['code'] == code]['area'].values[0]
result.setdefault(area, 0)
result[area] += 1
except Exception as e:
print(e)
new_result = sorted(result.items(), key=lambda x: x[1], reverse=True)
for k, v in new_result:
print(k, v)
def get_current_position(self):
engine = get_engine('db_position')
df = pd.read_sql('tb_position_2019-06-17', con=engine)
# 只关注可转债
df = df[df['证券代码'].map(self.identify_market)]
kzz_stocks = dict(zip(list(df['证券代码'].values),
list(df['证券名称'].values)))
cons = get_mysql_conn('db_stock', 'local')
cursor = cons.cursor()
query_cmd = 'select 正股代码,正股名称,溢价率 from tb_bond_jisilu where 可转债代码=%s'
zg_stocks = {}
kzz_yjl = {}
zg_yjl = {}
for code in kzz_stocks:
cursor.execute(query_cmd, (code))
ret = cursor.fetchone()
if ret:
zg_stocks[ret[0]] = ret[1]
kzz_yjl[code] = ret[2]
zg_yjl[ret[0]] = ret[2]
# 可转债代码
# dict,dict,dict,dict
return (kzz_stocks, zg_stocks, kzz_yjl, zg_yjl)
def plot_percent_distribution(date):
'''
:help:图形显示某一天的涨跌幅分布
:param date:
:return:
'''
total = []
engine = get_engine('db_daily')
df = pd.read_sql(date, con=engine)
df = exclude_kcb(df)
count = len(df[(df['changepercent'] >= -11)
& (df['changepercent'] <= -9.5)])
total.append(count)
for i in range(-9, 9, 1):
count = len(df[(df['changepercent'] >= i * 1.00)
& (df['changepercent'] < ((i + 1)) * 1.00)])
total.append(count)
count = len(df[(df['changepercent'] >= 9)])
total.append(count)
# print(total)
df_figure = pd.Series(total)
plt.figure(figsize=(16, 10))
X = range(-10, 10)
plt.bar(X, height=total, color='y')
for x, y in zip(X, total):
plt.text(x, y + 0.05, y, ha='center', va='bottom')
plt.grid()
plt.xticks(range(-10, 11))
plt.show()
def clone_database():
local_db = get_mysql_conn('db_zdt', local=True)
cur = local_db.cursor()
cur.execute('show tables')
tables = cur.fetchall()
local_engine = get_engine('db_zdt', local=True)
dfs = []
for table in tables:
try:
result = re.findall('(\d+)zdt$', table[0])
if result:
print(table[0])
current = result[0]
# d= datetime.datetime.strptime(current,'%Y%m%d').strftime('%Y-%m-%d')
# print(d)
df = pd.read_sql(table[0], local_engine, index_col='index')
# df['涨停日期']=d
df = df.rename(columns={
'最后一次涨停时间A': '最后一次涨停时间',
'第一次涨停时间A': '第一次涨停时间'
})
try:
print(df.head())
df.to_sql(table[0], local_engine, if_exists='replace')
except Exception as e:
print(e)
except Exception as e:
print(e)
print(table[0])
def basic_info(self,retry=5):
engine = get_engine('db_stock')
# 需要添加异常处理 重试次数
count = 0
while count < retry:
try:
df = ts.get_stock_basics()
except Exception as e:
logger.info(e)
time.sleep(10)
count+=1
continue
else:
if df is not None:
df=df.reset_index()
df['更新日期']=datetime.datetime.now()
df.to_sql('tb_basic_info',engine,if_exists='replace')
logger.info('入库成功')
break
else:
count+=1
time.sleep(10)
continue
def __init__(self):
# path=os.path.join(os.getcwd(),'data')
path = DATA_PATH
if os.path.exists(path) == False:
os.mkdir(path)
os.chdir(path)
self.name = 'simulation.xls'
self.df = pd.read_excel(self.name)
self.df['代码'] = self.df['代码'].map(lambda x: str(x).zfill(6))
self.engine = get_engine('db_stock')
self.engine1 = get_engine('db_daily')
# self.base = pd.read_sql('tb_basic_info', self.engine, index_col='index')
self.money = 10000
self.today = datetime.datetime.now().strftime('%Y-%m-%d')
def store(self):
self.df_today_all = self.gettodaymarket()
# 存储每天 涨幅排行 榜,避免每次读取耗时过长
filename = self.today + '_all_.xls'
# 放在data文件夹下
full_filename = os.path.join(self.path, filename)
if self.df_today_all is not None:
# 保留小数点的后两位数
self.df_today_all['turnoverratio'] = self.df_today_all[
'turnoverratio'].map(lambda x: round(x, 2))
self.df_today_all['per'] = self.df_today_all['per'].map(
lambda x: round(x, 2))
self.df_today_all['pb'] = self.df_today_all['pb'].map(
lambda x: round(x, 2))
try:
self.df_today_all.to_excel(full_filename)
except Exception as e:
logger.error(e)
engine = get_engine('db_daily')
# print(self.df_today_all)
try:
self.df_today_all.to_sql(self.today, engine, if_exists='fail')
except Exception as e:
# print(e)
logger.error(e)
else:
logger.error('today_all df is None')
def today_statistics(today):
'''
:help: 今天涨跌幅的统计分析： 中位数，均值等数据
:param today: 日期 2019-01-01
:return:None
'''
engine = get_engine('db_daily')
df = pd.read_sql(today, engine, index_col='index')
# 去除停牌的 成交量=0
df = df[df['volume'] != 0]
median = round(df['changepercent'].median(), 2)
mean = round(df['changepercent'].mean(), 2)
std = round(df['changepercent'].std(), 2)
p_25 = round(stats.scoreatpercentile(df['changepercent'], 25), 2)
p_50 = round(stats.scoreatpercentile(df['changepercent'], 50), 2)
p_75 = round(stats.scoreatpercentile(df['changepercent'], 75), 2)
print('中位数: {}'.format(median))
print('平均数: {}'.format(mean))
print('方差: {}'.format(std))
print('25%: {}'.format(p_25))
print('50%: {}'.format(p_50))
print('75%: {}'.format(p_75))
def __init__(self):
self.engine = get_engine('db_stock', local=True)
self.conn = get_mysql_conn('db_stock', local='local')
self.info = pd.read_sql('tb_basic_info', con=self.engine, index_col='code')
self.db = pymongo.MongoClient(MONGO_HOST, MONGO_PORT)
self.doc = self.db['db_stock']['break_low_high']
ts.set_token(token)
self.pro = ts.pro_api()
self.count = 0
def save_position(self):
self.engine = get_engine('db_position', True)
df= self.get_position_df()
# print(df)
try:
df.to_sql('tb_position_{}'.format(self.today),con=self.engine,if_exists='replace')
except Exception as e:
self.logger.error(e)
def main():
global jsl_df
jsl_df_ = remain_share(jsl_df)
jsl_df_ = double_low(jsl_df_)
zg_list = list(jsl_df_['正股代码'].values)
zg_df = basic_df[basic_df['code'].isin(zg_list)]
zg_df = market_share(zg_df, price_df)
con = get_engine('double_low_bond', 'local')
zg_df.to_sql('double_low_{}'.format(today), con=con, if_exists='replace')
def yesterday_zt_location(date='20180404'):
engine_zdt = get_engine('db_zdt')
engine_basic = get_engine('db_stock')
df = pd.read_sql(date + 'zdt', engine_zdt, index_col='index')
df_basic = pd.read_sql('basic_info', engine_basic, index_col='index')
result = {}
for code in df['代码'].values:
try:
area = df_basic[df_basic['code'] == code]['area'].values[0]
result.setdefault(area, 0)
result[area] += 1
except Exception as e:
print(e)
new_result = sorted(result.items(), key=lambda x: x[1], reverse=True)
for k, v in new_result:
print(k, v)
def read_data_source(today):
engine = get_engine('db_jisilu','local')
try:
df = pd.read_sql('tb_jsl_{}'.format(today),con=engine)
except Exception as e:
logger.error(e)
sendmail('代码{}出错\n读取表tb_jsl_{}失败'.format('bond_daily',today),'{}'.format(today))
return None
else:
return df
def cb_stock_year():
'''
上一年可转债正股的涨跌幅排名
:return:
'''
engine = get_engine('db_stock')
df_cb = pd.read_sql('tb_bond_jisilu', engine)
filename = '2019_all_price_change_ignore_new_stock.xls'
df_all = pd.read_excel(filename, encoding='gbk')
zg_codes = list(df_cb['正股代码'].values)
df = df_all[df_all['code'].isin(zg_codes)]
df.to_excel('2019_cb_zg.xls', encoding='gbk')
def merge_database():
local_db = get_mysql_conn('db_zdt', local=True)
cur = local_db.cursor()
cur.execute('show tables')
tables = cur.fetchall()
local_engine = get_engine('db_zdt', local=True)
dfs = []
for table in tables:
try:
result = re.findall('(\d+)zdt$', table[0])
if len(result) > 0:
print(table[0])
df = pd.read_sql(table[0], local_engine, index_col='index')
dfs.append(df)
except Exception as e:
print(e)
print(table[0])
dfx = pd.concat(dfs)
print(dfx.head())
# ali_engine = get_engine(None,local=False)
local_engine_stock = get_engine('db_stock', local=True)
dfx.to_sql('tb_zdt', local_engine_stock, if_exists='replace')
def store_new(self):
self.df_today_all = self.gettodaymarket()
filename = self.today + '_all_.xls'
full_filename = os.path.join(self.path, filename)
if not os.path.exists(full_filename):
if self.df_today_all is not None:
try:
self.save_to_excel(self.df_today_all, full_filename)
except Exception as e:
print(e)
engine = get_engine('db_daily')
try:
self.df_today_all.to_sql(self.today, engine)
except Exception as e:
print(e)
pass
def __init__(self):
# self.df = self.get_tick()
today = datetime.datetime.now().strftime('%Y-%m-%d')
# today = '2019-10-18'
if ts.is_holiday(today):
logger.info('{}假期 >>>>>'.format(today))
exit()
self.db_stock_engine = get_engine('db_stock', 'local')
self.jisilu_df = self.get_code()
self.code_name_dict = dict(
zip(list(self.jisilu_df['可转债代码'].values),
list(self.jisilu_df['可转债名称'].values)))
self.db = pymongo.MongoClient(config.mongodb_host, config.mongodb_port)
def hot_industry():
engine = get_engine('db_stock')
basic_df = pd.read_sql('tb_basic_info', engine, index_col='index')
industry_dict = {}
for name, group in basic_df.groupby('industry'):
# print(name, group)
industry_dict[name] = group['code'].values.tolist()
result = {}
for k, v in industry_dict.items():
mean = 0.0
for i in v:
try:
percent = daily_df[daily_df['code'] ==
i]['changepercent'].values[0]
name = daily_df[daily_df['code'] == i]['name'].values[0]
except:
percent = 0
name = ''
# print(i,name,percent)
mean = mean + float(percent)
m = round(mean / len(v), 2)
# print('{} mean : {}'.format(k,m))
result[k] = m
all_result = sorted(result.items(), key=lambda x: x[1], reverse=True)
kind = '元器件'
select_detail = {}
for code in industry_dict.get(kind):
try:
percent = daily_df[daily_df['code'] ==
code]['changepercent'].values[0]
except:
percent = 0
try:
name = daily_df[daily_df['code'] == code]['name'].values[0]
except:
name = ''
select_detail[name] = float(percent)
print('\n\n{} detail\n'.format(kind))
select_detail = sorted(select_detail.items(),
key=lambda x: x[1],
reverse=True)
for n, p in select_detail:
print(n, p)
def today_tendency(today):
engine = get_engine('db_daily')
today = datetime.datetime.strptime(today, '%Y%m%d').strftime('%Y-%m-%d')
df = pd.read_sql(today, engine, index_col='index')
# 去除停牌的 成交量=0
df = df[df['volume'] != 0]
median = df['changepercent'].median()
mean = df['changepercent'].mean()
std = df['changepercent'].std()
p_25 = stats.scoreatpercentile(df['changepercent'], 25)
p_50 = stats.scoreatpercentile(df['changepercent'], 50)
p_75 = stats.scoreatpercentile(df['changepercent'], 75)
print('中位数: {}'.format(median))
print('平均数: {}'.format(mean))
print('方差: {}'.format(std))
print('25%: {}'.format(p_25))
print('50%: {}'.format(p_50))
print('75%: {}'.format(p_75))
def monitor():
engine = get_engine('db_zdt')
table = '20180409zdt'
api = ts.get_apis()
df = pd.read_sql(table, engine, index_col='index')
price_list = []
percent_list = []
amplitude_list = []
start = datetime.datetime.now()
for i in df['代码'].values:
try:
curr = ts.quotes(i, conn=api)
last_close = curr['last_close'].values[0]
curr_price = curr['price'].values[0]
amplitude = round(
((curr['high'].values[0] - curr['low'].values[0]) * 1.00 /
last_close) * 100, 2)
# if last_close>=curr_price:
# print(i,)
# print(df[df['代码']==i]['名称'].values[0],)
# print( percent)
except Exception as e:
print('this point')
print(e)
api = ts.get_apis()
curr_price = 0
if last_close == 0:
percent = np.nan
percent = round((curr_price - last_close) * 100.00 / last_close, 2)
percent_list.append(percent)
price_list.append(curr_price)
amplitude_list.append(amplitude)
df['今日价格'] = price_list
df['今日涨幅'] = percent_list
df['今日振幅'] = amplitude_list
df['更新时间'] = datetime.datetime.now().strftime('%Y %m %d %H:%M%S')
end = datetime.datetime.now()
print('time use {}'.format(end - start))
df.to_sql(table + 'monitor', engine, if_exists='replace')
ts.close_apis(api)
def save_to_dataframe(self, data, indexx, choice, post_fix):
engine = get_engine('db_zdt')
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)
def main():
obj = Filter_Stock()
now =datetime.datetime.now()
today = now.strftime("%Y%m%d")
tb_name = today+'zdt'
end = str(now.year)+'-'+str(now.month - 1)
df = obj.get_new_stock('2015',end)
code_list =df['code'].values
engine = get_engine('db_zdt')
zt_df = pd.read_sql(tb_name,engine,index_col='index')
zt_df['涨停强度']=map(lambda x:round(x,0),zt_df['涨停强度'])
ret_df = zt_df[zt_df['代码'].isin(code_list)]
if not ret_df.empty:
tb_name_save = today+'_cx'
excel_name = today+'_cx.xls'
ret_df.to_excel(excel_name,encoding='gbk')
ret_df.to_sql(tb_name_save,engine)
s= ret_df[['代码','名称','涨停强度','打开次数','第一次涨停时间','最后一次涨停时间']].to_string()
sendmail(s,today+'次新涨停')
def get_hist_data(code, name, start_data):
try:
# start_data = datetime.datetime.strptime(str(start_data), '%Y%m%d').strftime('%Y-%m-%d')
df = ts.bar(code, conn=conn, start_date=start_data, adj='qfq')
except Exception as e:
print(e)
return
hist_con = get_engine('history')
df.insert(1, 'name', name)
df = df.reset_index()
#print(df)
df2 = pd.read_sql_table(code, hist_con, index_col='index')
try:
new_df = pd.concat([df, df2])
new_df = new_df.reset_index(drop=True)
new_df.to_sql(code, engine, if_exists='replace')
except Exception as e:
print(e)
return
def save_industry():
try:
doc.drop()
except Exception as e:
print(e)
engine = get_engine('db_stock')
basic_df = pd.read_sql('tb_basic_info', engine, index_col='index')
# print(basic_df)
for name, group in basic_df.groupby('industry'):
# print(name, group)
d = dict()
d['板块名称'] = name
d['代码'] = group['code'].values.tolist()
d['更新日期'] = today
try:
# pass
doc.insert(d)
except Exception as e:
print(e)
def _xiayingxian(self, row, ratio):
'''
下影线的逻辑 ratio 下影线的长度比例，数字越大，下影线越长
row: series类型
'''
open_p = float(row['open'])
# print(open_p)
closed = float(row['close'])
# print(closed)
low = float(row['low'])
# print(low)
high = float(row['high'])
p = min(closed, open_p)
try:
diff = (p - low) * 1.00 / (high - low)
diff = round(diff, 3)
except ZeroDivisionError:
diff = 0
if diff > ratio:
xiayinxian_engine = get_engine('db_selection')
date, code, name, ocupy_ration, standards = row['datetime'], row[
'code'], row['name'], diff, ratio
df = pd.DataFrame({
'datetime': [date],
'code': [code],
'name': [name],
'ocupy_ration': [ocupy_ration],
'standards': [standards]
})
try:
df1 = pd.read_sql_table('xiayingxian',
xiayinxian_engine,
index_col='index')
df = pd.concat([df1, df])
except Exception as e:
print(e)
#return None
df = df.reset_index(drop=True)
df.to_sql('xiayingxian', xiayinxian_engine, if_exists='replace')
return row
def plot_yesterday_zt(type_name='zrzt',
current=datetime.datetime.now().strftime('%Y%m%d')):
engine = get_engine('db_zdt')
table_name = type_name
table = '{}{}'.format(current, table_name)
try:
df = pd.read_sql(table, engine)
except Exception as e:
logger.error('table_name >>> {}{}'.format(current, table_name))
logger.error(e)
return
for i in range(len(df)):
code = df.iloc[i]['代码']
name = df.iloc[i]['名称']
plot_stock_line(api,
code,
name,
table_name=table_name,
current=current,
start='2018-07-01',
save=True)
__author__ = 'Rocky' ''' http://30daydo.com Contact: [email protected] ''' import re import time import datetime import requests import pandas as pd from settings import get_engine, llogger, is_holiday, get_mysql_conn import six from send_mail import sender_139 from sqlalchemy import VARCHAR import os engine = get_engine('db_jisilu') logger = llogger('log/' + 'jisilu') # 爬取集思录 可转债的数据 class Jisilu(object): def __init__(self): # self.check_holiday() # py2 if six.PY2: self.timestamp = long(time.time() * 1000) else: self.timestamp = int(time.time() * 1000) self.headers = {
import pandas as pd
import talib
import tushare as ts
import matplotlib as mpl
from mpl_finance import candlestick2_ochl, volume_overlay
import matplotlib.pyplot as plt
from settings import get_engine
mpl.rcParams['font.sans-serif'] = ['simhei']
mpl.rcParams['axes.unicode_minus'] = False
from settings import llogger
filename = os.path.basename(__file__)
logger = llogger('log/' + filename)
engine = get_engine('db_stock', local=True)
base_info = pd.read_sql('tb_basic_info', engine, index_col='index')
def plot_stock_line(api,
code,
name,
table_name,
current,
start='2017-10-01',
save=False):
title = '{} {} {} {}'.format(current, code, name, table_name)
title = title.replace('*', '_')
if os.path.exists(title + '.png'):
return
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(
datetime.datetime.now().strftime('%Y-%m-%d')),
engine,
if_exists='replace',
dtype={'可转债代码': VARCHAR(10)})
engine2 = get_engine('db_stock')
df.to_sql('tb_bond_jisilu'.format(
datetime.datetime.now().strftime('%Y-%m-%d')),
engine2,
if_exists='replace',
dtype={'可转债代码': VARCHAR(10)})
except Exception as e:
logger.info(e)
