Python爬虫-MySQL

谁践踏了优雅 2022-05-21 01:37 49阅读 0赞

# Python爬虫-MySQL #

*  Python爬虫-MySQL
    
     *  前言
     *  三范式
     *  常用字段类型
     *  约束
     *  SQL语句命令行
        
         *  登陆操作
         *  用户操作
         *  数据库操作
         *  表操作
         *  数据操作
     *  事务
     *  与Python交互
     *  QQ音乐之体面
     *  在最后

## 前言 ##

在请求数据、提取数据后，我们还需要做什么呢？答案无疑是：存储数据。这之前，我们每一次都将数据保存在文本txt格式中，这没什么不可以的。但并不存在“各样问题最优解指向同一答案”。我们需要多掌握——至少也得是了解——主流存储数据的方法。  
会以MySQL，MongDB，Redis顺序，以及其他这样的顺序一一介绍。然而我们并非担任“数据管理员”一职，所以介绍内容可能相对“简陋”，但我想够用就好，必要时动动手指找度娘！（并非我不用谷歌，是不想把你们“基佬”化）

## 三范式 ##

MySQL的一种规范。说到它，我们应该立马在脑海里反应出“关系型数据”。  
1. 第一范式：列不可以拆分  
2. 第二范式：唯一标识(通过属性找到唯一对象)  
3. 第三范式：引用主键(引用关系的时候只能引用主键)  
**每一个范式都建立在前一个范式的基础之上**

## 常用字段类型 ##

1.  数字类型：int, decimal
2.  字符串：char(固定长度，无数据自己补充), varchar(可变长度)，text  
    关于三者的详细区别![这里写图片描述][70]
3.  日期：datetime
4.  布尔：bit

## 约束 ##

1.  主键约束：primary key
2.  非空：not null
3.  唯一：unique
4.  默认：default
5.  外键：foreign key

## SQL语句命令行 ##

### 登陆操作 ###

（同样，我仍不爱讲如何安装，因为安装环境才是最难环节有木有啊）  
1. 启动数据库：win环境`net start mysql`；ubuntu环境`service mysql start`  
2. 关闭数据库：win环境`net stop mysql`；ubuntu环境`service mysql stop`  
3. 登陆数据库：`mysql -u 用户 -p`；远程登陆数据库：`mysql -h ip地址 -u 用户 -p`（远程连接需要设置，后面会讲）

### 用户操作 ###

首次安装mysql后需要root账户登陆，密码为空，一般情况下，我们需要为root设置一下密码  
1. `alter user "root"@"localhost" identified by "newPassword";`  
2. 另一种修改密码方式：  
`set password for "username"@"localhost"=password("newPassword");`  
如果修改的是当前用户密码也可以：`set password=password("newpassword");`  
==上面的localhost并非固定，需要看实际情况，但一般默认为localhost。查看方式：==  
`use mysql;`使用名为mysql的数据库  
`select Host,User from user;`显示user表中的Host,User字段  
![这里写图片描述][70 1]  
3. 一般不会直接root账户对数据库操作，所以创建普通用户：  
`create user "username"@"localhost" identified by "password"`（localhost的意思是本地，如果需要远程访问，可以改为：%）  
4. 为用户授权：  
`grant all on *.* to "username"@"loaclhost"`（全部权限）  
`grant all privileges on databasename.tablename to "username"@"localhost"`  
（仅增删查改的权限，指定某个数据库的某个表）  
5. 撤销权限：  
`revoke all privileges on databaseName.tableName from "user"@"localhost"`  
==撤销命令一定要对应授权语句，即：权限部分，针对数据必须一样，不然不起作用==  
6. 查看授权语句：`show grants for "username"@"%";`  
![这里写图片描述][70 2]  
7. 删除用户：`drop user "test"@"localhost";`

### 数据库操作 ###

1.  退出：`quit;`或者`exit;`
2.  显示时间：`select now();`
3.  查看版本：`select version()`
4.  创建数据库并且指定编码方式：`create database 数据库名 charset=utf8;`
5.  删除数据库：`drop database 数据库名;`
6.  切换数据库：`use 数据库名;`
7.  查看当前使用数据库：`select databse();`
8.  查看用户所有数据库名：`show databases;`

### 表操作 ###

1.  创建表：  
    `create table 表名(id int auto_increment primary key not null,sname varchar(10) not null);`表示创建一个表，有id字段，自增长型，主键，不为空；sname字段，varchar类型，限长10，不为空（不写not null默认可为空，且不能创建空表）
2.  增加列表：`alter table 表名 add 列名 数据类型;`
3.  修改列名：`alter table 表名 change 旧列 新列 数据类型`  
    如果仅仅修改数据类型，旧列和新列仍要写出来，写一样；或者另一种方法：  
    `alter table 表名 modify 列名 数据类型;`
4.  修改表明：`rename table 表名(旧) to 表名(新);`
5.  查看表的创建语句：`show create table 表名;`
6.  查看表结构：`desc 表名;`
7.  删除表：`drop table 表名;`

### 数据操作 ###

