Python3爬虫抓取东方财富网股票数据并实现MySQL数据库存储

1. 环境：

  • windows10
  • python3
  • mysql 5.7

2.Python爬虫抓取网页数据并保存到本地数据文件中

开启mysql数据库
首先导入需要的数据模块，定义函数：

# -*- coding: utf-8 -*-
"""
Created on Fri Dec 29 15:54:40 2017

@author: JayMo
"""

import urllib
import re
import pandas as pd
import pymysql
import os

#爬虫抓取网页函数
def getHtml(url):
    html = urllib.request.urlopen(url).read()
    html = html.decode('gbk')
    return html

#抓取网页股票代码函数
def getStackCode(html):
    s = r'<li><a target="_blank" href="http://quote.eastmoney.com/\S\S(.*?).html">'
    pat = re.compile(s)
    print(pat)
    code = pat.findall(html)
    print(code)
    return code

真正干活的代码块：

Url = 'http://quote.eastmoney.com/stocklist.html'#东方财富网股票数据连接地址
filepath = 'F:\\data\\'#定义数据文件保存路径
#实施抓取
code = getStackCode(getHtml(Url)) 
#获取所有股票代码（以6开头的，应该是沪市数据）集合
CodeList = []
for item in code:
    if item[0]=='6':
        CodeList.append(item)
#抓取数据并保存到本地csv文件
for code in CodeList:
    print('正在获取股票%s数据'%code)
    url = 'http://quotes.money.163.com/service/chddata.html?code=0'+code+\
        '&end=20171228&fields=TCLOSE;HIGH;LOW;TOPEN;LCLOSE;CHG;PCHG;TURNOVER;VOTURNOVER;VATURNOVER;TCAP;MCAP'
    urllib.request.urlretrieve(url, filepath+code+'.csv')

修改url中的end的值，可爬取截止日期不同的股票数据。先看下抓取的结果。CodeList是抓取到的所有股票代码的集合，我们看到它共包含1416条元素，即1416支股票数据。因为股票太多，所以只抓取以6开头的。
抓取到的股票数据会分别存储到csv文件中，一只股票数据一个文件。理论上会有1416个csv文件，和股票代码数一致。


图片.png

打开一个本地数据文件看一下抓取的数据长什么样子：


图片.png

3. 将数据存储到MySQL数据库

首先建立本地数据库连接：

#数据库名称和密码
name = 'xxxx'
password = 'xxxx'  #替换为自己的用户名和密码
#建立本地数据库连接(需要先开启数据库服务)
db = pymysql.connect('localhost', name, password, charset='utf8')
cursor = db.cursor()

其中，数据库名称(name)和密码(password)是安装MySQL时设置的。

创建数据库，专门用来存储本次股票数据：

#创建数据库stockDataBase，如果存在则跳过
sqlSentence1 = "create database if not exists stockDataBase"
cursor.execute(sqlSentence1)#选择使用当前数据库
sqlSentence2 = "use stockDataBase;"
cursor.execute(sqlSentence2)

在首次运行的时候一般都会正常创建数据库，但如果再次运行，因数据库已经存在，那么跳过创建，继续往下执行。创建好数据库后，选择使用刚刚创建的数据库，在该数据库中存储数据表。

下面看具体的存储代码：

#获取本地文件列
fileList = os.listdir(filepath)
#依次对每个数据文件进行存储
for fileName in fileList:
    data = pd.read_csv(filepath+fileName, encoding="gbk")
   #创建数据表，如果数据表已经存在，会跳过继续执行下面的步骤print('创建数据表stock_%s'% fileName[0:6])
    sqlSentence3 = "create table if not exists stock_%s" % fileName[0:6] + "(日期 date, 股票代码 VARCHAR(10), 名称 VARCHAR(10), 收盘价 float,\
                       最高价 float, 最低价 float, 开盘价 float, 前收盘 float, 涨跌额 float, 涨跌幅 float, 换手率 float,\
                       成交量 bigint, 成交金额 bigint, 总市值 bigint, 流通市值 bigint)"
    cursor.execute(sqlSentence3)#迭代读取表中每行数据，依次存储（整表存储还没尝试过）
    print('正在存储stock_%s'% fileName[0:6])
    length = len(data)
    for i in range(0, length):
        record = tuple(data.loc[i])
        #插入数据语句
        try:
            sqlSentence4 = "insert into stock_%s" % fileName[0:6] + "(日期, 股票代码, 名称, 收盘价, 最高价, 最低价, 开盘价,\
                               前收盘, 涨跌额, 涨跌幅, 换手率, 成交量, 成交金额, 总市值, 流通市值) \
                               values ('%s',%s','%s',%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" % record
            #获取的表中数据很乱，包含缺失值、Nnone、none等，插入数据库需要处理成空值
            sqlSentence4 = sqlSentence4.replace('nan','null').replace('None','null').replace('none','null') 
            cursor.execute(sqlSentence4)
        except:#如果以上插入过程出错，跳过这条数据记录，继续往下进行
            break

