tushare踩坑经验--数据存入本地mysql

踩坑

作为一个第一次接触tushare的小新手，在csdn关于tushare的文章里研究了蛮久，本文是总结了详细的踩坑记录，好在想明白后，存其他数据就轻而易举了，在此也感谢tushare团队，能让我有机会学到在学校学不到的知识。
id：475661
tushare链接：https://tushare.pro/register?reg=475661

from time import time
import datetime
import pandas as pd
from sqlalchemy.sql.expression import update
import tushare as ts
from sqlalchemy import String,Column,Float,Text, engine
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base


# pro = ts.pro_api('1d18be2431d516faeef26b53b348ad1260b568026bbbd444c5e221e7')
Base = declarative_base()
class Stock_base(Base):
    #股票基本信息
    __tablename__ = "test_sb"
    ts_code = Column(String(10), primary_key=True)  # TS代码
    name = Column(String(10))           # 股票名称
    area = Column(String(4))            # 所在地域
    industry = Column(String(4))        # 所属行业
    fullname = Column(String(30))       # 股票全称
    market = Column(String(3))          # 市场类型 （主板/中小板/创业板）
    list_date = Column(String(8))       # 上市日期
    #更新所有股票基础信息
class Operate_data():
    def __init__(self):
        self.engine = create_engine('mysql://root:240240@127.0.0.1:3306/test?charset=utf8mb4')
        self.pro = ts.pro_api('1d18be2431d516faeef26b53b348ad1260b568026bbbd444c5e221e7')

    def get_end_dt(self):
        time_temp = datetime.datetime.now() - datetime.timedelta(days=1)
        end_dt = time_temp.strftime('%Y%m%d')  
        return end_dt

     #创建所有表
    def create_table(self):
        Base.metadata.create_all(self.engine)


    def Update_stock_base(self,engine,pro,retry_count,pause):
        
        def get_stock_base(pro,retry_count=4,pause=5):
            #获取股票基础信息   retry_count应该指循环次数，pause用来指睡眠的时间
            frame = pd.DataFrame()
            for _ in range(retry_count):
                try:
                    df = pro.stock_basic(**{"ts_code": "","name": "","exchange": "","market": "","is_hs": "","list_status": "L","limit": "","offset": ""},
                        fields=["ts_code","name","area","industry","market","list_date","fullname"])
                except:
                    time.sleep(pause)
                else:
                    frame = pd.concat([frame,df])
                    break
            # print(frame)
            frame.reset_index(drop=True,inplace=True)
            return frame
                #重置索引时，会将旧索引添加为列，drop指是否删除旧索引的列
                #inplace 是否修改数据
            

        data = get_stock_base(pro,retry_count,pause)
        data.to_sql('test_sb',engine,if_exists='replace',index=False)
        # print(data)
            # stock_base = pro.stock_basic(**{"ts_code": "","name": "","exchange": "","market": "","is_hs": "","list_status": "L","limit": "","offset": ""},
            #     fields=["ts_code","name","area","industry","market","list_date","fullname"])
                
            #     print(tscode)
        
    def updata_all(self,last_date):
        #定时执行

        #now 获取现在的时间
        # timedelta类是用来计算二个datetime对象的差值的.此类中包含如下属性： 
            # 1、days:天数 
            # 2、microseconds：微秒数(>=0 并且 <1秒） 
            # 3、seconds：秒数(>=0 并且 <1天）         
        time_temp = datetime.datetime.now() - datetime.timedelta(days=1)
        #strftime 将时间类型格式化为字符串输出
        end_dt = time_temp.strftime('%Y%m%d')
        #strptime 按照特定时间格式将字符串转换(解析)为时间类型元组
        time_temp = datetime.datetime.strptime(last_data,"%Y%m%d") + datetime.timedelta(days=1)
        start_dt = time_temp.strftime("%Y%m%d")
        if self.end_dt == end_dt:
            # self.create_table()
            self.Update_stock_base(self.engine,self.pro,4,5)