python股票接口_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