pymysql使用指南

共计 4893 个字符，预计需要花费 13 分钟才能阅读完成。

1 引言

MySQL 应该说是如今使用最为普遍的数据库了，没有之一，而 Python 作为最为流行的语言之一，自然少不了与 mysql 打交道，pymysql 就是使用最多的工具库了。

2 创建库、表

我们先从创建数据库、数据表说起，有了库表，后面的增删改查才有根据。

2.1 创建数据库

pymysql 的所有对数据库的操作都必须先与数据库服务建立连接，然后创建游标为基础执行具体的 sql 语句。创建数据库方法如下：

# -*- coding: utf-8 -*-
import pymysql
 
conn = pymysql.connect(# 创建数据库连接
    host='10.10.11.131', # 要连接的数据库所在主机 ip
    user='chb', # 数据库登录用户名
    password='123456!', # 登录用户密码
    charset='utf8' # 编码，注意不能写成 utf-8
)
cursor = con.cursor()
cursor .execute("create database test_db character set utf8;")
# 执行完之后别忘了关闭游标和数据库连接
cursor.close()
conn.close()

上面代码执行完后，就创建了一个名为 test_db 的数据库：

2.2 创建数据表

# -*- coding: utf-8 -*-
import pymysql
 
conn = pymysql.connect(# 创建数据库连接
    host='10.10.11.131', # 要连接的数据库所在主机 ip
    user='chb', # 数据库登录用户名
    password='123456!', # 登录用户密码
    database='test_db', # 连接的数据库名，也可以后续通过 cursor.execture('user test_db')指定
    charset='utf8' # 编码，注意不能写成 utf-8
)
 
cursor = conn.cursor() # 创建一个游标
 
# 需要执行的创建表的 sql 语句
sql = """
create table book(
bookid int auto_increment primary key ,
bookname VARCHAR(255) not null ,
authors VARCHAR(255) not null ,
year_publication YEAR not null
);
"""
 
cursor.execute(sql) # 使用游标执行 sql
 
# 执行完之后别忘了关闭游标和数据库连接
cursor.close()
conn.close()

在这个创建数据表的例子中，在创建数据库服务连接时，我们通过 database=’test_db’ 这行代码指定参数连接了目标数据库，如果不想在创建数据库服务连接时指定数据库，可以在后续通过 cursor.execture(‘user test_db’)这种方法指定数据库。

从上面创建数据库和数据表的例子可以看出，pymysql 执行具体操作时都是先创建数据库服务连接，然后通过连接创建游标，以游标来执行具体的 sql 语句来完成具体的对数据库操作。不单单可以创建数据库、数据表，还可以创建索引、视图等等，方法也是一样的，这里不再介绍。

 3 增删改查

3.1 插入

插入操作可以通过游标的 execute 和 executemany 两个方法来完成。注意：只要是对数据表有修改的操作（插入、更新、删除）在使用 execute 方法后，都需要再次调用 commit 方法对数据库的修改才会最终生效。

execute 方法一次插入一条记录，executemany 一次插入多条记录：

（1）execute：一次插入一条记录

cursor.execute('insert into book(bookname, authors, year_publication) values("%s","%s", %s);' % ('Python 从入门到放弃 ', ' 乔布斯', 2019))
conn.commit()

上面这种写法是先用字符串利用 %s 占位生成一条完整的 sql 语句，然后去执行。其实，pymysql 的游标也自带这一功能，而且看起来更加方便，所以，我们还可以这样执行 execute 方法：

cursor.execute('insert into book(bookname, authors, year_publication) values(%s, %s, %s);', ('Python 从入门到放弃 ', ' 乔布斯', 2019))
conn.commit()

这种方法的好处是 sql 语句中的 %s 不需要加引号（如果加了引号，引号也会被当做数据插入到数据表中），游标执行这一语句时，会根据数据类型来判断是否要加上引号。

data = [('21 天完全入门 Java', '扎克伯格', 2018),
    ('Linux 学习手册 ', ' 李纳斯', 2017),
    ('MySQL 从删库到跑路 ', ' 比尔盖茨', 2018),
]
cursor.executemany('insert into book(bookname, authors, year_publication) values("%s","%s", %s);', data)
conn.commit()

