def run(cls):
cursor = conn.cursor()
cursor.execute(
'select substr(news_time, 1, 19) as news_time, src, content from news '
'where news_time >= date_sub(now(), interval 120 minute) '
'order by news_time desc limit 500;')
res = cursor.fetchall()
mail_list = []
for x in res:
news_time = x[0]
src = x[1]
content = x[2]
src_name = cls.SRC_MAP.get(src, '')
msg = u'{} {}\r\n' \
u'{}\r\n'.format(news_time, src_name, content)
# print msg
mail_list.append(msg)
print len(mail_list)
mail_content = '-------------------------\r\n'.join(mail_list)
MailTools.send_mail(subject=u'新闻简讯-{}'.format(
TimeTool.datetime_to_str(TimeTool.now(), '%m-%d %H:%M')),
content=mail_content)
def run(cls):
"""
股票列表
https://tushare.pro/document/2?doc_id=112
:return:
"""
# 设置ts token
ts.set_token(TOKEN)
pro = ts.pro_api()
fields = [
'ts_code', 'exchange', 'chairman', 'manager', 'secretary',
'reg_capital', 'setup_date', 'province', 'city', 'introduction',
'website', 'email', 'office', 'business_scope', 'employees',
'main_business'
]
cursor = conn.cursor()
cursor.execute('set SESSION autocommit = 1;')
# 上市公司基本信息
df = pro.stock_company(fields=fields)
for i, row in df.iterrows():
sql = 'replace into stock_company ({}) values ({})'.format(
','.join(fields), ','.join(['%s'] * len(row)))
try:
# print sql, row.values
cursor.execute(sql, tuple([x or '' for x in row.values]))
except Exception as e:
print row.values
cursor.execute('alter table stock_company engine=innodb; ')
conn.commit()
cursor.close()
conn.close()
def run(cls, st=None, et=None, flag_refresh_all=0):
"""
日线 通用行情接口
https://tushare.pro/document/2?doc_id=109
:return:
"""
print st, et, flag_refresh_all
st = TimeTool.str_to_datetime(st) or TimeTool.now(-1)
et = TimeTool.str_to_datetime(et) or TimeTool.now(1)
cursor = conn.cursor()
cursor.execute('set SESSION autocommit = 0;')
cursor.execute(
'select ts_code, list_date from stock where is_valid = 1 order by ts_code asc;'
)
db_res = cursor.fetchall()
for stock in db_res:
ts_code = stock[0]
list_date = stock[1]
if flag_refresh_all == 0:
date_pair = cls.cal_date_pair(st=st, et=et)
else:
date_pair = cls.cal_date_pair(st=list_date, et=et)
for _st, _et in date_pair:
time.sleep(1.0 / 500)
cls.save_daily_to_db(ts_code, _st, _et, cursor)
conn.commit()
cursor.close()
conn.close()
def run(cls):
"""
股票列表
https://tushare.pro/document/2?doc_id=25
:return:
"""
# 设置ts token
ts.set_token(TOKEN)
pro = ts.pro_api()
fields = [
'ts_code', 'symbol', 'name', 'area', 'industry', 'market',
'exchange', 'list_status', 'list_date', 'is_hs'
]
cursor = conn.cursor()
cursor.execute('set SESSION autocommit = 0;')
# 上市
df = pro.stock_basic(list_status='L', fields=fields)
for i, row in df.iterrows():
sql = 'replace into stock ({}) values ({})'.format(
','.join(fields), ','.join(['%s'] * len(row)))
print sql, row.values
cursor.execute(sql, tuple([x or '' for x in row.values]))
cursor.execute(
"update stock set is_valid = 0 where market in ('科创板') or name like '%%ST%%'"
)
cursor.execute('alter table stock engine=innodb; ')
conn.commit()
cursor.close()
conn.close()
def run(cls, st=None, et=None):
"""
新闻快讯
https://tushare.pro/document/2?doc_id=143
:return:
"""
et = TimeTool.str_to_datetime(
et) or TimeTool.now() + TimeTool.timedelta(minutes=1)
st = TimeTool.str_to_datetime(st) or et - TimeTool.timedelta(
minutes=30)
st = str(st)
et = str(et)
# 设置ts token
ts.set_token(TOKEN)
pro = ts.pro_api()
fields = [
'datetime',
'title',
'content',
'channels',
]
cursor = conn.cursor()
cursor.execute('set SESSION autocommit = 0;')
for src in cls.SRC_LIST:
df = pro.news(src=src, start_date=st, end_date=et, fields=fields)
for i, row in df.iterrows():
d = row.to_dict()
news_time = d.get(
'datetime', '0001-01-01 00:00:00') or '0001-01-01 00:00:00'
title = d.get('title', '') or ''
content = d.get('content', '') or ''
content_hash = hash(content)
channels = str(d.get('channels', '')) or ''
sql = 'select 1 from news where news_time = %s and content_hash = %s and src= %s;'
cursor.execute(sql, (news_time, content_hash, src))
res = cursor.fetchall()
if res:
continue
sql = "replace into " \
"news (src, news_time, title, content, content_hash, channels) " \
"values (%s, %s, %s, %s, %s, %s)"
cursor.execute(
sql,
(src, news_time, title, content, content_hash, channels))
conn.commit()
cursor.close()
conn.close()
def run(cls, year=None):
"""
沪深港通资金流向
https://tushare.pro/document/2?doc_id=47
:return:
"""
if year:
st = TimeTool.datetime(year=year, month=1, day=1)
else:
st = TimeTool.datetime(year=TimeTool.now().year, month=1, day=1)
et = st + TimeTool.timedelta(days=366)
st = TimeTool.datetime_to_str(st, '%Y%m%d')
et = TimeTool.datetime_to_str(et, '%Y%m%d')
# 设置ts token
ts.set_token(TOKEN)
pro = ts.pro_api()
fields = [
'trade_date',
'ggt_ss',
'ggt_sz',
'hgt',
'sgt',
'north_money',
'south_money'
]
cursor = conn.cursor()
cursor.execute('set SESSION autocommit = 1;')
# 上市公司基本信息
df = pro.moneyflow_hsgt(start_date=st, end_date=et)
for i, row in df.iterrows():
sql = 'replace into moneyflow_hsgt ({}) values ({})'.format(','.join(fields), ','.join(['%s'] * len(row)))
try:
# print sql, row.values
cursor.execute(sql, tuple([x if x is not np.nan else 0 for x in row.values]))
except Exception as e:
print row.values
print np.nan_to_num(row.values)
cursor.execute('alter table moneyflow_hsgt engine=innodb; ')
conn.commit()
cursor.close()
conn.close()
def run(cls, st=None, et=None):
"""
交易日历
https://tushare.pro/document/2?doc_id=26
:param st: 20190101
:param et: 20201231
:return:
"""
# 设置ts token
ts.set_token(TOKEN)
pro = ts.pro_api()
fields = [
'exchange',
'cal_date',
'is_open',
'pretrade_date',
]
kwargs = {}
if st:
kwargs.update(start_date=st)
if et:
kwargs.update(start_date=et)
# 交易日历
df = pro.trade_cal(fields=fields, **kwargs)
cursor = conn.cursor()
cursor.execute('set SESSION autocommit = 0;')
for i, row in df.iterrows():
sql = 'replace into trade_cal ({}) values ({})'.format(
','.join(fields), ','.join(['%s'] * len(row)))
print sql, row.values
cursor.execute(sql, tuple([x or '' for x in row.values]))
cursor.execute("""
UPDATE trade_cal a
JOIN ( SELECT * FROM trade_cal WHERE is_open = 1 ) t ON ( t.pretrade_date = a.cal_date )
SET a.nexttrade_date = t.cal_date
WHERE
a.is_open = 1;
""")
cursor.execute('alter table trade_cal engine=innodb; ')
conn.commit()
cursor.close()
conn.close()
def run(cls, ts_code=''):
"""
历史名称变更记录
https://tushare.pro/document/2?doc_id=100
:return:
"""
# 设置ts token
ts.set_token(TOKEN)
pro = ts.pro_api()
# 需要的字段
fields = [
'ts_code',
'name',
'start_date',
'end_date',
'ann_date',
'change_reason'
]
cursor = conn.cursor()
cursor.execute('select ts_code, list_date from stock where ts_code >= %s order by ts_code asc;', (ts_code, ))
res = cursor.fetchall()
cursor.execute('set SESSION autocommit = 0;')
for x in res:
ts_code = x[0]
# 名称变更记录
time.sleep(0.3)
try:
df = pro.namechange(ts_code=ts_code, fields=fields)
except Exception as e:
print e.message
time.sleep(60)
df = pro.namechange(ts_code=ts_code, fields=fields)
for i, row in df.iterrows():
sql = 'replace into namechange ({}) values ({})'.format(','.join(fields), ','.join(['%s'] * len(row)))
print sql, row.values
cursor.execute(sql, tuple([x or '' for x in row.values]))
conn.commit()
cursor.execute('alter table namechange engine=innodb; ')
conn.commit()
cursor.close()
conn.close()
def main(cls, st='', et=''):
cursor = conn.cursor()
cursor.execute('set SESSION autocommit = 0;')
for i in xrange(1, 400):
data = {
'seqno': i,
'startTime': st,
'endTime': et,
'pageNumber': 1,
'pageSize': 10000,
}
time.sleep(1)
print cls.API, data
res = requests.post(cls.API, data)
content = json.loads(res.content)
rows = content.get('rows')
for x in rows:
region = x.get('region', '')
yyyy = x.get('yyyy', '')
mm = x.get('mm', '')
dd = x.get('dd', '')
unit = x.get('unit', '')
price = x.get('price', '0') or 0
if not price:
continue
prod_spec = x.get('prod_spec', '')
prod_name = x.get('prod_name', '')
the_day = '{}-{}-{}'.format(yyyy, mm, dd)
info = {
'seqno': i,
'the_day': the_day,
'prod_name': prod_name,
'price': price,
'unit': unit,
'region': region,
'prod_spec': prod_spec,
}
cls.save_db(info, 'prod_price', cursor)
conn.commit()
conn.close()
def run(cls):
"""
沪深股通成份股
https://tushare.pro/document/2?doc_id=104
:return:
"""
# 设置ts token
ts.set_token(TOKEN)
pro = ts.pro_api()
# 需要的字段
fields = ['ts_code', 'hs_type', 'in_date', 'out_date', 'is_new']
cursor = conn.cursor()
cursor.execute('set SESSION autocommit = 0;')
# 沪
df = pro.hs_const(hs_type='SH', fields=fields)
for i, row in df.iterrows():
sql = 'replace into hs_const ({}) values ({})'.format(
','.join(fields), ','.join(['%s'] * len(row)))
print sql, row.values
cursor.execute(sql, tuple([x or '' for x in row.values]))
# 深
df = pro.hs_const(hs_type='SZ', fields=fields)
for i, row in df.iterrows():
sql = 'replace into hs_const ({}) values ({})'.format(
','.join(fields), ','.join(['%s'] * len(row)))
print sql, row.values
cursor.execute(sql, tuple([x or '' for x in row.values]))
cursor.execute('alter table hs_const engine=innodb; ')
conn.commit()
cursor.close()
conn.close()
def run(cls, trade_date=None, rebuild_table=True):
"""
沪深股票 每日指标
https://tushare.pro/document/2?doc_id=32
:return:
"""
trade_date = TimeTool.str_to_datetime(
trade_date) or TimeTool.date_to_datetime(TimeTool.now())
trade_date = TimeTool.datetime_to_str(trade_date, '%Y%m%d')
print 'begin task stock-daily-basic. trade_date:{}'.format(trade_date)
# 设置ts token
ts.set_token(TOKEN)
pro = ts.pro_api()
field_default = (
('ts_code', ''), # TS股票代码
('trade_date', ''), # 交易日期
('close', 0), # 当日收盘价
('turnover_rate', 0), # 换手率（%）
('turnover_rate_f', 0), # 换手率（自由流通股）
('volume_ratio', 0), # 量比
('pe', 0), # 市盈率（总市值/净利润， 亏损的PE为空）
('pe_ttm', 0), # 市盈率（TTM，亏损的PE为空）
('pb', 0), # 市净率（总市值/净资产）
('ps', 0), # 市销率
('ps_ttm', 0), # 市销率（TTM）
('dv_ratio', 0), # 股息率 （%）
('dv_ttm', 0), # 股息率（TTM）（%）
('total_share', 0), # 总股本 （万股）
('float_share', 0), # 流通股本 （万股）
('free_share', 0), # 自由流通股本 （万）
('total_mv', 0), # 总市值 （万元）
('circ_mv', 0), # 流通市值（万元）
)
fields = [i[0] for i in field_default]
cursor = conn.cursor()
cursor.execute('set SESSION autocommit = 0;')
offset = 0
limit = 5000
while 1:
# 上市
df = pro.daily_basic(trade_date=trade_date,
fields=fields,
offset=offset,
limit=limit)
df = df.fillna(dict(field_default))
for i, row in df.iterrows():
sql = 'replace into daily_basic ({}) values ({})'.format(
','.join(fields), ','.join(['%s'] * len(row)))
cursor.execute(sql, tuple([x for x in row.values]))
if df.shape[0] < limit:
break
offset += limit
if rebuild_table:
cursor.execute('alter table daily_basic engine=innodb; ')
conn.commit()
cursor.close()
conn.close()
print 'finish task stock-daily-basic. trade_date:{}'.format(trade_date)
def run(cls):
"""
公募基金列表
https://tushare.pro/document/2?doc_id=19
:return:
"""
# 设置ts token
ts.set_token(TOKEN)
pro = ts.pro_api()
field_default = (
('ts_code', ''),
('name', ''),
('management', ''),
('custodian', ''),
('fund_type', ''),
('found_date', ''),
('due_date', ''),
('list_date', ''),
('issue_date', ''),
('delist_date', ''),
('issue_amount', 0),
('m_fee', 0),
('c_fee', 0),
('duration_year', 0),
('p_value', 0),
('min_amount', 0),
('exp_return', 0),
('benchmark', ''),
('status', ''),
('invest_type', ''),
('type', ''),
('trustee', ''),
('purc_startdate', ''),
('redm_startdate', ''),
('market', ''),
)
fields = [i[0] for i in field_default]
cursor = conn.cursor()
cursor.execute('set SESSION autocommit = 0;')
offset = 0
limit = 5000
while 1:
# 上市
df = pro.fund_basic(fields=fields, offset=offset, limit=limit)
df = df.fillna(dict(field_default))
for i, row in df.iterrows():
sql = 'replace into fund_basic ({}) values ({})'.format(
','.join(fields), ','.join(['%s'] * len(row)))
cursor.execute(sql, tuple([x for x in row.values]))
if df.shape[0] < limit:
break
offset += limit
cursor.execute('alter table fund_basic engine=innodb; ')
conn.commit()
cursor.close()
conn.close()