def save_bond_data_info(kezhuanzhai_code='sh110003'):
print(kezhuanzhai_code)
# 查询每个转债的信息，存入数据库
bond_zh_hs_cov_daily_df = ak.bond_zh_hs_cov_daily(symbol=kezhuanzhai_code)
for index, row in bond_zh_hs_cov_daily_df.iterrows():
print(index, row['open'], row['high'], row['low'], row['close'],
row['volume'])
DBUtils.execute(
"insert into kezhuanzhai_data(date,open,high,low,close,volume,bond_code) values "
"('{date}',{open},{high},{low},{close},{volume},{bond_code})".
format(**{"date": index}, **dict(row),
**{"bond_code": kezhuanzhai_code[2:]}))
def save_bond_info():
# 查询 保存所有转债的信息
bond_zh_cov_df = ak.bond_zh_cov()
for index, row in bond_zh_cov_df.iterrows():
DBUtils.execute(
"insert into kezhuanzhai(`债券代码`,`交易场所`,`债券简称`,`申购日期`,`申购代码`,"
"`正股代码`,`正股简称`,`债券面值`,`发行价格`,`转股价`,`中签号发布日`,`中签率`,`上市时间`"
",`备忘录`,`正股价`,`市场类型`,`股权登记日`,`申购上限`,`转股价值`,`债现价`,`转股溢价率`"
",`每股配售额`,`发行规模`) "
"values('{债券代码}','{交易场所}','{债券简称}','{申购日期}','{申购代码}','{正股代码}',"
"'{正股简称}','{债券面值}','{发行价格}','{转股价}','{中签号发布日}','{中签率}','{上市时间}',"
"'{备忘录}','{正股价}','{市场类型}','{股权登记日}','{申购上限}','{转股价值}','{债现价}','"
"{转股溢价率}','{每股配售额}','{发行规模}')".format(**dict(row)))
def save_stock_price_info(stock_price={}):
result = DBUtils.execute(
"insert into stock_day_qfq(date,close,high,low,open,volume,outstanding_share,turnover,stock_code) "
" values ('{date}',{close},{high},{low},{open},{volume},{outstanding_share},{turnover},'{stock_code}')"
.format(**stock_price))
if result is None or result <= 0:
print(stock_price)
def query_kezhuanzhai_info():
# 查询所有转债的信息
kezhuanzhai_infos = DBUtils.execute(
"select * from kezhuanzhai where 上市时间 <'2020-11-30T00:00:00' and "
" 债券代码 not in (select bond_code from kezhuanzhai_data group by bond_code) and length(上市时间)>5 "
)
for data in kezhuanzhai_infos:
save_bond_data_info(str(data["交易场所"][-2:]).lower() + data['债券代码'])
def query_RT_stock():
stock_zh_a_spot_df = ak.stock_zh_a_spot()
for index, stock_data_info in stock_zh_a_spot_df.iterrows():
result = DBUtils.execute(
"INSERT INTO `stock_2020_1218`(`symbol`,`code`,`name`)"
"VALUES('{symbol}','{code}','{name}')".format(
**dict(stock_data_info)))
if result is None or result <= 0:
print(stock_data_info)
def query_stock_codes_save():
stock_infos = DBUtils.execute("select * from stock_2020_1218")
for data in stock_infos:
print(data['symbol'])
stock_zh_a_daily_hfq_df = ak.stock_zh_a_daily(symbol=data['symbol'],
adjust="qfq")
for index, stock_price in stock_zh_a_daily_hfq_df.iterrows():
if stock_price["open"] == 'nan' or stock_price["volume"] == 'nan':
continue
save_stock_price_info({
"date":
index,
**dict(stock_price), "stock_code":
data['symbol']
})
def query_bond_stock_codes_save():
bond_infos = DBUtils.execute(
"select 正股代码, 申购日期, 交易场所 from kezhuanzhai "
"where 申购日期>'2015-01-01' and 申购日期< '2020-11-01' order by 正股代码")
for data in bond_infos:
stock_code = str(data["交易场所"][-2:]).lower() + data['正股代码']
print(data['正股代码'])
stock_zh_a_daily_hfq_df = ak.stock_zh_a_daily(symbol=stock_code,
adjust="qfq")
for index, stock_price in stock_zh_a_daily_hfq_df.iterrows():
if stock_price["open"] == 'nan' or stock_price["volume"] == 'nan':
continue
save_stock_price_info({
"date":
index,
**dict(stock_price), "stock_code":
data['正股代码']
})
