class StockDoctor(BaseService, StockBase):
def __init__(self):
# super(StockDoctor, self).__init__(f'log/{self.__class__.__name__}.log')
BaseService.__init__(self, f'log/{self.__class__.__name__}.log') # 新写法
StockBase.__init__(self)
self.logger.info('start')
self.DB = DBSelector()
def check_blacklist(self, code):
conn = self.DB.get_mysql_conn('db_stock', 'qq')
cur = conn.cursor()
cmd = 'select * from tb_blacklist where code="{}"'.format(code)
cur.execute(cmd)
ret = cur.fetchone()
if ret:
return True
else:
return False
# 是否是东北的
def north_east(self, code):
north_east_area = ['黑龙江', '吉林', '辽宁']
conn = self.DB.get_mysql_conn('db_stock', 'qq')
cur = conn.cursor()
cmd = 'select area from tb_basic_info where code=\'{}\''.format(code)
cur.execute(cmd)
ret = cur.fetchone()
if ret and ret in north_east_area:
return True
else:
return False
def get_code(self, name):
conn = self.DB.get_mysql_conn('db_stock', 'qq')
cur = conn.cursor()
cmd = 'select code from tb_basic_info where name=\'{}\''.format(name)
cur.execute(cmd)
ret = cur.fetchone()
return ret
def diagnose(self, code):
if not self.valid_code(code):
raise ValueError('输入有误')
issue = False
if self.check_blacklist(code):
self.logger.info('存在黑名单')
issue = True
if self.north_east(code):
self.logger.info('是东北股')
issue = True
if issue:
self.logger.info(f'{code} 问题股')
def main():
filename = os.path.join(DATA_PATH, 'blacklist.csv')
# 本地更新
db_name = 'db_stock'
DB = DBSelector()
conn = DB.get_mysql_conn(db_name, 'qq')
create_tb(conn)
update_data(filename, conn)
# 远程更新
# db_name = 'db_stock'
remote_conn = DB.get_mysql_conn('qdm225205669_db', 'qq')
create_tb(remote_conn)
update_data(filename, remote_conn)
class CBInfo(BaseService):
def __init__(self):
super(CBInfo, self).__init__()
self.db = DBSelector()
self.conn = self.db.get_mysql_conn('db_stock', 'qq')
def double_low(self, nums):
df = jsl_fetch()
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': '评级',
'adjust_tip': '下修提示',
'adj_cnt': '下调次数',
'convert_amt_ratio': '转债剩余占总市值比',
'curr_iss_amt': '剩余规模',
'orig_iss_amt': '发行规模',
'ration_rt': '股东配售率',
'redeem_flag': '发出强赎公告',
'redeem_dt': '强赎日期',
}
df = df.rename(columns=rename_columns)
df = convert(df, nums)
print(str(df))
return df
class Jisilu(BaseService):
def __init__(self, check_holiday=False, remote='qq'):
super(Jisilu, self).__init__(logfile='log/jisilu.log')
if check_holiday:
self.check_holiday()
self.date = datetime.datetime.now().strftime('%Y-%m-%d')
# self.date = '2020-02-07' # 用于调整时间
self.timestamp = int(time.time() * 1000)
self.headers = {
'User-Agent':
'User-Agent:Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/62.0.3202.94 Safari/537.36',
'X-Requested-With': 'XMLHttpRequest'
}
self.url = 'https://www.jisilu.cn/data/cbnew/cb_list/?___jsl=LST___t={}'.format(
self.timestamp)
self.pre_release_url = 'https://www.jisilu.cn/data/cbnew/pre_list/?___jsl=LST___t={}'.format(
self.timestamp)
self.remote = remote
self.DB = DBSelector()
self.engine = self.DB.get_engine('db_jisilu', self.remote)
def check_holiday(self):
if self.is_weekday():
self.logger.info("Start")
else:
self.logger.info("Holidy")
exit(0)
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)
self.notify(title='下载失败', desp=f'{self.__class__}')
continue
return None
def daily_update(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', {})
df = self.data_parse(bond_list, adjust_no_use)
self.store_mysql(df)
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': '强赎日期',
'guarantor': '担保',
}
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)
return df
def store_mysql(self, df):
try:
df.to_sql('tb_jsl_{}'.format(self.date),
self.engine,
if_exists='replace',
dtype={'可转债代码': VARCHAR(10)})
db_stock = self.DB.get_engine('db_stock', self.remote)
df.to_sql('tb_bond_jisilu'.format(self.date),
db_stock,
if_exists='replace',
dtype={'可转债代码': VARCHAR(10)})
except Exception as e:
self.logger.info(e)
send_from_aliyun(title='jisilu可转债', content='写入数据库出错')
def init_release_table(self, conn):
creat_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
);
'''
self.execute(creat_table, (), conn)
def get_conn(self):
return self.DB.get_mysql_conn('db_stock', 'qq')
# 这个数据最好晚上10点执行
def release_data(self):
conn = self.get_conn()
self.init_release_table(conn)
post_data = {
'cb_type_Y': 'Y',
'progress': '',
'rp': 22,
}
r = self.download(url=self.pre_release_url, data=post_data)
js_data = r.json()
rows = js_data.get('rows')
self.save_release_data(rows, conn)
def save_release_data(self, rows, conn):
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()
if self.check_bond_exist(bond_id, conn):
if self.check_update(bond_id, conn):
update_data = (underwriter_rt, list_date, update_time,
bond_id)
self.update_release_data(update_data, conn)
else:
continue
# 插入
else:
insert_data_tuple = (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)
self.insert_release_data(insert_data_tuple, conn)
def check_update(self, bond_id, conn):
check_update = '''select * from tb_bond_release where 可转债代码=%s and 包销比例 is null'''
return self.execute(check_update, bond_id, conn)
def update_release_data(self, update_data, conn):
'''更新发布数据'''
update_sql = '''update tb_bond_release set 包销比例=%s , 上市日期=%s ,抓取时间=%s where 可转债代码 = %s'''
self.execute(update_sql, update_data, conn)
def insert_release_data(self, data, conn):
'''插入发布数据'''
insert_sql = '''insert into tb_bond_release (可转债代码 , 可转债名称 , 集思录建议 , 包销比例 , 中签率 ,上市日期 ,申购户数（万户）, 单账户中签（顶格）, 股东配售率 ,评级 , 现价比转股价,抓取时间) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'''
self.execute(insert_sql, data, conn)
def check_bond_exist(self, bond_id, conn):
'''
判断债券是否存在
'''
check_exist = '''select * from tb_bond_release where 可转债代码=%s'''
return self.execute(check_exist, bond_id, conn)
def execute(self, cmd, data, conn):
cursor = conn.cursor()
if not isinstance(data, tuple):
data = (data, )
try:
cursor.execute(cmd, data)
except Exception as e:
conn.rollback()
self.logger.error('执行数据库错误 {}'.format(e))
ret = None
else:
ret = cursor.fetchall()
conn.commit()
return ret
def convert_float(self, x):
if not x:
return None
if '%' in x:
ration = 100
else:
ration = 1
x = re.sub('%', '', x)
try:
ret = float(x) * ration
except Exception as e:
self.logger.error('转换失败{}'.format(e))
ret = None
return ret
_time = now.strftime('%H:%M:%S')
if _time < '11:30:00':
TODAY += 'morning'
elif _time < '14:45:00':
TODAY += 'noon'
else:
TODAY += 'close'
# TODAY += 'noon' # 调试
NOTIFY_HOUR = 13
MAX_PAGE = 50
try:
DB = DBSelector()
conn = DB.get_mysql_conn('db_fund', 'qq')
cursor = conn.cursor()
except Exception as e:
print(e)
class TencentFundSpider(BaseService):
# 腾讯 基金数据爬虫 套利使用
def __init__(self):
super(TencentFundSpider, self).__init__(f'../log/{self.__class__.__name__}.log')
self.create_table()
self.session = requests.Session()
self.logger.info('start...qq fund')
self.LAST_TEXT = ''
def __init__(self):
DB = DBSelector()
self.con = DB.get_mysql_conn('db_stock', 'qq')
self.cursor = self.con.cursor()
__author__ = 'Rocky' ''' http://30daydo.com Contact: [email protected] ''' # 交割单处理 保存交割单到数据库 import os import datetime import pandas as pd import numpy as np from configure.settings import DBSelector import fire DB=DBSelector() engine = DB.get_engine('db_stock', 'qq') conn =DB.get_mysql_conn('db_stock','qq') pd.set_option('display.max_rows', None) class DeliveryOrder(): def __init__(self): self.gj_table='tb_delivery_gj_django' self.hb_table ='tb_delivery_hb_django' def setpath(self,path): path = os.path.join(os.getcwd(), path) if os.path.exists(path) == False: os.mkdir(path) os.chdir(path) # 单独处理华宝证券的数据
class ReachTarget(BaseService):
def __init__(self):
super(ReachTarget, self).__init__('log/reach_target.log')
self.DB = DBSelector()
self.engine = self.DB.get_engine('db_stock', 'qq')
self.api = ts.get_apis()
# python3 这个返回的不是list,需要手工转换
# self.kzz_code_list = list(self.stocks.keys())
# pool_code,pool_name=self.stock_pool()
# self.pool_dict = dict(zip(pool_code,pool_name))
# self.pool_list= list(self.pool_dict.keys())
# 添加一部分持仓数据 或者 监测仓
# self.df = pd.read_table(file, encoding='gbk', dtype={'证券代码': np.str})
# try:
# del self.df['Unnamed: 15']
# except Exception as e:
# logger.error(e)
# logger.error('删除多余列失败')
#
# code_list = list(self.df['证券代码'].values)
#
# # 移除非法证券代码 中签
# t = [code_list.remove(i) for i in code_list.copy() if i.startswith('7') or i[:2] == '07']
#
# self.code_lists=code_list
def all_bond_market(self):
self.kzz_code, self.kzz_name, self.zg_code, self.name, self.yjl = self.zg_bond(
)
self.kzz_stocks = dict(zip(self.kzz_code, self.kzz_name))
self.zg_stocks = dict(zip(self.zg_code, self.name))
self.kzz_stocks_yjl = dict(zip(self.kzz_code, self.yjl))
self.zg_stocks_yjl = dict(zip(self.zg_code, self.yjl))
return (self.kzz_stocks, self.zg_stocks, self.kzz_stocks_yjl,
self.zg_stocks_yjl)
# 数据库获取模拟股，这个要废弃
def stock_pool(self):
pool_table = 'tb_current_hold'
pool_df = pd.read_sql(pool_table, self.engine, index_col='index')
return list(pool_df['代码'].values), list(pool_df['名字'].values)
# 判断市场
def identify_market(self, x):
if x.startswith('3') or x.startswith('6') or x.startswith('0'):
return False
else:
return True
# 获取当前持仓个股
def get_current_position(self):
engine = self.DB.get_engine('db_position', 'qq')
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 = self.DB.get_mysql_conn('db_stock', 'qq')
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 zg_bond(self):
bond_table = 'tb_bond_jisilu'
try:
jsl_df = pd.read_sql(bond_table, self.engine)
except Exception as e:
self.logger.info(e)
return [], [], [], [], []
else:
return list(jsl_df['可转债代码']), list(jsl_df['可转债名称']), list(jsl_df['正股代码'].values), \
list(jsl_df['正股名称'].values), list(jsl_df['溢价率'].values)
# 可转债的监测
def monitor(self, total_market=True):
'''
total_market 默认监控全市场 total_market = True
'''
if total_market:
(kzz_stocks, zg_stocks, kzz_yjl, zg_yjl) = self.all_bond_market()
else:
(kzz_stocks, zg_stocks, kzz_yjl,
zg_yjl) = self.get_current_position()
zg_code = list(zg_stocks.keys())
kzz_code = list(kzz_stocks.keys())
self.has_sent_kzz = dict(
zip(kzz_code, [datetime.datetime.now()] * len(kzz_code)))
self.has_sent_diff = dict(
zip(kzz_code, [datetime.datetime.now()] * len(kzz_code)))
self.has_sent_zg = dict(
zip(zg_code, [datetime.datetime.now()] * len(zg_code)))
while 1:
current = trading_time()
if current == MARKET_OPENING:
self.get_realtime_info(kzz_code, self.has_sent_kzz, '转债',
kzz_stocks, kzz_yjl, ZZ_ALERT_PERCENT)
self.get_realtime_info(zg_code, self.has_sent_zg, '正股',
zg_stocks, zg_yjl, ZG_ALERT_PERCENT)
self.get_price_diff(codes=kzz_code,
has_sent_=self.has_sent_diff,
types='差价',
kzz_stocks=kzz_stocks,
kzz_stocks_yjl=kzz_yjl)
time.sleep(LOOP_TIME)
elif current == -1:
time.sleep(LOOP_TIME)
elif current == 1:
try:
ts.close_apis(self.api)
except Exception as e:
self.logger.info('fail to stop monitor {}'.format(
datetime.datetime.now()))
self.logger.info(e)
exit(0)
# 获取实时报价
def get_realtime_info(self, codes, has_sent, types, stock, yjl, percent):
try:
price_df = ts.quotes(codes, conn=self.api)
except Exception as e:
self.logger.error('获取可转债异常 >>>> {}'.format(e))
try:
self.api = ts.get_apis()
except Exception as e:
self.logger.error('异常中存在异常{}'.format(e))
time.sleep(EXECEPTION_TIME)
else:
if len(price_df) != 0:
price_df = price_df[price_df['cur_vol'] != 0]
price_df['percent'] = (
price_df['price'] -
price_df['last_close']) / price_df['last_close'] * 100
price_df['percent'] = price_df['percent'].map(
lambda x: round(x, 2))
ret_dt = \
price_df[
(price_df['percent'] > percent) | (price_df['percent'] < -1 * percent)][
['code', 'price', 'percent']]
if len(ret_dt) > 0:
# 提醒一次后，下一次的间隔为DELTA_TIME分钟后
# sent_list = []
for i in ret_dt['code']:
if has_sent[i] <= datetime.datetime.now():
name_list = []
yjl_list = []
name_list.append(stock[i])
yjl_list.append(yjl[i])
has_sent[i] = datetime.datetime.now(
) + datetime.timedelta(minutes=DELTA_TIME)
ret_dt1 = ret_dt[ret_dt['code'] == i]
ret_dt1['名称'] = name_list
ret_dt1['溢价率'] = yjl_list
name = ret_dt1['名称'].values[0]
price = ret_dt1['price'].values[0]
percent = ret_dt1['percent'].values[0]
yjl_v = ret_dt1['溢价率'].values[0]
now = datetime.datetime.now().strftime(
'%Y-%m-%d %H:%M:%S')
content0 = '{t}\n{name}:价格:{price} 涨幅:{percent},溢价率:{yjl}'.format(
name=name,
price=price,
percent=percent,
yjl=yjl_v,
t=now)
self.logger.info(content0)
try:
notify(title=content0)
except Exception as e:
self.logger.info('发送微信失败')
self.logger.info(e)
# 获取差价 可转债
def get_price_diff(self, codes, has_sent_, types, kzz_stocks,
kzz_stocks_yjl):
# 针对可转债
try:
df = ts.quotes(codes, conn=self.api)
except Exception as e:
self.logger.error('获取可转债异常 >>>> {}'.format(e))
try:
self.api = ts.get_apis()
except Exception as e:
self.logger.error('异常中存在异常{}'.format(e))
time.sleep(EXECEPTION_TIME)
else:
df['bid1'] = df['bid1'].astype(float)
df['ask1'] = df['ask1'].astype(float)
df['diff'] = np.abs(df['bid1'] - df['ask1'])
result = df[df['diff'] >= DIFF_V]
if result.empty:
# continue
return
else:
for j in result['code']:
if has_sent_[j] <= datetime.datetime.now():
has_sent_[j] = datetime.datetime.now(
) + datetime.timedelta(minutes=DIFF_DELTA_TIME)
name_list = []
yjl_list = []
name_list.append(kzz_stocks[j])
yjl_list.append(kzz_stocks_yjl[j])
ret_dt1 = result[result['code'] == j]
ret_dt1['名称'] = name_list
ret_dt1['溢价率'] = yjl_list
# ret_dt1 = ret_dt1.set_index('code', drop=True)
code = j
name = ret_dt1['名称'].values[0]
price = ret_dt1['price'].values[0]
bid = ret_dt1['bid1'].values[0]
ask = ret_dt1['ask1'].values[0]
diff = round(ret_dt1['diff'].values[0], 2)
now = datetime.datetime.now().strftime(
'%Y-%m-%d %H:%M:%S')
content0 = '{t}\n{code}::{name}:价格:{price} 买1:{bid} 卖1:{ask}差价:{diff}'.format(
code=code,
name=name,
price=price,
bid=bid,
ask=ask,
diff=diff,
t=now)
self.logger.info(content0)
try:
wechat.send_content(content0)
except Exception as e:
self.logger.info('发送微信失败')
self.logger.info(e)
class CBIndexJSL(BaseService):
def __init__(self):
super(CBIndexJSL, self).__init__('../log/cb_index.log')
self.db = DBSelector()
def get_today_index(self):
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)
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') # 涨跌幅
current = self.today
value_tuple = (current, price, volume, increase_val, increase_rt,
count, amount)
self.save_mysql(value_tuple)
def save_mysql(self, data):
update_data_sql = '''
insert into `tb_cb_index` (`日期`,`指数`,`成交额(亿元)`,`涨跌`,`涨跌额`,`转债数目`,`剩余规模`) values (%s,%s,%s,%s,%s,%s,%s);
'''
con = self.db.get_mysql_conn('db_stock', 'qq')
self.execute(update_data_sql, data, con)
# 获取历史记录
def history_data(self):
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'
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') # 涨跌幅
con = self.db.get_mysql_conn('db_stock', 'qq')
create_sql = '''
create table if not exists tb_cb_index_test (`日期` date ,`指数` float ,`成交额(亿元)` float,`涨跌` float ,`涨跌额` float ,`转债数目` float ,`剩余规模` float )
'''
self.execute(create_sql, (), con)
update_data_sql = '''
insert into `tb_cb_index_test` (`日期`,`指数`,`成交额(亿元)`,`涨跌`,`涨跌额`,`转债数目`,`剩余规模`) 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])
self.execute(update_data_sql, value_tuple, con)
import time
import tushare as ts
import pandas as pd
import os, datetime, math
import numpy as np
import logging
from configure.settings import DBSelector, MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASSWORD, REDIS_HOST
import redis
from threading import Thread
from common.BaseService import BaseService
DB = DBSelector()
engine = DB.get_engine('history', 'qq')
conn = ts.get_apis()
MYSQL_DB = 'history'
cursor = DB.get_mysql_conn(MYSQL_DB, 'qq').cursor()
# pd.set_option('display.max_rows', None)
class Kline(BaseService):
def __init__(self):
super(Kline, self).__init__('log/kline.log')
path = os.path.join(os.getcwd(), 'data')
self.today_date = datetime.datetime.now().strftime('%Y-%m-%d')
if not os.path.exists(path):
os.mkdir(path)
os.chdir(path)
def db_init(self):
DB = DBSelector()
self.engine = DB.get_engine('db_stock', 'qq')
self.conn = DB.get_mysql_conn('db_stock', 'qq')