Python3 中，我们可以使用mysqlclient或者pymysql三方库来接入 MySQL 数据库并实现数据持久化操作。二者的用法完全相同，只是导入的模块名不一样。我们推荐大家使用纯 Python 的三方库pymysql，因为它更容易安装成功。

接入MySQL

在命令行或者 PyCharm 的终端中通过下面的命令安装pymysql，如果需要接入 MySQL 8，还需要安装一个名为cryptography的三方库来支持 MySQL 8 的密码认证方式。

  1. pip install pymysql cryptography

步骤

使用pymysql操作 MySQL 的步骤如下所示：

  1. 创建连接。MySQL 服务器启动后，提供了基于 TCP （传输控制协议）的网络服务。我们可以通过pymysql模块的connect函数连接 MySQL 服务器。在调用connect函数时，需要指定主机（host）、端口（port）、用户名（user）、口令（password）、数据库（database）、字符集（charset）等参数，该函数会返回一个Connection对象。
  2. 获取游标。连接 MySQL 服务器成功后，接下来要做的就是向数据库服务器发送 SQL 语句，MySQL 会执行接收到的 SQL 并将执行结果通过网络返回。要实现这项操作，需要先通过连接对象的cursor方法获取游标（Cursor）对象。
  3. 发出 SQL。通过游标对象的execute方法，我们可以向数据库发出 SQL 语句。
  4. 如果执行insert、delete或update操作，需要根据实际情况提交或回滚事务。因为创建连接时，默认开启了事务环境，在操作完成后，需要使用连接对象的commit或rollback方法，实现事务的提交或回滚，rollback方法通常会放在异常捕获代码块except中。如果执行select操作，需要通过游标对象抓取查询的结果，对应的方法有三个，分别是：fetchone、fetchmany和fetchall。其中fetchone方法会抓取到一条记录，并以元组或字典的方式返回；fetchmany和fetchall方法会抓取到多条记录，以嵌套元组或列表装字典的方式返回。
  5. 关闭连接。在完成持久化操作后，请不要忘记关闭连接，释放外部资源。我们通常会在finally代码块中使用连接对象的close方法来关闭连接。

示例

