[关闭]
@coder-pig 2018-08-18T10:32:01.000000Z 字数 10167 阅读 1550

小猪的Python学习之旅 —— 20.抓取Gank.io所有数据存储到MySQL中

Python


一句话概括本文

内容较多,建议先mark后看,讲解了一波MySQL安装,基本操作,语法速成,DataGrip使用,
链接远程数据库问题,爬取Gank.io API接口,存储到数据,还有遇到的三个问题。


引言

失踪人口回归,工种从开发变成了打杂后,供自己学习和写文章的时间也没以前充裕了,
大部分时间都在处理一些琐事,唉...在学习Python以后,要处理什么问题,我第一个想起
的都是它。比如前段时间在玩的微信小游戏 "萌犬变变变",前身是网页版的,只是最近迁移
到了小程序上。

游戏大概玩法如下

游戏的卖点:通过合成解锁各种各样有趣的狗子。

这样的小游戏看上去并不复杂,但是吸金数绝不可小觑,像我这种贫民玩家也
氪了30买月卡,然后排名5W开外:

站在个人视角分析下这个游戏火的原因:

以上就是个人的一些愚见,都差点忘记这个是个开发仔了,说回程序把,关于这个程序,
之前想到用Python做的两件事:

自动点箱子

裁剪快递箱的顶部小角,通过adb命令每隔一秒截屏,利用opencv进行模板匹配,获取图片
中顶部小角的坐标数组,adb命令模拟点击;

轮盘自动点击

先把轮盘的每种结果的执行流程都捋下,点哪里,跳那里,是否设置延时等,接着利用
adb截图,利用ocr图片识别关键字,比如'分享'自动关掉,'偷窃'点确定等。

因为觉得没什么意思,都不玩了,就没去整程序了,大概思路就是上面这样。
说这么多,只是想说明Python,真香。
嗯,扯得有点远了,前面的章节学爬虫,抓取到的数据存过txt,Excel和csv,
这三种对于非开发者来说挺友好的,对于开发仔来说,不存下数据库就说不过了。

数据库又分为两种:关系型数据库非关系型数据库
前者是基于关系模型的数据库,多个二维表通过表与表间的关联关系来组成一个数据库;
后者NoSQL是基于键值对的,数据间没有耦合性,非常高效;

本节使用的就是关系型数据库里的MySQL,相信很多童鞋都知道这个东东了,
编写一波gank.io的爬虫,爬取所有数据存起来,后续用Flask自己写接口玩玩~


1.MySQL安装


环境:阿里云服务器 Ubuntu 16.04

依次键入下述命令安装MySQL:

  1. # 安装MySQL服务,输入Y后,如图会让你输入密码,重复输入确认
  2. sudo apt-get install mysql-server
  3. # 安装MySQL客户端
  4. sudo apt-get install mysql-client
  5. # 安装libmysqlclient,输入Y
  6. sudo apt-get install libmysqlclient-dev

安装完后键入下述命令验证是否安装成功

  1. sudo netstat -tap | grep mysql


2.MySQL基本操作


用户登录

  1. # 回车后,需要输入在安装那里设置的密码
  2. mysql -u root -p

查看数据库

  1. show databases;

选择数据库

  1. use 数据库名

查看数据库里的所有表

  1. show tables;

停止,开始和重启MySQL服务

  1. # 开始服务
  2. /etc/init.d/mysql stop
  3. # 停止服务
  4. /etc/init.d/mysql start
  5. # 重启服务
  6. /etc/init.d/mysql restart

基本的操作就这些,对于数据库的相关操作,再进入数据库
后就可以通过数据库语句完成相关操作了。


3.MySQL数据库语法速成

