-- coding: utf-8 --

"""此处为用于爬取和存储到数据库的Tushare.py"""
import tushare as t1
import time # 用以计时
from config import * # 此库为配置库
from SQLserver import * # 此库为作者所写
class Fetch_Data():

"""code股票代码 sd开始日期 ed完结日期"""def __init__(self, code, start_data, end_data):    self.code = code    self.sd = start_data    self.ed = end_data    # self.csv_name = "csv_data/" + csv_name + ".csv"def web_spider(self):  # 爬虫tushare接口    try:        df = ts.daily(ts_code=self.code, start_date=self.sd, end_date=self.ed)  # 爬取数据--dataframe格局        # daily办法所获取的数据ts_code,trade_date,open,high,low,close,pre_close,change,pct_chg,vol,amount,avgprice        # df.to_csv(self.csv_name) # 转换为csv文件        return df  # 返回数据    except Exception as e:        print(e) # 打印报错信息

class Sql_Data_Handle():

def __init__(self, df, connect, curs):    self.df = df    self.connect = connect    self.curs = cursdef character(self):    # df = pandas.read_csv(self.csv_name, encoding='gbk') # 用pandas库读取csv文件    line_num = self.line_num    # pandas的iloc函数读取每行对应数据    ts_code = self.df.iloc[line_num]["ts_code"]    trade_date = self.df.iloc[line_num]["trade_date"]    open = self.df.iloc[line_num]["open"]    high = self.df.iloc[line_num]["high"]    low = self.df.iloc[line_num]["low"]    close = self.df.iloc[line_num]["close"]    pre_close = self.df.iloc[line_num]["pre_close"]    change = self.df.iloc[line_num]["change"]    pct_chg = self.df.iloc[line_num]["pct_chg"]    vol = self.df.iloc[line_num]["vol"]    amount = self.df.iloc[line_num]["amount"]    # 将数据转换为元祖tuple格局    values = (        ts_code, trade_date, float(open), float(high), float(low), float(close), float(pre_close),        float(change), float(pct_chg), float(vol), float(amount))    return valuesdef add_data(self):    self.line_num = len(self.df) - 1    # 循环调用character()函数-读取每行数据    while self.line_num >= 0:        value_list = self.character()        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]}\')")        except Exception:            # 出错信息            print(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

if name == '__main__':

count = 0line_count = len(Stock_list)print("连贯数据库")Sql = DataServer_Sqlite3("stock")  # 连贯数据库stock_code = Stock_list# Sql.Empty_database()  # 清空数据库# print("清空数据库")# Sql.Zero_Id()  # 将ID归零# print("将ID归零")

for key in stock_code:

try:    column = tushare["column"] # config中的tushare字典    t1.set_token(tushare["token"])  # token值    ts = t1.pro_api()  # 初始化pro接口    print("初始化pro接口胜利")    input_code = stock_code[key]    sd, ed = "20181231", "20201231"  # 起始日期，完结日期    print(f"抓取{key}[货币代码](https://www.gendan5.com/currencycode.html)股票数据")    start = time.perf_counter()  # 开始工夫    df = Fetch_Data(input_code, sd, ed).web_spider()  # dataframe格局股票数据    line_num = Sql_Data_Handle(df, Sql.connection, Sql.curs).add_data()    end = time.perf_counter()  # 完结工夫    # 记录用时    print(f"增加{key}股票数据到数据库--胜利,用时{start - end}")    # 记录增加的数据条数    print(f"{key} 共{line_num}条数据已增加到数据库！")except Exception as e:    print ecount += 1print(f"还剩{line_count - count}支股票...")