说明:python实现读取股票数据保存到sql server数据库。
具体步骤:
1.安装python模块
pip install requests
pip install BeautifulSoup
2.建表
//配置表
create table gpconfig
(
id int primary key identity(1,1),
gpname nvarchar(200),
gpid nvarchar(10),
gpurl nvarchar(2000),
addtime datetime default(getdate())
)
//本事例是从163网站读取，以下是配置表例子
insert into gpconfig(gpname,gpid,gpurl)
select '平安银行','000001','http://quotes.money.163.com/trade/lsjysj_000001.html'
//数据采集表
create table gpprice
(
id int primary key identity(1,1),
gpname nvarchar(200),
gpid nvarchar(10),
gpdate datetime,
gpkpprice decimal(20,4),
gpmaxprice decimal(20,4),
gpminprice decimal(20,4),
gpspprice decimal(20,4),
uplow decimal(20,4),
uplowpercent nvarchar(20),
dealcount bigint,
dealmoney decimal(20,4),
zhenfupercent nvarchar(20),
hspercent nvarchar(20),
addtime datetime default(getdate())
)
3.python语句
# coding: UTF-8
import requests
import ssl
from requests.auth import HTTPBasicAuth
import re
import sys
from BeautifulSoup import BeautifulSoup
import pymssql
s_host = '172.1.1.1'
s_user = 'test'
s_password = '123456'
s_database = 'gp'
def __init__(self):
self.conn=self.getConnection()
def getConnection(self):
#print self.host,self.username,self.userpwd,self.db
conn='a'
try:
conn=pymssql.connect(
host = s_host,
user = s_user,
password = s_password,
database = s_database)
except Exception,e:
print "connect database failed,%s" % e
#error().Errinsert(s_host,'conn','conn 1.79,1433 error','get day perflogs')
return conn
def query(self,sqlstring):
cursor=self.conn.cursor()
try:
cursor*ex.e**cute(sqlstring)
returnData=cursor.fetchall()
except Exception,e:
print 'sql error'
#error().Errinsert('192.168.1.79','select','get 1.79 day perflogs failed','get day perflogs')
cursor.close()
self.conn.close()
return returnData
def insert(self,insertstring,tup):
cursor=self.conn.cursor()
#print 'a'
try:
cursor*ex.e**cute(insertstring,tup)
except Exception,e:
print 'ssss %s' % e
#cursor.close()
self.conn.commit()
perlists=mssqldb().query("select gpname,gpid,gpurl from dbo.gpconfig where gpstatus=1")
for perlist in perlists:
gpname=perlist[0]
gpid=perlist[1]
gpurl=perlist[2]
response =requests.get(gpurl)
response.encoding="utf-8"
html=response.text
bs_xml = BeautifulSoup(html)
div=bs_xml.findAll('table',{'class':'table_bg001 border_box limit_sale'})
trs=div[0]
tr_arr=trs.findAll("tr")
for tr in tr_arr:
tds=tr.findAll("td")
pattern = re.compile(r'<[^>]+>',re.S)
td = pattern.sub('',str(tds))
td=td.replace(',','')
td=td.replace(' ',',')
#print td
tdall=td.replace('[','').replace(']','')
#tdallss=td.replace(' ','')
#print gpname,tdall
#for ss in tdall:
if tdall!='':
u1,u2,u3,u4,u5,u6,u7,u8,u9,u10,u11=tdall.split(',',-1)
#print u1,u2,u3,u4,u5,u6,u7,u8,u9,u10,u11
tup=(gpname,gpid,u1,u2,u3,u4,u5,u6,u7,u8,u9,u10,u11)
insertstring='insert into gpprice(gpname,gpid,gpdate,gpkpprice,gpmaxprice,gpminprice,gpspprice,uplow,uplowpercent,dealcount,dealmoney,zhenfupercent,hspercent) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
uexist=mssqldb().query("select gpid from dbo.gpprice where gpid='%s'and gpdate='%s'"%(gpid,u1))
#print type(uexist)
if not uexist:
perlists=mssqldb().insert(insertstring,tup)
#td=tr.findAll("td")