MySQL数据类型

  1. # 整型(取值范围如果加了unsigned,则最大值翻倍)
  2. TINYINT(m) 1个字节 范围(-128~127);
  3. SMALLINT(m) 2个字节 范围(-32768~32767);
  4. MEDIUMINT(m) 3个字节 范围(-8388608~8388607);
  5. INT(m) 4个字节 范围(-2147483648~2147483647);
  6. BIGINT(m) 8个字节 范围(+-9.22*1018次方);
  7. # 浮点型
  8. FLOAT(m,d) 单精度浮点型 8位精度(4字节) m总个数,d小数位;
  9. DOUBLE(m,d) 双精度浮点型 16位精度(8字节) m总个数,d小数位;
  10. # 字符串
  11. # 1.char(n)若存入字符小于n,以空格补齐后面,查询时再将空格去掉,所以char类型存储
  12. # 的字符串末尾不能有空格。
  13. # 2.char(n)固定长度,不管存几个字符,都占用n个字节
  14. # 3.varchar(n)可变长度,存入的实际字符数+1个字节(n<=255)或2个字节(n>255)
  15. # 4.char类型的字符串检索速度要比varchar类型的快
  16. # 5.text类型不能有默认值,varchar查询速度快于text
  17. CHAR(n) 固定长度,最多255个字符;
  18. VARCHAR(n) 可变长度,最多65535个字符;
  19. TINYTEXT 可变长度,最多255个字符;
  20. TEXT 可变长度,最多65535个字符;
  21. MEDIUMTEXT 可变长度,最多224次方-1个字符;
  22. LONGTEXT 可变长度,最多232次方-1个字符;
  23. # 二进制数据
  24. _BLOB 以二进制方式存储,不分大小写,不用指定字符集,只能整体读出;
  25. _TEXT 以文本方式存储,英文存储区分大小写,可以指定字符集;
  26. # 日期时间类型
  27. DATE 日期
  28. TIME 时间
  29. DATETIME 日期时间
  30. TIMESTAMP 自动存储记录修改时间

数据类型的属性

  1. NULL 数据列可包含NULL
  2. NOT NULL 数据列不允许包含NULL
  3. DEFAULT 默认值
  4. PRIMARY KEY 主键
  5. AUTO_INCREMENT 自动递增,适用于整数类型
  6. UNSIGNED 无符号
  7. CHARACTER SET name 指定一个字符集

库操作相关

  1. # 建库
  2. CREATE DATABASE 数据库名;
  3. # 删库(删除数据库无法恢复!!!),删除不存在的库会报
  4. # database doesn't exist的错误,故先用IF EXISTS判断下。
  5. DROP DATABASE IF EXISTS 数据库名;

表操作相关

  1. # 建表,比如
  2. CREATE TABLE test
  3. (
  4. _id VARCHAR(50) NOT NULL PRIMARY KEY,
  5. dsec TEXT NULL,
  6. images TEXT NULL,
  7. url TEXT NULL,
  8. type VARCHAR(50) DEFAULT '' NULL
  9. );
  10. # 清空表数据,整体删除,速度较快,会重置Identity(标识列、自增字段)
  11. TRUNCATE 表名
  12. # 删除表中数据,逐条删除,速度较慢,不会重置Identity,配合WHERE关键字可以删除部分
  13. DELETE FROM 表名
  14. # 删表
  15. DROP TABLE 表名
  16. # 重命名表
  17. ALTER TABLE 原表名 RENAME 新表名;
  18. RENAME TABLE 原表名 TO 新表名;
  19. # 增加列
  20. ALTER TABLE 表名 Add column 新字段 数据类型 AFTER 在哪个字段后添加
  21. # 删除列
  22. ALTER TABLE 表名 DROP 字段名;
  23. # 重命名列/数据类型
  24. ALTER TABLE 表名 CHANGE 原列名 新列名 数据类型;
  25. # 增加主键
  26. ALTER TABLE 表名 ADD PRIMARY KEY (主键名);
  27. # 删除主键
  28. ALTER TABLE 表名 DROP PRIMARY KEY;
  29. # 添加唯一索引
  30. ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);
  31. # 添加普通索引
  32. ALTER TABLE 表名 ADD INDEX 索引名 (列名);
  33. # 删除索引
  34. ALTER TABLE 表名 DROP INDEX 索引名;
  35. # 把表默认的字符集和所有字符列(CHAR, VARCHAR, TEXT)改为新的字符集:
  36. ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8;
  37. # 修改表某一列的编码
  38. ALTER TABLE 表名 CHANGE 列名 varchar(255) CHARACTER SET utf8;
  39. # 仅仅改变一个表的默认字符集
  40. ALTER TABLE 表名 DEFAULT CHARACTER SET utf8;

