def stat_all_batch(tmp_datetime):
datetime_str = (tmp_datetime).strftime("%Y-%m-%d")
datetime_int = (tmp_datetime).strftime("%Y%m%d")
print("datetime_str:", datetime_str)
print("datetime_int:", datetime_int)
try:
# 删除老数据。
del_sql = " DELETE FROM `stock_data`.`guess_indicators_daily` WHERE `date`= %s " % datetime_int
common.insert(del_sql)
except Exception as e:
print("error :", e)
sql_count = """
SELECT count(1) FROM stock_data.ts_today_all WHERE `date` = %s and `trade` > 0 and `open` > 0 and trade <= 20
and `code` not like %s and `name` not like %s
"""
# 修改逻辑，增加中小板块计算。 中小板：002，创业板：300 。and `code` not like %s and `code` not like %s and `name` not like %s
# count = common.select_count(sql_count, params=[datetime_int, '002%', '300%', '%st%'])
count = common.select_count(sql_count,
params=[datetime_int, '300%', '%st%'])
print("count :", count)
batch_size = 100
end = int(math.ceil(float(count) / batch_size) * batch_size)
print(end)
for i in range(0, end, batch_size):
print("loop :", i)
# 查询今日满足股票数据。剔除数据：创业板股票数据，中小板股票数据，所有st股票
# #`code` not like '002%' and `code` not like '300%' and `name` not like '%st%'
sql_1 = """
SELECT `date`, `code`, `name`, `changepercent`, `trade`, `open`, `high`, `low`,
`settlement`, `volume`, `turnoverratio`, `amount`, `per`, `pb`, `mktcap`, `nmc`
FROM stock_data.ts_today_all WHERE `date` = %s and `trade` > 0 and `open` > 0 and trade <= 20
and `code` not like %s and `name` not like %s limit %s , %s
"""
print(sql_1)
# data = pd.read_sql(sql=sql_1, con=common.engine(), params=[datetime_int, '002%', '300%', '%st%', i, batch_size])
data = pd.read_sql(
sql=sql_1,
con=common.engine(),
params=[datetime_int, '300%', '%st%', i, batch_size])
data = data.drop_duplicates(subset="code", keep="last")
print("########data[trade]########:", len(data))
stat_index_all(data, i)
def stat_all_batch(tmp_datetime):
datetime_str = (tmp_datetime).strftime("%Y-%m-%d")
datetime_int = (tmp_datetime).strftime("%Y%m%d")
print("datetime_str:", datetime_str)
print("datetime_int:", datetime_int)
try:
# 删除老数据。
del_sql = " DELETE FROM `stock_data`.`%s` WHERE `date`= %s " % (
table_name, datetime_int)
print("del_sql:", del_sql)
common.insert(del_sql)
except Exception as e:
print("error :", e)
sql_count = """
SELECT count(1) FROM stock_data.ts_today_all WHERE `date` = %s and `trade` > 0 and `open` > 0 and trade <= 20
and `code` not like %s and `name` not like %s
"""
# 修改逻辑，增加中小板块计算。 中小板：002，创业板：300 。and `code` not like %s and `code` not like %s and `name` not like %s
# count = common.select_count(sql_count, params=[datetime_int, '002%', '300%', '%st%'])
count = common.select_count(sql_count,
params=[datetime_int, '300%', '%st%'])
print("count :", count)
batch_size = 100
end = int(math.ceil(float(count) / batch_size) * batch_size)
print(end)
# for i in range(0, end, batch_size):
for i in range(0, end, batch_size):
print("loop :", i)
# 查询今日满足股票数据。剔除数据：创业板股票数据，中小板股票数据，所有st股票
# #`code` not like '002%' and `code` not like '300%' and `name` not like '%st%'
sql_1 = """
SELECT `date`, `code`, `name`, `changepercent`, `trade`, `open`, `high`, `low`,
`settlement`, `volume`, `turnoverratio`, `amount`, `per`, `pb`, `mktcap`, `nmc`
FROM stock_data.ts_today_all WHERE `date` = %s and `trade` > 0 and `open` > 0 and trade <= 20
and `code` not like %s and `name` not like %s limit %s , %s
"""
print(sql_1)
# data = pd.read_sql(sql=sql_1, con=common.engine(), params=[datetime_int, '002%', '300%', '%st%', i, batch_size])
data = pd.read_sql(
sql=sql_1,
con=common.engine(),
params=[datetime_int, '300%', '%st%', i, batch_size])
data = data.drop_duplicates(subset="code", keep="last")
print("########data[trade]########:", len(data))
# 使用 trade 填充数据
stock_sklearn = pd.DataFrame(
{
"date": data["date"],
"code": data["code"],
"next_close": data["trade"],
"sklearn_score": data["trade"]
},
index=data.index.values)
print(stock_sklearn.head())
stock_sklearn_apply = stock_sklearn.apply(apply_sklearn,
axis=1) # , axis=1)
# 重命名
del stock_sklearn_apply["date"] # 合并前删除 date 字段。
# 合并数据
data_new = pd.merge(data, stock_sklearn_apply, on=['code'], how='left')
# for index, row in data.iterrows():
# next_stock, score = stat_index_all(row, i)
# print(next_stock, score)
data_new["next_close"] = data_new["next_close"].round(2) # 数据保留4位小数
data_new["sklearn_score"] = data_new["sklearn_score"].round(
2) # 数据保留2位小数
data_new["trade_float32"] = data["trade"].astype('float32', copy=False)
data_new["up_rate"] = (
data_new["next_close"] -
data_new["trade_float32"]) * 100 / data_new["trade_float32"]
data_new["up_rate"] = data_new["up_rate"].round(2) # 数据保留2位小数
del data_new["trade_float32"]
try:
common.insert_db(data_new, table_name, False, "`date`,`code`")
print("insert_db")
except Exception as e:
print("error :", e)
# 重命名
del data_new["name"]
print(data_new)