不能错过的Python操作MySQL存储方法，很实用

在Python 2中，连接MySQL的库大多是使用MySQLdb，但是此库的官方并不支持Python 3，所以这里推荐使用的库是PyMySQL。本文就来讲解使用PyMySQL操作MySQL数据库的方法。

1. 准备工作

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

2. 连接数据库

这里，首先尝试连接一下数据库。假设当前的MySQL运行在本地，用户名为root，密码为123456，运行端口为3306。这里利用PyMySQL先连接MySQL，然后创建一个新的数据库，名字叫作spiders，代码如下：

运行结果如下：

Database version: ('5.6.22',)

这里通过PyMySQL的connect()方法声明一个MySQL连接对象db，此时需要传入MySQL运行的host（即IP）。由于MySQL在本地运行，所以传入的是localhost。如果MySQL在远程运行，则传入其公网IP地址。后续的参数user即用户名，password即密码，port即端口（默认为3306）。

连接成功后，需要再调用cursor()方法获得MySQL的操作游标，利用游标来执行SQL语句。这里我们执行了两句SQL，直接用execute()方法执行即可。

3. 创建表

一般来说，创建数据库的操作只需要执行一次就好了。当然，我们也可以手动创建数据库。以后，我们的操作都在spiders数据库上执行。

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

接下来，新创建一个数据表students，此时执行创建表的SQL语句即可。这里指定3个字段，结构如下所示。

字段名

含义

类型

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

运行之后，我们便创建了一个名为students的数据表。当然，为了演示，这里只指定了最简单的几个字段。实际上，在爬虫过程中，我们会根据爬取结果设计特定的字段。

4. 插入数据

下一步就是向数据库中插入数据了。例如，这里爬取了一个学生信息，学号为20120001，名字为Bob，年龄为20，那么如何将该条数据插入数据库呢？示例代码如下：

这里首先构造了一个SQL语句，其Value值没有用字符串拼接的方式来构造，如：

sql = 'INSERT INTO students(id, name, age) values(' + id + ', ' + name + ', ' + age + ')'

这样的写法烦琐而且不直观，所以我们选择直接用格式化符%s来实现。有几个Value写几个%s，我们只需要在execute()方法的第一个参数传入该SQL语句，Value值用统一的元组传过来就好了。这样的写法既可以避免字符串拼接的麻烦，又可以避免引号冲突的问题。

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

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

属性

解释

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

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

上面数据插入的操作是通过构造SQL语句实现的，但是很明显，这有一个极其不方便的地方，比如突然增加了性别字段gender，此时SQL语句就需要改成：

INSERT INTO students(id, name, age, gender) values(%s, %s, %s, %s)

相应的元组参数则需要改成：

(id, name, age, gender)

这显然不是我们想要的。在很多情况下，我们要达到的效果是插入方法无需改动，做成一个通用方法，只需要传入一个动态变化的字典就好了。比如，构造这样一个字典：

然后SQL语句会根据字典动态构造，元组也动态构造，这样才能实现通用的插入方法。所以，这里我们需要改写一下插入方法：

这里我们传入的数据是字典，并将其定义为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()方法将表名、字段名和占位符构造出来。最终的SQL语句就被动态构造成了：

INSERT INTO students(id, name, age) VALUES (%s, %s, %s)

最后，为execute()方法的第一个参数传入sql变量，第二个参数传入data的键值构造的元组，就可以成功插入数据了。如此以来，我们便实现了传入一个字典来插入数据的方法，不需要再去修改SQL语句和插入操作了。

5. 更新数据

数据更新操作实际上也是执行SQL语句，最简单的方式就是构造一个SQL语句，然后执行：

这里同样用占位符的方式构造SQL，然后执行execute()方法，传入元组形式的参数，同样执行commit()方法执行操作。如果要做简单的数据更新的话，完全可以使用此方法。

6. 删除数据

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

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

7. 查询数据

说完插入、修改和删除等操作，还剩下非常重要的一个操作，那就是查询。查询会用到SELECT语句，示例如下：

运行结果如下：

这里我们构造了一条SQL语句，将年龄20岁及以上的学生查询出来，然后将其传给execute()方法。注意，这里不再需要db的commit()方法。接着，调用cursor的rowcount属性获取查询结果的条数，当前示例中是4条。

我们还可以用while循环加fetchone()方法来获取所有数据，而不是用fetchall()全部一起获取出来。fetchall()会将结果以元组形式全部返回，如果数据量很大，那么占用的开销会非常高。因此，推荐使用如下方法来逐条取数据：

这样每循环一次，指针就会偏移一条数据，随用随取，简单高效。