tushare接口拉取存储数据示例

# db_utils.py

import tushare as ts
import pymysql

class DatabaseUtils:
    # 数据库连接信息
    _host = 'localhost'       # 替换为你的MySQL主机IP
    _user = 'root'   # 替换为你的MySQL用户名
    _password = 'root'  # 替换为你的MySQL密码
    _database = 'stock_cursor'  # 替换为你的MySQL数据库名
    _charset = 'utf8mb4'

    # Tushare API token
    _tushare_token = '替换为你的Tushare API token'  # 替换为你的Tushare API token

    @classmethod
    def init_tushare_api(cls):
        """
        初始化Tushare API
        :return: Tushare pro API对象
        """
        return ts.pro_api(cls._tushare_token)

    @classmethod
    def connect_to_mysql(cls):
        """
        连接到MySQL数据库
        :return: MySQL连接对象和游标
        """
        conn = pymysql.connect(
            host=cls._host,
            user=cls._user,
            password=cls._password,
            database=cls._database,
            charset=cls._charset
        )
        cursor = conn.cursor()
        return conn, cursor 
```
```


```
```
# daily_basic.py

from utils.db_utils import DatabaseUtils
import pandas as pd
import time

# 初始化Tushare API
pro = DatabaseUtils.init_tushare_api()

# 连接到MySQL数据库
conn, cursor = DatabaseUtils.connect_to_mysql()

# 创建表结构（复杂日线行情）
cursor.execute('''
CREATE TABLE IF NOT EXISTS stock_daily_basic (
  ts_code varchar(20) NOT NULL COMMENT 'TS股票代码',
  trade_date date NOT NULL COMMENT '交易日期',
  close decimal(10,2) DEFAULT NULL COMMENT '当日收盘价',
  turnover_rate decimal(10,2) DEFAULT NULL COMMENT '换手率（%）',
  turnover_rate_f decimal(10,2) DEFAULT NULL COMMENT '换手率（自由流通股）',
  volume_ratio decimal(10,2) DEFAULT NULL COMMENT '量比',
  pe decimal(10,2) DEFAULT NULL COMMENT '市盈率（总市值/净利润， 亏损的PE为空）',
  pe_ttm decimal(10,2) DEFAULT NULL COMMENT '市盈率（TTM，亏损的PE为空）',
  pb decimal(10,2) DEFAULT NULL COMMENT '市净率（总市值/净资产）',
  ps decimal(10,2) DEFAULT NULL COMMENT '市销率',
  ps_ttm decimal(10,2) DEFAULT NULL COMMENT '市销率（TTM）',
  dv_ratio decimal(10,2) DEFAULT NULL COMMENT '股息率 （%）',
  dv_ttm decimal(10,2) DEFAULT NULL COMMENT '股息率（TTM）（%）',
  total_share decimal(20,2) DEFAULT NULL COMMENT '总股本 （万股）',
  float_share decimal(20,2) DEFAULT NULL COMMENT '流通股本 （万股）',
  free_share decimal(20,2) DEFAULT NULL COMMENT '自由流通股本 （万）',
  total_mv decimal(20,2) DEFAULT NULL COMMENT '总市值 （万元）',
  circ_mv decimal(20,2) DEFAULT NULL COMMENT '流通市值（万元）',
  PRIMARY KEY (ts_code,trade_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='股票日线基本数据表';
''')

# cursor.execute('''truncate table stock_daily_basic;''')

conn.commit()

# 获取交易日期列表
cursor.execute('''
select DATE_FORMAT(cal_date, '%Y%m%d') from stock_trade_calendar 
    where is_open = 1 
    and cal_date >= '2025-07-22' 
    and cal_date <= '2025-08-31'
    order by cal_date
''')
trade_date_list = cursor.fetchall()

batch_size = 5
data_list = []

for i in range(0, len(trade_date_list), batch_size):
    batch_trade_date_list = trade_date_list[i:i + batch_size]
    for trade_date_tuple in batch_trade_date_list:
        trade_date = trade_date_tuple[0]
        print(f"正在获取 {trade_date} 的基本面数据...")
        
        # 获取指定日期的所有股票基本面数据
        df = pro.daily_basic(trade_date=trade_date, fields=[
            "ts_code", "trade_date", "close", "turnover_rate",
            "turnover_rate_f", "volume_ratio", "pe", "pe_ttm",
            "pb", "ps", "ps_ttm", "dv_ratio", "dv_ttm",
            "total_share", "float_share", "free_share", "total_mv",
            "circ_mv"
        ])
        
        if not df.empty:
            data_list.append(df)

    # 合并当前批次的数据
    if data_list:
        combined_data = pd.concat(data_list, ignore_index=True)
        time.sleep(0.1)

        # 将NaN值替换为None（这会在MySQL中转换为NULL）
        combined_data = combined_data.replace({pd.NA: None, float('nan'): None})

        # 插入数据
        for index, row in combined_data.iterrows():
            cursor.execute('''
            INSERT INTO stock_daily_basic (ts_code, trade_date, close, turnover_rate, turnover_rate_f, volume_ratio, pe,
                                            pe_ttm, pb, ps, ps_ttm, dv_ratio, dv_ttm, total_share, float_share,
                                            free_share, total_mv, circ_mv)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
            ''', (row['ts_code'], row['trade_date'], row['close'], row['turnover_rate'], row['turnover_rate_f'],
                  row['volume_ratio'], row['pe'], row['pe_ttm'], row['pb'], row['ps'], row['ps_ttm'],
                  row['dv_ratio'], row['dv_ttm'], row['total_share'], row['float_share'], row['free_share'],
                  row['total_mv'], row['circ_mv']))

        # 提交事务
        conn.commit()

        # 清空当前批次的数据列表，为下一个批次做准备
        data_list.clear()

# 关闭连接
cursor.close()
conn.close()
```
```