增删改查(INSERT,DELETE,UPDATE,SELECT)

关键词就上面几个,通过一个完整示例来快速上手MySQL

  1. # 建新数据库
  2. CREATE DATABASE test
  3. # 新建一个表person,字段有(自增id,名字,年龄,性别)
  4. CREATE TABLE person(
  5. id INT AUTO_INCREMENT PRIMARY KEY,
  6. name VARCHAR(30) NOT NULL DEFAULT '',
  7. age INT,
  8. sex CHAR(2)
  9. );
  10. # 往表中插入5条数据
  11. INSERT INTO person (name, age, sex) VALUES ('小明', 8, '男');
  12. INSERT INTO person (name, age, sex) VALUES ('小红', 14, '女');
  13. INSERT INTO person (name, age, sex) VALUES ('小白', 4, '男');
  14. INSERT INTO person (name, age, sex) VALUES ('小宝', 6, '男');
  15. INSERT INTO person (name, age, sex) VALUES ('小莉', 16, '女');
  16. # 更新表中数据(不添加WHERE子句筛选,更新的会是整个表的某列)
  17. UPDATE person SET age = 10, sex = '女' WHERE name = '小明'
  18. # 往表里插入数据,如果某自动已存在则更新数据
  19. INSERT INTO person (id,name, age, sex) VALUES (1,'小明', 20, '男') ON DUPLICATE KEY UPDATE age = '20'
  20. # 删除特定记录
  21. DELETE FROM person WHERE age < 10;
  22. # 查询数据
  23. SELECT * FROM person; #查询所有数据
  24. SELECT name,age FROM person; #查询特定列
  25. SELECT name AS '姓名',age AS '年龄'FROM person; #为检索出来的列设置别名
  26. SELECT name FROM person WHERE age > 15 AND age <=20; # 条件查询
  27. SELECT name FROM person WHERE age BETWEEN 15 AND 20; # 范围查询
  28. # 数据求总和,平均值,最大,最小值,记录数
  29. SELECT SUM(age),AVG(age), MAX(age),MIN(age), COUNT(age) FROM person;
  30. # 查询的时候排序:升序(ASC),降序(DESC)
  31. SELECT * FROM person ORDER BY age ASC;

事务

  1. BEGIN # 开始一个事务
  2. COMMIT # 事务确认
  3. ROLLBACK # 事务回滚

关于MySQL的基本语法就到这里,本节够用了,其他的后续用到再讲~


4.数据库图形化工具——DataGrip

一般来讲数据库操作很少写命令,基本都会依赖一些图形化工具来提高效率,
关于MySQL的图形化工具,网上貌似挺多的,大部分用的貌似是Navicat for mysql
(我司后台用的就是这个),不过我还是选择了idea全家桶里的DataGrip,没有为什么...

建立数据库关联

依次点击 New -> DataSource -> MySQL
如图依次配置下Host,Database,User,Password,然后Test Connection测试是否
连接成功,成功的话点击Ok**粗体文本**。

本地是这样,如果你的数据库不在本机而是在云服务器上,就要另外折腾了。

设置mysql允许远程访问

mysql默认是不允许远程访问的,笔者用的是阿里云的服务器,在连接远程仓库
的时候也遇到一些问题,顺带记录下,方便后来者。(下述操作发生在服务器上已经安装了mysql环境后!)

Step 1:云服务器开启安全组里的3306端口

Step 2停止mysql服务

  1. /etc/init.d/mysql stop

Step 3:修改my.cnf文件,注释掉bind-address = 127.0.0.1,键入sq保存退出;

  1. vim /etc/mysql/my.cnf

Step 4:启动mysql服务

  1. /etc/init.d/mysql start

Step 5:输入下述命令查看当前3306端口的状态

  1. netstat -an|grep 3306

