博客

使用前提：

• 安装Python
• 安装pandas
• lxml也是必须的，正常情况下安装了Anaconda后无须单独安装，如果没有可执行：pip install lxml

建议安装Anaconda，一次安装包括了Python环境和全部依赖包，减少问题出现的几率。

• 安装tushare并更新

pip install tushare

pip install tushare –upgrade

import tushare as ts
import MySQLdb


''' 
date：日期
open：开盘价
high：最高价
close：收盘价
low：最低价
volume：成交量
price_change：价格变动
p_change：涨跌幅
ma5：5日均价
ma10：10日均价
ma20:20日均价
v_ma5:5日均量
v_ma10:10日均量
v_ma20:20日均量
'''
def parse(code, codeName, cursor, num_temp):
    
    #sql语句
    sql = "insert into stock_info (stockId,code,stockName,stockDate,stockOpen,stockHigh,close,stockLow,stockVolume,price_change,p_change,ma5,ma10,ma20,v_ma5,v_ma10,v_ma20) values {SQL} ;"
    sql_value = "({StockId},'{Code}','{StockName}','{StockDate}','{StockOpen}','{StockHigh}','{StockClose}','{StockLow}','{StockVolume}','{StockPrice_change}','{StockP_change}','{StockMa5}','{StockMa10}','{StockMa20}','{StockV_ma5}','{StockV_ma10}','{StockV_ma20}')"
    #df存储当前代码所有的股票数据
    df = ts.get_hist_data(code)  
    print("-----------------------------------------")
    #print(df)
    #根据股票数据的字段取数据
    stockOpen = df[u'open']
    stockHigh=df[u'high']
    close = df[u'close']
    stockLow= df[u'low']
    stockVolume= df[u'volume']
    price_change= df[u'price_change']
    p_change= df[u'p_change']
    ma5= df[u'ma5']
    ma10= df[u'ma10']
    ma20 = df[u'ma20']
    v_ma5= df[u'v_ma5']
    v_ma10= df[u'v_ma10']
    v_ma20= df[u'v_ma20']
    #idx:表示当前代码的股票数据条数
    idx = len(ma20)
    temp = ""
    sum_temp = num_temp + idx

    print(sum_temp)
    print(num_temp)
    print(idx)
    
    for x in range(sum_temp, num_temp, -1):   
        idx -=1
        stockId=x
        code_val=code
        stockName_val=codeName
        stockDate_val = close.keys()[idx]
        stockOpen_val = stockOpen[idx]
        stockHigh_val = stockHigh[idx]
        close_val = close[idx]
        stockLow_val = stockLow[idx]
        stockVolume_val = stockVolume[idx]
        price_change_val = price_change[idx]
        p_change_val = p_change[idx]
        ma5_val =  ma5[idx]
        ma10_val = ma10[idx]
        ma20_val = ma20[idx]
        v_ma5_val =  v_ma5[idx]
        v_ma10_val =  v_ma10[idx]
        v_ma20_val =  v_ma20[idx]

        if idx > 0:
            temp = temp + sql_value.format(StockId=stockId,Code=code_val,StockName=stockName_val,StockDate=stockDate_val,StockOpen=stockOpen_val,StockHigh=stockHigh_val,StockClose=close_val,StockLow=stockLow_val,StockVolume=stockVolume_val,StockPrice_change=price_change_val,StockP_change=p_change_val,StockMa5=ma5_val,StockMa10=ma10_val,StockMa20=ma20_val,StockV_ma5=v_ma5_val,StockV_ma10=v_ma10_val,StockV_ma20=v_ma20_val)
            temp = temp + ","
        elif idx <= 0:
            temp = temp + sql_value.format(StockId=stockId,Code=code_val,StockName=stockName_val,StockDate=stockDate_val,StockOpen=stockOpen_val,StockHigh=stockHigh_val,StockClose=close_val,StockLow=stockLow_val,StockVolume=stockVolume_val,StockPrice_change=price_change_val,StockP_change=p_change_val,StockMa5=ma5_val,StockMa10=ma10_val,StockMa20=ma20_val,StockV_ma5=v_ma5_val,StockV_ma10=v_ma10_val,StockV_ma20=v_ma20_val)
            
        
    sql = sql.format(SQL=temp)
    #print(sql)
    cursor.execute(sql)
        
    # 使用 fetchone() 方法获取一条数据
    data = cursor.fetchone()

    return sum_temp

   
if __name__ == '__main__':
    # 打开数据库连接
    db = MySQLdb.connect("127.0.0.1", "root", "root", "stock", charset='utf8' )

    # 使用cursor()方法获取操作游标 
    cursor = db.cursor()
    #STOCK存储公司名称和代码，需要添加其他股票，则在后面追加
    STOCK = {'600000' :"浦发银行",'000001':"平安银行",'600519':"贵州茅台",'600007':"中国国贸",'600009':"上海机场"}
    #StockLen=len(STOCK)
    num_temp = 0

    '''
        code:股票代码
        codeName:公司名称
        sum_temp:股票数据记录累积和
        num_temp:股票数据记录
    '''
    for code, codeName in STOCK.items():
        num_temp = parse(code, codeName, cursor, num_temp)
    db.commit()
 
    # 关闭数据库连接
    db.close()