def after_bid_strategy(time):
# 查询指定连板数的股票信息
sql = "SELECT stock_code,stock_name,create_date,current_price from stock_limit_up_analyse WHERE continuous_time = " + time + " AND stock_name not like '%ST%' and create_date < '" + limit_date + "' "
pool = mysql_pool.sql_pool()
stocks = pool.selectMany(sql)
writebook = xlwt.Workbook() # 打开excel
test = writebook.add_sheet('after_bid_stock')
# 定义时间格式
dateFormat = xlwt.XFStyle()
dateFormat.num_format_str = 'yyyy-mm-dd'
# 定义表头
test.write(0, 0, "股票代码")
test.write(0, 1, "股票名称")
test.write(0, 2, "涨停时间")
test.write(0, 6, "第二天开盘幅度")
test.write(0, 7, "第二天最高幅度")
test.write(0, 8, "第二天最低幅度")
test.write(0, 9, "第二天收盘幅度")
test.write(0, 10, "第二天涨停（1是2否）")
test.write(0, 6, "第三天开盘幅度")
test.write(0, 7, "第三天最高幅度")
test.write(0, 8, "第三天最低幅度")
test.write(0, 9, "第三天收盘幅度")
# 依此遍历复盘股票
# for stock in stocks:
# day_data = ts.get_hist_data("300576", start="2020-05-20", end="2020-05-20")
# print(day_data)
# print(day_data.iloc[0,0])
def delete_stock():
sql = "delete from trade_stock "
con = sql_pool().get_connection()
cursor = con.cursor()
"""判断是否条件查询"""
cursor.execute(sql)
con.commit()
con.close()
def add_stock_list(stock_list):
sql = "insert ignore into trade_stock(stock_name,area_stock_code,code_short,stock_letters) values(%s,%s,%s,%s) "
con = sql_pool().get_connection()
cursor = con.cursor()
"""判断是否条件查询"""
cursor.executemany(sql, stock_list)
con.commit()
con.close()
def get_stock_count():
sql = "select count(1) count from trade_stock"
con = sql_pool().get_connection()
cursor = con.cursor()
"""判断是否条件查询"""
cursor.execute(sql)
result = cursor.fetchone()['count']
con.close()
return result
def get_today_two_limit_up():
pool = mysql_pool.sql_pool()
today = date_time_util.get_date(0)
today = "2020-05-15"
print(today)
sql = "select stock_name,stock_code from stock_limit_up_analyse where continuous_time = 2 and stock_name not like '%ST%'and create_date = '" + today + "'"
res = pool.selectMany(sql)
return res
def save_word_nephogram(begin_day,end_day,comment_txt_name):
pool = mysql_pool.sql_pool()
connection = pool.get_connection()
sql = "insert into trade_word_nephogram(begin_time,end_time,picture_name) value (%s,%s,%s)"
cursor = connection.cursor()
comment_txt_name = comment_txt_name.replace("txt","jpg")
cursor.execute(sql,(begin_day,end_day,comment_txt_name))
cursor.connection.commit()
result = cursor.fetchone()
connection.close()
print(result)
def get_stock_info(key_word=None):
sql = "select code_short,stock_name,stock_letters,area_stock_code from trade_stock"
con = sql_pool().get_connection()
cursor = con.cursor()
"""判断是否条件查询"""
if (key_word != None):
sql = sql + " where area_stock_code like %s or stock_name like %s or stock_letters like %s"
cursor.execute(sql, (key_word, key_word, key_word))
else:
cursor.execute(sql)
result = cursor.fetchall()
con.close()
return result
def deal_invalid_word(detail_comment_content):
pool = mysql_pool.sql_pool()
connection = pool.get_connection()
sql = "select invalid_word from trade_invalid_word"
cursor = connection.cursor()
cursor.execute(sql)
result = cursor.fetchall()
for row in result:
detail_comment_content = detail_comment_content.replace(row["invalid_word"],"")
return detail_comment_content
print(result)
def add_field_word(field_word):
"""分隔字符 获取多个词汇"""
field_words = field_word.split(",")
words_list = []
for word in field_words:
words_list.append((word,))
pool = mysql_pool.sql_pool()
connection = pool.get_connection()
sql = "insert into trade_invalid_word(invalid_word) values (%s)"
cursor = connection.cursor()
cursor.executemany(sql,tuple(words_list))
cursor.connection.commit()
result = cursor.fetchone()
print(result)
connection.close()
def get_history_picture():
pool = mysql_pool.sql_pool()
connection = pool.get_connection()
sql = "select begin_time,end_time,picture_name from trade_word_nephogram order by id desc limit 10"
cursor = connection.cursor()
cursor.execute(sql)
result = cursor.fetchall()
res = []
for row in result:
picture = sina_comment_picture.picture(row["begin_time"],row["end_time"],row["picture_name"])
res.append(picture.picture_to_string(picture))
# print(result)
return res
# picture = get_history_picture()
# print(picture)
# add_field_word("")
# save_word_nephogram("2017-01-01 08:45:45","2017-01-01 08:45:45","das,txt")
# print(platform.system())
# dat = requests.get("https://finance.sina.com.cn/stock/jsy/2019-09-09/doc-iicezzrq4598453.shtml")
# dat.encoding = "utf-8"
# format = BeautifulSoup(dat.text,"html.parser")
# find = format.find(name="description")
# print(find)
# pool = mysql_pool.sql_pool()
# connection = pool.get_connection()
# # sql = "insert into zjy_21_20190628153041_0 (离职时间) value (%s)"
# sql = "select code_short,code_full,stock_name,stock_letters,area_stock_code from trade_stock"
# sql = sql +" where area_stock_code like %s or stock_name like %s or stock_letters like %s"
# cursor = connection.cursor()
#
# cursor.execute(sql,("STXL","STXL","STXL"))
# print(cursor.fetchall())
# cursor.connection.commit()
def __init__(self):
self.mysqlService = mysql_pool.sql_pool()
self.redis = redis_pool.RedisPool()
self.stock_strategy_record_dao = stock_strategy_record_dao.stock_strategy_record(
)
def __init__(self):
self.mysqlService = mysql_pool.sql_pool()
from python.mysql import mysql_pool
import matplotlib.pyplot as plt
pool = mysql_pool.sql_pool()
import traceback
""""
获取所有版块
"""
def get_all_plate():
sql = "select stock_name,stock_code from stock_plate_info "
datas = pool.selectMany(sql)
return datas
"""
获取指定版块 2014-7.22 - 2015.6.12
2019.1.4 - 2019.4.19
"""
def get_special_plate(stockcode, begindate="2014-7.22", enddate="2015.6.12"):
sql = "select close_price from stock_plate_hostory_data where stock_code = %s and `current_date` > %s and `current_date` < %s order by `current_date` "
datas = pool.selectMany(sql, (stockcode, begindate, enddate))
return datas
"""
获取区间内时间 2014-7.22 - 2015.6.12
2019.1.4 - 2019.4.19
"""
def get_stock_data(time):
# 查询最近2板股票
sql = "SELECT stock_code,stock_name,create_date,current_price from stock_limit_up_analyse WHERE continuous_time = " + time + " AND stock_name not like '%ST%' and create_date < '" + limit_date + "' "
pool = mysql_pool.sql_pool()
data = pool.selectMany(sql)
writebook = xlwt.Workbook() # 打开excel
test = writebook.add_sheet('two_time_stock')
# 定义时间格式
dateFormat = xlwt.XFStyle()
dateFormat.num_format_str = 'yyyy-mm-dd'
# 定义表头
test.write(0, 0, "股票代码")
test.write(0, 1, "股票名称")
test.write(0, 2, "涨停时间")
test.write(0, 3, "1进2竞价量")
test.write(0, 4, "2进3竞价量")
test.write(0, 5, "一字板（1是2否）")
test.write(0, 6, "开盘幅度")
test.write(0, 7, "最高幅度")
test.write(0, 8, "最低幅度")
test.write(0, 9, "收盘幅度")
test.write(0, 10, "3板成功（1是2否）")
index = 1
for stock in data:
try:
test.write(index, 0, stock["stock_code"])
test.write(index, 1, stock["stock_name"])
test.write(index, 2, stock["create_date"], dateFormat)
# 查询1进2 竞价量
two_volume = Tushare.get_tick_data(
stock["stock_code"][0:6], str(stock["create_date"])).iloc[0, 3]
test.write(index, 3, int(two_volume))
# 查询下个交易日
sql = "select create_date from stock_limit_up_analyse where create_date > '" + str(
stock["create_date"]
) + "' group by create_date order by create_date limit 1"
next_date = str(pool.selectOne(sql)["create_date"])
# 查询2进3 竞价信息
three_data = Tushare.get_tick_data(stock["stock_code"][0:6],
next_date)
# 查询2进3 竞价量
three_volume = three_data.iloc[0, 3]
test.write(index, 4, int(three_volume))
limit_up_price = scale_util.round_up(
(float(stock["current_price"]) * 1.1), 2)
# 获取2进3开盘价
three_open_price = three_data.iloc[0, 1]
if limit_up_price == three_open_price:
test.write(index, 5, 1)
else:
test.write(index, 5, 2)
# 获取2进3 价格信息
day_data = ts.get_hist_data(stock["stock_code"][0:6],
start=next_date,
end=next_date)
three_open_price = day_data.iloc[0, 0]
three_max_price = day_data.iloc[0, 1]
three_close_price = day_data.iloc[0, 2]
three_min_price = day_data.iloc[0, 3]
last_price = float(stock["current_price"])
test.write(
index, 6,
scale_util.round_up(
((three_open_price - last_price) / last_price), 2))
test.write(
index, 7,
scale_util.round_up(
((three_max_price - last_price) / last_price), 2))
test.write(
index, 8,
scale_util.round_up(
((three_min_price - last_price) / last_price), 2))
test.write(
index, 9,
scale_util.round_up(
((three_close_price - last_price) / last_price), 2))
if limit_up_price == three_close_price:
test.write(index, 10, 1)
else:
test.write(index, 10, 2)
except Exception as e:
print("异常信息---->", stock)
traceback.print_exc()
index += 1
writebook.save('stock.xls')
def __init__(self):
self.con = sql_pool().get_connection()
def __init__(self):
self.mysqlService = mysql_pool.sql_pool()
self.redis = redis_pool.RedisPool()
