爬虫获取CSV数据并导入我的MySQL数据库

数据库相关开发永远躲不开的一个问题, 就是如何获取数据集. 如果打算把你的系统做的足够好, 一定需要在数据库中插入足够丰富的数据, 而要有这么多的数据通常是使用算法随机生成, 或者是通过数据平台购买. 最近在做一个车辆相关的系统, 因为不知道车辆参数为何, 因此上网查询汽车数据并且发现了 cars-data.com. 这个网站的数据很全, 因此选他来辅助做车辆实体的属性.

数据泄露引发的联想

正巧最近频频发生数据泄露的事情, 刚有40g数据(7亿行数据)泄露 kallydev/privacy, 就又爆出上海195万党员资料泄露 ccpdata/ShanghaiCCPMember. 后者泄露的文件是csv格式的, 明显是从MySQL数据库导出的, 还有命令的痕迹. 无聊把他改成csv格式后导入到MySQL试了试, 发现还能正常使用, 并且查询速度还挺快的, 两百万条数据只用了一两秒就完成 like "%%" 查询.

推荐一个用户数据泄露检查服务 ’;–have i been pwned?

思路: 爬虫->CSV文件->导入数据库

本来数据库开发和数据泄露是两件不相干的事情, 不过爬虫就能把这两件事连起来. 爬虫最后得到的数据通常就是csv格式的. 而导入数据库使用的数据, 也可以是csv格式的(并且效果还很好). 那么我是不是可以从 cars-data.com 爬数据, 存储到本地的csv文件下, 然后再导入到我的MySQL里呢? 这样一来我的数据库里就有大量的专业数据了. 嗯, 有种打通整条技能树的快乐~

我去检查了一下 cars-data.com 的标签, 看起来还很好爬. 传统HTML开发, 似乎没有JS, 非常好爬. 爬虫的代码用之前的爬虫改的, 最终成果 DolorHunter/AutoTBOXDataSystem/CarCrawlers, 数据 car.csv, 代码 main.py.

这次做的爬虫比较简单, 没什么难度, 网站也没有反爬虫机制, 要不是因为爬一半掉线了不然很可能能把网站所有车辆数据爬完. 如果想看爬虫的文章可以看看我之前写的 爬虫之Cookies模拟登录与BeautifulSoup点津, 项目代码在这里 DolorHunter/1p3aMSCSAdminReport, 数据比较敏感就没上传. 这还是目前能显示我爬虫最高水平项目, 技术也比较全, 甚至还做了个模拟登录进去, 美中不足没有数据分析.

文本格式读写CSV文件

查点忘了, 这次爬虫的最大长进, 除了发现 find_all() 方法也能非完全匹配 (比如 find_all(class_=”col-5”) 可以找到 class=”col-5-label”), (find不知道行不行) 更重要的是发现了csv写入和读取的全新方法.

这次因为csv数据是uft8格式, 用excel打开会乱码, 因此是用vscode之类的文本工具打开的. 因此我在想, 为什么不直接按csv的”首行为属性,逗号分隔数据,一条数据一行”的规则按照写入文本的方式写入csv呢?

一般写入csv文件是用 xlutils 或者 pandas, 前者十分难用, 后者文档看着头大. 而且凭什么写txt就能用file.write()写, 写个csv还要查找半天库然后用一堆垃圾库操作? 是不是可以直接用文本的方式写入csv呢? 毕竟用文本格式打开csv, 并不会出现如xls,xlsx址类的编码错误.

然后我就按照”首行为属性,逗号分隔数据,一条数据一行”的csv格式试了一下, 还真行. 所以之前我看的中文文档是在写什么鬼, 为什么要用xlutils这种垃圾东西, 为什么不直接用file.write()写就好?

下面是个写csv文件示例, 先用os.path.exists(filename)判断一下是不是新文件, 新文件就额外写入首行, 属性逗号间隔; 否则写入数据, 每列间逗号间隔, 用”,”.join(line)实现逗号间隔 (传入的line是个列表, 内有不同列信息); 行末加入 “\n” 换行, 同时注意用 encoding=”utf-8” 编码, 不然默认gbk编码可能会报错. 文件打开方式都是 “a+”, 追加方式.

