通过pymysql读取数据库中表格并保存到excel(实用篇)

本篇文章是通过pymysql将本地数据库中的指定表格保存到excel的操作。

这里我们假设本地已经安装了对应的数据库管理工具，里面有一个指定的表格，现在通过python程序，通过调用pymysql进行读取并保存到excel中。

通过pymysql读取数据库中表格并保存到excel(实用篇)-LMLPHP

关于数据库管理工具是Navicat Premium

我们所使用的数据库管理工具是Navicat Premium，它支持多种数据库系统，包括 MySQL, PostgreSQL, Oracle, SQLite, SQL Server, MariaDB 以及云数据库服务，例如 Amazon RDS, Amazon Aurora, Amazon Redshift, SQL Azure, Oracle Cloud 和 Google Cloud。Navicat Premium 提供了一个直观的用户界面使得数据库管理变得简单，即便是非专业人士也可以容易上手。

通过这款软件，用户可以进行各种数据库相关操作，如设置和维护数据库、进行数据迁移、备份和还原、建立和运行 SQL 查询、监控数据库的健康状况、优化数据库性能等。除了基本的数据编辑和浏览功能，Navicat Premium 还具备高级特性，比如数据传输、数据同步、结构同步、报告生成工具等。

读取数据库并保存

前期准备，自然我们是要在有一个类似于Navicat Premium 的数据库管理工具，并在里面建一个数据库，数据库中有一个名为 book_type的表。
该表中的内容如下：
通过pymysql读取数据库中表格并保存到excel(实用篇)-LMLPHP

现在我们通过pymysql来读取该表的信息。这里我们先用程序进行显示：

import openpyxl
import pymysql

# 从数据库某个表中取出所有记录
# 参数host指定数据库服务器的IP地址，参数db_name指定数据库的名字，
# 参数table_name指定表的名字
# 参数user指定数据库的登录用户名，参数passwd指定登录用户的密码
def get_data(host, db_name, table_name, user, passwd):
    # 生成一个数据库的连接
    conn = pymysql.connect(host=host, port=3306, database=db_name, user=user, passwd=passwd)
    # 建立一个游标
    cur = conn.cursor()
    # 组合一个SQL查询语句
    sql = 'select * from ' + table_name
    # 执行SQL语句
    cur.execute(sql)
    # rows取得记录，cur.fetchall()返回所有符合条件的记录
    rows = cur.fetchall()
    # cur.description返回数据表的字段信息，
    # 返回值fields是一个元组，其中的每一项元素也是一个元组（子元组），
    # 这个子元组的第一个元素是字段名
    fields = cur.description
    # 关闭游标
    cur.close()
    # 断开连接
    conn.close()
    return fields, rows


# 将表的记录导入到Excel中的函数
# 参数host指定数据库服务器的IP地址，参数db_name指定数据库的名字，
# 参数table_name指定表的名字，参数user指定数据库的登录用户名，
# 参数passwd指定登录用户的密码，参数filename指定导入的Excel文件名
def export_to_excel(fields, table_rows, filename):
    # 调用函数，取得数据表的字段信息和记录信息
    # 生成Excel文件的工作簿
    workbook = openpyxl.Workbook()
    # 在工作簿中生成一个工作表，表名设为"table_"加数据表名
    sheet = workbook.create_sheet('table_' + table_name, 0)
    # 在工作表第1行上写上字段名
    for i in range(0, len(fields)):
        # 在openpyxl模块中定义工作表的行始值是1，列起始值是1，
        # 所以cell()函数第1个参数是1表示第1行，第二参数为i+1是因为i从0开始计数,
        # fields[i][0]取得字段的名称
        sheet.cell(1, i + 1, fields[i][0])
    # 从工作表第2行开始写入每条记录的内容
    for row in range(0, len(table_rows)):
        for col in range(0, len(fields)):
            sheet.cell(row + 2, col + 1, '%s' % table_rows[row][col])
    # 保存到Excel文件中
    workbook.save(filename)


# 主函数main
if __name__ == '__main__':
    # 初始化各变量值
    host = 'localhost'# 数据库服务器的IP地址
    db_name = 'book_manager' # 数据库的名字
    table_name = 'book_type' # 指定数据库中表的名字
    user = 'root' # 用户名
    password = '123456' # 密码
    # 调用函数，将数据表的内容导入到一个Excel文件中
    fields, table_rows = get_data(host, db_name, table_name, user, password)
    print("fields", fields)
    print("table_rows", table_rows)
    export_to_excel(fields, table_rows, './book_type.xlsx')

运行结果展示：

fields (('bookTypeId', 3, None, 11, 11, 0, False), ('bookTypeName', 253, None, 80, 80, 0, False), ('bookTypeDesc', 253, None, 1020, 1020, 0, False))
table_rows ((1, '计算机科学', '计算机相关'), (2, '历史', '历史相关'), (3, '文学', '文学相关'), (4, '科幻', '科幻相关'), (6, '小说', '小说相关'), (7, '外语', '外语相关'))

我们打开生成的book_type.xlsx文件，里面内容如下：
通过pymysql读取数据库中表格并保存到excel(实用篇)-LMLPHP
从结果上和之前指定数据库中的表里面的内容一样，可见成功。

关于pymysql的基础操作及其代码展示在博主另外一篇博客：pymysql进行数据库各项基础操作

Dream_Bri