Step 6修改访问权限

  1. mysql -u root -p # 用户登录
  2. use mysql; # 选中mysql数据库
  3. GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION; # 授权
  4. FLUSH PRIVILEGES; # 更新权限
  5. EXIT # 退出mysql

PS:上面设置的结果是所有ip都能访问数据库,如需指定特定ip才能访问的话,
可以把'@'%改成特定ip。还有这里用的是root账户,你可以通过下述命令
创建一个新的用户,然后用这个用户进行访问,可以由此做一些权限控制操作。

  1. CREATE USER 新用户 IDENTIFIED BY '密码';
  2. GRANT ALL PRIVILEGES ON *.* TO '新用户'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION; # 授权
  3. FLUSH PRIVILEGES;

Step 7:连接远程mysql

这里用的是DataGrip进行连接,右键,new -> Data Source -> MySQL
这里要先配置SSH或者SSL,端口默认是22

再接着配置远程数据库相关,端口3306

配置完后点击Test Connection成功后,点击OK即可。

中途如果出现了异常,比如SSH Auth ERROR可能就是SSH密码错误;
除此之外的MySQL异常或问题可自行查阅:
云服务器 ECS Linux MySQL 无法远程连接问题常见错误及解决办法


5.编写爬虫程序

准备得差不多了,接着来编写爬虫程序了,因为代码家已经提供了
API接口,这里就不一个个网页爬取了,直接抓接口。

分析下接口:
有六种不同类型的数据:Android, iOS, 休息视频, 福利, 拓展资源, 前端, 瞎推荐, App
然后每个接口取五个需要的字段:_id, dsec, images, url, type

所以要做的第一件事:循环建表

接着定义一个Gank类

再接着定义一个网数据库里插入数据的函数(参数是一个gank对象列表):

再定义一个爬取接口数据的方法

接着main函数调用下,

运行等待程序抓取完成,完成后可以直接代码查询:

或者直接通过DataGrip查看:


6.遇到的三个问题

字段长度不够

接口返回的数据里有些字段比如标题和URL巨长,一开始用了varchar(250)的,
报错提示某列什么错误,后来就全改成TEXT了。

特殊符号和表情问题

因为有些标题里包含特殊符号和表情,在插入数据的时候报错,大概是这样的:
Incorrect string value: '\xF0\x9F...' for column 'XXX' at row 1

原因是:UTF-8编码有可能是两个、三个、四个字节。Emoji表情或者某些特殊字符是4个字节,
Mysql的utf8编码最多3个字节,所以数据插不进去。MySQL在5.5.3版本之后增加了
utf8mb4的编码,专门用来兼容四字节的unicode。理论上将字符集修改为utf8mb4
不会对已有的utf8编码读取产生任何问题。官方解释:
10.9.1 The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding)

解决过程

Step 1:打开终端,键入:locale my.cnf 定位到文件位置(window下是my.ini):

Step 2vim etc/mysql/my.cnf 追加下述内容,wq保存:

  1. [mysqld]
  2. character-set-server=utf8mb4
  3. [client]
  4. default-character-set=utf8mb4
  5. [mysql]
  6. default-character-set=utf8mb4

Step 3:重启MySQL服务器

Step 4:进入mysql,然后键入show variables like '%character%';确认设置是否生效

Step 5更改数据库,表,列编码

  1. ALTER DATABASE 数据库名 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
  2. ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
  3. ALTER TABLE 表名 CHANGE 列名 VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

到此问题就解决了,此时打开数据库表可以看到对应记录已存入,不过是显示成问号
的形式,读取到数据显示到支持emoji表情的页面上就可以了,比如手机。

DataGrip只能存500条记录?

打开一个表看到里面的数据只有500条,试了几次还是这样,以为DataGrip只能存储500条数据,
后来发现这里有个501+,分页,so,点击右面那个类似于播放的按钮就可以切换区间了!


小结

开头扯了一下犊子,接着详细讲解了一波MySQL相关的东西,接着写了一波简单爬虫
爬取gank.io,存储数据的方式又新增了数据库一种~


参考文献


