国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

從互聯(lián)網(wǎng)獲取股票數(shù)據(jù)（歷史數(shù)據(jù)，Python + MySQL）

weapon / 1384人閱讀

摘要：目標(biāo)從互聯(lián)網(wǎng)網(wǎng)易數(shù)據(jù)接口獲取股票歷史數(shù)據(jù)，包括開盤價最高價最低價收盤價等等環(huán)境記錄數(shù)條，以該表為基礎(chǔ)，制作清單。

目標(biāo)

從互聯(lián)網(wǎng)（網(wǎng)易數(shù)據(jù)接口）獲取股票歷史數(shù)據(jù)，包括開盤價、最高價、最低價、收盤價等等

環(huán)境

Python 3.6
MySQL 5.6.34

table: stock_list

記錄數(shù)：3592 條，以該表為基礎(chǔ)，制作 mission 清單。

table: stock_list_20190209（ mission list ）

從這個表里，每次讀取一定數(shù)量的記錄，依次從互聯(lián)網(wǎng)上獲取。

code: getStockData.py 
"""
《獲取股票歷史市值》
Created on 2018年2月12日
@author: Livon

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

（1）列表
        每次執(zhí)行前，手工新建一個當(dāng)前日期的表，如果存在就刪除重建（可能是執(zhí)行一個存儲過程）
        表名：stock_list_20180212 // 股票列表
        表字段：id, 股票代碼， 是否順利完成，獲取記錄數(shù)量
        每次取一條記錄，依次執(zhí)行，中斷了，下次可以從中斷處繼續(xù)。
        
（2）
每條記錄，按指定日期范圍進(jìn)行獲取

再建

# 從網(wǎng)易數(shù)據(jù)接口拉取市值數(shù)據(jù)

# 存入表 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():

    # 從數(shù)據(jù)池中讀取 n 記錄
    missionList = util.getMissionList( jobListTable )
    
    # 循環(huán)處理上述的 n 條記錄
    for mission in missionList:
#         for value in row:
#             print( value )
        # 根據(jù)記錄生成一條 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 )
        
        # 從互聯(lián)網(wǎng)上獲取股票數(shù)據(jù)
        dataList = util.getStockDataList( url )
        
        if( dataList != None ):
            # 將數(shù)據(jù)保存在目標(biāo)表：股票歷史數(shù)據(jù)表中
            insertedRows = util.insertTable( dataList )        
            # 更新 mission List 狀態(tài)標(biāo)志列
            util.updateJobList( jobListTable, mission, insertedRows )    
        else :
            p( "csv 文件無數(shù)據(jù)。" )
        
        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: util.py 
"""
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 ))



# 任務(wù)清單，每一次任務(wù)會領(lǐng)一份任務(wù)清單，清單中的第一項，是一個股票
# 任務(wù)：job - 大循環(huán)
# 目標(biāo)：mission - 小目標(biāo)
# missionList - 目標(biāo)清單
# getMissionList
# 參數(shù)：tableName 表名
def getMissionList( tableName ):
    
    rowsCount = "5" ;    
    
    conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="root", db="stock", charset="utf8")
    # 創(chuàng)建游標(biāo)
#     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("數(shù)量： "+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:
#         #獲取一個游標(biāo)
#         with conn.cursor() as cursor:
#             sql="select * from "+ tableName +" where doneTime is NULL limit 1"
#             cout=cursor.execute(sql)
#             print("數(shù)量： "+str(cout))
# 
#             for row in cursor.fetchall():
#                 #print("%s	%s	%s" %row)
#                 #注意int類型需要使用str函數(shù)轉(zhuǎn)義
#                 print("stockCode: "+str(row[0])+"  stockName： "+row[1]+"  startDate： "+ str(row[2]))
# #         conn.commit()
# 
#     finally:
#         print( "done" )
        
    
    cursor.close()
    conn.close()
    
#     print( datetimeUtil.getDatetime(), " - 任務(wù)清單裝載完畢！任務(wù)數(shù)量：", str( len( rows ))  )
#     print( "%s - %s missons loaded." % ( datetimeUtil.getDatetime(), str( len( rows )) ) )
    p( "%s missons loaded." % str( len( rows ) ))
    
    return rows




