def calculate_period_win(self, period, calculate_data):
period_field_map = {
3: "three_day",
7: 'seven_day',
30: 'one_month',
60: 'two_month'
}
if len(calculate_data) == 0:
return
for item in calculate_data:
# 首先查询出最近历史的数据
data_center = DataCenter.get_instance()
query_sql = "select close from stock_base_info where ts_code='"
query_sql = query_sql + item[0] + "' and trade_date > '"
query_sql = query_sql + item[1].strftime("%Y%m%d") + "' order by trade_date limit 70"
base_infos = data_center.common_query(query_sql)
for single_period in period:
if len(base_infos) >= single_period:
target_day_close = base_infos[single_period - 1][0]
in_price = item[2]
win_pct = (target_day_close - in_price) / target_day_close * 100
win_pct = win_pct
update_sql = "update " + self.table_name + " set "
update_sql = update_sql + period_field_map[single_period] + "='" + str(win_pct) + "'"
update_sql = update_sql + " where pk_period_verify='" + str(item[3]) + "'"
data_center.common_query(update_sql)
# FIXME -- 此处写死吧！！！！
if single_period == 60:
update_sql = "update " + self.table_name + " set finished=1 where pk_period_verify="
update_sql = update_sql + str(item[3]) + "'"
data_center.common_query(update_sql)
def tree_node_selected(self, selected, un_selected):
"""
树节点选中事件
:param selected:
:param un_selected:
:return:
"""
if selected.indexes() is None or len(selected.indexes()) == 0:
return
index = selected.indexes()[0]
pk_field = index.data(Category.IDRole)
# 查询一下是哪张表
table_name = None
pk_table_meta = None
is_redis = False
instance = DataCenter.get_instance()
query_table_name = "select table_name, ana_category.pk_tablemeta, is_redis from ana_category join table_meta " \
"on ana_category.pk_tablemeta=table_meta.pk_tablemeta where pk_category='"
query_table_name = query_table_name + str(pk_field) + "'"
ret_val = instance.common_query(query_table_name)
if len(ret_val) > 0:
table_name = ret_val[0][0]
pk_table_meta = ret_val[0][1]
is_redis = ret_val[0][2] == 'Y'
self.ana_result_type(table_name, pk_table_meta)
def init_data_from_db(self):
# 获取没有结束掉的股票
query_sql = "select ts_code, in_date, in_price, pk_period_verify from period_verify where finished='0'"
data_center = DataCenter.get_instance()
ret_data = data_center.common_query(query_sql)
# 首先计算一下各个阶段的收益
period = [3, 7, 30, 60]
self.calculate_period_win(period, ret_data)
super().init_data_from_db()
def fetch_daily_info():
data_center = DataCenter.get_instance()
last_record_day = data_center.common_query(
"select trade_date from stock_base_info order by trade_date desc limit 1"
)
last_record_day = last_record_day[0][0]
last_record_day = datetime.date(int(last_record_day[0:4]),
int(last_record_day[4:6]),
int(last_record_day[6:8]))
last_record_day += datetime.timedelta(days=1)
last_record_day = last_record_day.strftime("%Y%m%d")
print("last record day is " + last_record_day)
data_center.fetch_all_daily_info_until_now(trade_date=last_record_day)
def __init__(self, parent=None):
super().__init__(parent)
self.root_item: TreeItem = TreeItem()
# 做初始化逻辑
data_center = DataCenter.get_instance()
query_sql = "select * from ana_category"
ret_val = data_center.common_query(query_sql)
category_list = []
for item in ret_val:
temp_category = Category()
temp_category.id = item[3]
temp_category.parent_id = item[1]
temp_category.category_name = item[2]
category_list.append(temp_category)
self.root_item.init_with_data(category_list)
def init_data_from_db(self):
"""
通用的从数据库查询数据的结果处理类
:return:
"""
data_center = DataCenter.get_instance()
query_sql = "select table_name, is_redis from table_meta where pk_tablemeta='"
query_sql = query_sql + str(self.table_meta) + "'"
table_meta = data_center.common_query(query_sql)
if table_meta is None or len(table_meta) == 0:
return
self.db_table_name = table_meta[0][0]
is_redis = table_meta[0][1] == 'Y'
if is_redis:
return
query_sql = "select column_name, display_name, columntype from table_column where pk_tablemeta='"
query_sql = query_sql + str(
self.table_meta) + "' order by display_order"
table_infos = data_center.common_query(query_sql)
for item in table_infos:
self.db_columns.append(item[0])
self.display_head.append(item[1])
self.column_type.append(item[2])
if len(self.db_columns) == 0:
return
# 此处特殊处理一下ts_name字段
query_sql = "select stock_list.name as ts_name, "
for item in self.db_columns:
if item == 'ts_name':
continue
real_item = self.db_table_name + ".ts_code " if item == 'ts_code' else item
query_sql = query_sql + real_item + ","
query_sql = query_sql[:-1]
query_sql = query_sql + " from " + self.db_table_name
query_sql = query_sql + " left join stock_list on stock_list.ts_code=" + self.db_table_name + ".ts_code "
if self.filter is not None:
query_sql = query_sql + " " + self.filter
data = data_center.common_query(query_sql)
for item in data:
self.data.append(list(item))
# 处理一下数据类型问题
self.process_data_type()
def select_data(self):
"""
查询数据，更新表格
:return:
"""
query_sql = ""
for i in range(len(self.select_columns)):
query_sql = query_sql + self.select_columns[i] + ","
query_sql = query_sql + self.select_columns[len(self.select_columns) - 1]
query_sql = query_sql + " from " + self.table_name + " "
query_sql = query_sql + self.filter
data_center = DataCenter.get_instance()
query_rst = data_center.common_query(query_sql)
for item in query_rst:
self.table_data.append(item)
super().endResetModel()
def refresh_stock_list():
data_center = DataCenter.get_instance()
data_center.refresh_stock_list()