def avg_yjl_history():
con=get_mysql_conn('db_jisilu','local')
con2=get_mysql_conn('db_stock','local')
cursor=con.cursor()
cursor2=con2.cursor()
start = datetime.datetime(2019,2,25)
update_sql = 'insert into tb_bond_avg_yjl (Date,溢价率均值,溢价率最大值,溢价率最小值,溢价率中位数,转债数目) values (%s,%s,%s,%s,%s,%s)'
while 1:
if start>=datetime.datetime.now():
break
date_str = start.strftime('%Y-%m-%d')
try:
search_sql = 'select `溢价率` from `tb_jsl_{}`'.format(date_str)
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=(date_str,float(mean),float(max_value),float(min_value),float(median),count)
# print(t_value)
try:
cursor2.execute(update_sql,t_value)
con2.commit()
except Exception as e:
logger.error(e)
con2.rollback()
else:
logger.info('update')
finally:
start=start+datetime.timedelta(days=1)
def main():
filename = os.path.join(DATA_PATH, 'blacklist.csv')
# 本地更新
logger.info('update local')
db_name = 'db_stock'
conn = get_mysql_conn(db_name, local='local')
create_tb(conn)
update_data(filename, conn)
# 远程更新
# db_name = 'db_stock'
logger.info('update remote')
remote_conn = get_mysql_conn('', local='ali')
create_tb(remote_conn)
update_data(filename, remote_conn)
def convert_name_db():
conn = get_mysql_conn('db_stock', 'local')
cursor = conn.cursor()
query_cmd = '''
select * from tb_bond_jisilu
'''
cursor.execute(query_cmd)
ret = cursor.fetchall()
for item in ret:
name = item[1]
code = item[0]
zg_name = item[3]
zg_code = item[4]
grade = item[17]
get_area = '''
select area from tb_basic_info where code = %s
'''
cursor.execute(get_area,zg_code)
result= cursor.fetchone()[0]
insert_cmd = '''
insert into tb_bond_kind_info (可转债名称,可转债代码,正股名称,正股代码,评级,地区,更新时间) values(%s,%s,%s,%s,%s,%s,%s)
'''
cursor.execute(insert_cmd,(name,code,zg_name,zg_code,grade,result,datetime.datetime.now().strftime('%Y-%m-%d')))
conn.commit()
def data_sync(self):
conn = get_mysql_conn('db_stock', 'local')
cursor = conn.cursor()
# 最新的数据库
select_cmd = '''select * from tb_delivery_gj'''
cursor.execute(select_cmd)
ret = list(cursor.fetchall())
print('new db ', len(ret))
# 旧的数据库
select_cmd2 = '''select * from tb_delivery_gj_django'''
cursor.execute(select_cmd2)
ret2 = list(cursor.fetchall())
print('old db ', len(ret2))
ret_copy = ret.copy()
for item in ret:
# print(item)
for item2 in ret2:
if item[0] == item2[0] and item[1] == item2[1] and item[2] == item2[2] and item[4] == item2[4] and item[
5] == item2[5]:
try:
ret_copy.remove(item)
except Exception as e:
# print(e)
# print()
pass
# print(ret_copy)
for i in ret_copy:
# print(i)
update_sql = '''
insert into tb_delivery_gj_django (成交日期,证券代码,证券名称,操作,成交数量,成交均价,成交金额,)
'''
print('diff len ', len(ret_copy))
def get_code(name):
conn = get_mysql_conn('db_stock')
cur = conn.cursor()
cmd = 'select code from tb_basic_info where name=\'{}\''.format(name)
cur.execute(cmd)
ret = cur.fetchone()
return ret[0]
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 find_lower_bond():
# 和均值的比较因子，正常为1
percent = 1
con = get_mysql_conn('db_stock','local')
cursor = con.cursor()
query_avg_sql = '''
SELECT `评级`,count(*) as n,round(AVG(`最小值`),2) as `均值` FROM `tb_bond_kind_info` GROUP BY `评级`
'''
cursor.execute(query_avg_sql)
ret = cursor.fetchall()
d= {}
for item in ret:
d[item[0]]=item[2]
print(d)
query_all_bond_sql = '''
select `可转债代码`,`评级`,`可转债价格`,`可转债名称` from tb_bond_jisilu
'''
cursor.execute(query_all_bond_sql)
total_bond_ret = cursor.fetchall()
for item in total_bond_ret:
if item[2] <= percent * d.get(item[1]):
ration = round((item[2]-d.get(item[1]))/d.get(item[1])*100,2)
print(f'{item[3]}:评级{item[1]},当前价格：{item[2]},低于比例{ration}')
print('done')
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 history_data():
url = 'https://www.jisilu.cn/data/cbnew/cb_index/'
headers = {
'Host':
'www.jisilu.cn',
'Referer':
'https://www.jisilu.cn/data/cbnew/',
'User-Agent':
'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/67.0.3396.99 Safari/537.36'
}
r = requests.get(url=url, headers=headers)
r.encoding = 'utf8'
# print(r.text)
content = r.text
date = re.search('var __date = (.*?);', content, re.S).group(1)
data = re.search('var __data = (.*?);', content, re.S).group(1)
date_ = eval(date)
data_ = eval(data)
price = data_.get('price')
volume = data_.get('volume')
amount = data_.get('amount')
count = data_.get('count') # 数量
increase_val = data_.get('increase_val') # 涨跌额
increase_rt = data_.get('increase_rt') # 涨跌幅
# df = pd.DataFrame({'日期':date_,'指数':price,'成交额': amount,'涨跌':increase_val,'涨跌额':increase_rt,'转债数目':increase_rt})
con = get_mysql_conn('db_stock', 'local')
cursor = con.cursor()
create_sql = '''
create table if not exists tb_cb_index (`日期` date ,`指数` float ,`成交额(亿元)` float,`涨跌` float ,`涨跌额` float ,`转债数目` float ,`剩余规模` float )
'''
try:
cursor.execute(create_sql)
except Exception as e:
print(e)
con.rollback()
else:
con.commit()
update_data_sql = '''
insert into `tb_cb_index` (`日期`,`指数`,`成交额(亿元)`,`涨跌`,`涨跌额`,`转债数目`,`剩余规模`) values (%s,%s,%s,%s,%s,%s,%s);
'''
for index, item in enumerate(date_):
value_tuple = (item, price[index], volume[index], increase_val[index],
increase_rt[index], count[index], amount[index])
try:
cursor.execute(update_data_sql, value_tuple)
except Exception as e:
print(value_tuple)
print(e)
con.rollback()
else:
con.commit()
def update_daily():
'''
每天更新行情
:return:
'''
# 运行静态方法
SaveData.daily_market()
time.sleep(20)
daily_conn = get_mysql_conn('daily')
cursor = daily_conn.cursor()
today = datetime.datetime.now().strftime('%Y-%m-%d')
cmd = 'select * from `{}`;'.format(today)
cursor.execute(cmd)
#today = '2017-11-17'
#daily_df = pd.read_sql_table(today,daily_conn,index_col='index')
days_info = cursor.fetchall()
for i in days_info:
code = i[1]
name = i[2]
close = i[4]
opens = i[5]
high = i[6]
low = i[7]
vol = i[9]
amount = i[11]
try:
history_conn = get_mysql_conn('history')
history_cur = history_conn.cursor()
history_cur.execute('select count(*) from `{}`;'.format(code))
except Exception as e:
print(e)
continue
l = history_cur.fetchone()
df = pd.DataFrame(columns=[
'datetime', 'code', 'name', 'open', 'close', 'high', 'low', 'vol',
'amount'
])
df.loc[l] = [today, code, name, opens, close, high, low, vol, amount]
try:
df.to_sql(code, engine, if_exists='append')
print(code)
except Exception as e:
print(df)
print(e)
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 dongbei(code):
dongbei_area = ['黑龙江', '吉林', '辽宁']
conn = get_mysql_conn('db_stock')
cur = conn.cursor()
cmd = 'select area from tb_basic_info where code=\'{}\''.format(code)
cur.execute(cmd)
ret = cur.fetchone()
if ret[0] in dongbei_area:
return True
else:
return False
def check_blacklist(code):
conn = get_mysql_conn('db_stock')
cur = conn.cursor()
cmd = 'select * from tb_blacklist where code=\'{}\''.format(code)
cur.execute(cmd)
ret = cur.fetchone()
if len(ret) == 0:
return False
else:
print(ret[3])
return True
def find_zz_zg_diff_history():
con = get_mysql_conn('db_jisilu', 'local')
cursor = con.cursor()
current = datetime.date.today()
days=60
tb_name ='tb_jsl_{}'
num_list =[]
for i in range(days):
start = (current+datetime.timedelta(days=-1*i)).strftime("%Y-%m-%d")
name = tb_name.format(start)
query_cmd = 'select count(*) from `{}` WHERE `正股涨跌幅`>=`可转债涨幅` and `正股涨跌幅`<=0'.format(name)
try:
cursor.execute(query_cmd)
except Exception as e:
logger.error(e)
con.rollback()
continue
else:
get_count = cursor.fetchone()
num = get_count[0]
num_list.append((start,num))
# print(num_list)
# print(sorted(num_list,key=lambda x:x[1],reverse=True))
con.close()
con2 = get_mysql_conn('db_stock','local')
cur=con2.cursor()
insert_sql = 'insert into `tb_zz_more_drop_zg` (date,number) values (%s,%s)'
try:
cur.executemany(insert_sql,(num_list))
con2.commit()
except Exception as e:
logger.error(e)
con2.rollback()
else:
logger.info('入库成功')
def get_close_price(self):
conn = get_mysql_conn('db_jisilu','local')
cursor = conn.cursor()
cmd = 'select 可转债代码,可转债价格 from `tb_jsl_{}`'.format(self.yesterday)
try:
cursor.execute(cmd)
result = cursor.fetchall()
except Exception as e:
return None
else:
d={}
for item in result:
d[item[0]]=item[1]
return d
def main():
con = get_mysql_conn('db_stock', 'local')
cursor = con.cursor()
code_list = get_code()
update_sql = '''
update tb_bond_kind_info set `最小值` = %s, `最小值-发生时间` = %s where `可转债代码` = %s
'''
for i in code_list:
min_close, min_date = get_history_data(i)
try:
cursor.execute(update_sql, (float(min_close), min_date, i))
except Exception as e:
print(e)
con.rollback()
con.commit()
con.close()
def get_today_index():
url = 'https://www.jisilu.cn/data/cbnew/cb_index_quote/'
headers = {
'Host':
'www.jisilu.cn',
'X-Requested-With':
'XMLHttpRequest',
'Referer':
'https://www.jisilu.cn/data/cbnew/',
'User-Agent':
'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/67.0.3396.99 Safari/537.36'
}
r = requests.get(url=url, headers=headers)
# print(r.text)
data_ = r.json()
price = data_.get('cur_index')
volume = data_.get('volume')
amount = data_.get('amount')
count = data_.get('count') # 数量
increase_val = data_.get('cur_increase_val') # 涨跌额
increase_rt = data_.get('cur_increase_rt') # 涨跌幅
con = get_mysql_conn('db_stock', 'local')
cursor = con.cursor()
current = datetime.datetime.now().strftime('%Y-%m-%d')
update_data_sql = '''
insert into `tb_cb_index` (`日期`,`指数`,`成交额(亿元)`,`涨跌`,`涨跌额`,`转债数目`,`剩余规模`) values (%s,%s,%s,%s,%s,%s,%s);
'''
value_tuple = (current, price, volume, increase_val, increase_rt, count,
amount)
print(value_tuple)
try:
cursor.execute(update_data_sql, value_tuple)
except Exception as e:
print(value_tuple)
print(e)
con.rollback()
else:
con.commit()
logger.info('爬取成功并入库')
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 = get_mysql_conn('db_stock', 'local')
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()
def update_sold(self):
cur = self.conn.cursor()
tb_name = 'tb_sold_stock'
cur.execute('select * from {}'.format(tb_name))
content = cur.fetchall()
db_daily = get_mysql_conn('db_daily')
db_cursor = db_daily.cursor()
stock_table = datetime.datetime.now().strftime('%Y-%m-%d')
# stock_table = '2018-05-02'
for i in content:
cmd = 'select `trade` from `{}` where `code`=\'{}\''.format(
stock_table, i[0])
print(cmd)
db_cursor.execute(cmd)
ret = db_cursor.fetchone()
sold_price = i[3]
percentange = round(
float(ret[0] - sold_price) / sold_price * 100, 2)
update_cmd = 'update `{}` set `当前价`={} ,`卖出后涨跌幅`= {} where `代码`=\'{}\''.format(
tb_name, ret[0], percentange, i[0])
print(update_cmd)
cur.execute(update_cmd)
self.conn.commit()
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')
# @Time : 2020/1/14 0:05
# @File : fd_money.py
# 涨停封单数据
from settings import get_mysql_conn
import datetime
import tushare as ts
import matplotlib.pyplot as plt
conn = get_mysql_conn('db_zdt', 'local')
cursor = conn.cursor()
diff_day = 20
dataset = []
date = []
for d in range(diff_day):
day = datetime.datetime.now() + datetime.timedelta(days=-1 * d)
# if ts.is_holiday(day.strftime('%Y-%m-%d')):
# continue
sql = 'select 封单金额 as total_money from `{}zdt`'.format(
day.strftime('%Y%m%d'))
# sql = '''select sum(封单金额) as total_money from `20200113zdt`'''
# print(sql)
try:
cursor.execute(sql)
ret = cursor.fetchone()
# print(ret[0])
dataset.append(int(ret[0] / 10000))
date.append(day.strftime('%Y%m%d'))
except Exception as e:
pass
def years_gj_each_month_day(self,filename):
# filename = 'GJ_2019-05-11-05-16.csv'
try:
# 根据不同的格式选用不同的函数
# t=pd.read_table(filename,encoding='gbk',dtype={'证券代码':np.str})
t = pd.read_csv(filename, encoding='gbk', dtype={'证券代码': np.str})
# t = pd.read_excel(filename, encoding='gbk',dtype={'证券代码': np.str})
except Exception as e:
print(e)
# continue
# fee=t['手续费'].sum()+t['印花税'].sum()+t['其他杂费'].sum()
else:
# df_list.append(t)
# result.append(fee)
df = t
# df = pd.concat(df_list)
df = df.reset_index(drop='True')
# df['成交时间'] = df['成交时间'].map(lambda x: x.zfill(8))
df['成交日期'] = df['成交日期'].astype(np.str) + df['成交时间']
# for i in df['成交日期'].values:
# try:
# x = datetime.datetime.strptime(
# i, "%Y%m%d%H:%M:%S").strftime('%Y-%m-%d %H:%M:%S')
# except Exception as e:
# print(e)
df['成交日期'] = df['成交日期'].map(lambda x: datetime.datetime.strptime(
x, "%Y%m%d%H:%M:%S").strftime('%Y-%m-%d %H:%M:%S'))
try:
df['成交日期'] = pd.to_datetime(df['成交日期'])
except Exception as e:
print(e)
# df=df[df['摘要']!='申购配号']
# df=df[df['摘要']!='质押回购拆出']
# df=df[df['摘要']!='拆出质押购回']
# print(df.info())
# print(df)
# print(df['2017-01'])
# del df['合同编号']
# del df['备注']
del df['股东帐户']
del df['成交时间']
# del df['结算汇率']
# del df['Unnamed: 17']
df=df.fillna(0)
df=df[(df['操作']!='申购配号') & (df['操作']!='拆出质押购回') & (df['操作']!='质押回购拆出')]
df = df.sort_values(by='成交日期', ascending=False)
conn = get_mysql_conn('db_stock', 'local')
cursor = conn.cursor()
insert_cmd = '''
insert into tb_delivery_gj_django (成交日期,证券代码,证券名称,操作,成交数量,成交均价,成交金额,余额,发生金额,手续费,印花税,过户费,本次金额,其他费用,交易市场) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'''
check_dup = '''
select * from tb_delivery_gj_django where 成交日期=%s and 证券代码=%s and 操作=%s and 成交数量=%s and 余额=%s
'''
for index, row in df.iterrows():
date=row['成交日期']
date=date.to_pydatetime()
# print(type(date))
# print(date)
cursor.execute(check_dup, (date, row['证券代码'], row['操作'], row['成交数量'], row['余额']))
if cursor.fetchall():
print('有重复数据，忽略')
else:
cursor.execute(insert_cmd, (
date, row['证券代码'], row['证券名称'], row['操作'], row['成交数量'], row['成交均价'], row['成交金额'], row['余额'],
row['发生金额'], row['手续费'], row['印花税'], row['过户费'], row['本次金额'], row['其他费用'], row['交易市场']))
conn.commit()
conn.close()
def getinfo(days=-30):
last_day = datetime.datetime.now() + datetime.timedelta(days=days)
url = "http://app.cnstock.com/api/waterfall?callback=jQuery19107348148582372209_1557710326005&colunm=qmt-tjd_ggkx&page={}&num=20&showstock=0"
page = 1
temp_time = time.strftime("[%Y-%m-%d]-[%H-%M]", time.localtime())
store_filename = "StockNews-%s.log" % temp_time
f_open = codecs.open(store_filename, 'w', 'utf-8')
db_name = 'db_stock'
conn = get_mysql_conn(db_name, local='local')
cur = conn.cursor()
run_flag = True
while run_flag:
headers = {'Referer': 'http://ggjd.cnstock.com/company/scp_ggjd/tjd_ggkx',
'User-Agent': 'Mozilla/5.0 (Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/74.0.3729.131 Safari/537.36', }
retry = 3
response = None
for _ in range(retry):
try:
response = requests.get(url=url.format(page), headers=headers)
response.encoding = 'utf8'
except Exception as e:
if hasattr(e, 'code'):
logger.info("error code %d" % e.code)
elif hasattr(e, 'reason'):
logger.info("error reason %s " % e.reason)
time.sleep(5)
else:
if response.status_code == 200:
break
try:
text = response.text.encode('utf8').decode('unicode_escape')
js = re.search('jQuery19107348148582372209_1557710326005\((.*?)\)$', text, re.S).group(1)
js = re.sub('\r\n', '', js)
js_data = json.loads(js)
except Exception as e:
logger.error(e)
return None
content = js_data.get('data', {}).get('item', {})
if content is None:
continue
for item in content:
title = item.get('title')
if '晚间重要公告集锦' in title or '停复牌汇总' in title:
continue
link = item.get('link')
link = link.replace('\\', '')
pubdate_t = item.get('time')
pubdate_dtype = datetime.datetime.strptime(pubdate_t, '%Y-%m-%d %H:%M:%S')
if pubdate_dtype < last_day:
run_flag = False
keyword = item.get('keyword')
if keyword:
keyword = ' '.join(keyword)
sub_content = None
for i in range(2):
try:
sub_content = requests.get(url=link, headers=headers)
except Exception as e:
logger.error(e)
continue
# 重试
else:
if sub_content.status_code == 200:
break
root = Selector(text=sub_content.text)
detail_content = root.xpath('//div[@id="qmt_content_div"]')[0].xpath('string(.)').extract_first()
if detail_content:
detail_content = detail_content.strip()
temp_tuple = (pubdate_dtype, title, link, detail_content, keyword)
insert_sql = 'insert into tb_cnstock (Date,Title,URL,Content,keyword) values (%s,%s,%s,%s,%s)'
# es
try:
pubdate_dtype=pubdate_dtype.strftime("%Y-%m-%d"'T'"%H:%M:%S")
body = {'Title': title, 'ULR': link, 'keyword': keyword, 'content': detail_content, 'Date': pubdate_dtype}
es.index(index='cnstock',doc_type='doc',body=body)
except Exception as e:
logger.error(e)
# mysql
try:
cur.execute(insert_sql, temp_tuple)
conn.commit()
except Exception as e:
logger.error(e)
conn.rollback()
file_content = '{} ---- {}\n{}\n\n'.format(pubdate_t, title, link)
f_open.write(file_content)
page += 1
f_open.close()
def __init__(self):
self.conn = get_mysql_conn('db_stock', local=True)
self.cur = self.conn.cursor()
self.table_name = 'tb_profit'
self.today = datetime.datetime.now().strftime('%Y-%m-%d')
def history_data(self):
conn = get_mysql_conn('db_stock', local='local')
cursor = conn.cursor()
check_table = '''
create table if not exists tb_bond_release (
可转债代码 varchar(10),
可转债名称 varchar(10),
集思录建议 varchar(500),
包销比例 float(6,3),
中签率 float(6,3),
上市日期 varchar(20),
申购户数（万户） int,
单账户中签（顶格） float(6,3),
股东配售率 float(6,3),
评级 varchar(8),
现价比转股价 float(6,3),
抓取时间 datetime
);
'''
try:
cursor.execute(check_table)
conn.commit()
except Exception as e:
logger.error('创建数据库失败{}'.format(e))
post_data = {
'cb_type_Y': 'Y',
'progress': '',
'rp': 22,
}
r = self.download(url=self.pre_release_url, data=post_data)
# print(r.json())
js_data = r.json()
rows = js_data.get('rows')
for items in rows:
item = items.get('cell')
single_draw = item.get('single_draw')
if single_draw:
jsl_advise_text = item.get('jsl_advise_text') # 集思录建议
underwriter_rt = self.convert_float(
item.get('underwriter_rt')) # 包销比例
bond_nm = item.get('bond_nm')
lucky_draw_rt = self.convert_float(
item.get('lucky_draw_rt')) # 中签率
if lucky_draw_rt:
lucky_draw_rt = lucky_draw_rt * 100
list_date = item.get('list_date')
valid_apply = self.convert_float(
item.get('valid_apply')) # 申购户数（万户）
single_draw = self.convert_float(
item.get('single_draw')) # 单账户中签（顶格）
ration_rt = self.convert_float(item.get('ration_rt')) # 股东配售率
rating_cd = item.get('rating_cd') # 评级
bond_id = item.get('bond_id') # 可转债代码
pma_rt = self.convert_float(item.get('pma_rt')) # 现价比转股价
update_time = datetime.datetime.now()
check_exist = '''
select * from tb_bond_release where 可转债代码=%s
'''
try:
cursor.execute(check_exist, (bond_id))
except Exception as e:
logger.error('查询重复数据错误 {}'.format(e))
else:
ret = cursor.fetchall()
# 存在则更新
if ret:
check_update = '''
select * from tb_bond_release where 可转债代码=%s and 包销比例 is null
'''
try:
cursor.execute(check_update, (bond_id))
except Exception as e:
logger.error('查询重复数据错误 {}'.format(e))
else:
ret = cursor.fetchall()
if not ret:
continue
# 更新
else:
update_sql = '''
update tb_bond_release set 包销比例=%s , 上市日期=%s ,抓取时间=%s where 可转债代码 = %s
'''
try:
update_v = (underwriter_rt, list_date,
update_time, bond_id)
cursor.execute(update_sql, update_v)
conn.commit()
except Exception as e:
logger.error(e)
# 插入
else:
insert_sql = '''
insert into tb_bond_release (可转债代码 , 可转债名称 , 集思录建议 , 包销比例 , 中签率 ,上市日期 ,申购户数（万户）, 单账户中签（顶格）, 股东配售率 ,评级 , 现价比转股价,抓取时间)
values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
'''
v = (bond_id, bond_nm, jsl_advise_text, underwriter_rt,
lucky_draw_rt, list_date, valid_apply,
single_draw, ration_rt, rating_cd, pma_rt,
update_time)
try:
cursor.execute(insert_sql, v)
conn.commit()
except Exception as e:
logger.error(e)
conn.rollback()
if _time < '11:30:00':
today += 'morning'
headers = {
'Connection': 'keep-alive',
'Pragma': 'no-cache',
'Cache-Control': 'no-cache',
'User-Agent':
'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.130 Safari/537.36',
'Accept': '*/*',
'Referer': 'http://stockapp.finance.qq.com/mstats/?id=fund_close',
'Accept-Encoding': 'gzip, deflate',
'Accept-Language': 'zh,en;q=0.9,en-US;q=0.8',
}
conn = get_mysql_conn('db_fund', local='local')
cursor = conn.cursor()
def tencent_info():
create_table = 'create table if not EXISTS `{}` (`基金代码` varchar(20) PRIMARY KEY,`基金简称` varchar(100),`最新规模-万` float,`实时价格` float,`涨跌幅` float,`成交额-万` float,`净值日期` VARCHAR(10),`单位净值` float,`累计净值` float,`折溢价率` float ,`申购状态` VARCHAR(20),`申赎状态` varchar(20),`基金经理` VARCHAR(200),`成立日期` VARCHAR(20), `管理人名称` VARCHAR(200),`更新时间` VARCHAR(20));'.format(
today)
cursor.execute(create_table)
conn.commit()
for p in range(1, 114):
print('page ', p)
params = (
('appn', 'rank'),
('t', 'ranklof/chr'),
def update_jj(table):
# table='2020-02-25' # 用于获取code列
conn = get_mysql_conn('db_fund', local='local')
# today=datetime.datetime.now().strftime('%Y-%m-%d')
# print(today)
logger = llogger(f'{table}_realtime.log')
query = 'select `基金代码`,`基金简称`,`实时价格` from `{}`'.format(table)
# print(query)
cursor = conn.cursor()
cursor.execute(query)
session = requests.Session()
ret = cursor.fetchall()
url = 'http://web.ifzq.gtimg.cn/fund/newfund/fundSsgz/getSsgz?app=web&symbol=jj{}&_var=LOAD_1582735233556_37'
add_column1 = 'alter table `{}` add column `实时净值` float'.format(table)
add_column2 = 'alter table `{}` add column `溢价率` float'.format(table)
update_sql = 'update `{}` set `实时净值`= %s,`溢价率`=%s where `基金代码`=%s'.format(
table)
try:
cursor.execute(add_column1)
except Exception as e:
conn.rollback()
else:
conn.commit()
try:
cursor.execute(add_column2)
except Exception as e:
conn.rollback()
else:
conn.commit()
for item in ret:
# pass
# print(item[0])
code = item[0]
realtime_price = item[2]
s_resp = session.get(url.format(code), headers=headers)
# print(s_resp.text)
print()
print(code)
content = re.search('LOAD_\d+_\d+=(.*)', s_resp.text).group(1)
# print(content)
js = json.loads(content)
# print(js)
try:
data_list = js.get('data').get('data')
except Exception as e:
continue
# print(data_list)
last_one = data_list[-1]
time_ = last_one[0]
jj_ = last_one[1]
yjl = -1 * round((jj_ - realtime_price) / realtime_price * 100, 2)
print(f'溢价率-{yjl}')
cursor.execute(update_sql, (jj_, yjl, code))
conn.commit()
conn.close()
# 收集可转债的市场全景图
from settings import get_engine,sendmail,llogger,get_mysql_conn
import pandas as pd
import datetime
from config import token
import tushare as ts
today = datetime.datetime.now().strftime('%Y-%m-%d')
today_fmt = datetime.datetime.now().strftime('%Y%m%d')
# today='2020-02-06'
# today_fmt='20200206'
cons=ts.get_apis()
logger=llogger('log/bond_daily.log')
# ts.set_token(token)
# pro = ts.pro_api()
conn=get_mysql_conn('db_bond_daily','local')
def creat_table(day):
tb_name = 'bond_{}'.format(day)
create_cmd = 'create table if not exists `{tb_name}` (`date` varchar(20),`code` varchar(10) primary key,`name` varchar(16),`open` float ,' \
'`close` float,`high` float,`low` float,`vol` float,`amount` float) '.format(tb_name=tb_name)
cursor = conn.cursor()
try:
cursor.execute(create_cmd)
conn.commit()
except Exception as e:
logger.error(e)
return False
else:
return True
# -*-coding=utf-8-*-
# @Time : 2019/7/12 18:41
# @File : transfer_data_es.py
from settings import get_mysql_conn
from elasticsearch import Elasticsearch
es = Elasticsearch('10.18.6.102:9200')
conn = get_mysql_conn('db_stock', 'local')
cursor = conn.cursor()
query_cmd = 'select * from tb_cnstock'
cursor.execute(query_cmd)
ret = cursor.fetchall()
for item in ret:
# print(item)
date = item[0]
title = item[1]
url = item[2]
content = item[3]
keyword = item[4]
body = {
'Title': title,
'ULR': url,
'keyword': keyword,
'content': content,
'Date': date
}
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=get_mysql_conn('db_stock','local')
cursor=con.cursor()
query_cmd = '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(query_cmd)
get_count = cursor.fetchone()
num=get_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=[]
for i in cal_result:
cal_result_list.append(i[0])
cal_result_np=np.array(cal_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,num)
content=f'转债上涨比例：{raise_ratio}\n转债跌>正股数：{num}\n可转债涨幅>=0----{plug_count}\n可转债涨幅<0----{minus_count}\n涨幅最大值：{max_v}\n涨幅最小值：{min_v}\n涨幅均值：{mean}\n涨幅中位数：{median}\n涨幅波动的方差：{ripple_ratio}'
try:
send_aliyun(title,content,QQ_MAIL)
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,num,plug_count,minus_count))
con.commit()
except Exception as e:
logger.error(e)
con.rollback()
else:
logger.info('入库成功')