# 生成網(wǎng)址
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("
")
#     
#     print( len( lines))
    
    return ""

# 從互聯(lián)網(wǎng)上獲取數(shù)據(jù)
def getStockDataList( url ):
    
    print( datetimeUtil.getDatetime(), " - ", "準(zhǔn)備從互聯(lián)網(wǎng)獲取數(shù)據(jù) ..." )
    
#     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("
")    
        lines = csv_str.splitlines()
#         print( "行數(shù)：" + 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")
    # 創(chuàng)建游標(biāo)
#     cursor = conn.cursor()
    
    try:
        #獲取一個游標(biāo)
        with conn.cursor() as cursor:
            sql="select * from stock_list"
            cout=cursor.execute(sql)
            print("數(shù)量： "+str(cout))

            for row in cursor.fetchall():
                #print("%s	%s	%s" %row)
                #注意int類型需要使用str函數(shù)轉(zhuǎn)義
                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])    
    
    # 創(chuàng)建連接
    conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="root", db="stock", charset="utf8")
    # 創(chuàng)建游標(biāo)
    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)
        
    # 提交，不然無法保存新建或者修改的數(shù)據(jù)
    conn.commit()
  
    # 關(guān)閉游標(biāo)
    cursor.close()
    # 關(guān)閉連接
    conn.close()    
    
    return ""


def updateJobList( jobListTable, row, insertedRows ):
    
    
    # 創(chuàng)建連接
    conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="root", db="stock", charset="utf8")
    # 創(chuàng)建游標(biāo)
    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(), " - 更新任務(wù)清單：", sql )
    
    effect_row = cursor.execute( sql )
    
    conn.commit()  
    # 關(guān)閉游標(biāo)
    cursor.close()
    # 關(guān)閉連接
    conn.close()    
    
    
    
def insertTable( dataList ):
    
    # 創(chuàng)建連接
    conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="root", db="stock", charset="utf8")
    # 創(chuàng)建游標(biāo)
    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(), " - 數(shù)據(jù)數(shù)量：", insertedRows )
#     
    # 提交，不然無法保存新建或者修改的數(shù)據(jù)
    conn.commit()  
    # 關(guān)閉游標(biāo)
    cursor.close()
    # 關(guān)閉連接
    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: datetimeUtil.py 
"""
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
最終得到的數(shù)據(jù)：table: stock_his_data

記錄總數(shù)：9045612（九百萬）

文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請注明本文地址：http://specialneedsforspecialkids.com/yun/41411.html

相關(guān)文章

  • Python財經(jīng)數(shù)據(jù)接口包TuShare的使用

    摘要：本例以數(shù)據(jù)庫為代表，展示將獲取到的股票數(shù)據(jù)存入數(shù)據(jù)庫的方法其他類型數(shù)據(jù)庫請參考官網(wǎng)文檔的部分。存入數(shù)據(jù)庫追加數(shù)據(jù)到現(xiàn)有表財經(jīng)數(shù)據(jù)接口包的使用存入財經(jīng)數(shù)據(jù)接口包的使用 安裝TuShare 方式1：pip install tushare 方式2：訪問https://pypi.python.org/pypi/tushare/下載安裝 方式3：將源代碼下載到本地python setup.py ...

    20171112 評論0 收藏0

  • 磨刀霍霍：爬爬爬爬爬爬蟲爬起來~

    摘要：目標(biāo)很簡單，因為我想要爬一下證券化率，然后可視化輸出結(jié)果。證券化率的基礎(chǔ)就是上市公司的總市值，對于證券化率其實還蠻多說法的，比如雪球的這篇文。我們可以利用這個回調(diào)函數(shù)來顯示當(dāng)前的下載進(jìn)度。 寫在前面的叨叨 折騰了這么久，我終于在喝完一聽快樂肥宅水后下定決心來學(xué)習(xí)寫爬蟲了。目標(biāo)很簡單，因為我想要爬一下證券化率，然后可視化輸出結(jié)果。證券化率的基礎(chǔ)就是上市公司的總市值，對于證券化率其實還蠻多...

    Allen 評論0 收藏0

  • python爬蟲實戰(zhàn)二——股票數(shù)據(jù)定向爬蟲

    摘要：選取方法打開網(wǎng)頁，查看源代碼，搜索網(wǎng)頁的股票價格數(shù)據(jù)是否存在于源代碼中。將上述的代碼封裝成一個函數(shù)，對東方財富網(wǎng)頁面解析的完整代碼如下所示接下來是獲得百度股票網(wǎng)鏈接描述單只股票的信息。 功能簡介 目標(biāo)： 獲取上交所和深交所所有股票的名稱和交易信息。輸出： 保存到文件中。技術(shù)路線： requests---bs4--re語言：python3.5 說明 網(wǎng)站選擇原則： 股票信息靜態(tài)存在于ht...

    icattlecoder 評論0 收藏0

發(fā)表評論

0條評論

最新活動
閱讀需要支付1元查看
<