结果：


图片.png
图片.png

图片.png

代码并不复杂，只要注意其中几个点就好了。

1.逻辑层次：
包含两层循环，外层循环是对股票代码的循环，内层循环是对当前股票的每一条记录的循环。说白了就是按照股票一支一支的存储，对于每一支股票，按照它每日的记录一条一条的存储。是不是很简单很暴力？是的！完全没有考虑更加优化的方式。

2.读取本地数据文件的编码方式：
使用'gbk'编码，默认应该是'utf8'，但好像不支持中文。

3.创建数据表：
同样的，如果数据表已经存在（判断是否存在if not exists），则跳过创建，继续执行下面的步骤（会继续存储）。有个问题是，有可能数据重复存储，可以选择跳过存储或者只存储最新数据。我在这里没有考虑太多额外的处理。其次，指定字段格式，后边几个字段成交量、成交金额、总市值、流通市值，因为数据较大，选择使用bigint类型。

4.没有指定数据表的主键：
最初是打算使用日期作为主键的，后来发现获取到的数据中竟然包含重复日期的数据，这就打破了主键的唯一性，会出bug的，然后我也没有多去思考数据文件的内容，也不会进一步使用这些个数据，也就图省事直接不设置主键了。

5.构造sql语句sqlSentence4：
该过程实现中，直接把股票数据记录tuple了，然后使用字符串格式化（%操作符）。造成的精度问题没有多考虑，不知道会不会产生什么样的影响。%s有的上边带着' '，是为了在sql语句中表示字符串。其中有一个%s'，只有右边有单引号，匹配的是股票代码，只有一边单引号，这是因为从数据文件中读取到的字符串已经包含了左边的单引号，左边不需要再添加了。这是数据文件格式的问题，为了表示文本形式预先使用了单引号。

6.异常值处理：
文本文件中，包含有空值、None、none等不标准化数据，这里全部替换为null了，即数据库的空值。

完成MySQL数据库数据存储后，需要关闭数据库连接：

#关闭游标，提交，关闭数据库连接
cursor.close()
db.commit()
db.close()

不关闭数据库连接，就无法在MySQL端进行数据库的查询等操作，相当于数据库被占用。

4.MySQL数据库查询

db = pymysql.connect('localhost', name, password, 'stockDataBase', charset='utf8')
cursor = db.cursor()
#查询数据库并打印内容
cursor.execute('select * from stock_600000')
results = cursor.fetchall()
for row in results:
    print(row)
#关闭
cursor.close()
db.commit()
db.close()

以上逐条打印，会凌乱到死的。也可以在MySQL端查看，先选中数据库：use stockDatabase;，然后查询：select * from stock_600000;

5.完整代码

实际上，整个事情完成了两个相对独立的过程：1.爬虫获取网页股票数据并保存到本地文件；2.将本地文件数据储存到MySQL数据库。并没有直接的考虑把从网页上抓取到的数据实时（或者通过一个临时文件）扔进数据库，跳过本地数据文件这个过程。这里只是尝试着去实现了一下这件事情，代码没有做任何的优化考虑。本身不实际去使用，只是乐趣而已，差不多先这样。哈哈~~

#导入需要使用到的模块
import urllib
import re
import pandas as pd
import pymysql
import os

#爬虫抓取网页函数
def getHtml(url):
    html = urllib.request.urlopen(url).read()
    html = html.decode('gbk')
    return html