3.2 更新

cursor.execute('update book set authors=%s where bookname=%s;', ["马云", "Python 从入门到放弃"])
conn.commit()

3.3 查询

查询是最最要但也是最复杂的一个操作了。我们分为 fetch 操作、游标两部分来说。

（1）fetch 操作

插入、更新、删除操作必须再执行 commit 操作之后才会生效，而查询操作也只有在执行 fetch 操作之后才会生效。fetch 操作包括 3 个方法，分别是 fetchone()、fetchall()、fetchmany()。

• fetchall()：查询指定的所有记录

cursor.execute('select * from book where bookid < %s;', [4])
books = cursor.fetchall()
print(books)

输出结果为：

((1, ‘Python 从入门到放弃 ’, ‘ 马云 ’, 2019), (2, ‘Python 从入门到放弃 ’, ‘ 马云 ’, 2019), (3, ’21 天完全入门 Java’, ‘ 扎克伯格 ’, 2018))

输出结果是以元组的形式来保存，且每一条记录也是一个元素，这是由游标 cursor 决定的，在下文中还会说到。

cursor.execute('select * from book where bookid < %s;', [4])
books = cursor.fetchmany(2)
print(books)

输出结果为：

((1, ‘Python 从入门到放弃 ’, ‘ 马云 ’, 2019), (2, ‘Python 从入门到放弃 ’, ‘ 马云 ’, 2019))

可以看出，fetchmany(size)是取出符合查询条件的最前面的指定数量记录。这里的 size 指的就是想要取出的记录条数。

cursor.execute('select * from book where bookid < %s;', [4])
books = cursor.fetchone()
print(books)

输出结果：

fetchone()相当于是 fetchmany(1)，取出第一条符合查询条件的记录。

（2）游标

我们之前使用游标都是采用默认的 Cursor 类型，除此以外，pymysql 还提供了 DictCursor、SSCursor、SSDictCursor 这几类游标。

之前使用的 Cursor 返回的数据是以元组的方式保存，而 DictCursor 是以字典的形式保存。创建这种类型的游标方法也很简单，在 conn.sursor()方法中传入 DictCursor 这个类即可：

cursor = conn.cursor(pymysql.cursors.DictCursor) # 创建一个字典游标
cursor.execute('select * from book where bookid < %s;', [3])
books = cursor.fetchall()
print(books)
cursor.execute('select * from book where bookid < %s;', [3])
book_one = cursor.fetchone()
print(book_one)

输出结果如下：

SSCursor 和 SSDictCursor 被称为流式游标，这类游标不会像上面使用的 Cursor 和 DictCursor 那样，一次性返回所有的数据，流式游标会陆陆续续一条一条得返回查询数据，所以这类游标适用于内存低、网络带宽小、数据量大的应用场景中。

流式游标的使用方法类似于迭代器，再循环中每取一条，生成一条：

cursor = conn.cursor(pymysql.cursors.SSCursor) # 创建一个流式游标
cursor.execute('select * from book;')
book = cursor.fetchone()
while book:
print(book)
book = cursor.fetchone()

输出结果如下：

SSCursor 与 SSDictCursor 的区别就类似于 Cursor 和 DictCursor 的区别，这里就不在介绍了。

3.4 删除

使用 pymysql 执行插入、更新、删除操作都是相似的，最后都需要 commit 提交：

cursor.execute('delete from book where bookid=%s;', [1])
conn.commit()

这时候，bookid 为 1 的记录已经被删除，如下图所示：

也可以使用 executemany()一次性删除多条：

cursor.executemany('delete from book where bookid=%s;', [[2], [4]])
conn.commit()

如下图所示，bookid 为 2 和 4 的记录已经被删除：

 4 总结

pymysql 还提供了事务机制，但我还有疑问没搞清楚，不敢往博客上面写：执行多个修改数据库操作（多个 execute 方法）时，其中有一个方法有异常，这是 commit，所有的所有都不会成功，那么为什么还要 rollback 呢？如果哪位前辈知道答案，请一定告知，感激不尽！

：