附:最终代码(都可以在:https://github.com/coder-pig/ReptileSomething 找到):

  1. # 抓取Gank.io所有文章的爬虫
  2. import pymysql
  3. import requests as rq
  4. import urllib
  5. import coderpig_n as cn
  6. gank_api = "http://gank.io/api/data/"
  7. # 各种分类的表名:Android,iOS,休息视频,福利,拓展资源,前端,瞎推荐,App
  8. category_list = ["android", "ios", "video", "meizi", "other", "fed", "random", "app"]
  9. type_list = ["Android", "iOS", "休息视频", "福利", "拓展资源", "前端", "瞎推荐", "App"]
  10. column_list = ('_id', 'dsec', 'images', 'url', 'type')
  11. def init_db():
  12. db = pymysql.connect(host='localhost', user='root', password='zpj12345', port=3306, db='gank', charset="utf8")
  13. cursor = db.cursor()
  14. try:
  15. for category in category_list:
  16. sql = "CREATE TABLE IF NOT EXISTS %s (" \
  17. "_id VARCHAR(50) NOT NULL," \
  18. "dsec TEXT," \
  19. "images TEXT," \
  20. "url TEXT," \
  21. "type VARCHAR(50) DEFAULT ''," \
  22. "PRIMARY KEY (_id))" % category
  23. cursor.execute(sql)
  24. db.close()
  25. except:
  26. pass
  27. class Gank:
  28. _id = dsec = images = url = type = ''
  29. def __init__(self, _id, dsec, images, url, type):
  30. self._id = _id
  31. self.dsec = dsec
  32. self.images = images
  33. self.url = url
  34. self.type = type
  35. # 以元组的方式返回值
  36. def to_value_tuple(self):
  37. return self._id, self.dsec, self.images, self.url, self.type
  38. def insert_db(gank_list):
  39. db = pymysql.connect(host='localhost', user='root', password='zpj12345', port=3306, db='gank', charset="utf8")
  40. cursor = db.cursor()
  41. try:
  42. for data in gank_list:
  43. if data.type in type_list:
  44. category = category_list[type_list.index(data.type)]
  45. data_tuple = data.to_value_tuple()
  46. sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=category,
  47. keys=','.join(column_list),
  48. values=','.join(['%s'] * len(data_tuple)))
  49. cursor.execute(sql, data_tuple)
  50. print(data_tuple)
  51. db.commit()
  52. except Exception as e:
  53. print(str(e))
  54. db.rollback()
  55. db.close()
  56. def spider_data(pos):
  57. count = 1
  58. while True:
  59. resp = rq.get(gank_api + urllib.parse.quote(type_list[pos]) + "/50/" + str(count), proxies=cn.get_proxy_ip())
  60. resp_json = resp.json()
  61. print(resp.url)
  62. if resp.status_code == 200 and len(resp_json['results']) != 0:
  63. json_list = []
  64. for result in resp_json['results']:
  65. images = result.get('images')
  66. if images is None:
  67. images = ''
  68. else:
  69. images = images[0]
  70. gank = Gank(result['_id'], result['desc'], images, result.get('url', ''),
  71. result['type'])
  72. json_list.append(gank)
  73. insert_db(json_list)
  74. else:
  75. break
  76. count += 1
  77. if __name__ == '__main__':
  78. init_db()
  79. for i in range(0, len(type_list)):
  80. spider_data(i)
  81. db = pymysql.connect(host='localhost', user='root', password='zpj12345', port=3306, db='gank', charset="utf8")
  82. cursor = db.cursor()
  83. cursor.execute('SELECT * FROM android')
  84. print(cursor.rowcount)
  85. results = cursor.fetchall()
  86. for result in results:
  87. print(result)
  88. cursor.close()

来啊,Py交易啊

想加群一起学习Py的可以加下,智障机器人小Pig,验证信息里包含:
PythonpythonpyPy加群交易屁眼 中的一个关键词即可通过;

验证通过后回复 加群 即可获得加群链接(不要把机器人玩坏了!!!)~~~
欢迎各种像我一样的Py初学者,Py大神加入,一起愉快地交流学♂习,van♂转py。


添加新批注
在作者公开此批注前,只有你和作者可见。
回复批注