1.  增(insert)  
    a. 全列插入：`insert into 表名 values(值1,值2...);`  
    b. 缺省插入：`insert into 表名(列1,列2...) values(值1,值2);`  
    c. 同时插入多条数据：  
    `insert into 表名 values(...),(...)...;`  
    `insert into 表名(...) values(...),(...)...;`  
    ==主键如果是自增长型，全列插入时需要占位，通常用0，插入成功后以实际数据为准==
2.  删(delete)  
    a. 物理删除：`delete from where 条件;`  
    b. 逻辑删除：一般重要数据不做直接删除，可以添加列`isdelete`数据类型为bit，默认值0，如果删除对其赋值1  
    `alter table 表名 add isdelete bit default 0;`
3.  查(select)  
    a. 条件查询：`select * from 表名 where 条件;`  
    b. 模糊查询：  
    `select * from 表名 where 列名 like "黄%";`  
    `select * from 表名 where 列名 like "黄_";`  
    d. 查询范围：  
    `... where id in (1, 3, 5);`id值为1，3， 5  
    `...where id between 1 and 4;`id值在1~4间，包括1，4  
    `... where id is null;`id值为0  
    e. 如果某列数据类型二进制，直接查询是不会显示  
    `select isdelete+0 from...`十进制显示  
    `select bin(isdelete+0) from...`二进制显示  
    `select oct(isdelete+0) from...`八进制显示  
    `select hex(isdelete+0) from...`十六进制显示  
    ![这里写图片描述][70 3]  
    e. 综合：`...where 条件A and 条件B ...;`  
    ==条件可以随意搭配，通过and，or连接，优先级：括号>not>比较运算符>逻辑运算符；and比or先运算==
4.  改(update)  
    a. `update 表名 set 列1=值1,... where 条件;`

## 事务 ##

1.  什么是事务：一个业务需要多个操作时，如果有一个失败，那么看做全部失败。使用事物即可以退回到所有操作之前，使得数据不会受损
2.  事务的四个特性（ACID）：  
    a) 原子性(A)：事务的操作不可分割，要么都执行，要么都不执行  
    b) 一致性(C)：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致  
    c) 隔离性(I)：事务的执行不受其他事务的干扰，产生的中间结果对于其他事务来说必须是透明的  
    d) 持久性(D)：对于任意已提交的事务，系统必须保证该事务对数据的改变不被丢失，即使数据出现故障  
    ==表的格式必须是innodb 或者 bdb,才可以对这个表使用事务==
3.  查看表的创建语句：  
    `show create table students;`
4.  修改表的类型：  
    `alter table students engine=innodb;`
5.  事务语句：  
    a) `begin` 开始  
    b) `commit` 提交  
    c) `rollback` 回滚

## 与Python交互 ##

1.  导包：`import pymysql`
2.  创建连接对象  
    `conn = pymysql.connect(host="",port=3306, user="", password="",db="");`  
    默认开启**事务**，对应方法：`conn.commit()`提交；`rollback()`回滚
3.  创建光标对象  
    `cursor = connect.cursor();`  
    对象方法：  
    a. `cursor.execute(sql)`执行sql语句  
    b. `cursor.fetchall()`如果执行的是查询语句，fetchall()负责返回查询到的全部结果，返回元组类型
4.  关闭光标以及数据库连接  
    `cursor.close()`  
    `conn.close()`

## QQ音乐之体面 ##

目标歌曲《体面》，热门评论4000条  
虽然QQ音乐号称四千条，实际抓取下来只有3600+，我随机查看了一些接口，发现的确不可能四千条  
![这里写图片描述][70 4]  
![这里写图片描述][70 5]

**关注点**：  
1. 注意数据来源，这次不是获取静态网页，而是通过chrome浏览器开发者工具找到热评的API接口  
2. 数据库插入遇到表情符，修改编码格式为`utf8mb4`  
**如果windows环境可以以下操作**  
a. 在mysql的安装目录下找到my.ini，做如下修改：  
\[mysqld\]  
`character-set-server=utf8mb4`  
\[mysql\]  
`default-character-set=utf8mb4`  
修改后重启mySQL：net stop mysql ; net start mysql  
b.将已经创建好的表也转换成utf8mb4  
`alter table 表名 convert to character set utf8mb4 collate utf8mb4_bin;`  
3. 数据库插入双引号报错问题  
利用replace()替换，将所有英文双引号转义（`\"`）

## 在最后 ##

1.  MySQL的操做远不止这些，但针对【爬虫】而言中，需要用到的操做以上应该足够，如果想深入，可以网上查询资料学习
2.  利用pymysql模块可以动态创建数据库和数据表，但不建议这样做，应该提前在mysql中创建好

完整代码已上传[GitHub][]，点击可查看。

[70]: /images/20220521/531499f27d9b43e18a50b285b5c86823.png
[70 1]: /images/20220521/88800dc0eab34095ada751e00ef8573d.png
[70 2]: /images/20220521/4b0ecd28db3143ff975d84d00619f0a1.png
[70 3]: /images/20220521/35342b43dd7e45e89a0ea967549e8d35.png
[70 4]: /images/20220521/90268645c43548029c90262c7822c180.png
[70 5]: /images/20220521/35f9dd2b889c4618959597058dea295c.png
[GitHub]: https://github.com/youguanxinqing/QQ_MUSIC