def Build_txt_file(value, txt_file):
"""将数据转换为测试用数据格式"""
header, stock = Fetch_stock_data.Connecting_database(value)
stock.reverse()
str_text = []
pre_average_list = [0, 0, 0, 0, 0]
i = 0
j = 0
line_num = 0
for (t, trade_date, open, close, low, high, vol, change, pct_chg, pre_close, amount) in stock:
if j < 5:
pre_average_list[j] = amount * 10 / vol
j += 1
continue
if i == 5:
i = 0
if line_num == len(stock) - j - 1:
txt_file.writelines(" ")
pre_average = pre_average_list[i]
if line_num == 0:
text = f"{format(open, '.2f')}\t{format(close, '.2f')}\t{format(low, '.2f')}\t{format(high, '.2f')}\t{format(vol, '.2f')}\t{format(amount, '.2f')}\t{format(change, '.2f')}\t{format(pre_average, '.2f')} "
else:
text = f"{format(open, '.2f')}\t{format(close, '.2f')}\t{format(low, '.2f')}\t{format(high, '.2f')}\t{format(vol, '.2f')}\t{format(amount, '.2f')}\t{format(change, '.2f')}\t{format(pre_average, '.2f')}\n "
pre_average_list[i] = amount * 10 / vol
str_text.append(text)
i += 1
line_num += 1
str_text.reverse()
for text in str_text:
txt_file.write(text)
if not testing(txt_file):
logger.logerror(f"{value}不符合数据格式！")
txt_file.close()
def web_spider(self): # 网络爬虫tushare接口
try:
df = ts.daily(ts_code=self.code,
start_date=self.start_data,
end_date=self.end_data) # 爬取数据--dataframe格式
# df.to_csv(self.csv_name) # 转换为csv文件
return df # 返回数据
except Exception as e:
# logger.error(e, exc_info=True)
logger.logerror(e) # 将出错信息写入log内
def add_data(self):
self.line_num = len(self.df) - 1
# 将avg_list, avg_list_5day, avg_list_10day, avg_list_20day, avg_list_60day 进行初始化
avg_list, avg_list_5day, avg_list_10day, avg_list_20day, avg_list_60day = [
0 for i in range(self.line_num + 1)
], [0 for i in range(self.line_num + 1)
], [0 for i in range(self.line_num + 1)
], [0 for i in range(self.line_num + 1)
], [0 for i in range(self.line_num + 1)]
# f_day 五日平均线，ten_day 十日平均线，t_day 二十日平均线，s_day 六十日平均线
i, f_day, ten_day, t_day, s_day = 0, 0, 0, 0, 0
# 循环调用character()函数-读取每行数据
while self.line_num >= 0:
value_list = self.character()
vol = value_list[9] # 交易量
amount = value_list[10] # 交易额
avg_list[i] = float(format(amount * 10 / vol, '.3f')) # 保留三位小数
# 计算移动平均线
if i < 5:
f_day += avg_list[i]
else:
f_day = f_day + avg_list[i] - avg_list[i - 5]
avg_list_5day[i] = float(format(f_day / 5, '.3f'))
if i < 10:
ten_day += avg_list[i]
else:
ten_day = ten_day + avg_list[i] - avg_list[i - 10]
avg_list_10day[i] = float(format(ten_day / 10, '.2f'))
if i < 20:
t_day += avg_list[i]
else:
t_day = t_day + avg_list[i] - avg_list[i - 20]
avg_list_20day[i] = float(format(t_day / 20, '.3f'))
if i < 60:
s_day += avg_list[i]
else:
s_day = s_day + avg_list[i] - avg_list[i - 60]
avg_list_60day[i] = float(format(s_day / 60, '.3f'))
try:
# 插入数据库
Sql.Sql_execution(
f"""insert into stock ({tushare["Column"]}) values """ +
f"(\'{value_list[0]}\',\'{value_list[1]}\',\'{value_list[2]}\',\'{value_list[3]}\',\'{value_list[4]}\',\'{value_list[5]}\',\'{value_list[6]}\',\'{value_list[7]}\',\'{value_list[8]}\',\'{value_list[9]}\',\'{value_list[10]}\',\'{avg_list[i]}\',\'{avg_list_5day[i]}\',\'{avg_list_10day[i]}\',\'{avg_list_20day[i]}\',\'{avg_list_60day[i]}\')"
)
except Exception:
# 出错信息
logger.logerror(f"{value_list[0]}-{value_list[1]}未被添加！")
self.line_num -= 1
i += 1
print(f"The data has been added to the stock database")
return i
def Avg_price(value):
header, stock = Fetch_stock_data.Connecting_database(value)
avg_list, avg_list_5day, avg_list_10day, avg_list_20day, avg_list_60day = [0 for i in range(len(stock))], [0 for i
in range(
len(stock))], [0 for i in range(len(stock))], [0 for i in range(len(stock))], [0 for i in range(len(stock))]
i, f_day, ten_day, t_day, s_day = 0, 0, 0, 0, 0
sql = "SELECT \"temp\".* FROM \"temp\" ORDER BY \"temp\".trade_date ASC"
curs.execute(sql)
for (t, trade_date, open, close, low, high, vol, change, pct_chg, pre_close, amount) in stock:
avg_list[i] = float(format(amount * 10 / vol, '.3f'))
if i < 5:
# avg_list_5day[i] = ''
f_day += avg_list[i]
else:
f_day = f_day + avg_list[i] - avg_list[i - 5]
avg_list_5day[i] = float(format((f_day) / 5, '.3f'))
if i < 10:
# avg_list_10day[i] = ''
ten_day += avg_list[i]
else:
ten_day = ten_day + avg_list[i] - avg_list[i - 10]
avg_list_10day[i] = float(format(ten_day / 10, '.2f'))
if i < 20:
# avg_list_20day[i] = ''
t_day += avg_list[i]
else:
t_day = t_day + avg_list[i] - avg_list[i - 20]
avg_list_20day[i] = float(format((t_day) / 20, '.3f'))
if i < 60:
# avg_list_60day[i] = ''
s_day += avg_list[i]
else:
s_day = s_day + avg_list[i] - avg_list[i - 60]
avg_list_60day[i] = float(format((s_day) / 60, '.3f'))
try:
sql = f"""update stock set avgprice = {avg_list[i]} , avg_price_5day = {avg_list_5day[i]} , avg_price_10day = {avg_list_10day[i]} , avg_price_20day = {avg_list_20day[i]},avg_price_60day = {avg_list_60day[i]} where trade_date = \'{trade_date}\' and ts_code = \'{t}\'; """
print(sql)
curs.execute(sql)
except Exception:
logger.logerror(f"{t}-{trade_date}未被添加！")
i += 1
connection.commit()
logger.loginfo("连接数据库")
Sql = DataServer_Sqlite3("stock") # 连接数据库
logger.loginfo("连接数据库成功")
Sql.Empty_database("stock") # 清空数据库
logger.loginfo("清空数据库")
Sql.Zero_Id() # 将ID归零
logger.loginfo("将ID归零")
for key in stock_code:
try:
column = tushare["column"]
t1.set_token(tushare["token"]) # token值
ts = t1.pro_api() # 初始化pro接口
logger.loginfo("初始化pro接口成功")
input_code = stock_code[key]
sd, ed = "20181231", "20201231" # 起始日期，结束日期
logger.loginfo(f"抓取{key}股票数据")
start = time.perf_counter() # 开始时间
df = Append_Data(input_code, sd, ed).web_spider() # dataframe格式股票数据
line_num = Sql_Data_Handle(df, Sql.connection, Sql.curs).add_data()
end = time.perf_counter() # 结束时间
# 记录用时
logger.loginfo(f"添加{key}股票数据到数据库--成功,用时{start - end}")
# 记录添加的数据条数
logger.loginfo(f"{key} 共{line_num}条数据已添加到数据库！")
except Exception as e:
logger.logerror(e)
count += 1
print(f"还剩{line_count - count}支股票...")
Sql.connection.close() # 关闭数据库