爬虫-关系型数据库存储-MySQL

1. 准备工作

在开始之前，请确保已经安装好了 MySQL 数据库并保证它能正常运行，而且需要安装好 PyMySQL 库。

$ pip3 install PyMySQL

在进行python访问MySQL数据库时，程序报错：OperationalError: (2003, “Can’t connect to MySQL server on ‘localhost’ ([WinError 10061] 由于目标计算机积极拒绝，无法连接。)”)
解决办法：https://blog.youkuaiyun.com/BigData_Mining/article/details/88344513

2. 连接数据库-connect

首先，导包import pymysql；
其次，利用pymysql.connect()声明一个 MySQL 连接对象 db，传入 MySQL 运行的 host（即 IP）、user （即用户名），password（即密码）、port （即端口，默认为 3306）；
然后，调用 cursor 方法获得 MySQL 的操作游标，利用游标来执行 SQL 语句。
再之，利用execute 方法执行sql语句；
最后，调用close方法关闭数据库。
注意：一般来说，创建数据库的操作只需要执行一次就好了。当然，我们也可以手动创建数据库。

import pymysql  
#声明一个 MySQL 连接对象 db
db = pymysql.connect(
    host='localhost', #数据库在本机上
    user='root', ##用户名
    password='123',  #用户密码
    port=3306, #运行端口
    database="test",      #连接的数据库
    charset="utf8"
) 
cursor = db.cursor()  #获得 MySQL 的操作游标，利用游标来执行 SQL 语句


#用execute方法执行SQL语句
cursor.execute('SELECT VERSION()')  #获得 MySQL 的当前版本
data = cursor.fetchone()  #调用fetchone方法获得第一条数据，也就得到了版本号。
print('Database version:', data)  
cursor.execute("CREATE DATABASE spiders DEFAULT CHARACTER SET utf8")  #创建一个新的数据库，名字叫作 spiders
db.close()

3. 创建表-CREATE语句

以后，我们的操作都在 spiders 数据库上执行。

创建数据库后，在连接时需要额外指定一个参数 db。

接下来，新创建一个数据表 students，此时执行创建表的 SQL 语句即可。

字　段　名	含　　义	类　　型
id	学号	varchar
name	姓名	varchar
age	年龄	int

创建该表的示例代码如下：

import pymysql

db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='spiders')
cursor = db.cursor()

sql = """CREATE TABLE IF NOT EXISTS students (
	    id VARCHAR(255) NOT NULL, 
    	name VARCHAR(255) NOT NULL, 
           age INT NOT NULL, 
	    PRIMARY KEY (id)
        )"""
cursor.execute(sql)

db.close()

4. 插入数据-INSERT语句

值得注意的是，需要执行 db 对象的 commit方法才可实现数据插入，这个方法才是真正将语句提交到数据库执行的方法。对于数据插入、更新、删除操作，都需要调用该方法才能生效。

接下来，我们加了一层异常处理。如果执行失败，则调用 rollback 执行数据回滚，相当于什么都没有发生过。

直接插入数据

例如，这里爬取了一个学生信息，学号为 20120001，名字为 Bob，年龄为 20：

import pymysql

id = '20120001'
user = 'Bob'
age = 20

db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='spiders')
cursor = db.cursor()

sql = 'INSERT INTO students(id, name, age) values(% s, % s, % s)'
try:
    cursor.execute(sql, (id, user, age)) # 执行sql语句
    db.commit() # 提交到数据库执行
except:
    db.rollback() # 异常处理，如果发生错误则回滚rollback
db.close()

问题：

构建字典实现插入数据（推荐）

首先，把需要传入的数据设置为字典形式，并将其定义为 data 变量，表名也定义成变量 table。

其次，构造一个动态的 SQL 语句了：
——首先，需要构造插入的字段 id、name 和 age。
——将 data 的键名拿过来，然后用逗号分隔即可。所以 ‘, ‘.join(data.keys()) 的结果就是 id, name, age，
——需要构造多个 % s 当作占位符，有几个字段构造几个即可。比如，这里有三个字段，就需要构造 % s, % s, % s。这里首先定义了长度为 1 的数组 [’% s’]，然后用乘法将其扩充为 [’% s’, ‘% s’, ‘% s’]
——再调用 join 方法，最终变成 % s, % s, % s。
——最后，利用字符串的 format 方法将表名、字段名和占位符构造出来。

