Python + MySQL如何獲取股票數據（歷史數據）

從互聯網（網易數據接口）獲取股票歷史數據，包括開盤價、最高價、最低價、收盤價等等.

環境

Python 3.6 MySQL 5.6.34

table: stock_list

記錄數：3592 條，以該表為基礎，製作 mission 清單。

table: stock_list_20190209（ mission list ）

從這個表裡，每次讀取一定數量的記錄，依次從互聯網上獲取。

<code>'''
《獲取股票歷史市值》
Created on 2018年2月12日
@author: Livon

# 讀取 股票列表，含代碼及 上市日期、終止上市日期

（1）列表
        每次執行前，手工新建一個當前日期的表，如果存在就刪除重建（可能是執行一個存儲過程）
        表名：stock_list_20180212 // 股票列表
        表字段：id, 股票代碼， 是否順利完成，獲取記錄數量
        每次取一條記錄，依次執行，中斷了，下次可以從中斷處繼續。

（2）
每條記錄，按指定日期範圍進行獲取

再建

# 從網易數據接口拉取市值數據

# 存入表 stock_his_marketCap 中

'''

import util
import urllib
import csv
import time
import datetimeUtil
 

from urllib import request

jobListTable = 'stock_list_20180209'

def p( msg ):
    print( '%s - %s' % ( datetimeUtil.getDatetime(), msg ))

def startJob():

    # 從數據池中讀取 n 記錄
    missionList = util.getMissionList( jobListTable )

    # 循環處理上述的 n 條記錄
    for mission in missionList:
#         for value in row:
#             print( value )
        # 根據記錄生成一條 url，一個 url 可以獲取幾千條日記錄
        url = util.genUrl( mission )
    #     url = 'http://quotes.money.163.com/service/chddata.html?code=1000001&start=19910401&end=19910409'
    #     url += '&fields=LCLOSE;TOPEN;HIGH;LOW;TCLOSE;CHG;PCHG;TURNOVER;VOTURNOVER;VATURNOVER;TCAP;MCAP'  

#         print( dt(), ' - ', url )
        p( 'url: %s' % url )

        # 從互聯網上獲取股票數據
        dataList = util.getStockDataList( url )

        if( dataList != None ):
            # 將數據保存在目標表：股票歷史數據表中
            insertedRows = util.insertTable( dataList )        
            # 更新 mission List 狀態標誌列
            util.updateJobList( jobListTable, mission, insertedRows )    
        else :
            p( 'csv 文件無數據。' )

        p('standby a moment for next mission（ you can terminal the program at this time）.')
        time.sleep(3)

# main
for i in range( 0, 2 ):
    p( 'startJob: %s' % str(i)  )
    startJob() 


# done
print( '= = = = = = = = = = = = = = = = = = = = = = ' )
p( 'all done !')
print( '= = = = = = = = = = = = = = = = = = = = = = ' )/<code>

code.

<code>'''
Created on 2018年2月11日

@author: Livon
'''

import urllib.request

import re
import pymysql

from urllib import request

# from stock.獲取股票歷史市值 import datetimeUtil
import datetimeUtil

def p( msg ):
    print( '%s - %s' % ( datetimeUtil.getDatetime(), msg ))

# 任務清單，每一次任務會領一份任務清單，清單中的第一項，是一個股票
# 任務：job - 大循環
# 目標：mission - 小目標
# missionList - 目標清單
# getMissionList
# 參數：tableName 表名
def getMissionList( tableName ):

    rowsCount = '5' ;    

    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root', db='stock', charset='utf8')
    # 創建遊標
#     cursor = conn.cursor()

    cursor = conn.cursor() 


#     sql='select * from '+ tableName +' where doneTime is NULL limit ' + rowsCount
    sql = 'select * from %s where doneTime is NULL limit %s' % ( tableName, rowsCount )
    cout = cursor.execute(sql)
