Python 从 sina 股票数据接口读取数据,并保存到 MySQL 数据库
说明
从 sina 的数据接口获取数据,之后,保存到 MySql 数据库
文件:getDataFromSina.py
''' Created on 2018年2月11日 @author: Livon ''' import util import time for i in range( 1, 9999 ): print( time.strftime('%H:%M',time.localtime(time.time())) ) url = util.sinaStockUrl( pageNum = i ) stockData = util.sinaStockData( url = url ) if( stockData == 'null' ): break effect_rows = util.dbStore( str_stocks = stockData ) time.sleep(3) print( 'finished.' )
文件:util.py
''' Created on 2018年2月11日 @author: Livon ''' import urllib.request import re import pymysql import time def sinaStockUrl( pageNum ): print( 'pageNum : ' + str( pageNum )) rows = 10 url = 'http://vip.stock.finance.sina.com.cn/quotes_service/api/json_v2.php/Market_Center.getHQNodeData?' url += 'page=' + str( pageNum ) url += '&num=' + str( rows ) url += '&sort=symbol&asc=1&node=hs_a&symbol=&_s_r_a=init' print( url ) return url def sinaStockData( 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('gbk') # stockData = stockData.decode('gb2312') print( stockData ) return stockData 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 effect_rows def insertDb( properties, cursor ): 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 effect_row
相关推荐
yangkang 2020-11-09
lbyd0 2020-11-17
sushuanglei 2020-11-12
85477104 2020-11-17
KANSYOUKYOU 2020-11-16
wushengyong 2020-10-28
lizhengjava 2020-11-13
星月情缘 2020-11-13
huangxiaoyun00 2020-11-13
luyong0 2020-11-08
腾讯soso团队 2020-11-06
Apsaravod 2020-11-05
PeterChangyb 2020-11-05
gaobudong 2020-11-04
wwwjun 2020-11-02
gyunwh 2020-11-02
EchoYY 2020-10-31
dingyahui 2020-10-30