建库

  1. CREATE TABLE `book` (
  2.   `id` bigint NOT NULL AUTO_INCREMENT,
  3.   `name` varchar(50) NOT NULL COMMENT '图书名称',
  4.   `author` varchar(10) DEFAULT NULL COMMENT '作者',
  5.   `price` decimal(10,0) DEFAULT NULL COMMENT '价格',
  6.   `publish_date` datetime DEFAULT NULL COMMENT '上架日期',
  7.   PRIMARY KEY (`id`)
  8. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;

insert 插入数据

  1. """
  2. 插入数据
  3. """
  4. import pymysql
  5. import datetime
  7. name = input("请输入书名：")
  8. auth = input("请输入作者：")
  9. price = float(input("请输入价格："))
  10. nowTime = datetime.datetime.now()
  12. # 1. 创建连接（Connection）
  13. conn = pymysql.connect(host='127.0.0.1', port=3306,
  14.                        user='root', password='cj123456789',
  15.                        database='test', charset='utf8mb4')
  17. try:
  18.     # 2. 获取游标对象（Cursor）
  19.     with conn.cursor() as cursor:
  20.         # 3. 通过游标对象向数据库服务器发出SQL语句
  21.         affected_rows = cursor.execute(
  22.             'INSERT INTO book (name,author,price,publish_date) VALUES (%s, %s, %s, %s)',
  23.             (name, auth, price, nowTime)
  24.         )
  25.         if affected_rows == 1:
  26.             print('添加图书成功!!!')
  27.     # 4. 提交事务（transaction）
  28.     conn.commit()
  29. except pymysql.MySQLError as err:
  30.     # 4. 回滚事务
  31.     conn.rollback()
  32.     print(type(err), err)
  33. finally:
  34.     # 5. 关闭连接释放资源
  35.     conn.close()

如果要插入大量数据，建议使用游标对象的executemany方法做批处理（一个insert操作后面跟上多组数据），大家可以尝试向一张表插入10000条记录，然后看看不使用批处理一条条的插入和使用批处理有什么差别。游标对象的executemany方法第一个参数仍然是 SQL 语句，第二个参数可以是包含多组数据的列表或元组。

delete 删除数据

  1. """
  2. 删除数据
  3. """
  4. import pymysql
  6. id = int(input('输入图书id: '))
  8. # 1. 创建连接（Connection）
  9. conn = pymysql.connect(host='127.0.0.1', port=3306,
  10.                        user='root', password='cj123456789',
  11.                        database='test', charset='utf8mb4')
  12. try:
  13.     # 2. 获取游标对象（Cursor）
  14.     with conn.cursor() as cursor:
  15.         # 3. 通过游标对象向数据库服务器发出SQL语句
  16.         affected_rows = cursor.execute(
  17.             'delete from book where `id`= %s',
  18.             (id)
  19.         )
  20.         if affected_rows == 1:
  21.             print('删除图书成功!!!')
  22.         # 4. 提交事务
  23.         conn.commit()
  24. finally:
  25.     # 5. 关闭连接释放资源
  26.     conn.close()

说明：如果不希望每次 SQL 操作之后手动提交或回滚事务，可以connect函数中加一个名为autocommit的参数并将它的值设置为True，表示每次执行 SQL 成功后自动提交。但是我们建议大家手动提交或回滚，这样可以根据实际业务需要来构造事务环境。如果不愿意捕获异常并进行处理，可以在try代码块后直接跟finally块，省略except意味着发生异常时，代码会直接崩溃并将异常栈显示在终端中。

update 修改数据

  1. """
  2. 修改数据
  3. """
  4. import pymysql
  6. id = input('请输入图书ID: ')
  7. name = input('请输入书名: ')
  8. author = input('请输入作者: ')
  9. price = float(input('请输入价格: '))
  11. # 1. 创建连接（Connection）
  12. conn = pymysql.connect(host='127.0.0.1', port=3306,
  13.                        user='root', password='cj123456789',
  14.                        database='test', charset='utf8mb4')
  15. try:
  16.     # 2. 获取游标对象（Cursor）
  17.     with conn.cursor() as cursor:
  18.         # 3. 通过游标对象向数据库服务器发出SQL语句
  19.         affected_rows = cursor.execute(
  20.             'update book set `name`=%s, `author`=%s, price = %s where `id`=%s',
  21.             (name, author, price, id)
  22.         )
  23.         if affected_rows == 1:
  24.             print('图书信息更新成功!!!')
  25.     # 4. 提交事务
  26.     conn.commit()
  27. except pymysql.MySQLError as err:
  28.     # 4. 回滚事务
  29.     conn.rollback()
  30.     print(type(err), err)
  31. finally:
  32.     # 5. 关闭连接释放资源
  33.     conn.close()

select 查询数据

  1. """
  2. 查询数据
  3. """
  4. import pymysql
  6. # 1. 创建连接（Connection）
  7. conn = pymysql.connect(host='127.0.0.1', port=3306,
  8.                        user='root', password='cj123456789',
  9.                        database='test', charset='utf8mb4')
  10. try:
  11.     # 2. 获取游标对象（Cursor）
  12.     with conn.cursor() as cursor:
  13.         # 3. 通过游标对象向数据库服务器发出SQL语句
  14.         cursor.execute('select name,author,price from book')
  15.         # 4. 通过游标对象抓取数据
  16.         row = cursor.fetchone()
  17.         while row:
  18.             print(row)
  19.             row = cursor.fetchone()
  20. except pymysql.MySQLError as err:
  21.     print(type(err), err)
  22. finally:
  23.     # 5. 关闭连接释放资源
  24.     conn.close()

说明：上面的代码中，我们通过构造一个while循环实现了逐行抓取查询结果的操作。这种方式特别适合查询结果有非常多行的场景。因为如果使用fetchall一次性将所有记录抓取到一个嵌套元组中，会造成非常大的内存开销，这在很多场景下并不是一个好主意。如果不愿意使用while循环，还可以考虑使用iter函数构造一个迭代器来逐行抓取数据，有兴趣的读者可以自行研究。

分页查询

  1. """
  2. 分页查询
  3. """
  5. import pymysql
  7. page = int(input('页码: '))
  8. size = int(input('大小: '))
  10. # 1. 创建连接（Connection）
  11. conn = pymysql.connect(host='127.0.0.1', port=3306,
  12.                        user='root', password='cj123456789',
  13.                        database='test', charset='utf8mb4')
  14. try:
  15.     # 2. 获取游标对象（Cursor）
  16.     with conn.cursor(pymysql.cursors.DictCursor) as cursor:
  17.         # 3. 通过游标对象向数据库服务器发出SQL语句
  18.         cursor.execute(
  19.             'select name,author,price from book limit %s,%s',
  20.             ((page - 1) * size, size)
  21.         )
  22.         # 4. 通过游标对象抓取数据
  23.         for emp_dict in cursor.fetchall():
  24.             print(emp_dict)
  25. finally:
  26.     # 5. 关闭连接释放资源
  27.     conn.close()

数据库数据导出到Excel

  1. import openpyxl
  2. import pymysql
  4. # 创建工作簿对象
  5. workbook = openpyxl.Workbook()
  6. # 获得默认的工作表
  7. sheet = workbook.active
  8. # 修改工作表的标题
  9. sheet.title = '员工基本信息'
  10. # 给工作表添加表头
  11. sheet.append(('ID','书名', '作者', '价格', '日期'))
  13. # 1. 创建连接（Connection）
  14. conn = pymysql.connect(host='127.0.0.1', port=3306,
  15.                        user='root', password='cj123456789',
  16.                        database='test', charset='utf8mb4')
  17. try:
  18.     # 获取游标对象（Cursor）
  19.     with conn.cursor() as cursor:
  20.         # 通过游标对象执行SQL语句
  21.         cursor.execute(
  22.             'select id,name,author,price,publish_date from book'
  23.         )
  24.         # 通过游标抓取数据
  25.         row = cursor.fetchone()
  26.         while row:
  27.             # 将数据逐行写入工作表中
  28.             sheet.append(row)
  29.             row = cursor.fetchone()
  30.     # 保存工作簿
  31.     workbook.save('books.xlsx')
  32. except pymysql.MySQLError as err:
  33.     print(err)
  34. finally:
  35.     # 关闭连接释放资源
  36.     conn.close()