使用pymysql库，将tushare股票信息保存入本地MySQL数据库

1、前言

由于tushare存在积分权限限制，高频读取tushare数据容易挤占服务器带宽，因此对于常用的tushare数据，我们可以在第一次请求后，将DataFrame保存入我们本地的MySQL数据库，方便后续的使用。😁

2、具体步骤

导入所用库：

import tushare as ts
import pymysql
import numpy as np

构建tushareMethod类，将所有涉及tushare数据请求的方法放入这个类中：

class tushareMethod:
    pro = None

    def set_Token(self):
        ts.set_token('填入你自己的Token')
        self.pro = ts.pro_api()

    # 调用trade_cal接口，设置起始日期和终止日期
    def get_Trade_cal(self,start,end): 
        df = self.pro.query('trade_cal', start_date=start, end_date=end, fields='cal_date,is_open,pretrade_date')
        return df

新增DataFrame转为List的函数，方便后续数据入库：

def dataFrame_To_List(df):
    dataset = np.array(df)
    datalist = dataset.tolist()
    return datalist

构建mysqlMethod类，将所有mysql方法放入本类中：

class mysqlMethod:
    db = None
    cursor = None

    def connect_To_Mysql(self, user_, password_, db_="mystockdatas"):
        # 打开数据库连接
        self.db = pymysql.connect(host='localhost', port=3306, user=user_, passwd=password_, db=db_ ,charset='utf8mb4')
        self.cursor = self.db.cursor()

    def insert_Datas(self,table,keys_list,values_list):
        suc_count = 0
        err_count = 0
        sql = "INSERT INTO {} ({}) VALUES (%s,%s,%s)".format(table, keys_list)
        for i in values_list:
            try:
                self.cursor.execute(sql,i)
                self.db.commit()
                suc_count += 1

            except Exception as e:
                print("Error:{}".format(e))
                self.db.rollback()
                err_count += 1
        print("Finnish! Successed:{}, Failed:{}".format(suc_count,err_count))

    def disconnect_to_Mysql(self):
        self.cursor.close()
        self.db.close()

操作实现：

if __name__ == '__main__':

    # 实例化对象
    Obj_tushare = tushareMethod()
    Obj_mysql = mysqlMethod()

    # 设置Token
    Obj_tushare.set_Token()

    # 从tushare获取交易日数据
    df1 = Obj_tushare.get_Trade_cal("20160101","20180101")

    # 转换为list
    data = dataFrame_To_List(df1)

    # 连接数据库
    Obj_mysql.connect_To_Mysql("root","123456")

    # 新增数据
    Obj_mysql.insert_Datas("trade_cal", "cal_date,is_open,pretrade_date", data)

    # 断开数据库连接
    Obj_mysql.disconnect_to_Mysql()

3、整体代码

import tushare as ts
import pymysql
import numpy as np


def dataFrame_To_List(df):
    dataset = np.array(df)
    datalist = dataset.tolist()
    return datalist

class tushareMethod:
    pro = None

    def set_Token(self):
        ts.set_token('你自己的Token')
        self.pro = ts.pro_api()

    def get_Trade_cal(self,start,end): # 调用trade_cal接口，设置起始日期和终止日期
        df = self.pro.query('trade_cal', start_date=start, end_date=end, fields='cal_date,is_open,pretrade_date')
        return df


class mysqlMethod:
    db = None
    cursor = None

    def connect_To_Mysql(self, user_, password_, db_="mystockdatas"):
        # 打开数据库连接
        self.db = pymysql.connect(host='localhost', port=3306, user=user_, passwd=password_, db=db_ ,charset='utf8mb4')
        self.cursor = self.db.cursor()

    def insert_Datas(self,table,keys_list,values_list):
        suc_count = 0
        err_count = 0
        sql = "INSERT INTO {} ({}) VALUES (%s,%s,%s)".format(table, keys_list)
        for i in values_list:
            try:
                self.cursor.execute(sql,i)
                self.db.commit()
                suc_count += 1

            except Exception as e:
                print("Error:{}".format(e))
                self.db.rollback()
                err_count += 1
        print("Finnish! Successed:{}, Failed:{}".format(suc_count,err_count))

    def disconnect_to_Mysql(self):
        self.cursor.close()
        self.db.close()

if __name__ == '__main__':

    # 实例化对象
    Obj_tushare = tushareMethod()
    Obj_mysql = mysqlMethod()

    # 设置Token
    Obj_tushare.set_Token()

    # 从tushare获取交易日数据
    df1 = Obj_tushare.get_Trade_cal("20160101","20180101")

    # 转换为list
    data = dataFrame_To_List(df1)

    # 连接数据库
    Obj_mysql.connect_To_Mysql("root","123456")

    # 新增数据
    Obj_mysql.insert_Datas("trade_cal", "cal_date,is_open,pretrade_date", data)

    # 断开数据库连接
    Obj_mysql.disconnect_to_Mysql()