def get_blacklist(self):
conn = get_mysql_conn('db_stock', 'local')
cursor = conn.cursor()
query = 'select CODE from tb_blacklist'
cursor.execute(query)
ret = cursor.fetchall()
return [i[0] for i in ret]
def break_low(self, date):
'''
筛选出一年内创新低的股票
:param date: 某一天的日期 ‘'2017-11-11
:return:
'''
# cmd = 'select * from `{}`'.format(date)
df = pd.read_sql_table(date, daily_engine, index_col='index')
# **** 这里的index需要删除一个
low_db = get_mysql_conn('db_selection')
low_cursor = low_db.cursor()
for i in range(len(df)):
code = df.loc[i]['code']
cur_low = df.loc[i]['low']
mins_date, mins = self.get_lowest(code, '2017', date)
if not mins_date:
continue
if mins and float(cur_low) <= float(mins) and float(
cur_low) != 0.0:
print(code, )
print(df.loc[i]['name'])
print('year mins {} at {}'.format(mins, mins_date))
print('curent mins ', cur_low)
create_cmd = 'create table if not exists break_low' \
'(`index` int primary key auto_increment,datetime datetime,code text,name text,low_price float,last_price float, last_price_date datetime);'
low_cursor.execute(create_cmd)
insert_cmd = 'insert into break_low (datetime,code,name,low_price,last_price,last_price_date) values (%s,%s,%s,%s,%s,%s);'
insert_data = (date, code, df.loc[i]['name'], cur_low, mins,
mins_date)
low_cursor.execute(insert_cmd, insert_data)
low_db.commit()
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 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 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/2/26 9:38
# @File : fund_raise_speed.py
# 查看基金最后暴力拉伸的
import datetime
import random
import time
from configure.settings import get_mysql_conn,llogger
import tushare as ts
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'{today}_fund_raise_monitor.log')
query='select `基金代码`,`基金简称` from `2020-02-25`'
# print(query)
cursor = conn.cursor()
cursor.execute(query)
ret = cursor.fetchall()
code_list=[]
for item in ret:
code = item[0]
df = ts.get_realtime_quotes(code)
close_p=float(df['pre_close'].values[0])
b1=float(df['b1_p'].values[0])
a1=float(df['a1_p'].values[0])
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')
# -*- coding: utf-8 -*-
# @Time : 2019/1/19 14:37
# @File : stockholder_info.py
# 股东信息获取
import pandas as pd
import time
import pymysql
import tushare as ts
import config
from configure.settings import get_mysql_conn
conn = get_mysql_conn('db_stock', 'local')
cursor = conn.cursor()
token = config.token
ts.set_token(token)
pro = ts.pro_api()
def get_stock_list():
df = pro.stock_basic(exchange='', list_status='L', fields='ts_code,symbol,name,area,industry,list_date')
return dict(zip(list(df['ts_code'].values), list(df['name'].values)))
# 生产日期 2000到2018
def create_date():
start_date = '20{}0101'
end_date = '20{}1231'