def write_line(line):
    if not os.path.exists(filename):
        with open(filename, 'a+', encoding="utf-8") as file:
            file.write("headline info" + "\n")
            file.write(",".join(line) + "\n")
    else:
        with open(filename, 'a+', encoding="utf-8") as file:
            file.write(",".join(line) + "\n")

导入CSV文件到MySQL数据库

爬完了数据, 接下来就是导入了. 我本地数据库管理用的是Navicat, 附 Navicat Premium 15 永久激活版安装教程, 正版有点贵上天. 这个软件用的人还不少, 就我观察, 很多专业公司也都在用这个软件.

导入部分没什么说的, 教程很多. 唯一需要注意的地方就是时间, 默认是DDMMYY, 中间用/分隔, 时间中间用:分隔, 如果没选对会导致时间识别为 1900 年. 这个问题的解答网上也是写的天花乱坠, 各种方法一通乱冲, 你们就没发现是导入向导里的设置错误吗? 明明这么明显, 粗心如我都发现了.

VARCHAR类型的长度设置

数据库最常用的就是 VARCHAR 类型了, 通常对应Java里的String.

VARCHAR名为可变长字符串, 后面的数字是最大长度的值, 默认设置为255. 但是如果我们不需要那么长怎么办? 我们应该改动他吗?

从下面的 dev.mysql.com 提供的表格看来, VARCHAR的长度设置并没有什么意义. 设置255或是64, 对于实际存储的大小并没有区别. VARCHAR不管设置的最大长度为多少, 实际存储字节都是实际字节+1.

值	CHAR(4)	需要存储	VARCHAR(4)	需要存储
’’	’ ‘(4space)	4字节	’’	1个字节
‘ab’	‘ab ‘(2space)	4字节	‘ab’	3个字节
‘abcd’	‘abcd’	4字节	‘abcd’	5字节
‘abcdefgh’	‘abcd’	4字节	‘abcd’	5字节

所以是不是应该直接把VARCHAR长度直接设置为255或者更多, 以防止截断和无法插入的发生?

在网上搜了一圈, 得出结论: VARCHAR的长度设置是为了限制列最大长度(比如电话为11位, 设置VARCHAR(255)就没什么意义)和列最大存储大小(防止数据库最大大小过大).

The length indicates more of a constraint on the data stored in the column than anything else. This inherently constrains the MAXIMUM storage size for the column as well.

设置一个过长的长度可以应对数据增长的问题, 从技术角度看来这是好的. 不过, 好的设计原则还是建议你设置一个估计的最大值, a)如果有人通过表方案尝试计算出在特定字段中存储了多少数据，他们可以看到某些字段将容纳更少数据; b)可以防止数据库引擎完成少量额外的工作，因为在插入过程中，它必须从VARCHAR(10)字段中截取比VARCHAR(255)少的空间。

1) Technically it is fine, because the fields are created with only 1 or 2 bytes in length in the beginning. Afterwards, they’ll grow as necessary.

2) Having said that though, good design principles suggest that you set field lengths appropriately so a) If someone goes through the table scheme and tries to work out how much data is stored in particular fields, they can see that certain fields will hold less data than others and b) you can prevent small amounts of extra work done by the database engine because it has to truncate less space from a VARCHAR(10) field than a VARCHAR(255) during an insert.

除此之外, 如果你并没有长度的限制要求, 那么长度设置为255也没什么问题.

现在MySQL最大长度已经变为了65535, 我是不是应该设置为65535呢? 这样是不是永远都不会发生截断和无法插入了?

MySQL 5 后最大长度已经变为了65535 (目前最新的社区版本为8), 但是较低的版本或是其他数据库系统的 VARCHAR 最大长度可能还是255. 如果您想使用UTF-8并对该列进行索引（由于索引长度的限制），有时255仍会成为有效的最大长度。