#     print("數量： "+str(cout))
    rows = cursor.fetchall();

#     rows = conn.cursor().execute( sql ).fetchall()

#     for row in rows:
#         print("stockCode: "+str(row[0])+'  stockName： '+row[1]+"  startDate： "+ str(row[2]))

    cursor.close()
#     
#     try:
#         #獲取一個遊標
#         with conn.cursor() as cursor:
#             sql='select * from '+ tableName +' where doneTime is NULL limit 1'
#             cout=cursor.execute(sql)
#             print("數量： "+str(cout))
# 
#             for row in cursor.fetchall():
#                 #print('%s\\t%s\\t%s' %row)
#                 #注意int類型需要使用str函數轉義
#                 print("stockCode: "+str(row[0])+'  stockName： '+row[1]+"  startDate： "+ str(row[2]))
# #         conn.commit()
# 
#     finally:
#         print( 'done' )

    cursor.close()
    conn.close()

#     print( datetimeUtil.getDatetime(), ' - 任務清單裝載完畢！任務數量：', str( len( rows ))  )
#     print( '%s - %s missons loaded.' % ( datetimeUtil.getDatetime(), str( len( rows )) ) )
    p( '%s missons loaded.' % str( len( rows ) ))

    return rows

# 生成網址
def genUrl( row ):    

    stockCode = row[0]
    startDate = str( row[2] ).replace('-','')
#     endDate = ( row[3] == 'None' )? '': row[3]
#     endDate = ( row[3] == None ) and '' or row[3] 

    endDate = ( row[3] == None ) and row[3] or ''
    dataSource = row[4]
#     True and "Fire" or "Water"  
#     print( row[3] is 'None')
#     print( row[3] is None )
#     print( row[3] is '' )
#     print( type( row[3] ) )
#     print( type( row[3] ) is None )
#     print( type( row[3] ) is 'NoneType' )

    url = 'http://quotes.money.163.com/service/chddata.html?code=%s%s&start=%s&end=%s'
    url = url % ( dataSource, stockCode, startDate, endDate )
    url += '&fields=LCLOSE;TOPEN;LOW;HIGH;TCLOSE;CHG;PCHG;TURNOVER;VOTURNOVER;VATURNOVER;TCAP;MCAP'    

    return url

def getCsv( url ):    

#     csv = csv.decode('gbk')
#     
#     csv_str = str(csv)
#     lines = csv_str.split("\\\\n")
#     
#     print( len( lines))

    return ''

# 從互聯網上獲取數據
def getStockDataList( url ):

    print( datetimeUtil.getDatetime(), ' - ', '準備從互聯網獲取數據 ...' )

#     http = urllib3.PoolManager()
#     r = http.request('GET', url )
#     url="http://www.example.com/"
#     headers={"User-Agent":"Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1"}

#     try:
#         req = urllib3.request(url, headers )
# #         req=urllib2.Request(url,headers=headers)
#         response = urllib3.request2.urlopen(req)
#     except urllib3.exceptions,e:
#         print e.reason
    dataList = None 
    try:
        response = request.urlopen( url )

        csv = response.read()     
        csv = csv.decode('gbk')   

    #     csv = csv.decode('iso-8859-1')        
        csv_str = str(csv)

#         print( type( csv ))

        if( len( csv_str ) < 1 ):
            print( '獲取失敗' )

#         print( csv_str )
    #     lines = csv_str.split("\\\\n")    
        lines = csv_str.splitlines()
#         print( '行數：' + str( len( lines )))   

        dataList = [] 

        for line in lines:
#             print( line )
            dataRow = line.split(',')