最后，为 execute 方法的第一个参数传入 sql 变量，第二个参数传入 data 的键值构造的元组，就可以成功插入数据了。

data = {
    'id': '20120001',
    'name': 'Bob',
    'age': 20
}
table = 'students'
keys = ', '.join(data.keys())
values = ', '.join(['% s'] * len(data))
sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
try:
	if cursor.execute(sql, tuple(data.values())):
       	print('Successful')
       	db.commit()
except:
    print('Failed')
    db.rollback()
db.close()

5.事务机制

事务机制可以确保数据的一致性，也就是这件事要么发生了，要么没有发生。比如插入一条数据，不会存在插入一半的情况，要么全部插入，要么都不插入，这就是事务的原子性。另外，事务还有 3 个属性 —— 一致性、隔离性和持久性。这 4 个属性通常称为 ACID 特性，具体如表所示。

属　　性	解　　释
原子性（atomicity）	事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做
一致性（consistency）	事务必须使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的
隔离性（isolation）	一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰
持久性（durability）	持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响

插入、更新和删除操作都是对数据库进行更改的操作，而更改操作都必须为一个事务，所以这些操作的标准写法就是：

try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()

这样便可以保证数据的一致性。这里的 commit 和 rollback 方法就为事务的实现提供了支持。

6. 更新数据-UPDATE语句

简单的数据更新

#1.用占位符的方式构造sql语句
sql = 'UPDATE students SET age = % s WHERE name = % s'
#2.执行sql语句
try:
	cursor.execute(sql, (25, 'Bob'))  #执行 execute 方法，传入元组形式的参数
	db.commit() #执行 commit 方法执行操作
except:
	db.rollback()
db.close()

去重下的更新

在实际的数据抓取过程中，大部分情况下需要插入数据，但是我们关心的是会不会出现重复数据，如果出现了，我们希望更新数据而不是重复保存一次。
如果数据存在，则更新数据；如果数据不存在，则插入数据。

data = {
    'id': '20120001',
    'name': 'Bob',
    'age': 21
}

table = 'students'
keys = ', '.join(data.keys())
values = ', '.join(['% s'] * len(data))

sql = 'INSERT INTO {table}({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE'.format(table=table, keys=keys, values=values)
update = ','.join(["{key} = % s".format(key=key) for key in data])
sql += update
try:
    if cursor.execute(sql, tuple(data.values())*2):
        print('Successful')
        db.commit()
except:
    print('Failed')
    db.rollback()
db.close()

这里构造的 SQL 语句其实是插入语句，但是我们在后面加了 ON DUPLICATE KEY UPDATE。这行代码的意思是如果主键已经存在，就执行更新操作。比如，我们传入的数据 id 仍然为 20120001，但是年龄有所变化，由 20 变成了 21，此时这条数据不会被插入，而是直接更新 id 为 20120001 的数据。完整的 SQL 构造出来是这样的：

INSERT INTO students(id, name, age) VALUES (% s, % s, % s) ON DUPLICATE KEY UPDATE id = % s, name = % s, age = % s

这里就变成了 6 个 % s。所以在后面的 execute 方法的第二个参数元组就需要乘以 2 变成原来的 2 倍。

如此一来，我们就可以实现主键不存在便插入数据，存在则更新数据的功能了。

7. 删除数据-DELETE 语句

删除操作相对简单，直接使用 DELETE 语句即可，只是需要指定要删除的目标表名和删除条件，而且仍然需要使用 db 的 commit 方法才能生效。
示例如下：

table = 'students'  #指定表
condition = 'age > 20'  #删除的条件

sql = 'DELETE FROM  {table} WHERE {condition}'.format(table=table, condition=condition)
try:
    cursor.execute(sql) # 执行SQL语句
    db.commit() # 执行SQL语句
except:
    db.rollback() # 发生错误时回滚

db.close()# 关闭连接

因为删除条件有多种多样，运算符有大于、小于、等于、LIKE 等，条件连接符有 AND、OR 等，所以不再继续构造复杂的判断条件。这里直接将条件当作字符串来传递，以实现删除操作。

8.查询数据-SELECT 语句

查询会用到 SELECT 语句，示例如下：

