MySQL存储
关系型数据库是基于关系模型的数据库，而关系模型是通过二维表来保存的，所以关系型数据库中数据的存储方式就是行列组成的表，每一列代表一个字段、每一行代表一条记录。表可以看作某个实体的集合，实体之间存在的联系需要通过表与表之间的关联关系体现，例如主键和外键的关联关系。由多个表组成的数据库，就是关系型数据库。
关系型数据库有多种，例如 SQLite、MySQL、Oracle、SQL Server、DB2等，本节我们主要来了解一下 MySQL 数据库的存储操作。
在 Python2 中，连接 MySQL 的库大多是 MySQLdb，但是此库的官方并不支持 Python3，所以这里推荐使用的库是 PyMySQL。
下面，我们就来讲解使用 PyMySQL 操作 MySQL 数据库的方法。
准备工作
在开始之前，请确保已经安装好了 MySQL 数据库并保证它能正常运行，安装方式可以参考: https://setup.scmpe.center/mysql 。
除了安装好 MySQL 数据外，还需要安装好 PyMySQL 库，如尚未安装 PyMySQL，可以使用 pip3 来安装:
pip3 install pymysql更详细的安装方式可以参考: https://setup.scrape.center/pymysql
二者都安装好了之后，我们就可以开始本节的学习了。
连接数据库
首先尝试连接一下数据库。假设当前的 MySQL 运行在本地，用户名为 root，密码为 123456，运行端口为 3306。这里利用 PyMySQL 先连接 MySQL，然后创建一个新的数据库，叫作 spiders，代码如下:
import pymysql
db = pymysql.connect(host='localhost',user='root', password='123456', port=3306)
cursor = db.cursor()
cursor.execute('SELECT VERSION()')
data = cursor.fetchone()
print('Database version : ', data)
cursor.execute('CREATE DATABASE spiders DEFAULT CHARACTER SET utf8mb4')
db.close()运行结果如下:
Database version: ('8.0.19',)这里通过 PyMySQL 的 connect 方法声明了一个 MySQL 连接对象 db，此时需要传人的第一个参数是 MySQL 运行的 host（即 IP），由于 MySQL 运行在本地，所以传入的是 localhost，如果 MySQL 在远程运行，则传入其公网 IP 地址。后续参数分别是 user（用户名）、password（密码）和 port（端口，默认为 3306）。
连接成功后，调用 cursor 方法获得了 MySQL 的操作游标，利用游标可以执行 SQL 语句。这里我们执行了两个 SQL 语句，直接调用 execute 方法即可执行。第一个 SQL 语句用于获得 MySQL 的当前版本，然后调用 fetchone 方法就得到了第一条数据，即版本号。第二个 SQL 语句用于创建数据库 spiders，默认编码为 UTF-8，由于该语句不是查询语句，所以执行后就成功创建了数据库 spiders，可以利用这个数据库完成后续的操作。
创建表
一般来讲，创建数据库的操作执行一次就可以了。当然，也可以手动创建数据库。我们之后的操作都在 spiders 数据库上完成。
接下来，新创建一个数据表 students，此时执行创建表的 SQL 语句即可。这里指定 3 个字段，结构如表 4-1 所示。
| 字段名 | 含义 | 类型 |
|---|---|---|
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 sutdents(id VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, age INT NOT NULL, PRIMARY KEY(id))'
cursor.execute(sql)
db.close()运行之后，便创建了一个名为 students 的数据表。
当然，为了演示，这里只是指定了最简单的几个字段。实际上，在爬虫爬取的过程中，我们会根据爬取结果设计特定的字段。
插入数据
下一步就是往数据库中插人数据了。例如，这里爬取到一个学生信息，学号为 20120001、名字为 Bob、年龄为20，如何将这条数据插人数据库呢?实例代码如下:
import pymysql
db = pymysql.connect(host='localhost',user='root', password='123456', port=3306, db='spiders')
cursor = db.cursor()
sql = 'INSERT INTO students(id, name, age) VALUES(%ss, %ss, %ss)'
try:
cursor.execute(sql, (id, user, age))
db.commit()
except:
db.rollback()
db.close()这里首先构造了一个 SQL 语句，其值没有用如下字符串拼接的方式构造:
sql = 'INSERT INTO students(id, name, age) values(' + id + ',' + name + ',' + age + ')'这样的写法烦琐且不直观，所以我们直接用格式化符 %ss 来构造，有几个 value 就写几个 %ss。我们只需要在 execute 方法的第一个参数传入该 SQL 语句，value 值用统一的元组传过来就好了。这样的写法既可以避免字符串拼接的麻烦又可以避免引号冲突问题。
之后值得注意的是，需要执行 db 对象的 commit 方法才可以实现数据插入，这个方法才是真正将语句提交到数据库执行的方法。对于数据插人、更新、删除操作，都需要调用该方法才能生效。
接下来，我们加了一层异常处理。如果执行失败，则调用 rollback 执行数据回滚，相当于什么都没有发生过。
这里涉及事务的问题。事务机制能够确保数据的一致性，也就是一件事要么发生完整了，要么完全没有发生。例如插入一条数据，不会存在插人一半的情况—要么全部插入、要么都不插入，这就是事务的原子性。事务还有其他 3 个属性—一致性、隔离性和持久性。这 4 个属性通常称为 ACID 特性，具体如表 4-2 所示。
| 属性 | 解释 |
|---|---|
原子性(atomicity) |
事务是一个不可分割的工作单位，事务中包括的诸操作要么都做、要么都不做 |
一致性(consistency) |
事务必须使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的 |
隔离性(isolation) |
一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰 |
持久性(durability) |
持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据做的改变就应该是永久性的。接下来的其他操作或故障不应该对数据有任何影响 |
插入、更新和删除操作都是对数据库进行更改的操作，而更改操作都必须是一个事务，所以这些操作的标准写法是:
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()这样便可以保证数据的一致性。这里的 commit 和 rollback 方法就为事务的实现提供了支持。
上面数据插入的操作是通过构造 SQL 语句实现的，但是很明显，这里有一个极其不方便的地方，例如突然增加了性别字段 gender，此时 SQL 语句就需要改成:
INSERT INTO students(id, name, age) VALUES(%ss, %ss, %ss)相应的元组参数需要改成:
(id,name,age,gender)这显然不是我们想要的。在很多情况下，我们要达到的效果是插入方法无须做改动，其作为通用方法，只需要传人一个动态变化的字典就好了。例如，构造这样一个字典:
{
'id': '20120001',
'name': 'Bob',
'age': 20
}然后，SQL 语句会根据这个字典动态构造出来，元组也是，这样才是实现了通用的插入方法。于是我们改写一下插入方法:
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()这里我们传入的数据是字典，将其定义为了 data 变量，将 students 表定义为了变量 tab1e。接下来，构造一个动态的 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 方法将表名、字段名和占位符构造出来。于是 SQL 语句就动态构造出来了:
INSERT INTO students(id, name, age) VALUES (%s, %s, %s)最后，为 execute 方法的第一个参数传入 sql 变量，第二个参数传入由 data 的键值构造的元组，就可以成功插入数据了。
如此一来，我们便实现了通过传入一个字典来插入数据的方法，不需要再去修改 SQL 语句和插入操作。
更新数据
数据更新操作实际上也是执行 SQL 语句，最简单的方式就是先构造一个 SQL 语句，然后执行:
sql = 'UPDATE students SET age = %ss WHERE name = %s'
try:
cursor.execute(sql, (25, 'Bob'))
db.commit()
except:
db.rollback()
db.close()这里同样用占位符的方式构造 SQL，然后执行 execute 方法，传入元组形式的参数，同样执行 commit 方法执行操作。如果做的是简单的数据更新，完全可以使用此方法。
但是在实际的数据抓取过程中，大部分情况下需要插人数据，我们关心的是会不会出现重复数据，如果出现了，我们希望更新数据而不是重复保存一次。另外，就像前面所说的动态构造 SQL 的问题，所以这里可以再实现一种去重的方法:如果数据存在，就更新数据;如果数据不存在，则插人数据。
另外，这种做法支持灵活的字典传值。实例代码如下:
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} = %ss".format(key=key) for key in data])
sql += update
try:
if cursor.execute(sql, tuple(data.values())*2):
print('success')
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 倍。
如此一来，我们就可以实现主键不存在便插入数据，主键存在则更新数据的功能。
删除数据
删除操作相对简单，直接使用 DELETE 语句即可，只是需要指定要删除的目标表名和删除条件，而且仍然需要使用 db 的 commit 方法才能生效。实例代码如下:
table = 'students'
condition = 'age > 20'
sql = 'DELETE FROM {table} WHERE {condition}'.format(table=table, condition=condition)
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
db.close()因为删除条件多种多样，运算符有大于、小于、等于、LIKE 等，条件连接符有 AND，OR 等，所以不再继续构造复杂的判断条件。这里直接将条件当作字符串来传递，以实现删除操作。
查询数据
说完插入，修改和删除等操作，还剩下一个非常重要的操作，就是查询。查询会用到 SELECT 语句，实例代码如下:
sql = 'SELECT * FROM students WHERE age >= 20'
try:
cursor.execute(sql)
print('Count:', cursor.rowcount)
one = cursor.fetchone()
print('One:', one)
results = cursor.fetchall()
print('Results:', results)
print('Results Type:', type(results))
for row in results:
print(row)
except:
print('Error')运行结果如下:
这里我们构造了一个 SQL 语句，查询年龄为 20 及以上的学生，然后将其传给 execute 方法。注意，这里不再需要 db 的 commit 方法。接着，调用 cursor 的 rowcount 属性获取查询结果的条数，当前实例中是 4 条。
然后我们调用了 fetchone 方法，这个方法可以获取结果的第一条数据返回结果以元组形式呈现，元组中元素的顺序跟字段一一对应，即第一个元素就是第一个字段 id、第二个元素就是第二个字段 name，以此类推。随后，我们又调用了 fetchall 方法，可以得到结果的所有数据。之后将其结果和类型打印出来，是一个二重元组，其中每个元素都是一条记录，我们遍历这些元素并输出。
但是需要注意一个问题，这里结果显示的是 3 条数据而不是 4 条，fetchall 方法不是获取所有数据吗?这是因为它的内部实现有一个偏移指针，用来指向查询结果，偏移指针最开始指向第一条数据，取一次数据之后，指针偏移到下一条数据，于是再取就会取到下一条数据。我们最初调用了一次 fetchone 方法，这样结果的偏移指针就指向下一条数据，fetchall 方法返回的是从偏移指针指向的数据一直到结束的所有数据，所以它获取的结果就只剩 3 个了。
此外，我们还可以用 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')这样每循环一次，指针就会偏移一条数据，随用随取，简单高效。