(python股票实时监控)(python股票量化投资)

说明: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'

class mssqldb():

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.execute(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.execute(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")