从互联网获取股票数据（历史数据，Python + MySQL）
目标
从互联网（网易数据接口）获取股票历史数据，包括开盘价、最高价、最低价、收盘价等等
环境
Python 3.6
MySQL 5.6.34
table: stock_list
记录数：3592 条，以该表为基础，制作 mission 清单。
table: stock_list_20190209（ mission list ）
从这个表里，每次读取一定数量的记录，依次从互联网上获取。
code: getStockData.py
'''
《获取股票历史市值》
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 datetimeUtilfrom urllib import requestjobListTable = '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: util.py
'''
Created on 2018年2月11日@author: Livon
'''import urllib.requestimport re
import pymysqlfrom urllib import request# from stock.获取股票历史市值 import datetimeUtil
import datetimeUtildef 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 ' + rowsCountsql = '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 urldef 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.reasondataList = 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 dataListdef 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 urldef receiveCsv( url ):# http://www.cnblogs.com/sysu-blackbear/p/3629420.htmlstockDataResponse = 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 = 0for 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 += 1print( 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 insertedRowscode: datetimeUtil.py
'''
Created on 2018年2月14日@author: Livon
'''import timedef getDatetime():timeArray = time.localtime( time.time() )otherStyleTime = time.strftime("%Y-%m-%d %H:%M:%S", timeArray)return otherStyleTime最终得到的数据：table: stock_his_data
记录总数：9045612（九百万）
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！