–– 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 = curs
def 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 values
def 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 = 0
line_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 e
count += 1
print(f"还剩 {line_count - count} 支股票...")