Python实现数据库与Excel文件之间的数据自动化导入与导出_Python

引言

数据库和excel文件是两种常见且重要的数据存储方式。数据库通常用于大规模数据的高效存储、管理和查询，而excel则以其直观的界面和简单的操作方式广泛应用于数据分析、报告生成和可视化等领域。在实际工作中，可能需要在这两者之间进行数据的导入与导出。例如，从数据库中提取数据到excel进行深入分析和图表绘制，或者将excel文件中的数据清洗整理后导入数据库进行集中管理和进一步处理。而python因其便捷性和丰富的生态，能够帮助用户通过简单的代码处理数据库与excel文件之间数据转换。

本文将介绍如何使用python有效地实现数据库与excel文件之间的数据自动化导入与导出，以sqlite数据库为例。

本文所使用的方法需要用到sqlite3（python标准库中的组件）和spire.xls for python（pypi: pip install spire.xls）。

用python将数据库数据导出到excel表格

我们可以使用 sqlite3 模块从数据库读取数据，并利用 spire.xls 模块创建 excel 文件并将数据写入其中，从而实现数据库数据的导出。详细步骤如下：

导入必要的模块。
连接数据库：使用 sqlite3.connect() 连接 sqlite 数据库，并创建游标 cursor 执行 sql 命令。
获取表名：执行 sql 查询获取数据库中所有表的名称，并存储在 tablenames 列表中。
创建 excel 工作簿：初始化一个 workbook 对象，并使用 workbook.worksheets.clear() 方法清除默认工作表。
遍历数据库表：对于 tablenames 中的每一个表名：
- 查询表的列信息，提取列名并添加到 columnnames 列表中。
- 获取表中的所有数据行到 rows 中。
- 使用 workbook.worksheets.add(sheetname) 方法在 excel 中添加一个以表名命名的新工作表。
- 使用 worksheet.range[row, col].value 属性将 columnnames 作为标题写入工作表。
- 遍历数据行，并使用相同的属性将数据写入对应的单元格。
- 格式化工作表。
使用 workbook.savetofile() 方法将工作簿保存到文件。
释放 workbook 的资源并关闭数据库连接。

代码示例

from spire.xls import *
from spire.xls.common import *
import sqlite3

# 连接到数据库
conn = sqlite3.connect("output/companyinfo.db")
cursor = conn.cursor()

# 获取数据库中所有的表名
cursor.execute("select name from sqlite_master where type='table';")
tablenames = [name[0] for name in cursor.fetchall()]

# 创建 excel 文件
workbook = workbook()
workbook.worksheets.clear()

# 遍历数据库中的每个表
for tablename in tablenames:
    # 获取表的列名
    cursor.execute(f"pragma table_info('{tablename}')")
    columnsinfo = cursor.fetchall()
    columnnames = [columninfo[1] for columninfo in columnsinfo]

    # 获取表的数据
    cursor.execute(f"select * from {tablename}")
    rows = cursor.fetchall()
    
    # 创建工作表
    sheet = workbook.worksheets.add(tablename)
    
    # 将标题行写入工作表
    for i in range(len(columnnames)):
        sheet.range[1, i + 1].value = columnnames[i]
    
    # 将数据写入工作表
    for j in range(1, len(rows)):
        column = rows[j]
        for k in range(len(column)):
            sheet.range[j + 1, k + 1].value = column[k]
    
    # 设置工作表格式
    sheet.allocatedrange.style.font.fontname = "times new roman"
    sheet.allocatedrange.style.font.size = 12.0
    sheet.allocatedrange.autofitrows()
    sheet.allocatedrange.autofitcolumns()

# 保存 excel 文件
workbook.savetofile("output/databasetoexcel.xlsx", fileformat.version2016)
workbook.dispose()
conn.close()

结果

用python将excel表格数据导入到数据库

我们也可以使用 spire.xls 从 excel 文件读取各种数据类型，然后使用 sqlite3 将数据写入数据库。详细步骤如下：

导入必要的模块。
创建 workbook 实例：初始化一个 workbook 对象以操作 excel 工作簿。
加载 excel 文件：使用 loadfromfile 方法从指定路径加载 excel 文件。
连接数据库：使用 sqlite3.connect() 连接 sqlite 数据库，并创建游标 cursor 执行 sql 命令。
遍历工作表：对于工作簿中的每个工作表：
- 使用 worksheet.name 属性获取工作表对象和名称，并去除名称中的空格。
- 提取标题：使用 worksheet.range[row, col].value 属性收集第一行数据作为数据库表的列名，并去除空格。
- 创建数据库表：基于提取的标题动态生成 sql 语句以创建表（如果不存在）。
- 插入数据：遍历工作表的每一行，使用 worksheet.range[row, col].value 属性收集数据，然后构造 sql 插入语句将数据插入相应的数据库表。
提交并关闭数据库连接：将所有更改提交到数据库并关闭数据库连接。
释放 workbook 资源：清理 workbook 对象所使用的资源。

代码示例

from spire.xls import *
from spire.xls.common import *
import sqlite3

# 创建 workbook 实例
workbook = workbook()

# 加载 excel 文件
workbook.loadfromfile("sample.xlsx")

# 连接到数据库
conn = sqlite3.connect("output/exceltodatabase.db")
cursor = conn.cursor()

for s in range(workbook.worksheets.count):
    # 获取一个工作表
    sheet = workbook.worksheets.get_item(s)

    # 获取工作表名称
    sheetname = sheet.name
    sheetname = sheetname.replace(" ", "")

    # 获取标题行中的数据
    header = []
    for i in range(sheet.allocatedrange.columncount):
        headervalue = sheet.range[1, i + 1].value
        headervalue = headervalue.replace(" ", "")
        header.append(headervalue)

    # 创建数据库表
    createtablesql = f"create table if not exists {sheetname} ({', '.join([f'{header[i]} text' for i in range(len(header))])})"
    cursor.execute(createtablesql)

    # 插入数据到数据库表中
    for row in range(1, sheet.allocatedrange.rowcount):
        data = []
        for col in range(sheet.allocatedrange.columncount):
            # 获取单元格值
            value = sheet.range[row + 1, col + 1].value
            data.append(value)
        # 插入单元格值到数据库表中
        insertsql = f"insert into {sheetname} ({', '.join(header)}) values ({', '.join(['?' for _ in data])})"
        cursor.execute(insertsql, data)

# 提交更改并关闭连接
conn.commit()
conn.close()

workbook.dispose()

结果