#             print( dataRow[0] )
            dataObj = {}
            dataObj['日期']       = dataRow[0]
            dataObj['股票代碼']   = dataRow[1]
            dataObj['名稱']       = dataRow[2]
            dataObj['前收盤']     = dataRow[3]
            dataObj['開盤價']     = dataRow[4]
            dataObj['最高價']     = dataRow[5]
            dataObj['最低價']     = dataRow[6]
            dataObj['收盤價']     = dataRow[7]
            dataObj['漲跌額']     = dataRow[8]
            dataObj['漲跌幅']     = dataRow[9]
            dataObj['換手率']     = dataRow[10]
            dataObj['成交量']     = dataRow[11]
            dataObj['成交金額']   = dataRow[12]
            dataObj['總市值']     = dataRow[13]
            dataObj['流通市值']   = dataRow[14]
            dataList.append(dataObj)

    except Exception as e:
        print( e )

    return dataList

def netEaseUrl( stockCode, startDate, endDate ):

    url = 'http://quotes.money.163.com/service/chddata.html?code=%s&start=%s&end=%s&fields=' 

    url = url % ( stockCode, startDate, endDate )

    print( url )

    return url

def receiveCsv( url ):

    # http://www.cnblogs.com/sysu-blackbear/p/3629420.html
    stockDataResponse = urllib.request.urlopen( url )
    stockData = stockDataResponse.read()
    # stockData = stockDataResponse.decode('utf8')
    stockData = stockData.decode('gb2312')
    # stockData = stockData.decode('gb2312')

#     print( stockData )

    return stockData

'''

'''
def getStockList():

    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root', db='stock', charset='utf8')
    # 創建遊標
#     cursor = conn.cursor()

    try:
        #獲取一個遊標
        with conn.cursor() as cursor:
            sql='select * from stock_list'
            cout=cursor.execute(sql)
            print("數量： "+str(cout))

            for row in cursor.fetchall():
                #print('%s\\t%s\\t%s' %row)
                #注意int類型需要使用str函數轉義
                print("stockCode: "+str(row[0])+'  stockName： '+row[1]+"  startDate： "+ str(row[2]))
#         conn.commit()

    finally:
        cursor.close()
        conn.close()

def dbStore( str_stocks ):

    print( type( str_stocks )) 


    stocks = re.findall("\\[(.*?)\\]",str_stocks )
    stocks = re.findall("{(.*?)}",stocks[0])    

    # 創建連接
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root', db='stock', charset='utf8')
    # 創建遊標
    cursor = conn.cursor()

    for i in range( 0, len( stocks ) ):
        print( 'No.' + str(i))
        properties = stocks[i].split(',')            
#         print( properties )         
#         print( type( properties ))
#         effect_rows = insertDb( properties, cursor )
#         time.sleep(1)

    # 提交，不然無法保存新建或者修改的數據
    conn.commit()

    # 關閉遊標
    cursor.close()
    # 關閉連接
    conn.close()    

    return ''

def updateJobList( jobListTable, row, insertedRows ):

    # 創建連接
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root', db='stock', charset='utf8')
    # 創建遊標
    cursor = conn.cursor()
#     otherStyletime == "2013-10-10 23:40:00"

#     sql ='update ' + jobListTable + ' set doneTime = "' + otherStyleTime + '" where stockCode = "' + row[0] + '"'

    sql = 'update %s set doneTime = "%s" , receivedRows = %s where stockCode = "%s"' % ( jobListTable, datetimeUtil.getDatetime(), insertedRows, row[0] )

    print( datetimeUtil.getDatetime(), ' - 更新任務清單：', sql )

    effect_row = cursor.execute( sql )

    conn.commit()  
    # 關閉遊標 

    cursor.close()
    # 關閉連接
    conn.close()    

def insertTable( dataList ):

    # 創建連接
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root', db='stock', charset='utf8')
    # 創建遊標
    cursor = conn.cursor()

    insertedRows = 0

    for i in range( 1, len( dataList )):

        for (k,v) in  dataList[i].items(): 
#             print( "dataList[i][%s]=" % k,v )
#             print( v )
            if( v == 'None' ):
#                 print('Change to NULL')
                dataList[i][k] = 'NULL'

