Fetching and Storing Tushare Daily Stocck Data with Python Pandas and MySQL

Fetch Tushare Daily Stock Data

Use Tushare's pro_api to get daily stock data. The daily method supports single/multiple stock codes or specific trade dates.

import tushare as ts
import pandas as pd

# Initialize API
api = ts.pro_api('your_tushare_token_here')

# Single stock data
df_single = api.daily(ts_code='000001.SZ', start_date='20180701', end_date='20180718')
# Multiple stocks
df_multi = api.daily(ts_code='000001.SZ,600000.SH', start_date='20180701', end_date='20180718')
# Query via query method
df_query = api.query('daily', ts_code='000001.SZ', start_date='20180701', end_date='20180718')
# Data for specific date
df_date = api.daily(trade_date='20180810')

Sample data:

     ts_code trade_date  open  high   low  close  pre_close  change  pct_chg        vol       amount
0  000001.SZ   20180718  8.75  8.85  8.69   8.70       8.72   -0.02   -0.230  525152.77   460697.377
1  000001.SZ   20180717  8.74  8.75  8.66   8.72       8.73   -0.01   -0.110  375356.33   326396.994
2  000001.SZ   20180716  8.85  8.90  8.69   8.73       8.88   -0.15   -1.690  689845.58   603427.713

MySQL Tible Structure

Create a daily_stock table to store data. Note change is a MySQL reserved word, use backticks. Adjust amount to DECIMAL(15,3) for large values.

CREATE TABLE `daily_stock` (
  `record_id` int(11) NOT NULL AUTO_INCREMENT,
  `stock_code` varchar(12) DEFAULT '',
  `trade_date` varchar(10) DEFAULT '',
  `open_price` decimal(10,2) DEFAULT '0.00',
  `high_price` decimal(10,2) DEFAULT '0.00',
  `low_price` decimal(10,2) DEFAULT '0.00',
  `close_price` decimal(10,2) DEFAULT '0.00',
  `prev_close` decimal(10,2) DEFAULT '0.00',
  `price_change` decimal(10,2) DEFAULT '0.00',
  `pct_change` decimal(10,2) DEFAULT '0.00',
  `volume` decimal(10,2) DEFAULT '0.00',
  `amount` decimal(15,3) DEFAULT '0.000',
  PRIMARY KEY (`record_id`),
  UNIQUE KEY `uni_trade_stock` (`trade_date`,`stock_code`) USING BTREE,
  KEY `idx_stock_code` (`stock_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Common PyMySQL Errors & Fixes

1. TypeError: not all arguments converted during string formatting: Missing placeholders in SQL query.
2. TypeError: must be real number, not str: Use %s for all placeholders (not %f or %d), regardless of data type.
3. Duplicate Data Handling: Use INSERT IGNORE or ON DUPLICATE KEY UPDATE to avoid duplicates.

Complete Code Example

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import pandas as pd
import tushare as ts
import pymysql.cursors

# Initialize Tushare API
TS_TOKEN = 'your_tushare_token_here'
api = ts.pro_api(TS_TOKEN)

# Database connection settings
DB_CONFIG = {
    'host': '127.0.0.1',
    'user': 'root',
    'password': 'your_password_here',
    'db': 'stock_data',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor
}

# Date range
START_DATE = '20200101'
END_DATE = '20200131'

def fetch_and_store_daily_data():
    connection = None
    try:
        # Connect to MySQL
        connection = pymysql.connect(**DB_CONFIG)
        cursor = connection.cursor()
        
        # Get all stock codes from stock_basic table
        cursor.execute("SELECT ts_code FROM stock_basic")
        stock_codes = cursor.fetchall()
        
        for stock in stock_codes:
            ts_code = stock['ts_code']
            print(f"Processing: {ts_code}")
            
            # Fetch daily data
            daily_df = api.daily(ts_code=ts_code, start_date=START_DATE, end_date=END_DATE)
            
            if daily_df.empty:
                continue
            
            # Insert data
            insert_sql = """
            INSERT IGNORE INTO daily_stock (
                stock_code, trade_date, open_price, high_price, low_price,
                close_price, prev_close, price_change, pct_change, volume, amount
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            
            for idx, row in daily_df.iterrows():
                values = (
                    row['ts_code'], row['trade_date'], row['open'], row['high'], row['low'],
                    row['close'], row['pre_close'], row['change'], row['pct_chg'], row['vol'], row['amount']
                )
                cursor.execute(insert_sql, values)
        
        # Commit changes
        connection.commit()
        print("Data stored successfully.")
        
    except Exception as e:
        print(f"Error: {str(e)}")
        if connection:
            connection.rollback()
    finally:
        if connection:
            cursor.close()
            connection.close()
            print("Database connection closed.")

if __name__ == "__main__":
    fetch_and_store_daily_data()