首先Power Query并不是一个专门的网抓或者爬虫工具,没有编程语言那么专业,实现的功能也比较有限,但其优势就是简单易学,且无缝对接excel,所见即所得。
网上关于网抓的资料有很多,但没有用PQ实现的,之前写过一篇《Power Query网抓案例》,今天来详细讲讲。
本文将以纯新手的角度,介绍一些基础的网抓知识,尽可能让每个人都能学会。
网抓主要分为三个步骤:1、抓包并分析请求,2、构建并发送请求,3、对返回的数据清洗。
首先来看一个最简单的案例,http://quote.stockstar.com/stock/ranklist_a_3_1_1.html,这是一个静态页面,以html结尾,当你点击不同栏目不同页码的时候,URL也在相应的发生变化。
对服务器来说,静态页面是实实在在保存在服务器上的文件,每个网页都是一个独立的文件。所以比如我们要抓某一页的数据,只需要点击新建查询-从其他源-自网站,把对应的URL输入进去就可以了,这和我们导入本地xlsx文件的原理是一样的,至于如何批量抓取多页我们下面再讲。
但是静态页面缺点很明显:没有数据库支持,交互性差,非常不便于维护,所以大多数网站会采用动态页面设计,这就导致了我们想要抓取数据变得没那么简单。
什么是动态页面?打个比方,打开百度首页,搜索关键词powerquery。
那GET又是什么?这就要从HTTP协议开始说起了。
我们之所以能够使用浏览器访问网页,实际上就是浏览器向服务器提交请求(request),服务器收到请求后返回了响应(response)并把HTML代码发送给浏览器,浏览器解析并显示出来。
看到这里,我们已经知道了客户端和服务器端是如何进行信息传输的:客户端提交request,服务器返回response。注意我们这里说的是客户端,浏览器算一种,当然还有其他很多种,比如我们今天要讲的Power Query。
那么网抓的原理其实就是,找出我们所需要抓的数据,分析是浏览器向服务器提交了什么请求哪些参数,然后用Power Query构建同样的请求发送给服务器,服务器便会把对应的数据在Power Query中返回给我们。
了解了这个,下面我们就开始进行第一步:抓包。
抓包可以用浏览器自带的调试工具,按F12即可调出,比较轻量,但缺点是不能支持一些复杂的抓包,但话说回来Power Query本身就不太支持复杂的网抓,所以基本也够用了。
建议使用Chrome浏览器,下面的案例全部是基于Chrome,所以不要问我其他浏览器怎么找。
以http://221.215.38.136/grcx/kscx/list.action?kscxVo.jsp=ylypmlcx.jsp为例,我们点击下方无论选多少页发现URL都不会变化,那么如何获取到比如第5页的数据呢?按下F12调出调试工具。如果不起作用的话就右击选择检查-然后点击Network。
下面请动动你们的小手和我一起做如下几个操作:1、打开上面的URL,2、按下F12调出Network窗口,3、拉到页面最下方点击第5页。
按照刚才说的优先级依次找一下:Doc有一条,XHR空的,JS空的,如图所示。
讲了这么多,那么我们如何确定目前所看到的这条记录是不是就是能够返回我们想要数据的请求呢?
首先我们回想下我们是如何找到这条记录的,是因为点击了第5页。
所以我们大致能够推断出应该是提交了一个字段名和page相关且值=5的参数,而看一下最下方的Query String Parameters或者最上方的Requset URL,其中有个page_ylypmlcxQuery=5,假如我们再点击第6页第7页,这个参数也会对应的变成6,7,所以基本能够确定就是它了。
又因为是GET,参数已经全部在URL里了,所以我们可以直接把Requset URL复制粘贴到浏览器看一下。
继续下一个案例,http://www.drugfuture.com/cndrug/national.aspx?ApprovalDateStart=2016-01-01&ApprovalDateEnd=2016-12-31,同样要求抓第5页。
F12,点击第5页,在Doc里发现一条记录如下图:
以上介绍了使用浏览器自带调试工具分别实现GET和POST抓包的方法,但是毕竟案例比较简单,基本上都只有一条记录我们一下子就能找到。但是如果出现的记录很多,我们可以使用Fiddler来快速定位。
Fiddler是一款系统代理服务器软件,下载地址请自行百度。原本是客户端发送request,服务器返回response,而有了代理之后就相当于在客户端和服务器之间夹了个小三,客户端发送request给Fiddler,Fiddler再转发给服务器,反之亦如此。由于所有的网络数据都会经过Fiddler,自然Fiddler能够截获这些数据,实现网络数据的抓包。
安装好Fiddler后首先进行初始设置。
Rules,勾选"Remove all Encodings","Hide Image Requests","Hide CONNECTs",然后Tools-Options-HTTPS全部勾上。
刚才说到所有的网络数据都会经过Fiddler,所以使用Fiddler不仅可以抓到浏览器中的数据,甚至可以抓到一些应用软件中的数据,比如说抓QQ群成员。
打开QQ群资料-成员,刚打开的时候会看到短暂的"正在加载数据,请稍候"的提示。当加载完成后,Fiddler中多了几条记录,尝试搜索"施阳",高亮定位到其中的一条记录,查看response发现,没错这正是我们要的数据。
不知不觉已经5000字下去了,但到现在似乎都和Power Query没多大关系。
抓包是网抓的第一步也是最重要的一步,这步没做好或者找错了那么后面都是徒劳。不管你是使用PQ,还是VBA,还是python,到这一步的方法都是一样的。
至此我们已经知道了浏览器之所以能够获取到数据是因为它到底做了什么,那么下面就开始进入下一步,把刚才浏览器做的事交给Power Query去做。
在M语言中,实现网抓的核心函数是Web.Contents
,它能够对指定的URL向服务器发出request并接受返回的response,先看下函数介绍。
Web.Contents(url as text, optional options as nullable record) as binary
= Web.Contents("http://www.baidu.com/s?wd=powerquery")
和= Web.Contents("http://www.baidu.com/s", [Query=[wd="powerquery"]])
两者是等价的,但是后者结构更清晰更便于修改和维护,所以在简单的情况下使用前者更方便,在参数比较多的复杂情况下更推荐后者。服务器返回的数据可能有很多种类型,这个我们先不管,这一步我们的目的就是构建带参数的request,获取目标数据的response,我们先全部统一获取源码,到下一步再讲如何转换。
Text.FromBinary
能够将Web.Contents
返回的binary解析出HTML源码,而不同网站的编码方式不同,中文网站常见的有GBK和UTF-8两种编码方式,一般在网页头部的meta部分都有声明。
Text.FromBinary
加上第二参数0,否则会乱码,下面有案例会讲到。let url="", //Requset URL中?前面的部分 headers=[Cookie=""], //如果不需要登录请删除整行,同时删除下一行中的Headers=headers query=[], //Query String Parameters,即Requset URL中?后面的部分 web=Text.FromBinary(Web.Contents(url,[Headers=headers,Query=query])) in web
POST
其中的""和[]就是需要你自己去填的,建议在编辑器软件中编辑好再粘贴到Power Query的高级编辑器中,在此推荐一款好用的编辑器——Sublime Text,轻量方便颜值高。let
url="",
headers=[#"Content-Type"="",Cookie=""], //Content-Type必填,如不需要登录Cookie可省略
query=[],
content="",
web=Text.FromBinary(Web.Contents(url,[Headers=headers,Query=query,Content=Text.ToBinary(content)]))
in
web
下面结合案例来做填空题:抓QQ邮箱收件箱。
F12,点击收件箱-下一页,在Doc里出现一条mail_list,观察发现是GET方式,所以用第一个模板。
又因为邮箱肯定是需要登录才能访问的,所以要加上cookie。
把代码编辑好复制到高级编辑器,发现返回的源码有乱码,再看一眼编码方式是GBK,所以加上第二参数0,结果正确,你收件箱里某一页的数据就都出来了。
很简单吧,你可以再尝试抓QQ空间,百度网盘这些,方法都一样。
再来举个特殊情况的案例:http://bond.sse.com.cn/bridge/information/。
F12,点击下一页,这回是在JS里有个commonQuery.do,也不难找到,是GET方式,但是把Request URL复制粘贴到浏览器地址栏却发现打不开,用Power Query也无法抓到数据。
另外目前许多网站都部署了SSL认证的HTTPS协议,相当于HTTP的加强版,更加安全,比如本文一开始讲到的百度的案例。
但是Power Query目前对HTTPS支持不是太友好,所以如果碰到URL是https开头的请改成http,否则很可能会出错。
一般来说默认提交的GET或POST参数有很多,但很多都是无效或者不相关的参数,去掉也不影响结果,所以像我这种有强迫症的就习惯挨个把参数去掉测试。
本节介绍了如何在Power Query中构建参数并向服务器发出请求,这是最简单的一步,其实就是填空题套模板。
这步完成后,可以大致浏览下返回的源码,看下我们要的数据是否在其中,如果没问题就进行下一步——数据清洗。
经过上两步,我们已经抓到所需要的数据,但是格式比较乱,我们需要对其清洗成为规范的表格。
JSONhttp://platform.sina.com.cn/sports_all/client_api?_sport_t_=football&_sport_s_=opta&_sport_a_=teamOrder&app_key=3571367214&type=10&season=2016
XML:http://www.cffex.com.cn/sj/hqsj/rtj/201710/18/index.xml,与JSON类似,都是纯文本形式的结构化数据,但没有JSON常见,使用 以上都属于结构化数据,就是能够通过函数直接或间接转换为表格,但很多时候我们遇到的可能是一些非结构化数据,比如要抓本站所有文章的标题,它既不是表格,也不是JSON,函数解析不出来,那要怎么搞呢?
服务器返回的数据,有可能是HTML,JSON,XML等格式,举几个例子,请分别复制到浏览器打开比较下区别:
HTMLhttp://datacenter.mep.gov.cn:8099/ths-report/report!list.action?xmlname=1462261004631
普通的网页结构,最简单的一种情况,HTML源码中包含table标签,使用Web.Page
能够直接解析成表格,再深化出table即可。
纯文本形式的结构化数据,一个字段对应一个值,使用Json.Document
解析。但解析出来的不是表格而是record,除了我们要的数据还可能包含状态码、页数等等,所以需要找到数据所在的list,使用Table.FromReocrds
还原成表。不会也没关系,到这一步剩下的基本靠纯界面操作就能完成。
Xml.Tables
解析。
常见的有正则,XPath,CSS Selector等方法,但很遗憾PQ一个都不支持。。。所以PQ在处理这些数据的时候就比较痛苦了,只能保持第二步中Text.FromBinary
解析出来的源码,然后当作文本来用Text类函数提取。Web.Contents
返回的数据类型为binary,Text.FromBinary
把binary解析为text,我们可以直接使用上面三个函数来替换Text.FromBinary
的位置解析binary,也可以套在Text.FromBinary
的外面来解析text,理论上效果是一样的,但是有些时候却直接解析不出来,必须加一个Text.FromBinary
,比如案例篇的练习题。
接下来讲很多人关心的翻页问题,如何批量抓取多个网页然后合并呢?
以第一个静态页的案例为例http://quote.stockstar.com/stock/ranklist_a_3_1_1.html,我们先写出完整的代码:
let 源 = Web.Page(Web.Contents("http://quote.stockstar.com/stock/ranklist_a_3_1_1.html")){0}[Data] in 源
URL结尾的a_3_1_1中最后一个1表示页数,往后翻会依次变成23456..现在要抓1到10页,那么我们只要把最后一个数改成23456..依次抓一遍即可。
但问题是抓不同页数的代码只是改了一个数字,其他部分都是一样的,我们不可能要抓10页就把代码重复10遍,这太麻烦了,所以可以把变化的部分做成变量封装为自定义函数,写成fx = (page)=> Web.Page(Web.Contents("http://quote.stockstar.com/stock/ranklist_a_3_1_1"&Text.From(page)&".html")){0}[Data]
,然后用List.Transform
遍历循环{1..10},分别调用自定义函数fx得到一个包含10张表的列表,最后用Table.Combine
将10张表合并为一张表,写成:
let fx = (page)=> Web.Page(Web.Contents("http://quote.stockstar.com/stock/ranklist_a_3_1_1"&Text.From(page)&".html")){0}[Data], 结果 = Table.Combine(List.Transform({1..10},fx)) in 结果
注意,页数是数字,与URL的文本相连时要用Text.From
进行数据类型转换。
同理,如果要批量抓取多日期、多ID之类的,只要更改自定义函数中的变量即可。
而如果我们不是要抓前10页而是所有页,而且事先是不知道一共有多少页的怎么办?如果返回的是JSON,大部分情况下数据中都会包含一个叫做totalpage的字段,所以可以分两步,第一步先随便提交一个页码拿到totalpage,可参考案例篇附件。
但是比如你现在正在使用我介绍的方法批量抓取我的网站数据,如果再多几个你这样的,那我的网站基本上就炸了。
一直如此高频率地访问网站,那得给服务器带来多大的压力。
所以很多网站会采取防爬虫措施,如果访问太频繁就会被封IP。PQ虽然不支持代理IP池之类的,但是延时还是支持的。
如果你要抓的网站会封IP,那么可以在自定义函数外面嵌套Function.InvokeAfter
,设置每爬一次停顿个5秒。
比如= Function.InvokeAfter(()=>1+1,#duration(0,0,0,5))
,你会发现你的电脑算1+1还没你算的快。
很多时候我们希望能够实现类似网页中的体验,输入指定条件比如开始和结束日期,根据指定条件抓取数据,如下图:
API即应用程序编程接口,调用API能够实现很多Power Query本身无法实现的功能,比如根据地址获取经纬度、翻译、分词、正则、机器人自动回复等等功能,可参考《使用PQ调用API》。
调用API的原理和网抓是一样的,其实很多网站的数据本身也是使用API调出来的。
一般开发文档都有详细说明,是使用GET还是POST,然后根据说明向服务器提交参数即可,返回的数据一般都是JSON。
部分API有免费限额,就是可以免费调用多少次,超出限额的需要收费,所以常见的比如地图、翻译等API都需要去开放平台注册为开发者,然后把自己的密钥加入到提交的参数中。
上面简单介绍了一下API,你可以把API理解成封装在服务器中的自定义函数,只需要向服务器提交函数所需要的参数,就能够返回你想要的结果。
那么服务器中的函数是怎么来的?那肯定是用编程语言来写的,比如PHP,Python等等,流程就是:你通过API提交参数,服务器中的编程语言写的程序来计算,得出结果并返回给你。
所以理论上只要是能够配置服务器环境的编程语言,都可以与PQ形成交互,比如《在Power Query中使用正则表达式》就是用PHP写的。
再比如使用Python的bottle包搭建本地服务器框架,便能够通过访问localhost与Python交互,可参考《M与Python交互》。
本文介绍了使用Power Query实现网抓的一些基础知识,整个流程分为抓包——构建请求——清洗数据三个步骤,重点在于第一部分,搞清楚网抓的基本原理,再结合案例《Power Query网抓案例》熟悉代码,基本上能解决大部分简单的网抓需求。
最后再啰嗦下,关键在于抓包,一旦抓到包后面都好做。而在抓包的时候,一定要灵活变通,比如在抓一些电商网站数据时,PC端往往比较难抓,所以可以考虑PC转移动,去抓移动端网站,在Chrome中的F12窗口左上角可以一键切换PC和移动模式。再比如抓QQ好友列表,你直接抓软件是抓不到的,那你就思考除了在软件还可能在哪里有接口?比如QQ空间里@好友的时候,给好友充值QB的时候,都可以轻松获取到好友列表。一旦想通这点,你会发现网抓原来这么简单。
因内容较多,篇幅有限,部分知识点只是简要带过,如有问题欢迎留言。
联系客服