#抓取网页股票代码函数
def getStackCode(html):
    s = r'<li><a target="_blank" href="http://quote.eastmoney.com/\S\S(.*?).html">'
    pat = re.compile(s)
    code = pat.findall(html)
    return code
    
#########################开始干活############################
Url = 'http://quote.eastmoney.com/stocklist.html'#东方财富网股票数据连接地址
filepath = 'F:\\data\\'#定义数据文件保存路径
#实施抓取
code = getStackCode(getHtml(Url)) 
#获取所有股票代码（以6开头的，应该是沪市数据）集合
CodeList = []
for item in code:
    if item[0]=='6':
        CodeList.append(item)
#抓取数据并保存到本地csv文件
for code in CodeList:
    print('正在获取股票%s数据'%code)
    url = 'http://quotes.money.163.com/service/chddata.html?code=0'+code+\
        '&end=20171228&fields=TCLOSE;HIGH;LOW;TOPEN;LCLOSE;CHG;PCHG;TURNOVER;VOTURNOVER;VATURNOVER;TCAP;MCAP'
    urllib.request.urlretrieve(url, filepath+code+'.csv')


##########################将股票数据存入数据库###########################

#数据库名称和密码
name = 'xxxx'
password = 'xxxx'  #替换为自己的账户名和密码
#建立本地数据库连接(需要先开启数据库服务)
db = pymysql.connect('localhost', name, password, charset='utf8')
cursor = db.cursor()
#创建数据库stockDataBase
sqlSentence1 = "create database stockDataBase"
cursor.execute(sqlSentence1)#选择使用当前数据库
sqlSentence2 = "use stockDataBase;"
cursor.execute(sqlSentence2)

#获取本地文件列表
fileList = os.listdir(filepath)
#依次对每个数据文件进行存储
for fileName in fileList:
    data = pd.read_csv(filepath+fileName, encoding="gbk")
   #创建数据表，如果数据表已经存在，会跳过继续执行下面的步骤print('创建数据表stock_%s'% fileName[0:6])
    sqlSentence3 = "create table stock_%s" % fileName[0:6] + "(日期 date, 股票代码 VARCHAR(10),     名称 VARCHAR(10),\
                       收盘价 float,    最高价    float, 最低价 float, 开盘价 float, 前收盘 float, 涨跌额    float, \
                       涨跌幅 float, 换手率 float, 成交量 bigint, 成交金额 bigint, 总市值 bigint, 流通市值 bigint)"
    cursor.execute(sqlSentence3)
    except:
        print('数据表已存在！')

    #迭代读取表中每行数据，依次存储（整表存储还没尝试过）
    print('正在存储stock_%s'% fileName[0:6])
    length = len(data)
    for i in range(0, length):
        record = tuple(data.loc[i])
        #插入数据语句
        try:
            sqlSentence4 = "insert into stock_%s" % fileName[0:6] + "(日期, 股票代码, 名称, 收盘价, 最高价, 最低价, 开盘价, 前收盘, 涨跌额, 涨跌幅, 换手率, \
            成交量, 成交金额, 总市值, 流通市值) values ('%s',%s','%s',%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" % record
            #获取的表中数据很乱，包含缺失值、Nnone、none等，插入数据库需要处理成空值
            sqlSentence4 = sqlSentence4.replace('nan','null').replace('None','null').replace('none','null') 
            cursor.execute(sqlSentence4)
        except:
            #如果以上插入过程出错，跳过这条数据记录，继续往下进行
            break

#关闭游标，提交，关闭数据库连接
cursor.close()
db.commit()
db.close()


###########################查询刚才操作的成果##################################

#重新建立数据库连接
db = pymysql.connect('localhost', name, password, 'stockDataBase', charset='utf8)
cursor = db.cursor()
#查询数据库并打印内容
cursor.execute('select * from stock_600000')
results = cursor.fetchall()
for row in results:
    print(row)
#关闭
cursor.close()
db.commit()
db.close()
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 205,033评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 87,725评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,473评论 0赞 338
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,846评论 1赞 277
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,848评论 5赞 368
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,691评论 1赞 282
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,053评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,700评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 42,856评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,676评论 2赞 323
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,787评论 1赞 333
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,430评论 4赞 321
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,034评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,990评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,218评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,174评论 2赞 352
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,526评论 2赞 343