MySQL 是一款非常典型的关系型数据库,其数据通过二维表来存放,所以数据都是按照行列组成在表里,一列就是一个字段,一行就是一条数据。常见的除了 MySQL 以外还有 Oracle，DB2 等。本文将以 MySQL 为基础介绍 Python 如何使用 pymysql 库来操作关系型数据库。

开始使用

pymysql 是 Python3 中推荐使用的数据库操作库。因此在使用之前需要安装该库。

安装 pymsql操作库

pip install pymysql

pymysql 快速上手

import pymysql

db = pymysql.connect(host='localhost', 
                     user='root', 
                     password='root')
cursor = db.cursor()
cursor.execute("CREATE DATABASE IF NOT EXISTS `test`")
cursor.execute("USE `test`")
cursor.execute(
    "CREATE TABLE IF NOT EXISTS `test` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) NOT NULL,`email` varchar(255) NOT NULL,`password` varchar(255) NOT NULL,PRIMARY KEY (`id`))")
cursor.execute(
    "INSERT INTO `test` (`name`, `email`, `password`) VALUES ('test', 'test', 'test')")
db.commit()
db.close()
print("Done")

如上边代码中, 通过使用 connect 方法来创建一个 MySQL 的连接对象，在创建之前需要传递 host （数据库地址），user（用户名）和 password（密码） 三个参数。同时还可以指定 port 端口（默认为 3306，可以不传），database（使用的数据库, 可不传, 但需后后续手动指定）。连接成功后调用 cursor 方法获取数据库的游标，此时就可以使用 execute 方法来执行数据库 sql 语句了，执行成功后需调用 commit 和 close 提交事务和关闭连接，此时，我们去查看数据库，就可以看到数据成功插入到数据库中，如下图：

表操作及数据增删改（CUD）

pymysql 中在执行数据库操作时，需要先调用 cursor 方法来获取 MySQL 操作游标，后续都使用游标来执行相应的 SQL 语句。

创建数据库

创建数据库的过程非常简单，只需要获取到游标后，执行创建库的 sql 即可

cursor = db.cursor()
cursor.execute("CREATE DATABASE IF NOT EXISTS `test`")

需要注意的是，在创建的连接的时候可以指定要操作哪个数据库（database 参数），游标默认会使用该库，如果需要使用新创建的库，需要手动调用 cursor.execute("USE `test`") 来指定要执行的库。不过如果在创建连接的时候没有指定数据库，那手动指定使用哪个库，就是一个必须的步骤不能省略。

创建表

在指定使用哪个库后，即可执行创建表的 SQL 语句来完成表创建。

cursor.execute(
    "CREATE TABLE IF NOT EXISTS `test` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) NOT NULL,`email` varchar(255) NOT NULL,`password` varchar(255) NOT NULL,PRIMARY KEY (`id`))")

需要注意的是，在执行完 execute 后，需要继续调用 commit 方法才能实现数据插入。后续更新，删除等操作也需要 commit 才可以生效。

修改数据

修改数据依然执行相应的 sql 语句就行，并且在修改成功后返回执行修改的条数，因此可以根据返回值判断是否执行成功。

count = cursor.execute("update test set password = 'test2' where id = 2")

删除数据

删除数据和修改操作相同，也能获取到删除的条数，用于判断是否删除成功。

count = cursor.execute("delete from test where id = 2")

1. execute 方法还可以使用占位符来执行 SQL，只需要在 SQL 语句中使用%s 占位，并在参数 args 里设置占位的填充数据（参数为元组）即可。

cursor.execute("update test set password = %s where id = %s", ("test", 1))

2. 对数据库执行操作完成后, 始终需要调用commit方法,否则可能会导致数据丢失的情况。

查询数据

数据库中最为重要的功能就是查询，使用 pymsql 执行查询时，会有一些不同，需要使用到 fetch ，fetchall 和 fetchmany 方法。

import pymysql

db = pymysql.connect(host='localhost', 
                     user='root', 
                     password='root', 
                     port=3306, 
                     database='test')
cursor = db.cursor()
sql_str = "select * from test"
res = cursor.execute(sql_str)
one = cursor.fetchone()

print(res)
print(one)

# 2
# (1, 'test', 'test', 'test')

如上边代码所示，依然使用 connect 获取数据库的连接， 使用 cursor 获取到数据库的游标，需要注意的是，使用 execute 执行查询 sql 语句后，获得的结果是结果条数，并不是结果内容，需要使用 fetch*方法来获取里边的内容。

fetchone：该方法可以获取到结果的第一条数据，返回的内容会存到一个元组中，需要注意的是，每次调用 fetchone 都会使查询游标向下移动，直到没有任何数据时，返回 None，因此可以使用 while 循环一直调用 fetchone 方法来获取所有的数据。

fetchall：该方法可以获取到所有的数据，查询的结果每条数据集结果都会被存放到元组中。

fetchmany：该方法用于获取指定条数的数据结果，参数 size 可以指定查询的条数。

使用查询功能时，需要注意以下问题：

1. fetch*方法中获取到的数据，在元组中和数据库中的字段顺序是一一对应的。

2. 每次调用 fetch 方法都会使游标向下移动，因此，组合使用 fetch 方法需要注意实际获取到的可能与预想的情况不一致。比如，调用完 fetchone 后再调用 fetchall，则 fetchall 获取的结果中就会少了 fetchone 的数据，根本原因就是游标被移动了。

事务回滚

事务机制是数据库确保数据一致性的重要功能，简单的理解为，当同时执行多个数据库操作的时候，要么都完成，要么都失败，不允许有部分成功的情况，这就是事务的最基础表现。因此在上边的增，删，改的代码逻辑中，执行完成后必须要调用一次 commit，就相当于将当前的事务提交。因此如果需要确保事务的一致性，就需要确保所有的操作都需要再同一个事务下来完成，只要中途有一次完成了 commit，则当前事务就会被提前终止。

因此这里就引出一个非常重要的概念，事务回滚。当同一个事务下，其中一条执行出错的时候，就需要回滚当前事务，在 pymysql 中使用 rollback 来完成事务的回滚。

import pymysql

db = pymysql.connect(host='localhost', user='root', password='root', port=3306, database='test')
cursor = db.cursor()
try:
    cursor.execute("INSERT INTO `test` (`name`, `email`, `password`) VALUES ('test0', 'test0', 'test0')")
    1 / 0
    cursor.execute("INSERT INTO `test` (`name`, `email`, `password`) VALUES ('test1', 'test1', 'test1')")
    db.commit()
    print("success")
except:
    print("error")
    db.rollback()

db.close()

如上述代码中，在同一个事务中执行两条插入数据操作，其中在第一条和第二条中间故意放置一个引起错误的代码，此时，由于代码错误引起异常被捕获，进入到 except 块中，执行 rollbac 回滚当前事务，因此第一条执行成功的数据也不会被记录到库中。只有当两条同时执行成功，并且调用了 commit 才可以完成数据插入。