股票交易日定时爬取上交所/深交所所有股票行情数据存储到数据库

一、该项目主要分以下三步组成：

配置数据库信息
编写爬虫脚本
配置Jenkins定时任务
查看采集结果

二、详细过程

1.配置数据库信息

建表语句, 以其中部分字段为例：

CREATE TABLE `stockmarket` (
  `date` varchar(12) NOT NULL DEFAULT '' COMMENT '时间',
  `stockCode` varchar(100) NOT NULL DEFAULT '' COMMENT '股票代码',
  `stockName` varchar(100) DEFAULT NULL COMMENT '股票名字',
  `close` decimal(19,2) DEFAULT NULL COMMENT '闭市价',
  `high` decimal(19,2) DEFAULT NULL COMMENT '最高',
  `low` decimal(19,2) DEFAULT NULL COMMENT '最低',
  `amplitudeRatio` decimal(19,2) DEFAULT NULL COMMENT '振幅',
  `turnoverRatio` decimal(19,2) DEFAULT NULL COMMENT '换手率',
  `preClose` decimal(19,2) DEFAULT NULL COMMENT '昨收',
  `open` decimal(19,2) DEFAULT NULL COMMENT '开盘价',
  PRIMARY KEY (`date`,`stockCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

配置json数据到.json文件, 用于读取配置信息，进行数据库连接

"stockMarket":{
      "host":"localhost",
      "port":3326,
      "user":"root",
      "password":"password",
      "database":"stockMarket",
      "charset":"utf8"
    }

2.脚本编写

涉及到的python库

import re,pymysql,json,time,requests

代码编写

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Author : Torre Yang Edit with Python3.6
# @Email  : klyweiwei@163.com
# @Time   : 2018/6/28 10:50
# 定时 爬取每日股票行情数据;
# 股票数据内容：
import getSoup
import pymysql
import os
import re
import json
import requestsimport connect_dataBase
import time

# db连接
connectDB = connect_dataBase.ConnectDatabase()
get_conf = connectDB.get_conf('databases_conf.json')
conn, cur = connectDB.connect_db(get_conf["stockMarket"]["host"], get_conf["stockMarket"]["user"],
                     get_conf["stockMarket"]["password"], get_conf["stockMarket"]["database"], get_conf["stockMarket"]["port"])

# 第一步, 通过东方财富网  获取 上海/深圳 所有股票的 股票代码, 存储到list中
url = 'http://quote.eastmoney.com/stocklist.html#'
soup = getSoup.getSoup(url)
uls = soup.select('div#quotesearch li')
# 正则表达式获取所有的股票代码
re1 = re.compile(r'href="http://quote.eastmoney.com/(.+?).html"')
stockCodes = re1.findall(str(uls))
# print(stockCodes)

# 第二步, 将股票代码加入到 股票搜索 的网址中
stockValues = []
for stockCode in stockCodes:
    # url = 'https://gupiao.baidu.com/stock/'+stockCode+'.html'
    url = 'https://gupiao.baidu.com/api/rails/stockbasicbatch?from=pc&os_ver=1&cuid=xxx&vv=100&format=json&stock_code='+stockCode+''
    # print(url)
    # url = 'https://gupiao.baidu.com/api/rails/stockbasicbatch?from=pc&os_ver=1&cuid=xxx&vv=100&format=json&stock_code=sh201003'
    response = requests.get(url)
    response.raise_for_status()
    res = response.content
    try:
        JsonDatas = json.loads(res, encoding='utf-8')
    except:
        print('解析为空')
    datas = JsonDatas['data']
   )
    for data in datas:
        # 添加当天日期(交易日)
        date = time.strftime("%Y-%m-%d", time.localtime())
        stockCode = data['stockCode']
        stockName = data['stockName']
        close = data['close']
        high = data['high']
        low = data['low']
        amplitudeRatio = data['amplitudeRatio']
        turnoverRatio = data['turnoverRatio']
        preClose = data['preClose']
        open = data['open']
        sql = 'insert into stockmarket(date,stockCode,stockName,close,high,low,amplitudeRatio,turnoverRatio,preClose,open)values("'+str(date)+'","'+str(stockCode)+'","'+str(stockName)+'","'+str(close)+'","'+str(high)+'","'+str(low)+'","'+str(amplitudeRatio)+'","'+str(turnoverRatio)+'","'+str(preClose)+'","'+str(open)+'")'
        print(sql)
        if 'None' in sql:
            print('jump this data')
        else:
            try:
                connectDB.get_fetch(conn, cur, sql)
            except:
                print('数据异常, 跳过')

print('采集数据完毕')