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)