目标
从互联网（网易数据接口）获取股票历史数据，包括开盘价、最高价、最低价、收盘价等等
环境
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 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: 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 ))
# 任务清单，每一次任务会领一份任务清单，清单中的第一项，是一个股票
# 任务：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: 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最终得到的数据：table: stock_his_data
记录总数：9045612（九百万）
mysql
python
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。