sql = 'SELECT * FROM students WHERE age >= 20'  #构造了一条 SQL 语句，将年龄 20 岁及以上的学生查询出来,然后将其传给 execute 方法

try:
    cursor.execute(sql)
    print('Count:', cursor.rowcount) #调用 cursor 的 rowcount 属性获取查询结果的条数
    one = cursor.fetchone() #调用fetchone方法，获取结果的第一条数据，返回结果是元组形式，元组的元素顺序跟字段一一对应，即第一个元素就是第一个字段 id，第二个元素就是第二个字段 name，以此类推。
    print('One:', one) 
    results = cursor.fetchall()  #fetchall 方法，它可以得到结果的所有数据。返回的结果是二重元组，每个元素都是一条记录，将其遍历输出出来。
    print('Results:', results)
    print('Results Type:', type(results))
    for row in results:
        print(row)
except:
    print('Error')

运行结果如下：

Count: 4
One: ('20120001', 'Bob', 25)
Results: (('20120011', 'Mary', 21), ('20120012', 'Mike', 20), ('20120013', 'James', 22))
Results Type: <class 'tuple'>
('20120011', 'Mary', 21)
('20120012', 'Mike', 20)
('20120013', 'James', 22)

但是这里需要注意一个问题，这里显示的是 3 条数据而不是 4 条，fetchall 方法不是获取所有数据吗？这是因为它的内部实现有一个偏移指针用来指向查询结果，最开始偏移指针指向第一条数据，取一次之后，指针偏移到下一条数据，这样再取的话，就会取到下一条数据了。我们最初调用了一次 fetchone 方法，这样结果的偏移指针就指向下一条数据，fetchall 方法返回的是偏移指针指向的数据一直到结束的所有数据，所以该方法获取的结果就只剩 3 个了。

9.获取数据-fetchone\fetchall\rowcount

Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。

fetchone(): 该方法获取下一个查询结果集。结果集是一个对象

fetchall(): 接收全部的返回结果行.

rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。

cursor.fetchone()	调用fetchone方法，获取结果的第一条数据，返回结果是元组形式，元组的元素顺序跟字段一一对应
cursor.fetchall()	返回的结果是二重元组，每个元素都是一条记录，将其遍历输出出来。

推荐的取数据方法：
用 while 循环加 fetchone 方法来获取所有数据，而不是用 fetchall 全部一起获取出来。fetchall 会将结果以元组形式全部返回，如果数据量很大，那么占用的开销会非常高。
这样每循环一次，指针就会偏移一条数据，随用随取，简单高效。
因此，推荐使用如下方法来逐条取数据：

sql = 'SELECT * FROM students WHERE age >= 20'
try:
    cursor.execute(sql)
    print('Count:', cursor.rowcount)
    row = cursor.fetchone()
    while row:
        print('Row:', row)
        row = cursor.fetchone()
except:
    print('Error')

10.错误处理

异常	描述
Warning	当有严重警告时触发，例如插入数据是被截断等等。必须是 StandardError 的子类。
Error	警告以外所有其他错误类。必须是 StandardError 的子类。
InterfaceError	当有数据库接口模块本身的错误（而不是数据库的错误）发生时触发。 必须是Error的子类。
DatabaseError	和数据库有关的错误发生时触发。 必须是Error的子类。
DataError	当有数据处理时的错误发生时触发，例如：除零错误，数据超范围等等。 必须是DatabaseError的子类。
OperationalError	指非用户控制的，而是操作数据库时发生的错误。例如：连接意外断开、 数据库名未找到、事务处理失败、内存分配错误等等操作数据库是发生的错误。 必须是DatabaseError的子类。
IntegrityError	完整性相关的错误，例如外键检查失败等。必须是DatabaseError子类。
InternalError	数据库的内部错误，例如游标（cursor）失效了、事务同步失败等等。 必须是DatabaseError子类。
ProgrammingError	程序错误，例如数据表（table）没找到或已存在、SQL语句语法错误、 参数数量错误等等。必须是DatabaseError的子类。
NotSupportedError	不支持错误，指使用了数据库不支持的函数或API等。例如在连接对象上 使用.rollback()函数，然而数据库并不支持事务或者事务已关闭。 必须是DatabaseError的子类。

参考文献

根据崔庆才《Python 3 开发网络爬虫》整理