#         for data in dataList[i]:
#             print( data. )
#             data = ( data == None ) and data or 'NULL'        

        sql = 'INSERT INTO stock_his_data ( '
        sql += '日期,'
        sql += '股票代碼,'
        sql += '名稱,'
        sql += '前收盤,'
        sql += '開盤價,'
        sql += '最低價,'
        sql += '最高價,'
        sql += '收盤價,'
        sql += '漲跌額,'
        sql += '漲跌幅,'
        sql += '換手率,'
        sql += '成交量,'
        sql += '成交金額,'
        sql += '總市值,'
        sql += '流通市值'   
        sql += ' ) '    
        sql += 'VALUES'  
        sql += ' ( '   


        sql += '"' + dataList[i]['日期']    +  '",'
        sql += '"' + dataList[i]['股票代碼']+  '",'
        sql += '"' + dataList[i]['名稱']    +  '",'
        sql +=       dataList[i]['前收盤']  +  ' ,'
        sql +=       dataList[i]['開盤價']  +  ' ,'
        sql +=       dataList[i]['最低價']  +  ' ,'
        sql +=       dataList[i]['最高價']  +  ' ,'
        sql +=       dataList[i]['收盤價']  +  ' ,'
        sql +=       dataList[i]['漲跌額']  +  ' ,'
        sql +=       dataList[i]['漲跌幅']  +  ' ,'
        sql +=       dataList[i]['換手率']  +  ' ,'
        sql +=       dataList[i]['成交量']  +  ' ,'
        sql +=       dataList[i]['成交金額']+  ' ,'
        sql +=       dataList[i]['總市值']  +  ' ,'
        sql +=       dataList[i]['流通市值']
        sql += ' ) '

#         print( sql )

        effect_row = cursor.execute( sql )

        if( effect_row > 0 ):
            insertedRows += 1

    print( datetimeUtil.getDatetime(), ' - 數據數量：', insertedRows )
#     
    # 提交，不然無法保存新建或者修改的數據
    conn.commit()  
    # 關閉遊標
    cursor.close()
    # 關閉連接
    conn.close()    

#     arr_values = []
#     arr_columns = []
#     
#     for j in range( 0, len( properties) ):
#         
# #         print( 'propertie['+ str(j)+']: ' + properties[j] )
# #         key_value = properties[j].split(':')
# #         print( key_value[0] + ' -> ' + key_value[1] )
#         key = properties[j][:properties[j].find(':')] 

#         value = properties[j][properties[j].find(':')+1:]
#         value = value.replace('"', '')
# #         print( key + ' -> ' + value )
# #         sql += '"' + value + '"'
#         arr_columns.append( '`' + key + '`' )
# #         arr_columns.append( key )
#         arr_values.append( '"' + value + '"' )
#         
#     sql = 'insert into stock_sina '
#     sql = sql + ' ( ' + ','.join( arr_columns ) + ' ) VALUES ( ' + ','.join( arr_values ) + ' ) '

#     print( sql )

#     effect_row = cursor.execute( sql )

    return insertedRows/<code>

code: datetime Util

<code>'''
Created on 2018年2月14日

@author: Livon
'''

import time

def getDatetime():

    timeArray = time.localtime( time.time() )
    otherStyleTime = time.strftime("%Y-%m-%d %H:%M:%S", timeArray)

    return otherStyleTime/<code>

最終得到的數據：table: stock_his_data

記錄總數：9045612（九百萬）

最後小編幫助大家整理了一套python教程，下面展示了部分，希望也能幫助對編程感興趣，想做數據分析，人工智能、爬蟲或者希望從事編程開發的小夥伴，畢竟python工資也還可以，如果能幫到你請點贊、點贊、點贊。

以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值.

如果你喜歡python，並覺得這篇文章對你有益的話，麻煩多多點贊關注支持！！！！

閱讀更多 程序猿老旭 的文章

關鍵字: 歷史數據 網易 MySQL